Making use of the CREATE PUBLICATION and CREATE SUBSCRIPTION commands

For version 10.0, the PostgreSQL community created two new commands: CREATE PUBLICATION and CREATE SUBSCRIPTION. These can be used for logical replication, which means that you can now selectively replicate data and achieve close-to-zero downtime upgrades. So far, binary replication and transaction log replication has been fully covered. However, sometimes, we might not want to replicate an entire database instance – replicating a table or two might be enough. This is exactly when logical replication is the right thing to use.

Before getting started, the first thing to do is change wal_level to logical in postgresql.conf as follows, and then restart:

wal_level = logical  

Then, we can create a simple table:

test=# CREATE TABLE t_test (a int, b int);
CREATE TABLE

The same table layout has to exist in the second database as well to make this work. PostgreSQL will not automatically create those tables for us:

test...