Upgrade Streaming Replication Setup using pg_upgrade
OS:Redhat Linux
OS Version:9
Postgresql old version:9.5
Postgresql new version:16
Old Cluster: /app/data
New Cluster: /var/lib/pgsql/16/data
Primary server:172.31.95.130
Standby server:172.31.42.140
Step1) Collect database tables, tablespace and users, etc.. list
Step2) move the old cluster.
mv /usr/local/pgsql /usr/local/pgsql_old
Step3) Install the new PostgreSQL
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-
x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql16-server
Step4) Initialize the new PostgreSQL cluster
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
Step5) Install contrib extension.
#If you are using extensions install postgresql16-contrib for extension updates
sudo dnf install -y postgresql16-contrib
Step6) Stop both servers
pg_ctl -D /var/lib/pgsql/16/data stop --->new cluster
pg_ctl -D /app/data stop--->old cluster
Note: Streaming replication and log-shipping standby servers must be running during this
shutdown, so they receive all changes.
Step7)Run pg_upgrade
#Always run the pg_upgrade binary of the new server, not the old one. pg_upgrade requires the
specification of the old and new cluster's data and executable (bin) directories.
/usr/pgsql-16/bin/pg_upgrade --old-bindir "old-bindir_path" --new-bindir "new-bindir_path" --
old-datadir "old-datadir_path" --new-datadir "new-datadir_path"
#perform only the checks(option -c)
/usr/pgsql-16/bin/pg_upgrade -b /usr/local/pgsql_old/bin -B /usr/pgsql-16/bin -d /app/data -D
/var/lib/pgsql/16/data -c
#use hard links(option -k) instead of copying files to the new cluster
/usr/pgsql-16/bin/pg_upgrade -b /usr/local/pgsql_old/bin -B /usr/pgsql-16/bin -d /app/data -D
/var/lib/pgsql/16/data -k
Step8) Upgrade streaming replication and log-shipping standby servers
#Install the new PostgreSQL binaries on standby servers
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-
x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql16-server
#Make sure the new standby data directories do not exist
Make sure the new standby data directories do not exist or are empty. If initdb was run, delete
the standby servers' new data directories.
rm -rf recursive /var/lib/pgsql/16/data/*
#Install contrib extension
sudo dnf install -y postgresql16-contrib
#Stop standby servers
pg_ctl -D /app/data stop--->old cluster
#Run rsync
When using link mode, standby server can be quickly upgraded using rsync ,run this on the
primary for standby server:
rsync --archive --delete --hard-links --size-only --no-inc-recursive /app/data
/var/lib/pgsql/16/data
rsync --archive --delete --hard-links --size-only --no-inc-recursive /var/lib/pgsql/16/data
postgres@172.31.42.140:/var/lib/pgsql/16
#If you have tablespaces, you will need to run a similar rsync command for each
tablespace directory
rsync --archive --delete --hard-links --size-only --no-inc-recursive /app/tbl_space1
postgres@172.31.42.140:/app
rsync --archive --delete --hard-links --size-only --no-inc-recursive /app/tbl_space2
postgres@172.31.42.140:/app
Step9) Configure streaming replication and log-shipping standby server
#Create a file standby.signal in the standby's cluster data directory
cd /var/lib/pgsql/16/data
touch standby.signal
#Edit postgresql.conf
vim /var/lib/pgsql/16/data/postgresql.conf
listen_addresses = '*'
port = 5432
primary_conninfo = 'host=172.31.95.130 port=5432 user=repuser password=reppass@123
options=''-c wal_sender_timeout=5000'''
hot_standby = on
restore_command = 'rsync -a postgres@172.31.95.130:/mnt/server/archivedir/%f %p'
save&exit
Step10) Start the new server.
#Start the new upgraded primary server
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16
#Check the status
sudo systemctl status postgresql-16
Step11) Configure primary server for streaming replication and log-shipping
#Edit postgresql.conf file
vim /var/lib/pgsql/16/data/postgresql.conf
listen_addresses = '*'
port = 5432
wal_level = replica
archive_mode = on
archive_command = 'rsync -a %p /mnt/server/archivedir/%f && rsync -a %p
postgres@172.31.42.140:/mnt/server/archivedir/%f'
save&exit
#Edit pg_hba.conf file
vim /var/lib/pgsql/16/data/pg_hba.conf
# replication privilege.
host replication repuser 172.31.42.140/32 md5
save&exit
#Restart postgresql server
sudo systemctl restart postgresql-16
Step12) Start rsync'ed standby server.
#Start the postgresql
sudo systemctl start postgresql-16
#Check the status
sudo systemctl status postgresql-16
Step13) Verify the replication status.
#on primary database instance
select * from pg_stat_replication;
#on Standby database instance
select * from pg_stat_wal_receiver ;
Step14) Delete old cluster on both primary and standby.
rm -rf '/app/data'
rm -rf '/app/tbl_space1/PG_9.5_201510051'
rm -rf '/app/tbl_space2/PG_9.5_201510051'
Step15) Check database tables, tablespace, and users, etc. list
