Episode Transcript
[00:00:00] So this weekend I decided to try out PG Hard Storage. This is the backup solution from Cybertech that they just released, or at least released version 1.0 that I was aware of and I just started it on one of my databases for an mostly internal application just to see what it would be like and if I wanted to start using it as opposed to the scripts I've been using forever.
[00:00:27] As a reminder, I have not used Barman or PG Backrest, but I really like the, I guess simple architecture from a usage standpoint of pghard Storage. So I decided to give it a try and right now it's just taking backups in parallel. I will eventually, of course, test the restores and then if things are looking good, I can go ahead and roll it out to my other applications. And if that goes well, I'll potentially mention it to clients as well because I am seeing some disk savings based upon how it stores the data. And ironically, the first blog post we're going to talk about this week, or not so ironically, is about migrating to pghard Storage, but I hope you, your friends, family and co workers continue to do well. Our first piece of content is moving to PG Hard Storage without changing your recovery strategy. This is from cybertech PostgreSQL and they do have migration guides which I did not read, but it says PG Hard Storage approaches migration as a gradual operational transition. But that's pretty much what I did because it was logical. So existing repositories remain in place while new backups are written to a new pgrard storage repository. That's exactly what I did. I just created another directory on my S3 bucket, called it pghard storage. So that is the repository for that particular database. And he says also historical backups continue to be restored using the tool that created them. So basically you can eventually retire those processes once the retention time frames run out. So yes, right now I am doing double backups, but that's actually something they recommend. Combined with the dual write migration model, writing to both repositories, your existing one and the new PG Hard Storage one teams can evaluate the new environment, validate recovery procedures, and choose a cutover point that it's their own operational schedule. And that's exactly what I'm doing. But with regard to those migration guides, again, which I didn't read, but they do exist and he has links for them. If you're using PG Backrest, want to try another solution? Or Barman or Wall G? Again, mine are just scripts using PGbase Backup and a way to archive and Compress the wall. But if you want to learn more, definitely check this out. Next piece of content post PostgreSQL 19 beta 2 is released. This is from PostgreSQL so we're of course getting ready for the fall and they list all the different things that have been improved from Beta one. So if you are testing it out you can check out this new beta. Next piece of content There was another episode of Postgres FM last week. This one was on PG Statlog and Nick and Michael were joined by Fabrizio Melo to to discuss his new extension PGStatLog. So I went ahead and took a Look at the GitHub of this and what it basically does is it collects cumulative statistics about Postgres log messages and it's built on the custom Cumulative Stats API introduced in Postgres 18. So you need to be on Postgres 18 to use this. But what it does is it aggregates all the log messages and gets counts for them. So it's similar to PGStats statements, but it's basically aggregating and accumulating log messages and it groups them by the back end type the database, the user, the error level as well as the SQL state code. So this does get stored on a table in the database and the counters are exposed through the PGSTAT log view.
[00:03:58] The stats persist across clean restarts, but they're discarded with there's a crash recovery which is the same thing as other Postgres cumulative stats. So this is a great way to see what specific error messages are popping up in their frequency without having to parse through the whole log file. So basically you can look at this to get an inkling of what's going on and then you could potentially grep the actual raw log data to get all the detail with regards to the issue. So this is great. But of course if you want to learn more about that, definitely encourage you to listen to the show or watch the YouTube video down here.
[00:04:33] Next piece of content looking forward to Postgres 19 checkpoint control this is from pgedge.com and for the longest time when you ran a checkpoint it would just immediately, as fast as possible, do a checkpoint. It wouldn't adhere to the checkpoint completion target or try to spread it out. It would do it as fast as possible.
[00:04:54] But they've added two parameters to checkpoint in Postgres 19. The first is a mode. Now the default mode is fast, but you can also choose spread and what that does is it paces them out over a longer time window defined by checkpoint completion target and checkpoint timeout. So it's the same thing as a normal checkpoint. You're just kicking it off early.
[00:05:16] The next parameter they added is flushunlogged. So this just makes sure that your unlogged tables have been flushed to disk. So when you do a restart of the system, say, all the data should be current, because otherwise an ordinary checkpoint doesn't bother flushing its dirty buffers to disk, because essentially those buffers set in memory ignored by each checkpoint and only get written when a clean shutdown happens. Well, now you can optionally do a checkpoint and tell it to go ahead and flush those unlogged tables.
[00:05:50] But if you want to learn more, definitely Check out this blog post Next piece of content Postgres 19 compression from PGLZ to LZ4 the this is from CrunchyData.com and with Postgres 19 the plan is to change the default toast compression From PGLZ to LZ4.
[00:06:08] Now PGLZ has been with us for a while, but moving to LZ4, which is considered more modern, but we should get faster compression. So that's probably the biggest reason you do get a better compression ratio sometimes, not all the time.
[00:06:25] And it continues to fail fast if there's some issue and continue operating normally. But if you want to learn more about that, definitely check out this blog post.
[00:06:35] Next Piece of content building the OAPE PostgreSQL certification this is from corneliabiaxis.medium.com and that acronym OAPE is for the Open alliance for PostgreSQL Education.
[00:06:49] So they've set up an organization to set up a vendor neutral PostgreSQL certification. So if you're potentially interested in the story behind that, definitely check out this blog post.
[00:07:00] Next piece of content PostgreSQL vs destructive time travel the year 2038 problem so this is the issue basically when the Linux epoch runs out basically when we run out of 32 bit integers from a start in January 1, 1970.
[00:07:18] But he discusses how Postgres uses 64 bits for its timestamps, so it should avoid this, but you still need to check dependent libraries and make sure you're not using a 32 bit operating system and whatnot with Postgres. So if you're interested in that, check out this blog post.
[00:07:34] Next Piece of Content Every update leaves a ghost MVCC bloat and vacuum in PostgreSQL. This is from Planetscale.com and this is basically talking about MVCC, how it works in Postgres and vacuum and a lot of details behind operationally how Postgres works. If you're interested in that, you can check out this blog post and last piece of content Working on Postgres after 13 years on SQL Server with PanOS Antonopoulos. So if you're interested in learning about his transition, you can listen to the podcast or watch it down here.
[00:08:11] I hope you enjoyed this episode. Be sure to check out scalingpostgres.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. I'll see you next week.
