Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Logical replication was introduced in PostgreSQL 10 and it is different from streaming replication in many ways as described below:
- 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.
- 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.
- 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.
- Logical replication supports write queries on the standby database.
- Logical replication also supports cross version replication unlike streaming replication where both master and standby database need to be of same version.
- PostgreSQL 11 also supports replicating TRUNCATE commands which was not supported earlier.
- 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:
1 2 | wal_level = replica max_wal_senders=10 |
In PostgreSQL 10, default value for max_wal_senders is 10.
Add below entry in pg_hba file:
1 | host replication all standbyip/32 md5 |
Restart master server:
1 | pg_ctl restart -mf -D /opt/PostgreSQL/10/data |
Create publication on master database for table authorities:
1 | create publication core_test_logical 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:
1 | create table authorities (username character varying(50),authority character varying(50)); |
Create subscription on standby database for publication created on master database:
1 | CREATE SUBSCRIPTION core_sub_logical CONNECTION ‘host=masterip dbname=core port=5444’ PUBLICATION core_test_logical; |
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:
1 2 3 4 5 6 7 8 | postgres=# select * from pg_stat_replication; pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | writ e_lag | flush_lag | replay_lag | sync_priority | sync_state ——-+———-+———-+——————+————-+—————–+————-+——————————-+————–+———–+————+————+————+————+—– ——+———–+————+—————+———— 24372 | 10 | postgres | core_sub_logical | 127.0.0.1 | | 48618 | 2018-02-26 12:19:46.347143+01 | | streaming | 0/DC182F90 | 0/DC182F90 | 0/DC182F90 | 0/DC182F90 | | | | 0 | async (1 row) |
Check the subscription status on standby by running below command:
1 2 3 4 5 | postgres=# select * from pg_stat_subscription; subid | subname | pid | relid | received_lsn | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time ——-+——————+——-+——-+————–+——————————+——————————-+—————-+—————————— 16388 | core_sub_logical | 24362 | | 0/DC182F90 | 2018-02-26 12:21:50.74288+01 | 2018-02-26 12:21:50.742994+01 | 0/DC182F90 | 2018-02-26 12:21:50.74288+01 (1 row) |
Thank you!
