public inbox for [email protected]
help / color / mirror / Atom feedOnline PostgreSQL version() updates
8+ messages / 7 participants
[nested] [flat]
* Online PostgreSQL version() updates
@ 2026-04-01 10:48 Matthias van de Meent <[email protected]>
0 siblings, 4 replies; 8+ messages in thread
From: Matthias van de Meent @ 2026-04-01 10:48 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>
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".
Attachments:
[application/octet-stream] v2026-04-0001-Online-PostgreSQL-version-updates.patch (8.7K, 2-v2026-04-0001-Online-PostgreSQL-version-updates.patch)
download | inline diff:
From 25561d50abd219ca8e43cdc62bffaf0195d4d453 Mon Sep 17 00:00:00 2001
From: Matthias van de Meent <[email protected]>
Date: Tue, 31 Mar 2026 23:35:12 +0200
Subject: [PATCH v2026-04] Online PostgreSQL version updates
Many users have asked to allow them to update their PostgreSQL
version to a newer one without downtime. At first glance, one
would think that that is a hugely complicated process, but
the opposite is true: There are very few places that actually
depend on the version.
This is a patch with minimal locking, and backends that don't
want to know about PostgreSQL versions won't be impacted by
upgrades either.
---
src/backend/storage/ipc/ipci.c | 2 +
.../utils/activity/wait_event_names.txt | 1 +
src/backend/utils/adt/version.c | 133 +++++++++++++++++-
src/include/catalog/pg_proc.dat | 9 +-
src/include/miscadmin.h | 5 +
src/include/storage/lwlocklist.h | 1 +
6 files changed, 148 insertions(+), 3 deletions(-)
diff --git a/src/backend/storage/ipc/ipci.c b/src/backend/storage/ipc/ipci.c
index d692d419846..0e88d042af6 100644
--- a/src/backend/storage/ipc/ipci.c
+++ b/src/backend/storage/ipc/ipci.c
@@ -142,6 +142,7 @@ CalculateShmemSize(void)
size = add_size(size, AioShmemSize());
size = add_size(size, WaitLSNShmemSize());
size = add_size(size, LogicalDecodingCtlShmemSize());
+ size = add_size(size, VersionCtlShmemSize());
/* include additional requested shmem from preload libraries */
size = add_size(size, total_addin_request);
@@ -323,6 +324,7 @@ CreateOrAttachShmemStructs(void)
AioShmemInit();
WaitLSNShmemInit();
LogicalDecodingCtlShmemInit();
+ VersionCtlShmemInit();
}
/*
diff --git a/src/backend/utils/activity/wait_event_names.txt b/src/backend/utils/activity/wait_event_names.txt
index 6be80d2daad..6246f01af15 100644
--- a/src/backend/utils/activity/wait_event_names.txt
+++ b/src/backend/utils/activity/wait_event_names.txt
@@ -412,6 +412,7 @@ XactSLRU "Waiting to access the transaction status SLRU cache."
ParallelVacuumDSA "Waiting for parallel vacuum dynamic shared memory allocation."
AioUringCompletion "Waiting for another process to complete IO via io_uring."
ShmemIndex "Waiting to find or allocate space in shared memory."
+VersionControl "Waiting for the PostgreSQL version control lock."
# No "ABI_compatibility" region here as WaitEventLWLock has its own C code.
diff --git a/src/backend/utils/adt/version.c b/src/backend/utils/adt/version.c
index ec3843cab96..91d8f915118 100644
--- a/src/backend/utils/adt/version.c
+++ b/src/backend/utils/adt/version.c
@@ -14,11 +14,142 @@
#include "postgres.h"
+#include "miscadmin.h"
+#include "port/atomics.h"
+#include "storage/lwlock.h"
+#include "storage/shmem.h"
#include "utils/builtins.h"
+#include "utils/guc_tables.h"
+/*
+ * The version that this cluster exposes under `SELECT version()`.
+ */
+typedef struct PgVersionControl
+{
+ LWLock lock;
+ int size;
+ int version_num;
+ char version_short[PG_CACHE_LINE_SIZE];
+ char version[FLEXIBLE_ARRAY_MEMBER];
+} PgVersionControl;
+
+static PgVersionControl *versionCtl;
+
+static struct config_generic *VERSION_GUC;
+static struct config_generic *VERSION_NUM_GUC;
+
+#define GENERATION_IS_LOCKED(generation) ((generation & 1) == 1)
+
Datum
pgsql_version(PG_FUNCTION_ARGS)
{
- PG_RETURN_TEXT_P(cstring_to_text(PG_VERSION_STR));
+ char *version;
+
+ version = GetCurrentVersionStr();
+
+ PG_RETURN_TEXT_P(cstring_to_text(version));
+}
+
+Datum
+pgsql_update_version(PG_FUNCTION_ARGS)
+{
+ int version_int;
+ text *version_short;
+ text *version_str;
+
+ if (PG_ARGISNULL(0) || PG_ARGISNULL(1) || PG_ARGISNULL(2))
+ ereport(ERROR, errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED));
+
+ version_int = PG_GETARG_INT32(0);
+ version_short = PG_GETARG_TEXT_P(1);
+ version_str = PG_GETARG_TEXT_P(2);
+
+ SetCurrentVersion(version_int, version_short, version_str);
+
+ PG_RETURN_TEXT_P(version_short);
+}
+
+void
+SetCurrentVersion(int version_num, text *version_short, text *version_str)
+{
+ Size newlen = VARSIZE_ANY_EXHDR(version_str);
+ Size shortlen = VARSIZE_ANY_EXHDR(version_short);
+
+ if (!superuser())
+ ereport(ERROR, errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("Forbidden operation"));
+
+ if (newlen >= versionCtl->size)
+ ereport(ERROR, errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("Version string is too long"),
+ errdetail("Versions up to %d are supported", versionCtl->size));
+ if (shortlen >= PG_CACHE_LINE_SIZE)
+ ereport(ERROR, errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("Version string is too long"),
+ errdetail("Versions up to %d are supported", versionCtl->size));
+
+ LWLockAcquire(&versionCtl->lock, LW_EXCLUSIVE);
+ versionCtl->version_num = version_num;
+ memcpy(versionCtl->version_short, VARDATA_ANY(version_short), shortlen);
+ versionCtl->version_short[shortlen] = 0;
+ memcpy(versionCtl->version, VARDATA_ANY(version_str), newlen);
+ versionCtl->version[newlen] = 0;
+ LWLockRelease(&versionCtl->lock);
+}
+
+char *
+GetCurrentVersionStr(void)
+{
+ char *version = palloc0(versionCtl->size + 1);
+
+ LWLockAcquire(&versionCtl->lock, LW_SHARED);
+ strncpy(version, versionCtl->version, versionCtl->size);
+ LWLockRelease(&versionCtl->lock);
+
+ return version;
+}
+
+Size
+VersionCtlShmemSize(void)
+{
+ Size size = MAXALIGN(offsetof(PgVersionControl, version));
+
+ /* keep some margin*/
+ size += mul_size(MAXALIGN(strlen(PG_VERSION_STR)), 4);
+
+ return Max(size, PG_CACHE_LINE_SIZE * 2);
+}
+
+void
+VersionCtlShmemInit(void)
+{
+ bool found;
+ Size size = VersionCtlShmemSize();
+
+ versionCtl = ShmemInitStruct("VersionCtl", size, &found);
+
+ if (!found)
+ {
+ LWLockInitialize(&versionCtl->lock, LWTRANCHE_VERSION_CTL);
+ versionCtl->size = size - offsetof(PgVersionControl, version);
+
+
+ memset(versionCtl->version, 0, versionCtl->size);
+ versionCtl->size -= 1; /* guarantee 0 byte at end of version */
+ memset(versionCtl->version_short, 0, PG_CACHE_LINE_SIZE);
+
+ versionCtl->version_num = PG_VERSION_NUM;
+ strncpy(versionCtl->version_short, PG_VERSION, PG_CACHE_LINE_SIZE);
+ strncpy(versionCtl->version, PG_VERSION_STR, versionCtl->size);
+
+ VERSION_GUC = find_option("server_version", false, false, LOG);
+ VERSION_NUM_GUC = find_option("server_version_num", false, false,
+ LOG);
+
+ *(VERSION_GUC->_string.variable) = versionCtl->version_short;
+ VERSION_NUM_GUC->_int.variable = &versionCtl->version_num;
+ VERSION_NUM_GUC->_int.min = 0;
+ VERSION_NUM_GUC->_int.max = INT_MAX;
+ }
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 3579cec5744..3955592f284 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -261,8 +261,13 @@
proname => 'boolne', proleakproof => 't', prorettype => 'bool',
proargtypes => 'bool bool', prosrc => 'boolne' },
{ oid => '89', descr => 'PostgreSQL version string',
- proname => 'version', provolatile => 's', prorettype => 'text',
- proargtypes => '', prosrc => 'pgsql_version' },
+ proname => 'version', provolatile => 'v', proparallel => 'u',
+ prorettype => 'text', proargtypes => '', prosrc => 'pgsql_version' },
+{ oid => '9901', descr => 'Update PostgreSQL to a new specified version',
+ proname => 'pg_update_version', provolatile => 'v', prorettype => 'text',
+ proargtypes => 'int4 text text', prosrc => 'pgsql_update_version',
+ proparallel => 'u', proisstrict => 'f',
+ proargnames => '{version_num,version_short,full_version}' },
{ oid => '86', descr => 'I/O',
proname => 'pg_ddl_command_in', prorettype => 'pg_ddl_command',
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 04f29748be7..8fcfebb0fc1 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -436,6 +436,11 @@ extern void InitializeSystemUser(const char *authn_id,
const char *auth_method);
extern const char *GetSystemUser(void);
+extern void SetCurrentVersion(int version_num, text *version_short, text *version_str);
+extern char *GetCurrentVersionStr(void);
+extern Size VersionCtlShmemSize(void);
+extern void VersionCtlShmemInit(void);
+
/* in utils/misc/superuser.c */
extern bool superuser(void); /* current user is superuser */
extern bool superuser_arg(Oid roleid); /* given user is superuser */
diff --git a/src/include/storage/lwlocklist.h b/src/include/storage/lwlocklist.h
index 59ee097977d..0a1ceb4b133 100644
--- a/src/include/storage/lwlocklist.h
+++ b/src/include/storage/lwlocklist.h
@@ -138,3 +138,4 @@ PG_LWLOCKTRANCHE(XACT_SLRU, XactSLRU)
PG_LWLOCKTRANCHE(PARALLEL_VACUUM_DSA, ParallelVacuumDSA)
PG_LWLOCKTRANCHE(AIO_URING_COMPLETION, AioUringCompletion)
PG_LWLOCKTRANCHE(SHMEM_INDEX, ShmemIndex)
+PG_LWLOCKTRANCHE(VERSION_CTL, VersionControl)
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Online PostgreSQL version() updates
@ 2026-04-01 11:17 Ashutosh Bapat <[email protected]>
parent: Matthias van de Meent <[email protected]>
3 siblings, 0 replies; 8+ messages in thread
From: Ashutosh Bapat @ 2026-04-01 11:17 UTC (permalink / raw)
To: Matthias van de Meent <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
Hi Matthias,
On Wed, Apr 1, 2026 at 4:18 PM Matthias van de Meent
<[email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Online PostgreSQL version() updates
@ 2026-04-01 11:24 Julien Rouhaud <[email protected]>
parent: Matthias van de Meent <[email protected]>
3 siblings, 0 replies; 8+ messages in thread
From: Julien Rouhaud @ 2026-04-01 11:24 UTC (permalink / raw)
To: Matthias van de Meent <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
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!
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Online PostgreSQL version() updates
@ 2026-04-01 11:35 Andreas Karlsson <[email protected]>
parent: Matthias van de Meent <[email protected]>
3 siblings, 1 reply; 8+ messages in thread
From: Andreas Karlsson @ 2026-04-01 11:35 UTC (permalink / raw)
To: Matthias van de Meent <[email protected]>; PostgreSQL Hackers <[email protected]>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Online PostgreSQL version() updates
@ 2026-04-01 12:01 Andrey Borodin <[email protected]>
parent: Matthias van de Meent <[email protected]>
3 siblings, 1 reply; 8+ messages in thread
From: Andrey Borodin @ 2026-04-01 12:01 UTC (permalink / raw)
To: Matthias van de Meent <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
> On 1 Apr 2026, at 15:48, Matthias van de Meent <[email protected]> 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.
Best regards, Andrey Borodin.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Online PostgreSQL version() updates
@ 2026-04-01 12:47 Laurenz Albe <[email protected]>
parent: Andrey Borodin <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Laurenz Albe @ 2026-04-01 12:47 UTC (permalink / raw)
To: Andrey Borodin <[email protected]>; Matthias van de Meent <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Wed, 2026-04-01 at 17:01 +0500, Andrey Borodin wrote:
>
> > On 1 Apr 2026, at 15:48, Matthias van de Meent <[email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Online PostgreSQL version() updates
@ 2026-04-01 13:18 Mihail Nikalayeu <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Mihail Nikalayeu @ 2026-04-01 13:18 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Andrey Borodin <[email protected]>; Matthias van de Meent <[email protected]>; PostgreSQL Hackers <[email protected]>
Thanks, Matthias!
I am going to push that later today with some minor changes (some fixed
from Claude).
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Online PostgreSQL version() updates
@ 2026-04-01 14:38 Andreas Karlsson <[email protected]>
parent: Andreas Karlsson <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Andreas Karlsson @ 2026-04-01 14:38 UTC (permalink / raw)
To: Matthias van de Meent <[email protected]>; PostgreSQL Hackers <[email protected]>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2026-04-01 14:38 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-01 10:48 Online PostgreSQL version() updates Matthias van de Meent <[email protected]>
2026-04-01 11:17 ` Ashutosh Bapat <[email protected]>
2026-04-01 11:24 ` Julien Rouhaud <[email protected]>
2026-04-01 11:35 ` Andreas Karlsson <[email protected]>
2026-04-01 14:38 ` Andreas Karlsson <[email protected]>
2026-04-01 12:01 ` Andrey Borodin <[email protected]>
2026-04-01 12:47 ` Laurenz Albe <[email protected]>
2026-04-01 13:18 ` Mihail Nikalayeu <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox