Making use of CREATE PUBLICATION and CREATE SUBSCRIPTION

For version 10.0, the PostgreSQL community created two new commands: CREATE PUBLICATION and CREATE SUBSCRIPTION. These can be used for logical replication. So far, binary replication and transaction log replication has been fully covered. However, sometimes you 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 to change  wal_level to logical in postgresql.conf and restart:

wal_level = logical

Then you 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 you:

test=# CREATE DATABASE repl;CREATE DATABASE

After creating the database, the identical table can be added:

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