 12. Welcome, everybody, to the last day of TAPConf. My talk is about Postgres, QL, and Debian. Who doesn't know what Postgres is? Who knows Postgres? Okay, good. I know it's a database system. So I'm giving this talk together. I put up Chris Berk as a co-author because he wrote some of the slides. I took quite a bit, like, two or three slides from his presentation at Heidelberg. And also because he's one of the, if you see here, that's him that's uploaders from the Package Postgres QL team. So he's doing a lot of work in the last couple of years being the most prominent maintainer of Postgres and Postgres-related packages. So Package Postgres QL is the team that maintains the Postgres database server, but also quite a lot of extensions and associated projects. So Chris Berk is a colleague of mine. And Adrian von Entreech is another colleague of mine who has been doing quite a few work last year and this year. And Michael Meskis is boss of mine, who's also been doing a couple of uploads. And so I actually have only uploaded two packages for now. But I'm doing a lot of work with Postgres during my day job at Creative. We are supporting Postgres and doing consulting on it. That's the reason why Chris is credited as a co-author. So I first want to give a small overview of what Postgres is and what's new in Postgres since the Jesse release, so now that stretch is released, what happens between Jesse and Stretch. And then I want to give a short overview about how the Postgres database server packages are structured and you can use them as a user, but then also how you can actually develop or package Postgres QL extensions for Debian. And if there is enough time, there is a slight product thing that we did at Creative, which I might be able to show at the end. We'll see. So from an overview perspective, Postgres is an extensional object relational database system. Extensible means the user can define new data types and even operators between the data types or even new indexes since the last release. So it can be extended by the user by creating extensions. There is hundreds of extensions for all types of data types, IPv4 addresses, geographical units, everything. It was created as an academic project, the successor to Ingress at Berkeley and it got open sourced in the 90s. First as Postgres 95 and when they decided to add the SQL language, which wasn't the case originally, they decided to call it Postgres QL and now they regret it because it's a rather unwirly name. So you're fine to call it Postgres. Just don't call it Postgre or something like that. Postgres is fine. What is important compared to other popular free software database systems? It's vendor neutral. It's still commercial support there from quite a few companies, including us. But there is no vendor actually keeping tap on the whole thing, right? And there's this Postgres global development group, which is kind of not a very legal organization. It's just the group of, like the Debian project, basically, I would say, of people working on Postgres and the copyright of the Postgres database server is at that group, which I don't believe is a legal entity, so it's a bit shaky, but still. And there's a five-member core team, and they have a release team now, which is kind of important because they didn't have in the last couple of years, but for the last two releases, they have a release team which keeps tabs on open items, and that's working pretty well so far. And they're on a yearly release cycle, but it's kind of like Debian, in a sense. It's a free space. So the freeze is always in February or March when they release the beta, and then over summer they stabilize. And then it takes longer or lesser until the final release is done, and there was some problems that it took until the next year, so they decided to have this release team. Now they have it. It will probably be, there was just a beta three announced for the next version, and it will probably happen in September or October. What's also important is that there used to be the major-minor version thing, where the major version was 9.3, 9.4, 9.5, 9.6, and now they switch to 10 as the next major version, and then it will be 11 afterwards, so they drop the third digit. So there will be 10, and the first patch release will be 10.0, or the initial release, the first patch release will be 10.1, 10.2, and so on. So they maintain upstream all branches, all releases for five years, and they have quarterly patch releases, which are very easy to figure out. There was one just two days ago, and it's every three months on the last, no, this time probably Thursday of the quarter. So we just had one, there will be another one in autumn, and then another one in winter. What's also important is that those patch releases are strictly bug-fix releases, no new features. They try to really be careful about not adding any regressions in it, and so they're basically whitelisted by the stable release team in the sense that we just upload the patch releases. We don't backport specific fixes, we just upload the patch release to Debian Stable, and there's actually usually quite a few bug fixes, like 20 maybe. There's also no copyright assignments. I already said it's a Postgres Global Development Group, but you just, so you're not allowed to assert your copyright, but there's also no copyright assignments, you don't give it up. There's certainly no open core in the sense that the main system is BSD licensed, and there's no open core. There might be some companies who have proprietary software or Fox on it that's fine with everybody there, because it's a BSD license, and they're very BSD centric, so they're probably fine with it, but in general it's a BSD license, no open core, no dual licensing, nothing, when they're neutral, that's the main point. From a legal point of view or a community point, if you have what to say. Now from the feature point of view, most people consider Postgres from the beginning or mostly from the beginning. They stabilize very quickly in the 90s to be rock solid, so it's probably not going to eat your data. They have been some rare bugs mostly during a replication which has been added eight, nine years ago, but they have been shaken out. It has a good consistent coverage of the SQL standard. There's no, a lot of gotchas like, why, why is it doing this, this way, and the next command does it that way, and there's a useful modern additions to it, which might now also be an SQL standard, but it's pretty good SQL. If you like writing SQL, Postgres is usually a good good database and you would like doing it. As I already said, there's a large number of extensions and associated projects in the hundreds these years. The extension system got integrated actually with Weezy with 9.1, and since then a lot of extensions have shown up, and it's very easy to create an extension database. Also what's important for some people, maybe not, it's transactional changes to the database system, so you can roll back schema changes if they don't, if there's a problem, and you have several schema changes, and one thing that makes an error, you can roll back the whole thing. You don't have to start over or like tidy up yourself afterwards. You don't get to keep both case pieces. And there's many drivers for programming languages. There's many different procedural languages, so you can write it in all kinds of languages, R, Java, Node, whatever. And the final thing that I want to stress, maybe that's also because it's rather recent, so you might not know, is that there's also a lot of foreign data wrappers it's called for federated access to other databases, or Postgres itself, which is also a major use case, so you can say, okay, this is a foreign server, and it has these and these tables, and then you just can query them locally, and this can be an Oracle database, this can be MySQL database, or it can be a whatever database, and you can even run write queries on it, and it's getting better and better, and certainly the Postgres one is pretty advanced, so you can do advanced things, and they start building, shouting, and these kinds of projects on top of it. And finally, it's Package and Davion. I get to the Package and Davion in a bit, as I said, 9.1 was Weezy, 9.4, Jesse, and 9.6 is going to be stretched, so just a quick rundown of the changes that happen since Jesse, if you haven't looked at it yet, so in 9.5, there's one thing which a lot of developers love is absurd, it's called insert on conflict update ignore, and what's also, I think, important is row-level security, so you can tell the database, okay, only show this row to a user if he matches a certain criteria he or she. There's block range indexes, I don't want to go through all this, but if you're interested, you can ask questions and I'm trying to answer them. Analyze the queries, rewinding old masters, setting unlocked or locked, so not replicated tables for performance, and you can import whole schemas now from other databases via foreign data wrappers. You just import the schema, you don't have to set up the same tables, make sure it's the same tables on your local as on the remote server, and it's always improving in scalability, actually, so since 9.1 or 9.2, the Postgres developers have made sure that more and more multi-core systems can be used for single queries, so that means that you can have lots of queries in parallel and they use all the cores, and since 9.6, you can also use parallel queries, so in stretch, you're able to run a query and it will run, if there's certain requirements met, it will run in parallel on a number of cores. That's mostly useful for sequential scans, but it will be improved in the next version and there's quite a huge improvement in some benchmarks to that. It still has to be worked on, it's an incremental thing, but it's there and it's going to be only better. Also, Wacom has been improved dramatically, so there's Wacom-freeze problems that some people had when there's a wraparound of the transaction counter, and basically everything stops because Postgres has the Wacom that has been vastly improved in 9.6. You can do consistent reads from standby, so you always know if you write something on a master and immediately read it from the standby, it will be the same thing with some performance penalty, but that's important if you do load balancing on reading. And yeah, as I said, the Postgres foreign data wrapper interface gets better, improved scalability. So I just want to quickly run out because that's maybe the least you know that what's going to happen in 10, which is the next major release, which will happen in, as I said, autumn. So the main things are it will have logical replication finally, so until now that was physical replication, which meant you have to replicate the whole cluster block by block basically, and that implies that you cannot replicate just one database out of several or just a couple of tables. And it also implies that you always have to have the same version of the operating system in Postgres, so you cannot just replicate from 9.1 to 9.4 or from Jesse to stretch in order to upgrade because it has to be the same version. So with logical replication, you're able to replicate to another Postgres server which runs a different version. And this will be hopefully one of the things which makes people first possible to do major upgrades much easier, but also there's lots of other use cases in order to just capture some data for changes and something like stream changes. There's lots of things people will be able to work with that. I mean, the infrastructure has been there since 9.4, so there are third-party external extensions, which I also will get to later for the logical replication, but this will be in core now. It will be, you can run create publication, blah, blah, blah. And also there will be extended parallel query. I already said that, so now indexes are also in parallel. And there will be native partitioning, which might also be important for some people who have huge databases. So they will be able to partition them natively, and there will be some things. It will also be improved in the next couple of years, but this is also the beginning of native partitioning. I will not go through really the other ones, because I have a couple of more slides I want to show on other things, but those are the major features in 10. Just a few extensions, which are also new, which I looked up, and this is alphabetically. Cytus is basically, they used to be a proprietary web-scale sharding thing, and now they decided that the extension architecture of Postgres is so great that they can unfork. So now it's just an extension, and they also open source it at the same time. So you have several Postgres clusters. You create the Cytus extension, and then you have sharding and distributed joins on top of it, which is pretty powerful thing to have. PG Partman now a bit not, I mean it's still useful, but now that we have partitioning maybe not so much, but it's in partition manager, which does the lifting, which you could do before with lots of functions and inheritance and whatever, you could implement partitioning before, and PG Partman is helping with that for a lot. But as I said, now there is native partitioning. Will a repack reorganize your tables if they become bloated on the fly, so you don't have to recreate the table with locks and anything? PG Backrest is a heavy duty backup and restore thing like Barman, which is quite popular these days. And I just want to shout out Esquire Smith, because a colleague of mine who wrote it, and he found several dozen bugs in Postgres due to it. He's just randomly generating SQL queries and basically fuzzing the parser and then extensions, so he found quite a few bugs with that, and that was also very helpful for quality assurance of the Postgres server. And not in stretch, but there since stretch is PG logical, that's the external logical replication solution I was talking about. PG audit, which is an auditing extension, so you can say, okay, I want to lock when somebody touches this table, but only if they touch the password column on it, and I want to have that locked, or things like that. And finally, this background worker, Rappel status is a small project, but might be useful. It just tells you via HTTP port, is this a standby or a primary, so it's useful for tooling of replicated systems. So that's basically it for the part, but what's new? Now, how is Postgres in Demian? That's the next thing I want to talk about. And the plan is simple. There's Demian, there's Postgres, package it done. But the problem with that is we don't have just Demian, we don't have just Postgres. There's SIT, Stretch, JSC, Weezy, and we have five, as I said, every Postgres major release is supported for five years, so basically there's five different versions in support. And people, I mean, some people are using Postgres as a backend for a small thing, and they don't mind if it's going down during upgrade or not, but some people are using it for their business critical applications and just upgrading Postgres with a Demian upgrade, and then suddenly there is some breakage, and everything's down and you have to fix it. It's not what they want. So they might want to stick with 9.4 in Stretch for a while until they figured out, okay, this is how we did it, this is okay, now we can upgrade. Or they have some legacy vendor applications which still run on 9.1 and the vendor says, no, no, no, you cannot upgrade it, but they really want to upgrade at least operating system. So there's these problems. And the main, I mean, this is written here, so the underlying reason you cannot just easily upgrade is on this format changes. So you always have to do something for an upgrade. It's either a dump restore, you can try now to do an in-place upgrade which still has some downtime but works most of the time and it's rather quick, or you can use one of the logical replication solutions by like replicating everything to another cluster and then changing the application to look at the new cluster. So as I said, you need both versions and it takes a lot of disk space. So what the PostgreSQL maintainers came up with is the so-called PostgreSQL common framework, yeah, sorry. When you say major release is not from 9 to 10 but from 9.1 to 9.2? Exactly, yeah. So major releases used to be, it's always used to be 9.1, 9.2, 9.3, those are all major releases and 10 is also a major release. So from 10 on it will be 10, 11, 12, but those are the major releases. So 9.1, 12, that's the patch level. So from 9.1, so to make clear, if you're at 9.1, 12 and PostgreSQL or somebody else releases 9.1, 13, that's just a restart. So you have to restart your, you install the new packages and you restart. That's also a bit of a downtime. Everybody has to stop using database server. You can go around with bouncers and things to have that more useful but still that's just a restart. You don't have to dump your 10 terabyte database for one hour and then relay it for three hours and have lots of downtime. It's just a restart. But for major release you have to do something. So that's why we have PostgreSQL common which makes all these Postgres servers and versions co-installable. And they're creating, it's basically a wrapper around the Postgres, upstream Postgres commands and they're creating database clusters. And that's cool but still we have the problem that in Debian we only support one major version per Debian release. So we have 9.6 in stretch and 9.4 in Weezy. And maybe as I said users might want to have several versions for testing or they still need an old version and that kind of stuff. So what's there in addition for users and what Christoph worked a lot on is the app.postgresql.org package repository. There's one for RPMs as well. I'm only talking about app. So it's basically organized and architecture by Christoph as part of the package Postgresql Debian team but it's run by upstream Postgres development global development group in their namespace. And it's an app repository and we are rebuilding all Postgres servers and extension packages in all their versions for all Debian and some Ubuntu versions. So right now we have 126 targets. We support seven Debian and Ubuntu releases on three architectures. So we kind of recently added PowerPC 64. And for all the five to six Postgres releases. So 10 is kind of better right now but it's still there if you want to test it. You can already do it there. I think 10 is in experimental as far as Debian is concerned. So we have around 160 source package we build and this is done with Debian Jenkins Clue. And just to give you an idea is I believe that's the number of packages. So it's okay. Now I'm getting I think that's the number of files actually 18,000 in the in the archive these days. And the other ones are you have to look at the left one number of Debian file names is around 900. And that's probably the number of source packages. So it's almost 200 like 180. So it's increasing steadily and we're adding new versions and new things and there is a lot of things. So if you want to use as a as a user you want to use any version go to app postgresquial.org. There is a wiki there explaining which app sources list you have to add and then you can just go. And those are all tested which I will get to a bit later but usually we run all the the integration tests. So there is a pretty good chance that they're working. I mean we obviously don't test all these 1000 packages manually but they're automatically tested via Jenkins. So and when we fix the test you'd fail us. So that's for if you want to run any kind of version or anything. But there's also the long term support thing. So what if you what if you want to run for some reason you're still stuck or used to be still stuck on squeeze and you still need Postgres. So that's the other thing and the main the main point is that upstream support ended roughly when squeeze LTS began. So there was no more when squeeze happened or when squeeze LTS happened. So there was no more upstream support. They also stopped it because it was five years old. And what what we did at Credit Chief we decided to keep the an LTS branch of Postgres 8.4 maintained and backport all those patches which are applicable like most some of them are quite a few weren't applicable but somewhere in order to to support or contribute to the squeeze LTS effort. So that was we backported around 250 commits and only a few of them were security commits but we did that and we we released six upstream versions LTS 8.4 22 which was the last patch release LTS 1 to LTS 6. So that's that's here on that GitHub repository. You can still there's an PG 8.4 underscore LTS branch and yeah we stopped we stopped doing it when squeeze LTS stopped was end of life a while ago. But but we did all their work on the other hand I have to say that we didn't really get a lot of feedback so nobody was like oh great your support you're backporting all these little bug fixes is super great. So we did the work as a proof of concept but we decided for Weezy it's probably not all that worth it. So for Weezy which ships 911 and upstream support also ended last September we decided to only backport the security fixes for now. I mean if somebody's really interested in that on on a they have a business and their business depends on it or something please come talk to me but for now we decided as on a community basis to only do that for security uploads. So the first security upload in in January sorry the first patch release in January didn't have any security uploads and the second one in May we fixed now so it's now fixed it took us a while but we caught up and actually we released the DLA-1051 during DebConf so now it's as I said there was just a patch release on Thursday and those security fixes are now also in Weezy LTS. That's the LTS part I wanted to talk about. Now a bit more about how the package is actually structured how is PostgreSQL com actually structured. I already mentioned that there is multiple major versions that you can install and we call in PostgreSQL that's a bit of a problem but you call an instance is the same name as a cluster. These days clusters are usually considered something which is a replicated system where the primary is standby but cluster in this context means an instance and we identify them by name and version and every new instance gets an incremental part starting from five four three two so I'm not sure this will work very well but I can try to maybe give a small demo okay that seems to be whatever then let's skip that so what's important to know is that the server packages are called PostgreSQL-version like 9.1, 9.4 and these are the basically the directories where you should look for stuff so the database files are in while at PostgreSQL which it's also configurable but that's the default and the log files are in var log and the configuration files are in ETC PostgreSQL so it's kind of the Debian standard. I think the RPM packages are doing a bit differently because upstream likes to keep the configuration in the data directory but Debian removes them into into ETC and they're in a versioned version directory and in there you have PostgreSQL Conf which is the main Postgres server configuration file and you have the PG HPA Conf which configures which users are allowed to connect to Postgres from which addresses or sockets to which databases and PG IDENT Conf which is for IDENT mapping which is not used a lot these days but you also have PG Control Conf and Start Conf which are Debian specific so in those you are able to add additional options about what to do when PG Control which is the main start-stop thing in Postgres works or whether to in Start Conf you can configure whether a cluster should automatically start on boot up or whether it should be started manually or whether it should be disabled at all. The other thing that PostgreSQL Conf provides is a wrapper so there is a thing called PG wrapper and it wraps around the client binaries for and it selects a default cluster and version that you should target which is usually the main version and the first cluster but you can tell them so example we patched PSQL or PG wrapper is having this additional option cluster where you can select a cluster and then PSQL which is the main command line tool will connect to that cluster. If you don't you edit it's going to add to it's going to connect to the main cluster and you can add an environment variable or you can set up your own PostgreSQL for your default cluster and you can even have this user cluster thing in ETC PostgreSQL where you can configure a default cluster for each user and then there's a couple of PG cluster commands which wrap around the upstream Postgres commands basically create cluster drop cluster which work as you would expect you can rename the clusters you can list all the clusters which is one I wanted to show but sorry I didn't get the terminal up and PG control cluster which is the main thing for starting and stopping clusters finally PG upgrade cluster can upgrade a cluster it can it does a do it does do a dump restore by default but you can also run it in place so there is an option that allows you to run an in place upgrade which makes the whole thing much more much faster and finally PG conf tool is a nice tool to read and edit parameters in a configuration file for tooling and scripts and stuff so you don't have to rely on vi or some additional shell scripting during cluster creation which maybe also not everybody knows you can actually configure where you find great what what debion should do with your Postgres clusters so you can thanks you can tell it whether or not to automatically create a main cluster if it installs a new major version some people are annoyed by that so you can remove that actually it's it's all in at ETC PostgreSQL com create cluster start com I already mentioned that's the main one whether auto manual or disabled so that's just the the system-wide default for the start com which is in every ETC postgresql version cluster directory you can also tell it to put the transaction lock somewhere else if you want to do that and it will automatically sim link them in in the database database directory and add some in a db option so for example one thing which my which is not there but default is data checksums because there is a small performance overhead but data checksums make sure that the data you write to the disk and and postgres reads is actually consistent and there is no so postgres can then detect hidden hardware problems with the with the data being written to this so that's something that you might want to enable and you can also add their parameters which get written into the postgresql com during cluster start so if you have some default parameters so for example the the default postgresql dot com um configuration file is not tuned for huge installations it's supposed to be working on a notebook for example so be usable for about everybody so if you have really huge databases it makes sense to increase some of the performance and tuning limits so you can do this here uh as a default and it gets automatically included if you create a new cluster but that's about it uh i wanted to talk about from a user perspective now just a few more slides how to actually build extensions and uh projects for postgres if you're interested because there's lots of extensions and certainly not all of them are packaged and if you're if you're working with postgres and there is an extension that you really want to see packaged for easier inclusion uh this is how it works it's rather easy and you can join a package postgresql team so postgresql dot com common also provides something called pg underscore buildx for build extension it helps building packages for multiple postgres versions so it basically loops over uh the versions that you can provide in debian slash pg versions in the source package directory so you can say there everything from nine two is supported or everything from nine five is supported and then if you build it locally it will it will build packages for all of those if you build it on a debian autobilder it will only build packages for the default version in debian which is nine six right now for for stretch and also the supported versions are generally um defined by this so this will be nine six for for debian stable release but for example on this infrastructure of app postgresql dot org that all the versions that is supported by that um infrastructure are there so automatically packages for all the different uh postgres versions are built from buildx there's a couple of examples in the main file and the debian rules looks like this you include this um control main file snippet and basically you just run buildx with the proper argument for for building testing not we do auto package test and installing and this is the this will get um substituted for the version that that pg buildx figured out would be used or built so it's rather actually rather simple so this this probably works for already for quite a few extensions just as is you might have to add something or tweak it a bit but but this is the the skeleton which which actually works for rather well as a starting point for building package extension so it's it's not not a big deal yeah you have to write a debian control file you have to copy this over check that it works and almost done don't forget the copyright um as i just mentioned we are using auto package test so we are trying to test the packages during edit stall time so from the actual package that gets created there's been a lot of talks about auto package tests at dev comp so i don't want to get into how it works on a technical level but that's um i think so postgres girl com already has uh over 1000 tests for for postgres girl 94 or 96 that are doing all the kinds of things that put these pg cluster commands are doing so there's a lot there's a huge test suit that debian actually wrote for for postgres clusters and then there's of course also the upstream test suits that are that are being run and in that during that thing so for a package for a for a postgres girl server package we run all the upstream test suits and just so the integration with pg build x i want to quickly get into then you this is the auto package testing you have debian test control it run test suit and that's built depends and basically um it just goes there and runs and this is for postgres ql common and as you can see this is this is an output where it is running that for 94 if you want to do it for an extension which might be more interesting usually we run make install check which is an upstream target that all most of the packages at most of the extensions provide in their make file we have this postgres extension framework and then we have a very simple debian test install check which just runs build pg build x install check and pg build x takes care to actually start up a postgres server cluster installing the extension and running the test suit of the extension and checking that everything's fine and you can have it a bit more complicated if for example you only want to have um you don't want to run it on several versions then you can just continue in that case otherwise uh run the build x to all x so that's roughly about what i wanted to talk in terms of creating extensions it's rather easy so please help us if you're interested and chris off is quite overloaded and um on the other hand we are looking at so so at this point we looked at which are important extensions so for example i packaged pg logical and pg audit because i thought they're they're export important extensions so that we should have and we are looking at which extensions or um data types i used in the big um cloud offerings like google compute engine or asia or amazon what what are they doing if there's anything missing that that that doesn't have that they have we also would package it i guess but there's as i said there's hundreds so we are not going to package everything just because it's a postgres extension if you have a particular need file an rfp bug request for packaging and we look at it or package it yourself and let us know so finally um i wanted to talk about something we um did in the last couple of weeks i would say we're together with thomas crann it's a it's a company and it's a postgres appliance it's heavily based on postgres ql common so basically what you see here is is kind of a webified output of pgls clusters can you see that it's maybe a bit too small so basically um we're offering an appliance and the thomas crann is offering the hardware and the hardware support and we're doing the the software and the software support if needed and you can buy those and um get this web front end to your postgres cluster so it basically as i said there's a right now here there's three clusters they're all online and there's buttons to get to the service files so you can start or stop them you can go to the log files and you can check the backup and the log reports so this is done through integration with a couple of packages that you can see here um which is a pg admin 4 for a web based administration so pg admin 3 used to be the main thing that people were using for administrating or for gui administrating postgres clusters the new version pg admin 4 is web based it's not in debian yet because there's lots of python um dependencies so we haven't managed to package them all but um we are working on it so that that will be one of the things we're contributing in that terms and then we use krafana for as a monitoring dashboard um backed by promethos and we use pg badger which is in debian as a log file analyzer pg backrest just mentioned that this is new in stretch is used for for backups and cockpit is generally used to for system upgrades so martin pitt has recently implemented system upgrades in cockpit cockpit is a redhead project but it works with debian based systems and we are also using it to start and stop the services and show the log files and stuff and finally there's shell in the box which is a neat tool so you can just click on that click on that thing here and you get a shell in your web browser in on on that appliance if you need to debug something on on the command line i'm just stressed so you get an idea this is how the the krafana dashboard looks like um so there is a couple of dashboards um cpu just want to click through this a couple of graphs and um we're currently deploying it via ray grind if you want to first try it yourself i mean the um we haven't pushed it to github yet i think um i have to check but if you're interested in then talk to me and um it's called elephant chat uh internally so there's a couple of elephant chat packages which do all the system d integration and other setup and dropping configuration files and then point d directories so you get the integration and in general you can check the packages already from packages creative dot com but they're kind of better right now so take care but if you're interested you can look at this this is the aptly key for it so that's basically it that i wanted to talk about are there any questions thanks for maintaining the postgres packages um i've been using them as a dependency for other things including um post books and now um reciprocate um in reciprocate i've added um upstream i've added postgres support um so i'm using postgres tables now for things like users nice um so we've got a few different back ends and i was looking at merging the code for different databases and using an abstraction layer or something and so i'm just wondering if you can comment on that for upstream developers who want to use postbooks but sorry postgres but don't want a code directly to the postgres api and would you have any suggestions yeah preferably things that are packaged already or things that could be packaged well i think it mostly depends on which programming language your thing is so there's sql alchemy c plus plus c plus plus well there's the first um so if you're doing java then hibernate or something that would be an obvious abstraction layer but i'm not not a c plus passcode so unfortunately i'm not super sure i mean we can talk about that later i can have i can have a look but i don't know often what's a very useful thing i mean what what is it using right now as a database is there any database abstraction layer at all or is it just running sql queries no it's just going straight to the postgres api and running queries so yeah i'm familiar with the java world and hibernate and what have you um i've seen a few different c plus plus abstraction layers like not a full object mapping layer but just a way to like things like the unix odbc but the licensing on that isn't compatible with every c plus plus project okay so yeah also i mean it depends usually there's obviously an abstraction layer so lowest com denominator denominator um so you're losing some of the stuff you might get with postgres depending on what you're doing if it's very advanced analytics or things or you want to do you want to have some of the intelligence in the database by running stored procedures that's usually not possible when you're using abstraction layer so then on the other hand i don't want to tell you you should only use postgres so if you want to keep it open to to other projects then obviously you need to either support everything like you have to code everything for every database or you need to have an abstraction layer that's unfortunate okay thanks is there another question you mentioned a new replication system that's logical rather than physical is this intended as a replacement for the existing replication system or as an alternative that both will be maintained in parallel certainly both will be maintained in parallel upstream for a while to come i mean the um the physical replication works very well it's very easy to set up and um it's faster than logical replication but not by a lot so there used to be trigger based logical replications and that's very slow compared to physical replication logical replication is pretty fast it scales to quite a few cores but it's not it's performance wise still uh trade off um usually what people were using logical replication before was either major upgrades so replicate everything and then move over or they were using it to only replicate parts of it if you want to anyway replicate everything probably physical replication it's going to stay because basically what physical replication does is just uh streaming over the transaction locks to the standby and the standby is applying them so there is no i mean that anyway have to generate those strange excellent locks for crash recovery and and backup purposes so they're not going to throw that away it's going to stay there i don't think there's a i mean there's some maintenance burden but it's so useful that physical replication i'm pretty sure will be there for the next couple of years at least so and there are certainly no talk about removing it at this point is that does that answer your question yes okay so i think we're running out of time anyway do we have more question we still might have time for one if there is any hi with logical replication can you just uh uh make a replication of one that one table for example yes you can um you can tell it to only replicate one table yeah i mean actually with a physiological project there is a new version out where you can even tell it to only replicate parts of a table if you want so there is a wear condition you can add i think and but with the internal 10 logical replication i believe um you can either tell it to to replicate all the tables or um so basically it's a publication subscribers thing and you have a set and the default set will be all tables in the database or in the schema but i believe it's easy it's possible to only tell it okay this set is only this table and you can also tell it to um so you can have several standbys and they subscribe to different things so one would replicate only this table another one would replicate only another table but if you have the same table in several sets that's not a bottom performance over so it's not going to replicate the whole thing several times okay then you can do be directional or replication no you cannot well you can do it but it will break yeah so i mean what you can do right now with logical replication is you have you can write on the standby so i mean i didn't get into details with that but with physical replication you can only do reads from the standby it's called hot standby with logical replication you can write but but there is no internal thing right now which makes sure that you don't write over stuff which on the primary is different there is a called a be directional replication project by a company um called second quadrant and um they are so they also implemented most of the logical replication stuff so and certainly the end point at some point will be be directional replication as a like it's not master master but it's it's useful asynchronous or synchronous let's see a be directional replication so you have two data centers and you can actually do writes on both but certainly there has to be some transaction either some transaction manager or there has to be some conflict resolution last wins or whatever then so that they don't get out of sync and this is not in core so you can look at the bdr project if you're interested but i believe they went to a open core type of model recently so take it with a grain of salt but it will come in the next year so this is so nine four got logical change set decoding change data capture and um 10 gets logical replication and as with physical replication it will get improved on the next version um there is a several limitations now i think there is um failover for standbys is not working i believe so if the failover if you need to failover on a stand by you have to do that manually and re rebalance things and so i'm not sure it's i mean i wouldn't you bet my business on on the upstream logical replication right now but certainly to to look at it and and to check it out it's it's there and it's useful and it will be in 10 as i said okay thank you okay i think we're running out of time so thanks everybody thank you