Migrating the data for PostgreSQL major version upgrade
PostgreSQL databases can be migrated without doing database dump-and-restore.Here are the steps to migrate from Postgres 9.6 to 12 in Ubuntu 18.04
Existing PostgreSQL 9.6
Let’s first check the list of installed postgres related packages, service status and the running cluster details.
Packages list:
$dpkg -l| grep postgrespgdg-keyring 2018.2 all keyring for apt.postgresql.orgpostgresql-9.6 9.6.17-2.pgdg18.04+1 amd64 object-relational SQL database, version 9.6 serverpostgresql-client-9.6 9.6.17-2.pgdg18.04+1 amd64 front-end programs for PostgreSQL 9.6postgresql-client-common 213.pgdg18.04+1 all manager for multiple PostgreSQL client versionspostgresql-common 213.pgdg18.04+1 all PostgreSQL database-cluster managerpostgresql-contrib-9.6 9.6.17-2.pgdg18.04+1 amd64 additional facilities for PostgreSQLService:
$systemctl list-dependencies postgresqlpostgresql.service● ├─[email protected]● ├─system.slice● └─sysinit.target● ├─ ...Cluster:
$pg_lsclustersVer Cluster Port Status Owner Data directory Log file9.6 main 5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.logInstallig PostgreSQL 12
Multiple versions of postgres can co-exist in a system.
Installation:
$cat /etc/apt/sources.list.d/pgdg.listdeb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main$wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc| sudo apt-key add -$apt-get update$apt-get install postgresql-12Postgres 12 cluster will start automatically after the installation.
Verify status:
You’ll be seeing version 12 in packages list:
$dpkg -l| grep postgrespgdg-keyring 2018.2 all keyring for apt.postgresql.orgpostgresql-12 12.2-2.pgdg18.04+1 amd64 object-relational SQL database, version 12 serverpostgresql-9.6 9.6.17-2.pgdg18.04+1 amd64 object-relational SQL database, version 9.6 serverpostgresql-client-12 12.2-2.pgdg18.04+1 amd64 front-end programs for PostgreSQL 12postgresql-client-9.6 9.6.17-2.pgdg18.04+1 amd64 front-end programs for PostgreSQL 9.6postgresql-client-common 213.pgdg18.04+1 all manager for multiple PostgreSQL client versionspostgresql-common 213.pgdg18.04+1 all PostgreSQL database-cluster managerpostgresql-contrib-9.6 9.6.17-2.pgdg18.04+1 amd64 additional facilities for PostgreSQLServices:
$systemctl list-dependencies postgresqlpostgresql.service● ├─[email protected]● ├─[email protected]● ├─system.slice● └─sysinit.target● ├─...Clusters:
$pg_lsclustersVer Cluster Port Status Owner Data directory Log file9.6 main 5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log12 main 5433 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.logVersion 12 cluster will be running along with version 9.6.
Upgrading PostgreSQL 9.6 to 12
pg_upgrade command format
To perform upgrade we will be usingpg_upgrade which comes along with postgres package. Usage format is as follows:
<target_version_pg_upgrade> \ -b <source_version_binary_dir> \ -B <target_version_binary_dir> \ -d <source_version_config_dir> \ -D <source_version_config_dir>Get directory paths of binary & config
Binary & config directory paths required for this command can be obtained from the respective version’s service status:
$systemctl status[email protected]●[email protected] - PostgreSQL Cluster 9.6-main ... CGroup: /system.slice/system-postgresql.slice/[email protected] ├─2469 /usr/lib/postgresql/9.6/bin/postgres -D /var/lib/postgresql/9.6/main -c config_file=/etc/postgresql/9.6/main/postgresql.conf ├─...$systemctl status postgresql@12-main●[email protected] - PostgreSQL Cluster 12-main ... CGroup: /system.slice/system-postgresql.slice/[email protected] ├─2469 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf ├─...As you can see, here the binary dir is/usr/lib/postgresql/<version>/bin and config dir is/etc/postgresql/9.6/main.
Stop clusters
Stop both servers before proceeding:
sudo pg_ctlcluster 9.6 main stopsudo pg_ctlcluster 12 main stopRun upgrade
Now runpg_upgrade (example shown uses the default postgres user)
$su - postgrespostgres@istance:~$ /usr/lib/postgresql/12/bin/pg_upgrade \ -b /usr/lib/postgresql/9.6/bin \ -B /usr/lib/postgresql/12/bin \ -d /etc/postgresql/9.6/main \ -D /etc/postgresql/12/main \ --verboseThe data migration will take time to complete.
Shutting down the old cluster, making the new cluster as the default
Switch ports
9.6 is currently configured to run on port 5432 and 12 in 5433. This need to be switched:
$vim /etc/postgresql/9.6/main/postgresql.conf...port = 5432 # <-- change to 5433...$vim /etc/postgresql/12/main/postgresql.conf...port = 5433 # <-- change to 5432...Veriy chages:
$grep -H'^port' /etc/postgresql/*/main/postgresql.conf/etc/postgresql/12/main/postgresql.conf:port = 5432/etc/postgresql/9.6/main/postgresql.conf:port = 5433Start the new cluster
At this point both clusters are not running. We can start version 12 alone.
sudo pg_ctlcluster 12 main start$pg_lsclustersVer Cluster Port Status Owner Data directory Log file9.6 main 5433 down postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log12 main 5432 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.logVerify
The migration has completed, verify whetherpsql connects to the version 12 cluster by default:
$su - postgrespostgres@istance:~$ psqlpsql (12.2 (Ubuntu 12.2-2.pgdg18.04+1))Type "help" for help.postgres=#🎉