Introduction:

The forte of pglogical is the ability to logically replicate without any downtime. This extends from an entire database to a single table or down further to even a few rows. The major intent of this document is to upgrade 9.4 (and above) PostgreSQL instances to PostgreSQL10 with less or no downtime.

Prior to proceeding any further, it would be worth revisiting the core concepts of replication. As you may be aware, there are two types of replication – physical replication and logical replication. Physical replication is a bit-for-bit copy of the master server. Changes made in the primary DB are recorded in the write ahead logs (WALs) and shipped to the standby server, where these binary transactions are applied. The WALs determine what the change is, what bytes to add and in which file the respective change needs to be done. The sequel of this is the standby DB – a perfect copy of the primary DB.

Logical replication uses the same information that is in the WALs. However, instead of directly applying the byte changes these are translated back into logical statements. These statements are then executed on the target database. Taking a look at decoded WAL file, we get to know the number of rows would be inserted or the records that are going to be updated.

Installation:

Install the appropriate rpm repository as per your distribution.

For Fedora 25: yum install http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-fedora-1.0-3.noarch.rpm

For RHEL/CentOS 6 and 7: yum install http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-rhel-1.0-3.noarch.rpm

Once the repository is installed, proceed to install the matching pglogical version.

PostgreSQL 9.4 version: yum install postgresql94-pglogical

PostgreSQL 9.5 version: yum install postgresql95-pglogical

PostgreSQL 9.6 version: yum install postgresql96-pglogical

PostgreSQL 10 version: yum install postgresql10-pglogical

Configuration of postgresql.conf parameters: On Source or Provider Node (PostgreSQL 9.4 and above): The below parameters need to modified in the postgresql.conf file of the source machine.

wal_level = 'logical' 
max_worker_processes = 10 # one per database needed on provider node 
# one per node needed on subscriber node 
max_replication_slots = 10 # one per node needed on provider node 
max_wal_senders = 10 # one per node needed on provider node 
shared_preload_libraries = 'pglogical'

*On Destination or Subscriber Node (PostgreSQL 10): * The below parameters need to modified in the postgresql.conf file of the target machine.

max_worker_processes = 10 # one per database needed on provider node # one per node needed on subscriber node shared_preload_libraries = ‘pglogical’

*Configuration of pg_hba.conf parameters: * Allow replication connections from localhost on each node. To allow connection between the source and the target machines, add each machine’s IP address in the pg_hba.conf of the other node i.e. IP of the provider node in the subscriber and the IP of the subscriber in the provider node respectively.

host replication all 127.0.0.1/32 md5 
host DB_name User <IP> md5

To take into effect the new values made to the postgresql.conf file, restart both the postgresql instances.

Creation of pglogical extensions pglogical extensions should be created on both the source and destination servers.

---extensions should be created in all nodes.

CREATE EXTENSION pglogical_origin; ## For PostgreSQL 9.4 version only 
CREATE EXTENSION pglogical; ## On all nodes

Creation of provider or publisher node (on source) and creating replication set

--- creating provider node

SELECT pglogical.create_node(node_name := 'provider_source', 
dsn := 'host=127.0.0.1 port=5433 dbname=test' );

--- adding tables to REPLICATION SET

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

SELECT pglogical.create_replication_set('inventory');

Creation of subscriber node (on target) and creating subscription

-- create subscriber node

SELECT pglogical.create_node(node_name := 'subscriber_target', 
dsn := 'host=127.0.0.1 port=5432 dbname=test' );

-- creation of subscription - to start synchronization 
-- and replication process in the background:

SELECT pglogical.create_subscription( subscription_name := 'subscription_source', 
provider_dsn := 'host=127.0.0.1 port=5433 dbname=test' );

**PREREQUISITES: ** It should be noted that when trying to create a replication set on the provider node (or source), the tables that form the replication set should have primary keys. However, if the table does not have primary key, the below error occurs:

test=# SELECT pglogical.replication_set_add_all_tables('default', ARRAY['test4logical']);
ERROR: table invoice_details cannot be added to replication set default 
DETAIL: table does not have PRIMARY KEY and given replication set 
is configured to replicate UPDATEs and/or DELETEs 
HINT: Add a PRIMARY KEY to the table

