Making use of the CREATE PUBLICATION and CREATE SUBSCRIPTION commands

The PostgreSQL community provides two increasingly important 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 have 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 we must do is change wal_level to logical in postgresql.conf, 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 to make this work. PostgreSQL won’t automatically create those tables for...