Subject: Online PostgreSQL version() updates

From: Matthias van de Meent <[email-masked]>
Date: Wed, 1 Apr 2026 12:48:08 +0200

Hi,

March has gone and passed, and the feature freeze for 19 is on the
horizon; which also means it's time for users to start thinking about
version updates.

One of the greatest complaints people have about PostgreSQL is that
they can't update its version() without at least some downtime being
involved, either due to failovers or because their postmaster needs to
be restarted.  Updating to a new major version() is often
time-consuming, complicated, and may involve moving terabytes of data;
sometimes with no practical methods to avoid this.

Until now.

Attached is a patch that allows superusers to update the version() of
their running system with a simple SQL call: SELECT
pg_update_version(version_num, 'version_short', 'the full version()
outout'). Running backends will automatically get updated without
impacting their active workloads, and new backends will immediately
notice the new version().

Local testing of the patch indicates no significant performance loss
in normal workloads, with only a small amount of shared memory being
spent on the coordination of the update process.

Enjoy,

Matthias

Example usage:
$ SELECT pg_update_version(190000, '19beta1', 'PostgreSQL 19beta1 on
aarch64-darwin, compiled by clang-17.0.0, 64-bit');
$ SHOW server_version;
'19beta1'
$ SHOW server_version_num;
'190000'
$ SELECT version();
'PostgreSQL 19beta1 on aarch64-darwin, compiled by clang-17.0.0, 64-bit'
$ \c
psql (19devel, server 19beta1)
You are now connected to database "postgres" as user "posgres".

$ SELECT pg_update_version(40100, '4.1', 'PostgreSQL 4.1 compatibility
edition');
$ SHOW server_version;
'4.1'
$ SHOW server_version_num;
'40100'
$ SELECT version();
'PostgreSQL 4.1 compatibility edition'
$ \c
psql (19devel, server 4.1)
WARNING: psql major version 19, server major version 4.1.
         Some psql features might not work.
You are now connected to database "postgres" as user "posgres".

---

From: Ashutosh Bapat <[email-masked]>
Date: Wed, 1 Apr 2026 16:47:07 +0530

Hi Matthias,

> notice the new version().

Thanks a lot for providing such useful functionality in such a short
time and with a tiny patch.

This happened to be the 41st unread email in my inbox, so I was
compelled to review the patch and respond.

> $ SELECT pg_update_version(40100, '4.1', 'PostgreSQL 4.1 compatibility

I thought the last two zeros should be replaced by 2 and 6 respectively.

> You are now connected to database "postgres" as user "posgres".

The patch LGTM.

-- 
Best Wishes,
Ashutosh Bapat

---

From: Julien Rouhaud <[email-masked]>
Date: Wed, 1 Apr 2026 19:24:48 +0800

Hi,

> spent on the coordination of the update process.

I might be missing something but I don't see how this can be safe.

Indeed, there is no WAL record emitted for this critical operation!  Making it
WAL logged will make it crash safe, and will ensure that your replica and
backup are updated too!

---

From: Andreas Karlsson <[email-masked]>
Date: Wed, 1 Apr 2026 13:35:46 +0200

> notice the new version().

Thanks for the patch!

Some small quibbles:

- The NULL checks for arguments to pgsql_update_version() should say 
which field contained a NULL value.

- The error message for the superuser check should be something like 
"must be superuser to perform online version updates"

- The error messages for the version length checks could be imrpoved, 
e.g. by saying how long it was comapred to the limit which was hit.

- There is a copy pasto in the second length check. Surely 
versionCtl->size in the detail should be PG_CACHE_LINE_SIZE. Also I 
wonder if detail shouldn't actually be a hint.

- Shouldn't GetCurrentVersionStr() be static? Or do you think it would 
be useful for extensions?

- There is an accidental extra newline in the middle of 
VersionCtlShmemInit().

- Maybe I am missing something but why do we need to size field at all 
in PgVersionControl? Is this size known at compile time?

Thanks again for a great feature!

-- 
Andreas Karlsson
Percona

---

From: Andrey Borodin <[email-masked]>
Date: Wed, 1 Apr 2026 17:01:51 +0500

> On 1 Apr 2026, at 15:48, Matthias van de Meent <[email-masked]> wrote:
> 
> outout')

While I find this proposal very useful, I think the interface can be improved.

Consider SELECT pg_update_version(commit_hash) so we can do stuff like
SELECT pg_update_version('REL_18_2') or SELECT pg_update_version('HEAD~10000').
In future we can even create a bisect facility, so when user encounters a bug in their production they can iterate over several commits to trace root cause.

Best regards, Andrey Borodin.

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 01 Apr 2026 14:47:19 +0200

On Wed, 2026-04-01 at 17:01 +0500, Andrey Borodin wrote:
> In future we can even create a bisect facility, so when user encounters a bug
> in their production they can iterate over several commits to trace root cause.

I don't think that has to go into the first release; perhaps such functionality
can be added later.

But the patch, as it is, is missing something important: the "internal" parameters
"server_version" and "server_version_num" need to reflect the changed version.

Yours,
Laurenz Albe

---

From: Mihail Nikalayeu <[email-masked]>
Date: Wed, 1 Apr 2026 15:18:15 +0200

Thanks, Matthias!

I am going to push that later today with some minor changes (some fixed
from Claude).

---

From: Andreas Karlsson <[email-masked]>
Date: Wed, 1 Apr 2026 16:38:22 +0200

>> notice the new version().

Another thing: What should the return value be? Right now 
pgsql_update_version() returns the new value for version_short, but that 
feels not that useful and pretty arbitrary. Maybe we should simply have 
it return void?

-- 
Andreas Karlsson
Percona