Use Cases: Using pglogical, let us see how we can upgrade our current DB version to PostgreSQL10. For demonstration purposes, we have two environments i.e. source and target. Our source is a postgresql 9.4 instance and the target is a postgresql 10 instance. The following would the steps that one need to follow for pglogical upgrade.

Test Environment: Operating System: CentOS 7 Postgres Version: Source – pg9.4; Destination – pg10 pglogical Version: 2.1

Scenario 1: Upgrading database from PG 9.4 to PG 10 * We have PostgreSQL 9.4 database *dvdrental running on the local machine on port #5433. This database has around 15 tables. We shall see how we can use pglogical to upgrade our DB to PostgreSQL 10.

Let us connect to the database dvdrental.

-- connecting to the source database 
-bash-4.2$ /usr/pgsql-9.4/bin/psql -d dvdrental -p 5433

-- list of all the tables in this database 
dvdrental=# \dt+
                          List of relations
 Schema |     Name      | Type  |  Owner   |    Size    | Description
--------+---------------+-------+----------+------------+-------------
 public | actor         | table | postgres | 40 kB      |
 public | address       | table | postgres | 88 kB      |
 public | category      | table | postgres | 8192 bytes |
 public | city          | table | postgres | 64 kB      |
 public | country       | table | postgres | 8192 bytes |
 public | customer      | table | postgres | 96 kB      |
 public | film          | table | postgres | 464 kB     |
 public | film_actor    | table | postgres | 264 kB     |
 public | film_category | table | postgres | 72 kB      |
 public | inventory     | table | postgres | 224 kB     |
 public | language      | table | postgres | 8192 bytes |
 public | payment       | table | postgres | 888 kB     |
 public | rental        | table | postgres | 1224 kB    |
 public | staff         | table | postgres | 16 kB      |
 public | store         | table | postgres | 8192 bytes |
(15 rows)

Now, to start the pglogical upgrade process, we need to create a ‘provider_node’ on the source DB and create a ‘replication_set’ of the tables.

-- creating a provider node 
SELECT pglogical.create_node(node_name := 'provider_node', 
dsn := 'host=127.0.0.1 port=5433 dbname=test' );

-- adding all the tables in the DB to the replication set 
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

Here ‘public’ is the schema. In case of multiple schemas, add them to the list separated by commas.

Once the provider node has been created and all tables have been added to the replication set, pglogical background worker manager and pglogical supervisor check to see if there is any subscriber node available.

Now, on the target node, we first need to create the roles, schemas, and tables as per the source database. To accomplish that, first create a database called dvdrental on the target node.

postgres=# create database dvdrental;
CREATE DATABASE

Next, take the globals from the source database and run it on the target machine.

--- On the Source Node /usr/pgsql-10/bin/pg_dumpall -p 5433 --globals-only > /tmp/globals.sql

--- On the Target Node /usr/pgsql-10/bin/psql -d dvdrental -p 5432 -f /tmp/globals.sql

Then, take a schema dump of the database on the source machine and restore is on the target server. When working with upgrades, it is always advisable to take dump using the higher version binaries. In our case, we are taking pg_dump using PostgreSQL 10 binaries.

— On the Source Node /usr/pgsql-10/bin/pg_dump -d dvdrental -p 5433 -s > /tmp/tabs_structure.sql

— On the Target Node /usr/pgsql-10/bin/psql -d dvdrental -p 5432 -f /tmp/tabs_structure.sql

Next, create a “subscriber node” and start the subscription.

-- creating the subscriber node 
SELECT pglogical.create_node(node_name := 'subscriber_target', 
dsn := 'host=127.0.0.1 port=5432 dbname=test' );

-- creation of subscription - to start synchronization 
-- and replication process in the background: 
SELECT pglogical.create_subscription( subscription_name := 'subscription1', 
provider_dsn := 'host=127.0.0.1 port=5433 dbname=test');

Once subscriber node is created and subscription is started, the WALs are shipped and the SQL statements are applied in the standby db.

-- connecting to the target database -bash-4.2$ /usr/pgsql-10/bin/psql -d dvdrental -p 5432

-- list of tables on the target database 
dvdrental=# \dt+
                          List of relations
 Schema |     Name      | Type  |  Owner   |    Size    | Description
