Converting to logical replication v6.2.0

To facilitate a major version upgrade of your Postgres distribution and the subsequent installation of Postgres Distributed (PGD), you must convert the replication stream to the seed node to logical replication. For a seamless transition without data loss, logical replication must resume at the exact Log Sequence Number (LSN) within the WAL stream of the source node where physical replication was interrupted. This is known as the switch-over LSN.

Creating a publication on the source node

Prepare the source node by defining which data will be replicated and creating a dedicated slot to hold WAL files.

1. Create a publication for all tables in the database:

   → WrapCopy

   CREATE PUBLICATION migration_seed_pub FOR ALL TABLES;

2. Verify the publications:

   → WrapCopy

   SELECT * FROM pg_publication;
   SELECT * FROM pg_publication_tables;

3. Create a logical replication slot:

   → WrapCopy

   SELECT pg_create_logical_replication_slot('migration_node_${SEED_NODE_NAME}', 'pgoutput');

   Creating the slot at this stage ensures that the source node begins retaining the necessary WAL data before the switch-over operation. Because the source node will eventually support multiple PGD nodes, each slot is named specifically to identify the target node it serves.

Promoting the seed node

Promoting the seed node stops the incoming physical replication stream and converts the node into a standalone, writable instance.

On the seed node, run the following command:

su -u enterprisedb --command "pg_ctl promote"

Enabling logical replication to the seed node

After promotion, you must align the new logical replication slot with the point where physical replication ended.

1. On the seed node, identify the last LSN replayed from the physical stream:

   → WrapCopy

   SELECT pg_last_wal_replay_lsn();

2. On the source node, manually move the logical slot forward to that LSN. Replace ${SWITCH_OVER_LSN} with the value retrieved from the step above.

   → WrapCopy

   SELECT pg_replication_slot_advance('migration_node_${SEED_NODE_NAME}', '${SWITCH_OVER_LSN}');

3. Create a logical subscription on the seed node:

   → WrapCopy

   CREATE SUBSCRIPTION migration_seed_sub
           CONNECTION '${SOURCE_DSN}'
           PUBLICATION migration_seed_pub
               WITH (
                   enabled = false,
                   copy_data = false,
                   create_slot = false,
                   slot_name = 'migration_node_${SEED_NODE_NAME}'
                   );

4. Verify that the subscription has correctly identified the tables for replication:

   → WrapCopy

   SELECT n.nspname AS schemaname,
       c.relname AS tablename,
       sr.srsubstate AS state,
       s.subname AS subscription_name
   FROM pg_subscription_rel sr
   JOIN pg_class c ON sr.srrelid = c.oid
   JOIN pg_namespace n ON c.relnamespace = n.oid
   JOIN pg_subscription s ON sr.srsubid = s.oid
   WHERE s.subname = 'migration_seed_sub'
   ORDER BY n.nspname, c.relname;

5. Enable logical replication:

   → WrapCopy

   ALTER SUBSCRIPTION migration_seed_sub ENABLE;

Cleaning up physical replication

1. Once the logical stream is verified and active, run the following command on the source node to remove the legacy physical replication components and free up resources:

   → WrapCopy

   SELECT pg_drop_replication_slot('migration_phy_slot');

2. On the seed node, run the following commands to remove any remaining configuration parameters for physical replication:

   → WrapCopy

   ALTER SYSTEM RESET primary_conninfo;
   ALTER SYSTEM RESET primary_slot_name;

Next step: Upgrade the seed node.