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...
