How to skip a logical replication transaction

This article explains how to remove or skip transactions in the logical replication queue containing one or more combined incompatible statements that the subscriber node cannot replay so that replication can advance.

Assuming there are two nodes, named node1 and node2 using a replication slot named logical_subscription_slot_node1_node2, it should be possible to use the following steps. In this case, node2 is the subscriber and is falling behind in replication due to an error.

1. Stop the bad node (node2). This prevents the node from attaching to the replication slot so we can modify its contents.
2. Execute the following SQL on the unaffected provider (node1) to verify data being skipped:

SET bytea_output TO 'escape';
SELECT *
FROM pg_logical_slot_peek_binary_changes( 
'logical_subscription_slot_node1_node2',
NULL,
1,
'min_proto_version', '1',
'max_proto_version', '1',
'expected_encoding', 'UTF8',
'startup_params_format', '1',
'proto_format', 'json'
);

The first 3 arguments are the following:

• slot_name: The name of the slot, on the provider, associated with the defective subscriber;
• upto_lsn: The desired LSN up to it's intended to be skipped. If set to NULL, then it will take the third argument into consideration;
• upto_nchanges: The number of transactions intended to be skipped. If set to NULL, then it will take the second argument into consideration.

IMPORTANT: If both upto_lsn and upto_nchanges are set to NULL, then Postgres will skip all transactions until the end of WAL.

So the example above peeks the next transaction in the backlog, because:

• upto_lsn := NULL;
• upto_nchanges := 1.

Alternatively, knowing the target LSN, it's possible to skip any number of transactions until that specific LSN, for example:

SET bytea_output TO 'escape';
SELECT *
FROM pg_logical_slot_peek_binary_changes( 
'logical_subscription_slot_node1_node2',
'A/91D903C0'::pg_lsn,
NULL,
'min_proto_version', '1',
'max_proto_version', '1',
'expected_encoding', 'UTF8',
'startup_params_format', '1',
'proto_format', 'json'
);

However, it is safest to only skip one transaction at a time to avoid missing transactions which may contain critical data.

It's also possible to save that transaction into a CSV file for analysis, for example:

SET bytea_output TO 'escape';
\copy (
SELECT *
FROM pg_logical_slot_peek_binary_changes( 
'logical_subscription_slot_node1_node2',
NULL,
1,
'min_proto_version', '1',
'max_proto_version', '1',
'expected_encoding', 'UTF8',
'startup_params_format', '1',
'proto_format', 'json'
))
TO 'transaction_to_skip.csv' WITH (FORMAT csv);

IMPORTANT: The following steps apply only to Native Logical Replication and pglogical. For PGD you should use the function bdr.alter_subscription_skip_changes_upto(...), as described in the documentation.

3. Execute the following on node1 to skip the next transaction in the backlog:

SET bytea_output TO 'escape';
SELECT *
FROM pg_logical_slot_get_binary_changes( 
'logical_subscription_slot_node1_node2',
NULL,
1,
'min_proto_version', '1',
'max_proto_version', '1',
'expected_encoding', 'UTF8',
'startup_params_format', '1',
'proto_format', 'json'
);

Alternative, if you are skipping according to a target LSN, you can execute:

SET bytea_output TO 'escape';
SELECT *
FROM pg_logical_slot_get_binary_changes( 
'logical_subscription_slot_node1_node2',
'A/91D903C0'::pg_lsn,
NULL,
'min_proto_version', '1',
'max_proto_version', '1',
'expected_encoding', 'UTF8',
'startup_params_format', '1',
'proto_format', 'json'
);

Note how the execution of pg_logical_slot_get_binary_changes(...) is exactly the same as pg_logical_slot_peek_binary_changes(...), i.e., same arguments.

4. Start node2 again.
5. Examine the node2 Postgres logs to see if the errors stop.
6. If errors haven't stopped, use the LSN information in the Postgres logs to determine how far the slot has advanced. Take note of this and repeat from step 1.

It may be necessary to repeat this process multiple times if there are several broken DDL statements in the queue. If no more errors show up in the logs on the subscriber node, replication should be fully functional, the replication slot will turn active and lag will begin to decrease.