Обсуждение: Online PostgreSQL version() updates

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".

Hi Matthias,

On Wed, Apr 1, 2026 at 4:18 PM Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
>
> 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().

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
> edition');

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

> $ 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".

The patch LGTM.

--
Best Wishes,
Ashutosh Bapat

Hi,

On Wed, Apr 01, 2026 at 12:48:08PM +0200, Matthias van de Meent wrote:
> 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.

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!

On 4/1/26 12:48 PM, Matthias van de Meent wrote:
> 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().

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

> On 1 Apr 2026, at 15:48, Matthias van de Meent <boekewurm+postgres@gmail.com> wrote:
>
> 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')

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
severalcommits to trace root cause. 


Best regards, Andrey Borodin.

On Wed, 2026-04-01 at 17:01 +0500, Andrey Borodin wrote:
>
> > On 1 Apr 2026, at 15:48, Matthias van de Meent <boekewurm+postgres@gmail.com> wrote:
> >
> > 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')
>
> 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.

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

On 4/1/26 1:35 PM, Andreas Karlsson wrote:
> On 4/1/26 12:48 PM, Matthias van de Meent wrote:
>> 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().
> 
> Thanks for the patch!
> 
> Some small quibbles:

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