There are 3 different methods to perform major version upgrades on your PostgreSQL databases. These are:
- Moving all data to a database cluster created in the target version with pg_dumpall
- Refreshing system tables and using user data as is with pg_upgrade
- Moving all data to the new version in a standby environment with logical replication and changing roles with switch-over.
Let’s quickly go over the advantages/disadvantages of these methods. Since upgrading with pg_dumpall means copying all the data, its applicability decreases as the database size increases. As the size increases, the downtime and the possibility of errors in data transfer will increase. In addition, the disk space we will need for the upgrade process may become a problem. As an advantage, we can mention that the process is safe since the cluster data in the old version is not overwritten, and that all objects in the databases are recreated, fixing problems such as fragmentation and “index bloat”. We also have the option to speed up our upgrade process by performing data export and import operations with a single command as in the example below.
$ pg_dumpall -p 5432 | psql -d postgres -p 5433You can review the upgrade steps of this method, from the PostgreSQL documentation:
pg_upgrade is a method that will make our work easier in large sizes. With the “–link” option offered by pg_upgrade, existing data files can be used by creating a “hard link” on them without having to move the data. (By the way, if it is run with the default settings, pg_upgrade copies the database files from the data directory in the old version to the data directory in the new version.) In the link method, the old version cluster cannot be opened again properly after the upgrade process, so it is important to back up the databases before the upgrade. Another important point is that in order to be able to upgrade in link mode, the data directories of the old and new clusters must be on the same filesystem. It is also possible to perform checks before the upgrade process with the “–check” option of pg_upgrade. Again, you can find more detailed information in the official PostgreSQL documentation.
The third method is an option that we can choose to minimize the upgrade time of large databases by using the advantage of the “logical replication” configuration to work in different versions (primary/standby). After installing our standby database on the same server as the primary or on a different server and the target PostgreSQL version and completing the replication configuration, we can complete the upgrade process in a short time by performing the switch-over process while the databases are synchronized. For details
After examining these three methods within the scope of PostgreSQL major upgrade, we will also talk about “pg_upgradecluster”, which makes our job easier in Ubuntu/Debian environments. This program, which comes with the “postgresql-common” package, performs the PostgreSQL cluster upgrade to the new version with a single command, while the configuration files of the old version are copied by adjusting them in accordance with the new version cluster. When using “pg_upgradecluster”, we can choose pg_dump or pg_upgrade for the upgrade method.
-m, –method=dump|upgrade|link|clone
- “dump” option uses pg_dump and pg_restore, while the “upgrade” option uses pg_upgrade, the default is “dump”.
- “link” option is a shortcut for “upgrade –link” and uses pg_upgrade with the link option.
- “clone” is a shortcut for “upgrade –clone” and uses pg_upgrade with the clone option.
The “pg_upgrade –clone” option, which we did not mention before, is an option that is supported only by certain filesystems and during the upgrade, it creates a clone of the files in the old cluster in the new cluster as quickly as in the hard link method, but unlike the hard link, it keeps the original blocks of the old files and makes the old cluster openable. This method, also known as “reflink”, is actually a filesystem feature that allows two different files to use common data blocks.
You can find more information and examine the command options in the man page of pg_upgradecluster.
Now let’s go over the steps of upgrading our PostgreSQL 16 version cluster to version 17 with pg_upgradecluster. In our example, there are two version 16 clusters running and we will upgrade one of them to version 17.
# pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
16 test 5433 online postgres /var/lib/postgresql/16/test /var/log/postgresql/postgresql-16-test.logFirst, we install the new version of PostgreSQL on our server.
# sudo apt-get upgrade
# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
# sudo apt-get install postgresql-17 postgresql-client-17With the installation of the new version, a new cluster named “main” was automatically started. We are deleting this cluster since we will not be using it.
# pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
16 test 5433 online postgres /var/lib/postgresql/16/test /var/log/postgresql/postgresql-16-test.log
17 main 5434 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log
# pg_dropcluster --stop 17 main
# pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
16 test 5433 online postgres /var/lib/postgresql/16/test /var/log/postgresql/postgresql-16-test.log
Now we update the cluster named “test” to version 17 using the pg_upgradecluster command with its default settings. This will create a cluster named “test” in version 17 and move the data to the new cluster using pg_dump. It will also move the configuration files and change the port number of the old cluster in version 16.
# pg_upgradecluster 16 test
Stopping old cluster...
Restarting old cluster with restricted connections...
Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation
Creating new PostgreSQL cluster 17/test ...
/usr/lib/postgresql/17/bin/initdb -D /var/lib/postgresql/17/test --auth-local peer --auth-host scram-sha-256 --no-instructions --encoding UTF8 --lc-collate en_US.UTF-8 --lc-ctype en_US.UTF-8 --locale-provider libc
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/17/test ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Copying old configuration files...
Copying old start.conf...
Copying old pg_ctl.conf...
Starting new cluster...
Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation
Running init phase upgrade hook scripts ...
Roles, databases, schemas, ACLs...
set_config
------------
(1 row)
set_config
------------
(1 row)
Fixing hardcoded library paths for stored procedures...
Upgrading database postgres...
Fixing hardcoded library paths for stored procedures...
Upgrading database template1...
Stopping target cluster...
Stopping old cluster...
Disabling automatic startup of old cluster...
Starting upgraded cluster on port 5433...
Running finish phase upgrade hook scripts ...
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
pg_dropcluster 16 test
Ver Cluster Port Status Owner Data directory Log file
16 test 5434 down postgres /var/lib/postgresql/16/test /var/log/postgresql/postgresql-16-test.log
Ver Cluster Port Status Owner Data directory Log file
17 test 5433 online postgres /var/lib/postgresql/17/test /var/log/postgresql/postgresql-17-test.log
# pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
16 test 5434 down postgres /var/lib/postgresql/16/test /var/log/postgresql/postgresql-16-test.log
17 test 5433 online postgres /var/lib/postgresql/17/test /var/log/postgresql/postgresql-17-test.logNow, let’s use the “pg_upgrade –link” method when running pg_upgradecluster. Let’s delete the cluster we upgraded, reopen our old test cluster, and then run the pg_upgradecluster command with the “link” method and try the “parallel” and “rename” options.
# pg_dropcluster 17 test --stop
# pg_ctlcluster start 16 test
# pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
16 test 5434 online postgres /var/lib/postgresql/16/test /var/log/postgresql/postgresql-16-test.log
# pg_upgradecluster 16 test --method link --jobs 2 --rename test17
Stopping old cluster...
Creating new PostgreSQL cluster 17/test17 ...
/usr/lib/postgresql/17/bin/initdb -D /var/lib/postgresql/17/test17 --auth-local peer --auth-host scram-sha-256 --no-instructions --encoding UTF8 --lc-collate en_US.UTF-8 --lc-ctype en_US.UTF-8 --locale-provider libc
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/17/test17 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Copying old configuration files...
Copying old start.conf...
Copying old pg_ctl.conf...
Running init phase upgrade hook scripts ...
/usr/lib/postgresql/17/bin/pg_upgrade -b /usr/lib/postgresql/16/bin -B /usr/lib/postgresql/17/bin -p 5434 -P 5433 -d /etc/postgresql/16/test -D /etc/postgresql/17/test17 --link -j 2
Finding the real data directory for the source cluster ok
Finding the real data directory for the target cluster ok
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking data type usage ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Setting locale and encoding for new cluster ok
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_xact to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
ok
Adding ".old" suffix to old global/pg_control ok
If you want to start the old cluster, you will need to remove
the ".old" suffix from /var/lib/postgresql/16/test/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.
Linking user relation files
ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to delete old cluster ok
Checking for extension updates ok
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
/usr/lib/postgresql/17/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
pg_upgradecluster: pg_upgrade output scripts are in /var/log/postgresql/pg_upgradecluster-16-17-test17.WyLf
Disabling automatic startup of old cluster...
Starting upgraded cluster on port 5434...
Running finish phase upgrade hook scripts ...
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
pg_dropcluster 16 test
Ver Cluster Port Status Owner Data directory Log file
16 test 5433 down postgres /var/lib/postgresql/16/test /var/log/postgresql/postgresql-16-test.log
Ver Cluster Port Status Owner Data directory Log file
17 test17 5434 online postgres /var/lib/postgresql/17/test17 /var/log/postgresql/postgresql-17-test17.log
# pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
16 test 5433 down postgres /var/lib/postgresql/16/test /var/log/postgresql/postgresql-16-test.log
17 test17 5434 online postgres /var/lib/postgresql/17/test17 /var/log/postgresql/postgresql-17-test17.log
You can also see the pre and post operations performed by pg_upgradecluster along with the upgrade operation in the command output. Check out this warning in the output : Because “link” mode was used, the old cluster cannot be started safely once the new cluster has been started.
Regardless of the method, the two most important points in upgrade processes are correct preparation and creating a fallback plan. In the correct preparation stage, it is very important to test all applications running on the database on the target version of the database, and also to test and document the upgrade steps. The fallback plan is a preparation against serious problems that may occur during or after the upgrade process. In cases where the database cannot be transferred to the new version properly or the application functions do not work correctly in the new version and there is no chance of fixing it in a short time, it is necessary to be able to switch to the old version without losing data. For this, we should create and test our fallback plan before the upgrade.
Bir yanıt yazın