Upgrading to PostgreSQL13
In a recent blog about what is new in PostgreSQL 13, we reviewed some of the new features of this version, but now, let’s see how to upgrade to be able to take advantage of all these mentioned functionalities.
Upgrading to PostgreSQL 13
If you want to upgrade your current PostgreSQL version to this new one, you have three main native options to perform this task.
Pg_dump/pg_dumpall: It is a logical backup tool that allows you to dump your data and restore it in the new PostgreSQL version. Here you will have a downtime period that will vary according to your data size. You need to stop the system or avoid new data in the primary node, run the pg_dump, move the generated dump to the new database node, and restore it. During this time, you can’t write into your primary PostgreSQL database to avoid data inconsistency.
Pg_upgrade: It is a PostgreSQL tool to upgrade your PostgreSQL version in-place. It could be dangerous in a production environment and we don’t recommend this method in that case. Using this method you will have downtime too, but probably it will be considerably less than using the previous pg_dump method.
Logical Replication: Since PostgreSQL 10, you can use this replication method which allows you to perform major version upgrades with zero (or almost zero) downtime. In this way, you can add a standby node in the last PostgreSQL version, and when the replication is up-to-date, you can perform a failover process to promote the new PostgreSQL node.
So, let’s see these methods one by one.
In case downtime is not a problem for you, this method is an easy way for upgrading.
To create the dump, you can run:
$ pg_dumpall > dump_pg12.out
Or to create a dump of a single database:
$ pg_dump world > dump_world_pg12.out
Then, you can copy this dump to the server with the new PostgreSQL version, and restore it:
$ psql -f dump_pg12.out postgres
Keep in mind that you will need to stop your application or avoid writing in your database during this process, otherwise, you will have data inconsistency or a potential data loss.
First, you will need to have both the new and the old PostgreSQL versions installed on the server.
$ rpm -qa |grep postgres postgresql13-contrib-13.3-2PGDG.rhel8.x86_64 postgresql13-server-13.3-2PGDG.rhel8.x86_64 postgresql13-libs-13.3-2PGDG.rhel8.x86_64 postgresql13-13.3-2PGDG.rhel8.x86_64 postgresql12-libs-12.7-2PGDG.rhel8.x86_64 postgresql12-server-12.7-2PGDG.rhel8.x86_64 postgresql12-12.7-2PGDG.rhel8.x86_64 postgresql12-contrib-12.7-2PGDG.rhel8.x86_64
Then, first, you can run pg_upgrade for testing the upgrade by adding the -c flag:
$ /usr/pgsql-13/bin/pg_upgrade -b /usr/pgsql-12/bin -B /usr/pgsql-13/bin -d /var/lib/pgsql/12/data -D /var/lib/pgsql/13/data -c Performing Consistency Checks on Old Live Server ------------------------------------------------ Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for system-defined composite types in user tables ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch 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 *Clusters are compatible*
The flags mean:
-b: The old PostgreSQL executable directory
-B: The new PostgreSQL executable directory
-d: The old database cluster configuration directory
-D: The new database cluster configuration directory
-c: Check clusters only. It doesn’t change any data
If everything looks fine, you can run the same command without the -c flag and it will upgrade your PostgreSQL server. For this, you need to stop your current version first and run the mentioned command.
$ systemctl stop postgresql-12 $ /usr/pgsql-13/bin/pg_upgrade -b /usr/pgsql-12/bin -B /usr/pgsql-13/bin -d /var/lib/pgsql/12/data -D /var/lib/pgsql/13/data ... Upgrade Complete ---------------- Optimizer statistics are not transferred by pg_upgrade so, once you start the new server, consider running: ./analyze_new_cluster.sh Running this script will delete the old cluster's data files: ./delete_old_cluster.sh
When it is completed, as the message suggests, you can use those scripts for analyzing the new PostgreSQL server and deleting the old one when it is safe.
Using Logical Replication
Logical replication is a method of replicating data objects and their changes, based upon their replication identity. It is based on a publish and subscribe mode, where one or more subscribers subscribe to one or more publications on a publisher node.
So based on this, let’s configure the publisher,in this case the PostgreSQL 12 server, as follows.
Edit the postgresql.conf configuration file:
listen_addresses = '*' wal_level = logical max_wal_senders = 8 max_replication_slots = 4
Edit the pg_hba.conf configuration file:
# TYPE DATABASE USER ADDRESS METHOD host all rep1 10.10.10.141/32 md5
Use the subscriber IP address there.
Now, you must configure the subscriber, in this case the PostgreSQL 13 server, as follows.
Edit the postgresql.conf configuration file:
listen_addresses = '*' max_replication_slots = 4 max_logical_replication_workers = 4 max_worker_processes = 8
As this PostgreSQL 13 will be the new primary node soon, you should consider adding the wal_level and archive_mode parameters in this step, to avoid a new restart of the service later.
wal_level = logical archive_mode = on
These parameters will be useful if you want to add a new replica or for using PITR backups.
Some of these changes require a server restart, so restart both publisher and subscriber.
Now, in the publisher, you must create the user to be used by the subscriber to access it. The role used for the replication connection must have the REPLICATION attribute and, in order to be able to copy the initial data, it also need the SELECT privilege on the published table:
world=# CREATE ROLE rep1 WITH LOGIN PASSWORD '********' REPLICATION; CREATE ROLE world=# GRANT SELECT ON ALL TABLES IN SCHEMA public to rep1; GRANT
Let’s create the pub1 publication in the publisher node, for all the tables:
world=# CREATE PUBLICATION pub1 FOR ALL TABLES; CREATE PUBLICATION
As the schema is not replicated, you must take a backup in your PostgreSQL 12 and restore it in your PostgreSQL 13. The backup will only be taken for the schema since the information will be replicated in the initial transfer.
In PostgreSQL 12, run:
$ pg_dumpall -s > schema.sql
In PostgreSQL 13, run:
$ psql -d postgres -f schema.sql
Once you have your schema in PostgreSQL 13, you need to create the subscription, replacing the values of host, dbname, user, and password with those that correspond to your environment.
world=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=10.10.10.140 dbname=world user=rep1 password=********' PUBLICATION pub1; NOTICE: created replication slot "sub1" on publisher CREATE SUBSCRIPTION
The above will start the replication process, which synchronizes the initial table contents of the tables in the publication and then starts replicating incremental changes to those tables.
To verify the created subscription you can use the pg_stat_subscription catalog. This view will contain one row per subscription for the main worker (with null PID if the worker is not running), and additional rows for workers handling the initial data copy of the subscribed tables.
world=# SELECT * FROM pg_stat_subscription; -[ RECORD 1 ]---------+------------------------------ subid | 16421 subname | sub1 pid | 464 relid | received_lsn | 0/23A8490 last_msg_send_time | 2021-07-23 22:42:26.358605+00 last_msg_receipt_time | 2021-07-23 22:42:26.358842+00 latest_end_lsn | 0/23A8490 latest_end_time | 2021-07-23 22:42:26.358605+00
To verify when the initial transfer is finished you can check the srsubstate variable on pg_subscription_rel catalog. This catalog contains the state for each replicated relation in each subscription.
world=# SELECT * FROM pg_subscription_rel; srsubid | srrelid | srsubstate | srsublsn ---------+---------+------------+----------- 16421 | 16408 | r | 0/23B1738 16421 | 16411 | r | 0/23B17A8 16421 | 16405 | r | 0/23B17E0 16421 | 16402 | r | 0/23B17E0 (4 rows)
srsubid: Reference to subscription.
srrelid: Reference to relation.
srsubstate: State code: i = initialize, d = data is being copied, s = synchronized, r = ready (normal replication).
srsublsn: End LSN for s and r states.
When the initial transfer is finished, you have everything ready to point your application to your new PostgreSQL 13 server.
As you can see, PostgreSQL has different options to upgrade, depending on your requirements and downtime tolerance.
No matter what kind of technology you are using, keeping your database servers up to date by performing regular upgrades is a necessary but difficult task, as you need to make sure that you won’t have data loss or data inconsistency after upgrading. A detailed and tested plan is the key here, and of course, it must include a rollback option, just in case.
Subscribe to get our best and freshest content