Episode Transcript
[00:00:00] When I first started using postgres and I needed to do a major version upgrade, what I did because my databases were small back then is simply did a dump of the data and then restored it into the new version of the database. And this worked great until your database gets up to a particular size and you can't take the downtime associated with that dump and restore process. Luckily, Postgres offers a tool called PGUpgrade that allows you to upgrade major versions with minimal downtime. There are some things to watch out for, but it's a great tool. But more recently I've been using logical replication to do upgrades and personally I kind of think that's the future. But we're going to be talking about some of these different tools in this episode, but I hope you, your friends, family and co workers continue to do well Our first piece of content is plugging the postgres upgrade hole. This is from Tempo IO and this post seems to be a little bit of a reaction to a post we covered a couple of weeks ago on scaling postgres. Why does everyone run ancient postgres versions? Personally, I think the reason why so many people are running ancient versions is because unless you have a super reliable, high availability setup, you only have one primary database, so no matter what, you don't want that to go down. It's quite easy to dump and recreate ephemeral servers like application servers or web servers or job servers, but your database you want to keep running 24. 7 as much as you can. But another barrier to it could also be the difficulty of doing some of these PostgreSQL upgrades. So this blog post talks about the three ways that are generally used to upgrade your system. One is using PG dumps and PGrestores, basically a logical dump and a logical restore of all the data. Again, the downside that I mentioned is that it does take an incredibly long time to do with a larger database and you're going to encounter downtime. The next solution is using the PGUpgrade tool, and that's generally considered, he says, the fastest and most recommended approach, and I would say fastest in terms of the point at which you start from which you're done. I actually think some of the cutover times are better with the third solution mentioned here, but he says one disadvantage of PGUpgrade is that it requires running multiple versions of Postgres on an existing installation, because how that upgrade process works is it brings up both versions of the database to do the upgrade to the destination cluster, and then the third option is using Logical replication, so you set up an entirely new database cluster on the new version, logically replicating all the data from the source database cluster. But there are some things you need to watch out for with regards to analyzing the data on the new cluster, transferring sequences over and logical replication still doesn't do things like transfer over schema changes, if there are any. So generally what I recommend, if you're going to be using logical replication for an upgrade, try to do a schema freeze on your source database. But some of the issues he's describing with PGE upgrade is the fact that we have to have these two different versions at the same time. Because what he mentions is MySQL when it starts an upgraded version of its service, it automatically upgrades the system catalog from the previous version, which it can still read. So basically each new version can still read the old catalog and it does a conversion of it when it starts to the new catalog version. And so he's proposing, you know, in theory this is possible to do for Postgres as well, basically supporting version system catalogs. Now, he's not denying it's going to take work to do that, but he says, you know, it is possible to do, and if MySQL can do it, so can Postgres. He also saw that in Postgres 17 there's the PG Create Subscriber tool, which allows you to create a logical replica from a physical replica. And perhaps this could be used as part of the backup process. But with regard to some persistency of the slots, it doesn't make it an ideal candidate for this case. But one thing you'll notice by what I said and is represented in this blog is that for each of these three solutions, Dumps and Restores, PGUpgrade and even the logical replication method, none of those support an in place upgrade of postgres. So he says there is no such thing as an in place postgres upgrade, which presumably is what MySQL could do. So basically could pgupgrade be modified to do that type of an upgrade? Which definitely seems it's possible to do. Although I also think that logical replication upgrades have a place, because I think it's only a matter of time where logical replication can transfer sequences from the publisher to the subscriber as well as transfer schema changes or DDL commands as well, because those two issues represent, I would say, the most thorny things to look out for when you're doing a logical replication upgrade. And it looks like some system providers like aws, they now offer a blue green upgrade approach in rds. So that's essentially using logical replication under the hood. So I think the future is still going to be these three methods. Probably nothing happening on the PGDump PGRestore front, but if PGUpgrade could support in place upgrades, that could definitely help a lot of people and make upgrades easier. And when your needs are more sophisticated or you require minimal downtime, I think continuing to add features to logical replication would be a great benefit for many people as well. But if you want to learn more, definitely Check out this blog post Next Piece of Content why PGDUMP is Amazing this is from rhas.blogspot.com and he did say in a previous post he made is that you shouldn't use PGDUMP primarily as your main backup tool. You should probably use something like Barman or PG Backrest because of its lack of features. But PGDump can be enormously useful when you're wanting to do different types of logical backups or restores. Because PGDUMP is not tied to a particular binary format, you can do it in text. You can literally see the statements that it creates in order to recreate the database. Therefore it's also human readable and also modifiable. Like for example, when I'm doing upgrades and I'm transferring the schema from the source database to the destination database, I might pull out the indexes, or I take the create index commands and I insert concurrently into it. When running on the destination system, you can just programmatically modify the text for what objects are built when you're running, say, PGrestore. He says it also handles object dependencies really well. So if you're trying to export certain tables that have dependencies on other objects, PGDump will take those along too. And it can also help you get around DB corruption issues. So if your database becomes corrupted, you could restore from a backup and then export that specific table using PGDUMP to get back as much data as you can and recreate that object in your currently running database. And some final reasons it's a great tool because it's logical. It can be used across versions in postgres as well as CPU architectures. So if you're wanting to go from x86 to arm, you could do that. But if you want to learn more, definitely check out this blog post.
[00:07:26] Next piece of content PostgreSQL partitioning an existing table Almost online using table Inheritance this is from dbi services.com and this is an interesting way of setting up partition tables. So what he does Is first he has a table with 2 million rows inserted and let's say he wants to partition it. So what he does is he creates a child table that's inherits from the parent table along with a check constraint for the type of data that should go in that partition. Then he creates a trigger to actually insert the data appropriately into the right child table. So basically there's 2 million rows in the parent table and you have two empty child tables. But it's set up such that any new data going into the parent tables will be written to the child tables. But using a cte, you can delete from the primary table and insert it into the respective child table using these two CTEs. Here the parent table is empty, whereas the children table have all the data. So basically you've set up partitioning using inheritance as opposed to declarative partitioning. But now you can switch to declarative partitioning. So he starts a transaction, he creates a table and partitions it using declarative partitioning. He alters the first child table that's using inheritance and said it is no longer using inheritance and then it attaches that partition to the partition table he just created. Then he does the same thing for the second child, setting it to no inherit and then attaching it as a new partition table to the partition table. And then he renames the tables and commits. And this switches you from inheritance based partitioning to declarative partitioning. But he does have some caveats with this.
[00:09:15] One, this is a really simple test case. Of course this works well in busy systems. This might get more tricky. Two, routing data with a trigger might introduce some performance degradation. And three, you somehow have to deal with data coming in while you go from the old to the new structure or stop data from coming in during the last step. So basically in this transaction that does the transition, you should stop writes to those tables at that point. But I thought this was a very interesting technique or transitioning a table to declarative partitioning using inheritance as a part of that next piece of content. Also from dbi services.com is PostgreSQL delaying authentication to debug a process.
[00:09:59] So apparently there's a configuration called preauth delay and basically it adds a delay before a session is authenticated. And this gives you enough time if you wanted to do things like set up an S trace to see what's going on with that particular session. So this is just a configuration and he altered the system to set it to 10 seconds pre auth delay and then when he goes to connect using Postgres, it's going to cause that 10 second delay. And then in another session you try to find that active session that's delayed and you can start strace on the process. And he said for completeness there's also postauth delay, so you can pause after the authentication step. So check this out if you want to learn more. Next piece of content. Amazon Aurora PostgreSQL limitless database is now generally available. This is from aws.Amazon.com and this is basically a scale out database. So you no longer have just one writer but multiple writers for the Aurora database. And you basically have three different table types. One is a sharded table. So these are tables that are distributed across multiple servers through a shard key. So writes based upon that shard key are routed to individual servers. So that basically lets you scale out your rights. Then you have reference tables which are fully copied to every shard that maybe needs to be present for joins and things of that nature.
[00:11:29] And then standard tables that I'm assuming would only stay on one single shard. Yes, so it does stay on one single shard. So again, this seems relatively similar to Citus's offerings, but if you're looking for something like this with Aurora, you can definitely check this out.
[00:11:46] Next piece of content. There was another episode of Postgres FM last week. This one was on Postgres online communities. So Nikolai Michael discussed all the different places that you can go and learn more about postgres and ask questions. Basically the main locations for the core postgres community seems to be the listservs that the postgres community maintains as well as the irc. But also the slack seems pretty popular as well. So there's a postgres slack and of course all sorts of other different social media options. Things like Telegram and Twitter and Reddit and Discord. And of course all the links for all these different communities are on the show page here, so you can definitely check those out. But if you want to listen to the episode you can click here or watch the YouTube video down here. Next piece of content poking around PostgreSQL full text. A beginner's primer. This is from Remy Mercier and this is quite a long post, but he starts off easy and then shows you all sorts of different features that full text search offers for searching through text and postgres. So if that's of interest to you, you can check out this blog post. Next piece of content. An elephant in the cluster making postgresql feel at home on Kubernetes. This is from percona.com and Percona does have their own operator for using Postgres and Kubernetes and they kind of compare and contrast from some other solutions like Cloud Native PG and talk about why they chose the decisions they've made for how they've designed their operator for working with Kubernetes. So you can check this out if you're interested in that next post. Also from percona.com is exposing PostgreSQL with nginx ingress controller. So this is how you can set up a controller in Kubernetes such that you can connect to the postgres system from outside the container and how you can do that with their operator and the last piece of content the PostgreSQL Person of the Week is Rashad Latie. You can learn more about him and his work with Postgres in this blog post.
[00:13:51] I hope you enjoyed this episode. Be sure to check out scalingpostcares.com where you can find links to all the content discussed, as well as sign up to receive weekly notifications of each episode there. You can also find an audio version of the show as well as a full transcript. Thanks and I'll see you next week.
