I recently helped a customer upgrade their PostgreSQL instance from 9.4.x on RHEL to 10.x on Ubuntu. While it initially sounded daunting, the use of pglogical and some planning actually made it rather straightforward. While there’s nothing new or original in this post, I still felt compelled to write it up both for posterity’s sake and for anyone else that might find the info useful as an example in their own endeavors.
pglogical is a logical replication system implemented entirely as a PostgreSQL extension. Fully integrated, it requires no triggers or external programs. This makes it faster than Slony, Londiste, et al. It is also (roughly) the basis upon which logical replication in Pg 10 core is built.
pglogical is available from 2ndQuadrant in both a YUM repository for RedHat-based distros and in an APT repository for Debian-based distros. It will need to be installed on both the source (old Pg version) and destination servers (new Pg version).
The instructions for installing their repo and the needed packages can be found here.
You will need to adjust the postgresql.conf file to accommodate pglogical. On both the source and destination servers, do the following:
$ echo "include 'pglogical.conf'" >> $PGDATA/postgresql.conf
$ echo "wal_level = 'logical'" >> $PGDATA/pglogical.conf
$ echo "max_worker_processes = 10" >> $PGDATA/pglogical.conf
$ echo "max_replication_slots = 10" >> $PGDATA/pglogical.conf
$ echo "max_wal_senders = 10" >> $PGDATA/pglogical.conf
$ echo "shared_preload_libraries = 'pglogical'" >> $PGDATA/pglogical.conf
NOTE: If you already have one or more values in shared_preload_libraries, simply append pglogical to the list of values already there.
Logical replication doesn’t work without primary keys. Identify all tables that do not have one:
SELECT
n.nspname as schema,
c.relname as table
FROM
pg_class c
JOIN
pg_namespace n
ON n.oid = c.relnamespace
WHERE
c.relkind = 'r'
AND NOT EXISTS (
SELECT 1
FROM pg_constraint con
WHERE con.conrelid = c.oid
AND con.contype = 'p'
)
AND n.nspname <> ALL (
ARRAY [
'pg_catalog',
'sys',
'dbo',
'information_schema'
]
);
On both the source and destination Pg instances, create the pglogical extension in every database you wish to replicate:
CREATE EXTENSION pglogical;
NOTE: On Pg 9.4 only you will need to CREATE EXTENSION pglogical_origin; FIRST.
The pglogical tool runs at the database level which means that global objects like roles are not copied. Therefore, you need to ensure these objects are created yourself.
On the source Pg server:
$ pg_dumpall -g -f globals.sql
Then copy globals.sql to the destination server and run:
$ psql -f globals.sql
At this time, pglogical doesn’t replicate DDL, so it is necessary to ensure that both the source and destination have matching schema object definitions before attempting to replicate.
As such, for each source database that you want to replicate, you need to run a ‘schema only’ dump:
$ pg_dump -Fc -s -f dbname_schema.dmp dbname
Now copy the dbname_schema.dmp file(s) to the destination server, and run for each database:
$ pg_restore -d dbname dbname_schema.dmp
We’ll need a user that has the replication permission for this all to work, so let’s create one:
CREATE ROLE pglogical LOGIN REPLICATION SUPERUSER ENCRYPTED PASSWORD 'secret';
Do this on both the source and destination Pg instances.
Tweak the pg_hba.conf on both the source and destination Pg instances to allow the replication user to connect:
local replication pglogical trust
host replication pglogical 0.0.0.0/0 md5
local dbname pglogical trust
host dbname pglogical 0.0.0.0/0 md5
NOTE: Make sure to edit 0.0.0.0/0 to match your actual CIDR or IP address and dbname to match the db you wish to replicate.
Now, we’re ready to actually setup and start the replication. First, we need to SIGHUP the postmaster so it sees all the config changes we made on both the source and target Pg instances:
$ pg_ctl -D $(ps -efw|grep -- "[p]ost.*-D"|cut -d\- -f2|cut -d" " -f2) reload
On the source Pg instance, we need to create a publication to ‘push’ the data to the new instance:
SELECT pglogical.create_node(node_name := 'dbname_provider', dsn := 'host=127.0.0.1 port=5432 dbname=test user=pglogical');
Adjust the port= and dbname= parameters to match your source Pg instance. If replicating more than one database, repeat this command for each database, changing dbname and dbname_provider accordingly.
Now that we have a publication channel, we need content to publish. Let’s add that now:
1: Add all your tables:
SELECT pglogical.replication_set_add_all_tables('default', '{public}'::text[]);
2: Add all your sequences:
SELECT pglogical.replication_set_add_all_sequences(set_name := 'default', schema_names := '{public}'::text[], synchronize_data := true );
Obviously, you should change public in both the above if you are using a different schema for your objects. If you are using multiple schemas, simply repeat the above and change public appropriately.
NOTE: The nextval of sequences will be synced every 60-70s roughly.
Now that we have a publication channel and its content defined, we need to setup a subscriber on the target Pg instance to consume the channel:
SELECT pglogical.create_node(node_name := 'subscriber', dsn := 'host=127.0.0.1 port=5432 dbname=test user=pglogical');
Adjust the dbname= parameter to match your target Pg instance. If replicating more than one database, repeat this command for each database.
Now, tell the subscriber what to subscribe to:
SELECT pglogical.create_subscription(subscription_name := 'subscription', provider_dsn := 'host=172.28.173.18 port=5432 dbname=test user=pglogical', replication_sets := '{default}'::text[] );
Adjust host=, port=, and dbname= parameters to match your source Pg instance. If replicating more than one database, repeat this command for each database, changing dbname and subscription_name accordingly.
At this point, data should be replicating and (if not already) it will catch up to ‘current’ quickly. Once caught up, replication will maintain sync between the source and target instances in almost real time. You can easily determine the current state of pglogical by issuing this SQL on the subscriber:
SELECT subscription_name, status FROM pglogical.show_subscription_status();
If the query returns initializing then it is copying the original source data to the destination. If the query returns replicating then the initial synchronization has completed and replicating is now happening in real time as data changes.
When ready, you can simply stop any applications pointing at the source Pg instance, wait a few minutes to ensure replication drains any outstanding items, force an update of your sequences:
SELECT pglogical.synchronize_sequence( seqoid ) FROM pglogical.sequence_state;
and then re-point your applications at the target instance.
Post-upgrade, if you wish to clean everything up, simply:
1: Remove the subscription:
SELECT pglogical.drop_subscription('subscription', true);
2: Remove the subscriber:
SELECT pglogical.drop_node('subscriber', true);
3: Remove the extension:
DROP EXTENSION pglogical CASCADE;
4: Remove the user:
DROP ROLE pglogical;
5: Remove any pglogical lines in pg_hba.conf
6: Remove $PGDATA/pglogical.conf
7: Reload PostgreSQL
8: Remove the OS packages using yum or apt