Create a pglogical subscriber from a pg_basebackup for online upgrade

Martin Marques
Martin Marques

In some cases where there's a very large database involved in an online upgrade plan with pglogical, it may be interesting to seed the subscriber from a pg_basebackup copy to speed up the initial sync (The subscriber will be the node you will upgrade the database). With this process you will also require less resources for the initialization.

This describes the process of setting up a node seeded from pg_basebackup to be a subscriber on a newer version of postgres.

Note1: This only works with 9.5 or better. Minor changes can be made for it to work on 9.4 as well.

Note2: The example below is for the upgrade of a cluster of 1 database from 9.6 to 11. Some simple changes can make it extensible to a list of databases

Note3: Examples below are for Red Hat based installations. Only paths for binaries and data directories would need to be amended for other distributions.

On the source node, the current primary, connect to the database which is meant to be migrated, in the case below the database to be migrated is called scipio.

The examples below show how to upgrade from PostgreSQL 9.6 to 11 using pglogical 3.6.18. It should work just fine if using pglogical 2.3.x.

The source node, where the production 9.6 database runs will be karat, and the destination node, initially a 9.6 replica which we will upgrade to the final version of 11, will be klutz. This is to have reference for the instructions below.

Prepare the upgrade hosts

You could upgrade on the same DB server (this requires enough space to host both the old and new cluster), or use this process that does a logical upgrade to migrate to a new server. Our examples will use separate nodes, which bring the possibility of also upgrading the Operating System.

Either way, the host where you're upgrading to must have both versions of PostgreSQL (9.6 and 11 in our examples here) with all the same extensions used.

On the node we'll be upgrading to install PostgreSQL 11. This node must also have all the PostgreSQL 11 extensions and add-on packages (including pglogical) used in the current production environment. Match extension versions if possible. If you're provisioning on a new separate host or VM, you must also ensure that you have installed PostgreSQL 9.6 packages on the destination host, again with all the same extensions as the current production host.

Prepare the current production host

Connect to the database of the currently running primary 9.6 node, in our examples it's karat which you'll be upgrading from, and create the pglogical extension there. After that create the pglogical node just like in the SQL code below:


