Some time ago, I presented a typical approach to upgrade PostGIS under Ubuntu. Please take a look at my previous post to recall how we accomplished this task.   Today, we’ll extend this procedure by upgrading PostgreSQL and PostGIS in one row to replay a quite realistic scenario: A customer runs PostgreSQL 13 with an […]

LZ4 and ZSTD pg_dump compression I wrote a “pg_dump compression specifications in PostgreSQL 16” post a while ago. Frankly speaking, I thought new compression methods would not be implemented in PostgreSQL until 2-3 years from now. Probably demand is so high that LZ4 and ZSTD made their way into PostgreSQL 16! The LZ4 patch author […]

What is pg_dump compression? pg_dump is a PostgreSQL utility for backing up a local or remote PostgreSQL database. It creates a logical backup file that contains either plain SQL commands for recreating the database, or a binary file that can be restored with the pg_restore utility. The binary backup file can be used to restore […]

© Laurenz Albe 2022 Recently, a customer asked me why a bulk load into a PostgreSQL table kept slowing down as time went by. They saw that it was a trigger on the table that took longer and longer, and in that trigger, the updates were getting slower all the time. Now slow updates are […]

Last time, we installed PostGIS on top of PostgreSQL. Today, I will complement this article by describing how to upgrade PostGIS on Ubuntu. A detailed description can be found at postgis.net and should be referred to in parallel. Let me first define our scenario and goal: An artificial customer wants his PostGIS-enabled PostgreSQL 13 cluster […]

Blog post updated 05.12.2022 People keep asking: “What are the best ways to upgrade and update my PostgreSQL to the latest version?” This blog post covers how you can upgrade or update to the latest PostgreSQL release. Before we get started, we have to make a distinction between two things: Updating PostgreSQL Upgrading PostgreSQL Let’s […]

© Laurenz Albe 2020 UPDATED blog article on 09.01.2023 HOT updates are not a new feature. They were introduced by commit 282d2a03dd in 2007 and first appeared in PostgreSQL 8.3. But since HOT is not covered by the PostgreSQL documentation (although there is a README.HOT in the source tree), it is not as widely known […]

© Laurenz Albe 2019 The biggest change in PostgreSQL v12 from the viewpoint of backward compatibility is that recovery.conf has been absorbed into postgresql.conf. This article describes the changes and how you can adapt to them. Getting rid of recovery.conf Up to now, the presence of the file recovery.conf was the trigger for PostgreSQL to […]

By Kaarel Moppel   With the latest major version freshly released, it’s again time to gauge its performance. I’ve already been doing this for years, and I have my scripts ready, so it’s not too much work for me – but rather, for the machines. The v12 release, as always, adds quite a lot of […]

By Kaarel Moppel – Some weeks ago, in the light of PostgreSQL v12 release, I wrote a general overview on various major version upgrade methods and benefits of upgrading in general – so if upgrading is a new thing for you I’d recommend to read that posting first. But this time I’m concentrating on the […]

Upgrade to a new major version of PostgreSQL, by Kaarel Moppel – Soon it’s that time of the year again – basically a 2nd Christmas for followers of the “blue elephant cult” if you will :). I’m, of course, referring to the upcoming release of the next PostgreSQL major version, v12. So I thought it’s […]

By Kaarel Moppel – Last week the first release candidate of the upcoming Postgres version 11 was released, stating that everything is going more or less as planned – great news! The 11 release is promising a lot of very cool stuff as usual, including – better partitioning, more parallezation and JIT capabilities (which are […]