Understanding logical replication

Since version 8.0, PostgreSQL has supported binary replication (also often referred to as WAL-shipping). The ability to distribute transaction log ( WAL) has been improved steadily over the years.

With the introduction of PostgreSQL 10.0, a new feature has been added to PostgreSQL—Logical replication. How does it work? Logical replication allows you to publish a set of tables on one server and ask other servers to subscribe to the changes.

To publish data, the new CREATE PUBLICATION command has been introduced:

test=# \h CREATE PUBLICATION  
Command:  CREATE PUBLICATION 
Description: define a new publication 
Syntax: 
CREATE PUBLICATION name 
    [ FOR TABLE [ ONLY ] table_name [ * ] [, ...] 
      | FOR ALL TABLES ] 
    [ WITH ( publication_parameter [= value] [, ... ] ) ] 

Once the data has been published, remote servers can subscribe to these changes and receive information about what has happened to those published data sets:

test=# \h CREATE SUBSCRIPTION  
Command:  CREATE SUBSCRIPTION 
Description: define a new subscription 
Syntax: 
CREATE SUBSCRIPTION subscription_name 
    CONNECTION 'conninfo' 
    PUBLICATION publication_name [, ...] 
    [ WITH ( subscription_parameter [= value] [, ... ] ) ] 

CREATE SUBSCRIPTION is used on the slave side to attach to these changes. The beauty of the concept is that a server can publish one set of tables while subscribing to some other tables at the same time—there is no such thing as always master or always slave anymore. Logical replication allows you to flexibly distribute data.