1. Parameter Settings

To use the wal2json logical replication, the following parameters need to be set:

wal_level = logical
enable_slot_log = on

Setting wal_level to logical means that the WAL log supports logical replication. If standby decoding is needed, set enable_slot_log to on on the corresponding primary server.

2. Using Logical Replication Slot

2.1 Creating a Logical Replication Slot

Here is an example of creating a logical replication slot named test_slot, using the wal2json replication slot plugin:

SELECT pg_create_logical_replication_slot('test_slot', 'wal2json');

2.2 Querying the Logical Replication Slot

SELECT * FROM pg_replication_slots;
SELECT pg_get_replication_slots();

The PG_REPLICATION_SLOTS view provides information about the replication slot.

Name	Type	Description
`slot_name`	`text`	Name of the replication slot.
`plugin`	`text`	Output plugin name corresponding to the logical replication slot.
`slot_type`	`text`	Type of replication slot. `physical`: Physical replication slot. `logical`: Logical replication slot.
`datoid`	`oid`	OID of the database where the replication slot is located.
`database`	`name`	Name of the database where the replication slot is located.
`active`	`boolean`	Whether the replication slot is active. `t` (true): Yes. `f` (false): No.
`xmin`	`xid`	Earliest transaction ID that must be retained by the replication slot.
`catalog_xmin`	`xid`	Earliest transaction ID involving system tables that must be retained.
`restart_lsn`	`text`	Earliest xlog physical location required by the replication slot.
`dummy_standby`	`boolean`	Whether the remote end of the replication slot is a standby. `t` (true): Yes. `f` (false): No.
`confirmed_flush`	`text`	For logical replication slots, the log position confirmed by the client.

2.3 Deleting a Logical Replication Slot

SELECT pg_drop_replication_slot('test_slot');

2.4 Decoding the Replication Slot

There are two ways to decode the replication slot: without advancing the slot (so you can retrieve the same data again next time) and with advancing the slot.

Decode without advancing the slot:

  SELECT pg_logical_slot_peek_changes('slot_name', 'LSN', upto_nchanges, 'options_name', 'options_value');

Decode and advance the slot:

  SELECT pg_logical_slot_get_changes('slot_name', 'LSN', upto_nchanges, 'options_name', 'options_value');

Example:

Create a table and insert data:

postgres=# create table t1(id int, c1 int);
CREATE TABLE
postgres=# insert into t1 values (1,1);
INSERT 0 1
postgres=# insert into t1 values (2,2);

Decode and advance the logical slot:

SELECT pg_logical_slot_get_changes('test_slot', NULL, NULL);

Decode without advancing the logical slot:

If there are no records, it is normal because the slot has been advanced previously. New data changes will appear in the results.

3. Monitoring Logical Replication Slots

SELECT
  slot_name,
  database AS datname,
  plugin,
  slot_type,
  datoid,
  database,
  active,
  xmin,
  catalog_xmin,
  restart_lsn,
  pg_size_pretty(pg_xlog_location_diff(
    CASE
      WHEN pg_is_in_recovery() THEN pg_last_xlog_receive_location()
      ELSE pg_current_xlog_location()
    END,
    restart_lsn
  )) AS delay_lsn_bytes,
  dummy_standby,
  confirmed_flush
FROM pg_replication_slots;

Not advancing the replication slot will prevent GBase from automatically cleaning up WAL logs. The accumulation of logs will occupy local disk space. The above SQL query helps determine whether to advance the replication slot by checking the byte count between the current logical slot's required LSN and the latest LSN.

Using wal2json Logical Replication Slot in GBase 8c