Running PostgreSQL 9.6? Time for a major upgrade!

In the last blog post, we mentioned the latest PostgreSQL Minor release of PostgreSQL 14.1, 13.5, 12.9, 11.14, 10.19, and 9.6.24.

An important note is that this is the final release of the PostgreSQL 9.6 major version.

The PostgreSQL Global Development Group supports a major version for 5 years after its initial release. After its five-year anniversary, a major version will have one last minor release containing any fixes and will be considered end-of-life (EOL) and no longer supported. For more info, read the Versioning policy.

We would like to talk about the major upgrade options available with less downtime.

Progressive approach

Before delving into any PostgreSQL major upgrade, it is important to carry out a progressive approach.

Testing the upgrade: Using a Logical replication solution, like, for ex: pglogical, you can do this in two steps which allows you to properly test applications working with the upgraded version of PostgreSQL,
as well as the switch operation itself. Meaning, you could have both your PosgreSQL 9.6 and the latest version up and running at the same point in time.

Near zero cut-over time: By using logical replication, you can have a near-zero cut-over time switch to the latest version, therefore reducing the risk of failure and issues.

Reducing technical debt: It is recommended to utilize the upgrade downtime/maintenance window to install an updated minor version of the Operating system and of PostgreSQL on a new server.

The pglogical way

The pglogical extension can be used to implement logical replication between PostgreSQL version 9.6 and any latest PostgreSQL version.

This method guarantees a very short downtime that just depends on the time needed to redirect all the connections to the new instance, once the subscriber has caught up with the old instance and replication is in streaming.

It is recommended to use a different database instance as the replication target.

pglogical will replicate from the current cluster to a new PostgreSQL cluster, also composed of one master and one physical replica, that will work in parallel with the current 9.6 master.

The steps would be:

1. Prepare the subscriber
   • Take a pg_dumpall of your PostgreSQL 9.6 instance
   • If there are any tables without Primary key, pglogical cannot logically replicate them. So, take a dump of the tables that don’t have a PKey.
   • Restore the dumps and your target instance is partially ready
2. Setup the pglogial provider
   • Make sure you have the minimal configuration parameters set on postgresql.conf file

wal_level = logical
max_wal_senders = 10
max_replication_slots = 10
shared_preload_libraries = 'pglogical'

• Create pglogical extension and node
• Add tables and sequences to the replication, the following SQLs would help:
  
  Adding tables:

SELECT pglogical.replication_set_add_table(
set_name := 'default',
relation := c.oid,
synchronize_data := true
)
FROM
pg_class c
JOIN
pg_namespace n
ON n.oid = c.relnamespace
WHERE
c.relkind = 'r'
AND EXISTS (
SELECT 1
FROM pg_constraint con
WHERE con.conrelid = c.oid
AND con.contype = 'p'
)
AND n.nspname <> ALL (
ARRAY [
'pg_catalog',
'information_schema',
'pglogical',
'pglogical_origin'
]
);

Adding sequences to replication:

SELECT pglogical.replication_set_add_all_sequences(
set_name := 'default',
schema_names := '{public, bo}'::text[],
synchronize_data := true
);

The remaining steps would get the publisher and subscriber communicate and logical replication

• Setup the pglogical subscriber
• Monitor logical replication on database

SELECT * FROM pglogical.show_subscription_status();
SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL);

Now, the actual cutover from PostgreSQL 9.6 to the Latest target version

• Make sure applications are stopped on the provider
• Make sure there is no replication lag on the logical connection
• Dump and restore of tables without PK from the subscriber onto new instance
• Synchronize database sequence values
• Make sure everything works as expected
• Switch the database connection from PG9.6 to the latest server
• Cleanup replication and old server

While working with a couple of our customers, we had observed issues with Database sequence values not synching 100%. Here’s an alternate SQL approach we took using pg_dump and psql restore.

Execute the pg_dump command on PG13 instance and you will get an output file sequence_dump.sql

\pset tuples_only
\o pgdump_command.out
SELECT 'pg_dump -h <pg96-host> -d <database> -U postgres -c -f sequence_dump.sql '||string_agg('-t ' || n.nspname||'.'||c.relname, ' ')
 as pg_dump_statement
 FROM pg_catalog.pg_class c
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind IN ('S','')
       AND n.nspname <> 'pg_catalog'
       AND n.nspname <> 'information_schema'
       AND n.nspname !~ '^pg_toast'
   AND pg_catalog.pg_table_is_visible(c.oid);
\o

For restoring, simple call psql and the file, like:

psql -d <dbname> -f sequence_dump.sql

Note: PgBouncer could also be used to reduce downtime when pointing applications to the new server.

Using pg_upgrade

Alternatively, an in-place upgrade from 9.6 to any latest version is also a possibility. There’s a definite downtime involved in this approach.

Well, how does pg_upgrade work?

The pg_upgrade utility works by creating a new set of database catalog tables, and then creating the old objects again in the new tables using the same identifiers as before. The pg_upgrade utility works easily because the data block format hasn’t changed between some releases.

How to go about pg_upgrade?

• Set up a test server with the PG9.6 on it. Restore one of your backups in it. Upgrade that system to the new release to verify that there are no conflicts from software dependencies.
• Test your application. Make sure you identify and test each add-in PostgreSQL module you were using to confirm that it still works at the new release level.
• Back up your production server, before even thinking about pg_upgrade
• Install new version of PostgreSQL on the production server, and create a new database server.
• Shut down both the PG9.6 and the latest database instance
• Run pg_upgrade
  • For example: pg_upgrade -v -b /usr/pgsql-9.6/bin -B /usr/pgsql-13/bin -d /pg96/data -D /pg13/data
• Start up the new database server and immediately run a server-wide ANALYZE operation using vacuumdb --analyze-in-stages
• Run through your Application validation and sanity tests
• If everything works fine, re-enable wide access to the database server. Restart the applications.
  and boom!
  

Talk to us if you need assistance upgrading your PostgreSQL 9.6 databases to the latest version with minimal downtime.