In this post we'll set up a 3-node EDB Postgres Distributed (PGD) cluster running community Postgres 16. Then we'll upgrade the entire cluster in place from Postgres 16 to Postgres 17. We'll demonstrate that even while bringing individual nodes down for the major version upgrade, the cluster overall will remain available for reads and writes.
There may be nuances to your particular environment (additional extensions, permissions, etc.) that I'm ignoring in this post to focus on the simple and happy path.
I'm demonstrating this on a fresh Debian 12 machine.
Grab Postgres 16
First we follow the postgresql.org instructions for configuring the package repository.
# Import the repository signing key:
sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
# Create the repository configuration file:
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Update the package lists:
sudo apt updateNow we can install Postgres 16.
sudo apt install -y postgresql-16 postgresql-client-16Grab bdr extension
PGD works as a Postgres extension called bdr.
Get your EDB repository token from the EDB Repos 2.0 page. If you don't already have an EDB account, you can sign up for free to get a trial subscription to EDB's software repositories.
With that token, set an environment variable:
export EDB_SUBSCRIPTION_TOKEN=<your-repo-token>And use our helper script to set up package repositories:
curl -1sLf "https://downloads.enterprisedb.com/$EDB_SUBSCRIPTION_TOKEN/postgres_distributed/setup.deb.sh" | sudo -E bashNow install PGD related packages for Postgres 16:
sudo apt install -y edb-bdr5-pg16 edb-bdr-utilities edb-pgd5-cliCreating the Postgres 16 instances
Switch to the postgres user.
sudo su postgres
cd ~And create three Postgres instances with initdb:
rm -rf /var/lib/postgresql/*
/usr/lib/postgresql/16/bin/initdb /var/lib/postgresql/db1.16
/usr/lib/postgresql/16/bin/initdb /var/lib/postgresql/db2.16
/usr/lib/postgresql/16/bin/initdb /var/lib/postgresql/db3.16NOTE! If at any point you mess things up, or you want to get back to a clean slate for any reasons, stop each Postgres instance with pg_ctl and start over from this above snippet. It's easy! :)
Now append some PGD-specific settings to each instance's postgresql.conf.
echo "
port=6001
shared_preload_libraries = 'bdr'
wal_level = 'logical'
track_commit_timestamp = on
# These depend on the size of the cluster.
max_worker_processes = 12
max_wal_senders = 6
max_replication_slots = 6" | tee -a \
/var/lib/postgresql/db1.16/postgresql.conf
echo "
port=6002
shared_preload_libraries = 'bdr'
wal_level = 'logical'
track_commit_timestamp = on
# These depend on the size of the cluster.
max_worker_processes = 12
max_wal_senders = 6
max_replication_slots = 6" | tee -a \
/var/lib/postgresql/db2.16/postgresql.conf
echo "
port=6003
shared_preload_libraries = 'bdr'
wal_level = 'logical'
track_commit_timestamp = on
# These depend on the size of the cluster.
max_worker_processes = 12
max_wal_senders = 6
max_replication_slots = 6" | tee -a \
/var/lib/postgresql/db3.16/postgresql.confAnd then we'll start the three instances.
/usr/lib/postgresql/16/bin/pg_ctl -D /var/lib/postgresql/db1.16 -l /tmp/logfile-db1 start
/usr/lib/postgresql/16/bin/pg_ctl -D /var/lib/postgresql/db2.16 -l /tmp/logfile-db2 start
/usr/lib/postgresql/16/bin/pg_ctl -D /var/lib/postgresql/db3.16 -l /tmp/logfile-db3 startNext we'll connect them to each other in a PGD cluster.
Turning three Postgres databases into a PGD cluster
On each instance we must create a database.
/usr/lib/postgresql/16/bin/psql -p 6001 postgres -c "CREATE DATABASE pgdtest;"
/usr/lib/postgresql/16/bin/psql -p 6002 postgres -c "CREATE DATABASE pgdtest;"
/usr/lib/postgresql/16/bin/psql -p 6003 postgres -c "CREATE DATABASE pgdtest;"And in each database, on each instance, we'll install the bdr extension.
/usr/lib/postgresql/16/bin/psql -p 6001 pgdtest -c 'CREATE EXTENSION bdr CASCADE;'
/usr/lib/postgresql/16/bin/psql -p 6002 pgdtest -c 'CREATE EXTENSION bdr CASCADE;'
/usr/lib/postgresql/16/bin/psql -p 6003 pgdtest -c 'CREATE EXTENSION bdr CASCADE;'Then on each instance we'll initialize the instance as its own PGD node.
/usr/lib/postgresql/16/bin/psql -p 6001 pgdtest -c "SELECT bdr.create_node(node_name := 'db1', local_dsn := 'port=6001 dbname=pgdtest host=localhost user=postgres');"
/usr/lib/postgresql/16/bin/psql -p 6002 pgdtest -c "SELECT bdr.create_node(node_name := 'db2', local_dsn := 'port=6002 dbname=pgdtest host=localhost user=postgres');"
/usr/lib/postgresql/16/bin/psql -p 6003 pgdtest -c "SELECT bdr.create_node(node_name := 'db3', local_dsn := 'port=6003 dbname=pgdtest host=localhost user=postgres');"Then we'll create a PGD group on the first instance.
/usr/lib/postgresql/16/bin/psql -p 6001 pgdtest -c "SELECT bdr.create_node_group(node_group_name := 'pgdtest-group');"And ask the other two instances to join that group.
/usr/lib/postgresql/16/bin/psql -p 6002 pgdtest -c "SELECT bdr.join_node_group(join_target_dsn := 'port=6001 dbname=pgdtest host=localhost user=postgres');"
/usr/lib/postgresql/16/bin/psql -p 6003 pgdtest -c "SELECT bdr.join_node_group(join_target_dsn := 'port=6001 dbname=pgdtest host=localhost user=postgres');"All set! Let's test it out.
Initializing data in the PGD cluster
Let's create a table and insert some data into it one one node (it doesn't matter which).
/usr/lib/postgresql/16/bin/psql -p 6002 pgdtest -c "CREATE TABLE x (a INT PRIMARY KEY);"
/usr/lib/postgresql/16/bin/psql -p 6002 pgdtest -c "INSERT INTO x VALUES (1), (34), (-2), (5);"And query this table on all nodes.
/usr/lib/postgresql/16/bin/psql -p 6001 pgdtest -c "SELECT * FROM x;"
/usr/lib/postgresql/16/bin/psql -p 6002 pgdtest -c "SELECT * FROM x;"
/usr/lib/postgresql/16/bin/psql -p 6003 pgdtest -c "SELECT * FROM x;"They should all produce the same result:
a
----
1
34
-2
5
(4 rows)Upgrading the first instance
Stop the first instance.
/usr/lib/postgresql/16/bin/pg_ctl -D /var/lib/postgresql/db1.16 -l /tmp/logfile-db1 stopNote that the cluster is still perfectly functional by inserting data into the second instance.
/usr/lib/postgresql/16/bin/psql -p 6002 pgdtest -c "INSERT INTO x VALUES (99), (33);"And querying on the third instance.
/usr/lib/postgresql/16/bin/psql -p 6003 pgdtest -c "SELECT * FROM x;"
a
----
1
34
-2
5
99
33
(6 rows)Now, just like the steps for a regular pg_upgrade, we install the new Postgres version.
sudo apt install -y postgresql-17 postgresql-client-17And we create a new instance running Postgres 17.
/usr/lib/postgresql/17/bin/initdb /var/lib/postgresql/db1.17Then we copy the old instance's postgresql.conf over.
cp /var/lib/postgresql/db1.16/postgresql.conf /var/lib/postgresql/db1.17/If it were not a PGD cluster we'd run pg_upgrade now. But there is some PGD-specific logic we must do when doing an upgrade so we'll call pgd node upgrade instead (which itself wraps pg_upgrade).
pgd node db1 upgrade \
--old-datadir /var/lib/postgresql/db1.16 \
--new-datadir /var/lib/postgresql/db1.17 \
--old-bindir /usr/lib/postgresql/16/bin \
--new-bindir /usr/lib/postgresql/17/bin \
--username postgres \
--database pgdtest \
--old-port 6001Now we can start db1 running Postgres 17.
/usr/lib/postgresql/17/bin/pg_ctl -D /var/lib/postgresql/db1.17 -l /tmp/logfile-db1 startWhen it comes back it will receive all data it missed while it was offline. So if we query the x table we'll see the data that was added while this instance was offline.
/usr/lib/postgresql/17/bin/psql -p 6001 pgdtest -c "SELECT * FROM x;"
a
----
1
34
-2
5
99
33
(6 rows)To reiterate, we've got one instance in the cluster running Postgres 17 and the rest are still on Postgres 16.
/usr/lib/postgresql/17/bin/psql -p 6001 pgdtest -c "SELECT * FROM bdr.group_versions_details;"
node_id | node_name | postgres_version | bdr_version
------------+-----------+--------------------------------+-------------
4292081331 | db1 | 17.4 (Debian 17.4-1.pgdg120+2) | 5.6.1
4215100525 | db2 | 16.8 (Debian 16.8-1.pgdg120+1) | 5.6.1
2822738592 | db3 | 16.8 (Debian 16.8-1.pgdg120+1) | 5.6.1
(3 rows)Let's upgrade the second instance to Postgres 17 now.
Upgrading the second instance
Stop the second instance.
/usr/lib/postgresql/16/bin/pg_ctl -D /var/lib/postgresql/db2.16 -l /tmp/logfile-db2 stopCreate a new Postgres 17 instance.
/usr/lib/postgresql/17/bin/initdb /var/lib/postgresql/db2.17Copy the old instance's postgresql.conf over.
cp /var/lib/postgresql/db2.16/postgresql.conf /var/lib/postgresql/db2.17/Run pgd node upgrade.
pgd node db2 upgrade \
--old-datadir /var/lib/postgresql/db2.16 \
--new-datadir /var/lib/postgresql/db2.17 \
--old-bindir /usr/lib/postgresql/16/bin \
--new-bindir /usr/lib/postgresql/17/bin \
--username postgres \
--database pgdtest \
--old-port 6002Start db2 running Postgres 17.
/usr/lib/postgresql/17/bin/pg_ctl -D /var/lib/postgresql/db2.17 -l /tmp/logfile-db2 start
/usr/lib/postgresql/17/bin/psql -p 6002 pgdtest -c "SELECT * FROM bdr.group_versions_details;"
node_id | node_name | postgres_version | bdr_version
------------+-----------+--------------------------------+-------------
4292081331 | db1 | 17.4 (Debian 17.4-1.pgdg120+2) | 5.6.1
4215100525 | db2 | 17.4 (Debian 17.4-1.pgdg120+2) | 5.6.1
2822738592 | db3 | 16.8 (Debian 16.8-1.pgdg120+1) | 5.6.1
(3 rows)Two down, one to go!
Upgrading the third instance
Stop the third instance.
/usr/lib/postgresql/16/bin/pg_ctl -D /var/lib/postgresql/db3.16 -l /tmp/logfile-db3 stopCreate a new Postgres 17 instance.
/usr/lib/postgresql/17/bin/initdb /var/lib/postgresql/db3.17Copy the old instance's postgresql.conf over.
cp /var/lib/postgresql/db3.16/postgresql.conf /var/lib/postgresql/db3.17/Run pgd node upgrade.
pgd node db3 upgrade \
--old-datadir /var/lib/postgresql/db3.16 \
--new-datadir /var/lib/postgresql/db3.17 \
--old-bindir /usr/lib/postgresql/16/bin \
--new-bindir /usr/lib/postgresql/17/bin \
--username postgres \
--database pgdtest \
--old-port 6003Start db3 running Postgres 17.
/usr/lib/postgresql/17/bin/pg_ctl -D /var/lib/postgresql/db3.17 -l /tmp/logfile-db3 start
/usr/lib/postgresql/17/bin/psql -p 6002 pgdtest -c "SELECT * FROM bdr.group_versions_details;"
node_id | node_name | postgres_version | bdr_version
------------+-----------+--------------------------------+-------------
4292081331 | db1 | 17.4 (Debian 17.4-1.pgdg120+2) | 5.6.1
4215100525 | db2 | 17.4 (Debian 17.4-1.pgdg120+2) | 5.6.1
2822738592 | db3 | 17.4 (Debian 17.4-1.pgdg120+2) | 5.6.1
(3 rows)Basics tests
Now that the whole cluster has been upgraded let's make sure replication still works! Let's insert a unique row targeting each instance.
/usr/lib/postgresql/17/bin/psql -p 6001 pgdtest -c "INSERT INTO x VALUES (6001);"
/usr/lib/postgresql/17/bin/psql -p 6002 pgdtest -c "INSERT INTO x VALUES (6002);"
/usr/lib/postgresql/17/bin/psql -p 6003 pgdtest -c "INSERT INTO x VALUES (6003);"And query all instances.
/usr/lib/postgresql/17/bin/psql -p 6001 pgdtest -c "SELECT * FROM x;"
/usr/lib/postgresql/17/bin/psql -p 6002 pgdtest -c "SELECT * FROM x;"
/usr/lib/postgresql/17/bin/psql -p 6003 pgdtest -c "SELECT * FROM x;"And they should all produce:
a
------
1
34
-2
5
99
33
6001
6002
6003
(9 rows)Which is just swell.
In closing
We did a rolling upgrade of a three node PGD cluster from Postgres 16 to Postgres 17. The cluster was able to continue servicing reads and writes even while we brought every node down one at a time to upgrade that node. There was minimal overall downtime. And with proper use of the PGD proxy and careful draining of connections you could get even closer to a zero downtime upgrade.
The only way it would be possible to recreate this process in a cluster based on physical replication would be to set up two entire clusters and do logical replication in between them. That's a lot more effort and cost!
Subscribe to the EDB Engineering Newsletter to learn about future posts from the EDB Engineering team.