PostgreSQL Logical Replication

Logical replication is a method of replicating data objects and their changes, based upon their replication identity (usually a primary key). We use the term logical in contrast to physical replication, which uses exact block addresses and byte-by-byte replication.

Logical replication uses a publish and subscribe model with one or more subscribers subscribing to one or more publications on a publisher node. Subscribers pull data from the publications they subscribe to and may subsequently re-publish data to allow cascading replication or more complex configurations.

Logical replication of a table typically starts with taking a snapshot of the data on the publisher database and copying that to the subscriber. Once that is done, the changes on the publisher are sent to the subscriber as they occur in real-time. The subscriber applies the data in the same order as the publisher so that transactional consistency is guaranteed for publications within a single subscription. This method of data replication is sometimes referred to as transactional replication.

The subscriber database behaves in the same way as any other PostgreSQL instance and can be used as a publisher for other databases by defining its own publications. When the subscriber is treated as read-only by application, there will be no conflicts from a single subscription. On the other hand, if there are other writes done either by an application or by other subscribers to the same set of tables, conflicts can arise.

example best practice

we use this environment

server1:1st_oracle:192.168.100.94

server2:2st_oracle:192.168.100.97

on server 1 as publisher:

edit postgres configuration

[postgres@1st_Oracle ~]$ vi 13/data/postgresql.conf

change :

            wal_level = logical

restart database cluster

[postgres@1st_Oracle ~]$ /usr/pgsql-13/bin/pg_ctl -D 13/data/ restart

also make sure you have edited pg_hba file for both connection.

on server 1 (publisher/master):

CREATE TABLE log_rep_test (no int PRIMARY KEY);
INSERT INTO log_rep_test values (1);
CREATE PUBLICATION my_pub FOR TABLES log_rep_test;

on server 2 (subscriber/slave):

CREATE TABLE log_rep_test (no int PRIMARY KEY);
CREATE SUBSCRIPTION my_sub CONNECTION 'host=192.168.100.94 port=5432 dbname=postgres' PUBLICATION my_pub;

now if we run this query we should see the same as master

SELECT * FROM log_rep_test;

now table log_rep_test will be replicated on server 2, listening to publisher (server 1).