public inbox for [email protected]
help / color / mirror / Atom feedFrom: Matthias van de Meent <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: Online PostgreSQL version() updates
Date: Wed, 1 Apr 2026 12:48:08 +0200
Message-ID: <CAEze2WjgxD-uZrjiD7Gtp2gy=ztAOTU+xyLNJg6saYG4erP_=w@mail.gmail.com> (raw)
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)
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Online PostgreSQL version() updates
In-Reply-To: <CAEze2WjgxD-uZrjiD7Gtp2gy=ztAOTU+xyLNJg6saYG4erP_=w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox