Logical Replication

 

Logical replication was introduced in PostgreSQL 10 and it is different from streaming replication in many ways as described below:

  1.  Logical replication works at the object level unlike streaming replication which works at the cluster level, so you can replicate individual tables as per requirements such as logging,analysis,backup, etc.
  2. Once you have decided which tables to replicate, you also have the flexibility to decide which operation (INSERT,UPDATE and DELETE) you want to be replicated, by default all operations will be replicated.
  3. WAL segments are preserved in case of master database crash so that as soon as the master is up, preserved WAL segments that have not been replicated because of crash are replicated to the standby database.
  4. Logical replication supports write queries on the standby database.
  5. Logical replication also supports cross version replication unlike streaming replication where both master and standby database need to be of same version.
  6. PostgreSQL 11 also supports replicating TRUNCATE commands which was not supported earlier.
  7. Logical replication is based on publish – subscribe model and one publisher can have multiple subscribers.

Let us now see how to setup logical replication in PostgreSQL 10.

This blog assumes that you have already installed PostgreSQL on two server, running on port 5444 with listen_addresses set to ‘*’ and ssh password-less communication is enabled between master and standby server  for the OS user postgres. We will be replicating “authorities” relation from master to standby.

On Master server

Make below changes in the postgresql.conf file:

In PostgreSQL 10, default value for max_wal_senders is 10.

 

Add below entry in pg_hba file:

 

Restart master server:

 

Create publication on master database for table authorities:

 

On Standby server

Create table authorities with the same table name, same column names and data types.You can add additional columns if required:

 

Create subscription on standby database for publication created on master database:

 

Once publication and subscriptions are created in master and standby databases respectively.Initially, using snapshot, data is loaded for table authorities from master to standby and from then on only incremental changes are replicated.

 

Checking Replication status

Monitor replication on master database by running below command:

 

Check the subscription status on standby by running below command:

Thank you!