There are several ways to perform a major version upgrade of the PostgreSQL/EPAS database, each with its pros and cons, and each is limited by the Source and Target version of PostgreSQL/EPAS (usually the latest stable). This article addresses EnterpriseDB's preferred paths.
The purpose of this article is to demonstrate different methods to perform a PostgreSQL major version upgrade with minimal downtime.
There are four ways to perform a major version upgrade. You can use either option as per your requirements.
- pg_upgrade
- pg_upgrade with -k (link option)
- pg_dump and pg_restore utility.
- Using EDB replication server (xDB)
Step 1] Download and install the version of PostgreSQL/EPAS (15)
Step 2] Before upgrading, you will have to make the following changes to the 14.x server and 15.x.
- Create a new database cluster of v15 with the 'initdb' utility
- Change the 'method' in pg_hba.conf file to 'trust'
- Stop both clusters
Step 3] Run the pg_upgrade utility to check the cluster compatibility with the -c option.
/usr/edb/as15/bin/pg_upgrade
--old-datadir <path_to_14_data_directory>
--new-datadir <path_to_15_data_directory>
--user <superuser_name>
--old-bindir <path_to_14_bin_directory>
--new-bindir <path_to_15_bin_directory>
--old-port <14_port> --new-port <14_port>
- c
Once you see the clusters are compatible you can go with your upgrade procedure.
Step 4] Upgrade the cluster with the below sample command.
/usr/edb/as15/bin/pg_upgrade
--old-datadir <path_to_14_data_directory>
--new-datadir <path_to_15_data_directory>
--user <superuser_name>
--old-bindir <path_to_14_bin_directory>
--new-bindir <path_to_15_bin_directory>
--old-port <14_port> --new-port <14_port>
Please refer to the below link for details on options available with pg_upgrade:
Note: Test the above steps in a test box before applying them to the production server.
Include the -k or --link keyword to create a hard link from the new cluster to the old cluster. See Linking versus Copying for more information about using a symbolic link.
Linking is much faster because pg_upgrade simply creates a second name (a hard link) for each file in the cluster; linking also requires no extra workspace because pg_upgrade does not make a copy of the original data. When linking the old cluster and the new cluster, the old and new clusters share the data; note that after starting the new cluster, your data can no longer be used with the previous version of EDB Postgres Advanced Server.
Sample command.
pg_upgrade
--old-datadir <path_to_14_data_directory>
--new-datadir <path_to_15_data_directory>
--user <superuser_name>
--old-bindir <path_to_14_bin_directory>
--new-bindir <path_to_15_bin_directory>
--old-port <14_port> --new-port <14_port>
--link
Please refer to the below link for details on Linking versus Copying for more information about using a symbolic link.
It is recommended that you use the pg_dump and pg_dumpall binaries from the newer version of (Here EPAS v15), to take advantage of enhancements that are available in higher version.
Perform backup during the maintenance window. This does not affect the integrity of the backup, but the changed data would not be included in the dump.
If necessary, edit the permissions in the file //pg_hba.conf (or equivalent) to disallow access from everyone except you.
Step 1] Install the binaries of the new PostgreSQL/EPAS version(15) and initialize a new cluster using the initdb command i.e:
./initdb -D /data
Edit the postgresql.conf file in the data directory and start the DB
Step 2] Perform the global objects dump of the current version using pg_dump binaries from the new installation created in Step 1.
./pg_dumpall -p -U -g > file.sql
Step 3] Create a compressed dump of each database using the pg_dump utility of the new installation created in Step 1.
./pg_dump -f -Fc -i -v -U -p DBNAME 2>>/tmp/dump.log
Step 4] Restore the modified global object dump into a new version of Postgres/EPAS:
./psql -d -p -U -f file.sql
Step 5] Restore the compressed dump in the new version of the Postgres/EPAS server using the pg_restore utility.
./pg_restore -d -Fc -v -p -U -j 5 2>>/tmp/restore.log
If you would like to obtain a near-zero downtime, then you cannot use pg_upgrade as this option will require downtime. To perform such an operation use XDB (replication server).
- Registering a publication server
- Adding a publication database
- Adding a publication
- Control schema objects created for a publication
- Registering a subscription server
- Adding a subscription database
- Adding a subscription
- Subscription metadata object
To learn more about this tool, you can use the below links: