How do you use logical replication in PostgreSQL to replicate specific tables or schemas between databases?

Logical replication is a powerful feature introduced in PostgreSQL 10 that allows replicating specific tables or schemas between databases. It works by sending logical changes to a replication target rather than physical changes, which makes it more flexible and efficient than traditional physical replication methods.

To set up logical replication, follow these steps:

1. Enable logical replication on both the source and target databases by adding ‘logical‘ to the ‘wal_level‘ configuration parameter in ‘postgresql.conf‘:

    wal_level = logical

2. Create a replication slot on the source database using the
‘pg_create_logical_replication_slot()‘ function. This will create a logical replication slot that the target database can use to receive changes:

    Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/source", "postgres", "password");
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT pg_create_logical_replication_slot('my_slot_name', 'pgoutput');");
    rs.next();
    String slotName = rs.getString(1);

Note that the second parameter to ‘pg_create_logical_replication_slot()‘ is the output plug-in. In this example, we’re using ‘pgoutput‘, which is the default built-in plug-in that produces a binary representation of the changes.

3. Configure the source database to replicate the tables or schemas you’re interested in using a publication. A publication is a named set of tables or schemas that are replicated as a unit. You can create a publication using the ‘CREATE PUBLICATION‘ SQL command:

    Statement stmt = conn.createStatement();
    stmt.execute("CREATE PUBLICATION my_publication FOR TABLE my_schema.my_table;");

4. On the target database, create a subscription to the publication on the source database using the ‘CREATE SUBSCRIPTION‘ SQL command. Note that you’ll need to supply the replication slot name you created in step 2:

    Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/target", "postgres", "password");
    Statement stmt = conn.createStatement();
    stmt.execute("CREATE SUBSCRIPTION my_subscription CONNECTION 'host=localhost port=5432 dbname=source user=postgres password=password' PUBLICATION my_publication WITH (slot_name = my_slot_name);");

The ‘CONNECTION‘ option specifies the connection string and credentials for the source database, and the ‘WITH‘ option specifies the replication slot name.

That’s it! Once the subscription is created, the target database will begin receiving logical changes from the source database for the tables or schemas specified in the publication. You can monitor the replication progress using the ‘pg_stat_replication‘ system catalog view.