SELECT pglogical.create_node(
node_name := 'datafiles',
dsn := 'host=karat port=5432 dbname=datafiles'

Add all tables from the public schema to the default replication set:

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

If you have tables on other schemata, you can add them the the ARRAY above. For example, if there's a schema appfiles, you should use this statement call:

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public', 'appfiles']);

Create a 9.6 replica on the destination node

Now, on the upgrade-destination, in the examples from this article it's the klutz node, take a base backup from the production node karat using pg_basebackup using PostgreSQL 9.6 binaries:

/usr/pgsql-9.6/bin/pg_basebackup -h karat -D ~postgres/9.6/data/ -X stream -P -v -R

Start new replica node using the same tools you'd usually use to start a replica and check that it's streaming:

/usr/pgsql-9.6/bin/pg_ctl -D ~postgres/9.6/data/ -w start

Verify it caught up with the primary. You can use this query on the primary node karat to verify that the lag in contained:

pg_xlog_location_diff(pg_current_xlog_insert_location(), flush_location) AS lag_bytes,
pid, application_name
FROM pg_stat_replication;

Turn the physical replica into a logical subscriber

At this point we have a primary node with a replica which will be our candidate for upgrade to 11. We can now use pglogical_create_subscriber to convert the PostgreSQL 9.6 physical replica running on klutz into a pglogical subscriber, still running on PostgreSQL 9.6.

But the replica has to be stopped before running pglogical_create_subscriber. Make sure it's not running before creating the subscriber.

# Stop postgres replica on klutz
/usr/pgsql-9.6/bin/pg_ctl -D ~postgres/9.6/data/ stop -m fast

# Run pglogical_create_subscriber to make this replica a logical subscriber
/usr/pgsql-9.6/bin/pglogical_create_subscriber --pgdata ~postgres/9.6/data/ \
--subscriber-name='datafiles_consumer' \
--subscriber-dsn='host=klutz port=5432 dbname=datafiles' \
--provider-dsn='host=karat port=5432 dbname=datafiles' -v 

Upgrade the 9.6 subscriber to PostgreSQL 11

At this point we are ready to upgrade the 9.6 instance in klutz which is the subscriber for the database to upgrade from the production environment to version 11 with pg_upgrade.

Prepare the 11 cluster for the upgrade

First initialize an empty PG11 cluster:

/usr/pgsql-11/bin/initdb ~postgres/11/data/

Add pglogical to the configuration file at ~postgres/11/data/postgresql.conf in the shared_preload_libraries entry, as well as any other extensions you are using or want to use that require preloading:

# Enter this in postgresql.conf:
shared_preload_libraries = 'pglogical'

Set any other postgresql.conf parameters you had configured in 9.6, and also copy pg_hba.conf from the 9.6 subscriber's to ~postgres/11/pg_hba.conf (should be identical to the 9.6 primary as it was copied by pg_basebackup).

Backup logical replication origin from 9.6 subscriber

The upgrade using pg_upgrade will need to be performed with logical replication disable, and once it's finished, we'll need to restore replication origin information. For that we'll need to store a copy before doing so.

First disable (not drop!) the subscription in ~postgres/9.6/data cluster on klutz by connecting on this node to the database we're going to upgrade (in this case datafiles) and executing the following statement:

SELECT * FROM pglogical.alter_subscription_disable('datafiles_consumer' , true)

Now we can dump the contents of pg_replication_origin_status on this subscriber node you just disabled using psql (this won't work in PgAdmin etc):

\copy (select * from pg_replication_origin_status) to '~/pg_replication_origin_status.out';

You should have in the current directory a file with the replication origin data. It should look something like this:

1 pgl_datafiles_karat_datafile2ed218d 3/D71A8F88 0/0

We'll need the value from the third column later, that is 3/D71A8F88.

Now stop the logical streaming 9.6 replica on klutz, e.g.:

/usr/pgsql-9.6/bin/pg_ctl -D ~postgres/9.6/data/ stop -m fast

Run pg_upgrade on the 9.6 cluster

Upgrade the database cluster to PostgreSQL 11 using pg_upgrade.

/usr/pgsql-11/bin/pg_upgrade -b /usr/pgsql-9.6/bin/ -B /usr/pgsql-11/bin/ 
-d ~postgres/9.6/data -D ~postgres/11/data/ -v -r

Note that this creates another copy of the database, so allow sufficient disk space to be available on this node, or use the --link option which creates hard links, saving a lot of space. You may also want to do a pre-check run before. See the pg_upgrade manual.

Re-establish logical replication on the upgraded database

Now we have to initialize the logical replication again for the upgraded cluster. For this we'll be using the dumped data from pg_replication_origin_status. In order to do this we will need the PostgreSQL 11 cluster on klutz running, so start node in ~postgres/11/data/:

/usr/pgsql-11/bin/pg_ctl -D ~postgres/11/data/ start

Connect to database you are upgrading, in this example it's datafiles on the PG11 cluster and run the following SQL commands, where pgl_datafiles_karat_datafile2ed218d and 3/D71A8F88 are the external_id and remote_lsn fields respectively from the data we've dumped earlier from pg_replication_origin_status. Use the values from your output, do not run the following verbatim!

SELECT * FROM pg_replication_origin_create('pgl_datafiles_karat_datafile2ed218d');
SELECT * FROM pg_replication_origin_advance('pgl_datafiles_karat_datafile2ed218d', 

Finally enable once again the subscription on the ~postgres/11/data cluster by connecting to the datafiles database on node klutz and executing the following statement:

SELECT * FROM pglogical.alter_subscription_enable('datafiles_consumer')

From this moment on, the datafiles database from the PostgreSQL instance running on node klutz will be receiving replication changes from the production provider we set up at the beginning of the article.

Final step: quick switchover

Once you have a scheduled time the switchover for your application from the 9.6 instance to the new 11 server, all that needs to be done is orchestrate the the change of the conninfo string on all application nodes.

Was this article helpful?

0 out of 0 found this helpful