--------+---------------+-------+----------+------------+-------------
 public | actor         | table | postgres | 40 kB      |
 public | address       | table | postgres | 88 kB      |
 public | category      | table | postgres | 8192 bytes |
 public | city          | table | postgres | 64 kB      |
 public | country       | table | postgres | 8192 bytes |
 public | customer      | table | postgres | 96 kB      |
 public | film          | table | postgres | 464 kB     |
 public | film_actor    | table | postgres | 264 kB     |
 public | film_category | table | postgres | 72 kB      |
 public | inventory     | table | postgres | 224 kB     |
 public | language      | table | postgres | 8192 bytes |
 public | payment       | table | postgres | 888 kB     |
 public | rental        | table | postgres | 1224 kB    |
 public | staff         | table | postgres | 16 kB      |
 public | store         | table | postgres | 8192 bytes |
(15 rows)

Once the entire data is copied over to the Postgresql10 server, the older 9.4 instance can be stopped and all the application connections can be pointed to the new instance. Without any downtime, upgradation can be done seamlessly.

**Scenario 2: ** Upgrading database having partitioned tables from PG 9.4 to PG 10 In the above example, we saw how databases with simple tables can be upgraded to pg10. Upgrading databases constituting huge tables would be a bit complex. Postgresql 9.4 saw the introduction of inheritance and partitioning techniques for management of huge tables. To benefit from PostgreSQL 10’s native partitioning, refer to our earlier post Towards 10’s Native Partitioned Tables. Once the partitioned table structures are created in pg10, follow the above method for upgrading using pglogical.

**Scenario 3: ** Single table replication using pglogical There might be certain situations wherein a single table has a higher number of reads and thereby puts undue load on the server. Undoubtedly, pglogical could provide the respite. The table can be replicated to another physical machine and have all the “reads” diverted there. All this can be done without any downtime.

Let us say, we have a table invoice_details. Let us see how we can create a replication set of only this table and then start the subscription on the standby.

test=# \dt+
                             List of relations
    Schema    |      Name       | Type  |  Owner   |  Size   | Description
--------------+-----------------+-------+----------+---------+-------------
 test4logical | invoice_details | table | postgres | 8192 bytes |
(1 row)

test=# select * from invoice_details;
 invoice_num | customer_name | product_details | date_of_invoice
-------------+---------------+-----------------+-----------------
           1 | Roger Sears   | Amazon Kindle   | 2017-10-10
           2 | Daphne Moore  | Sony XBox       | 2017-10-10
           3 | David Parker  | Samsung Galaxy  | 2017-10-10
(3 rows)

test=# SELECT pglogical.create_node(node_name := 'provider_node', 
dsn := 'host=127.0.0.1 port=5433 dbname=test' );
 create_node
-------------
  2811903744
(1 row)

-- creating a replication set

test=# select pglogical.create_replication_set('single_table');
 create_replication_set
------------------------
             2557160431
(1 row)

-- adding a single table to the replication set 

test=# SELECT pglogical.replication_set_add_table ('single_table','test4logical.test');
 replication_set_add_table
---------------------------
 t
(1 row)

Can I use pglogical to upgrade/replicate postgres in RDS environment ?

The answer, at the present time, is “no.” The first reason being, lack of super user privileges to modify some of the parameters like ‘shared_preload_libraries,’ ‘wal_level’ etc. Secondly, it requires installation of pglogical extension, which AWS RDS does not support at this moment. Since RDS is a sealed system, they cannot allow user supplied code. So unless RDS decides to add a modified pglogical as a supported tool on the platform you cannot use it.

Downsides of pglogical:

  1. Tables should have a primary key to be added to a replication set. This means that if a table does not have a primary key, it cannot be replicated using pglogical.
  2. Temporary tables and unlogged tables cannot be replicated.
  3. Only one database can be added to a provider/subscriber relationship at any given time. In a PostgreSQL cluster with multiple databases, configuring replication for all databases at once is not possible.
  4. Automatic DDL replication is not supported. However. using the function pglogical.replicate_ddl_command can manage the DDLs on the provider and subscriber at a consistent point.
  5. For other limitations, please refer to pglogical’s official documentation.

References: Official Documentation of pglogical