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.

Using pg_dump/pg_dumpall

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.

Using pg_upgrade

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)

Column descriptions:

• 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.

Conclusion

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.