Table of Contents

PostgreSQL Upgrades

Using pg_upgrade

https://www.postgresql.org/docs/14/pgupgrade.html

/usr/lib/postgresql/14/bin/pg_upgrade   --old-datadir=/var/lib/postgresql/10/main   
                                        --new-datadir=/var/lib/postgresql/14/main   
                                        --old-bindir=/usr/lib/postgresql/10/bin   
                                        --new-bindir=/usr/lib/postgresql/14/bin   
                                        --old-options='-c config_file=/etc/postgresql/10/main/postgresql.conf'   
                                        --new-options='-c config_file=/etc/postgresql/14/main/postgresql.conf'   
                                        --link --jobs=4 --check

Upgrade Streaming Replication server.

rsync --verbose --archive --delete --hard-links --size-only --no-inc-recursive --human-readable 
      10 14 postgres@postgres02-upgrade:/var/lib/postgresql

Using pg_dumpall

https://www.postgresql.org/docs/14/upgrading.html#UPGRADING-VIA-PGDUMPALL

If necessary, create a new instance …

pg_createcluster -d /var/lib/postgresql/14/doob 14 doob

On postgres-test, this used port 5434 for the new instance.
Copy the pg_hba.conf and postgresql.conf files from the old instance to the new one …

cp /var/lib/postgresql/10/main/postgresql.conf /var/lib/postgresql/14/doob
cp /var/lib/postgresql/10/main/pg_hba.conf /var/lib/postgresql/14/doob

Edit the postgresql.conf and update relevant entries to 14/doob.
Restart the new PostgreSQL 14 instance …

sudo systemctl restart postgresql@14-doob

Copy data from the old instance to the new using the following command …

pg_dumpall -p 5432 -U postgres -d postgres | psql -p 5434 -U postgres -d postgres

If you wish to test the above process again then delete the cluster and begin again from the start …

pg_dropcluster 14 doob --stop

Otherwise, stop the old instance and then configure the new instance to use port 5432 and restart.