"G" Moment | Practical Application of GBase Logical Replication Slots

Published on 2025-04-10

General-purpose data migration tools can synchronize data between GBase and heterogeneous databases like Oracle, but most lack real-time replication capabilities and cannot support the need for real-time data synchronization during parallel operation with heterogeneous databases. To address this scenario, GBase provides a logical decoding feature. The core idea is to generate logical logs by reverse-parsing xlog, which the target database then parses to replicate data in real time.

The logical replication function removes restrictions on the target database format, perfectly supporting data synchronization scenarios between heterogeneous databases or homogeneous but differently structured databases. It allows the target database to remain read-writable during synchronization and achieves low data replication latency.

So how to apply it in practice? This article provides an operational guide using simple data examples:

(1) Before using replication slots, configure the following GUC parameters:

wal_level = logical

enable_slot_log = on

host replication all 0.0.0.0/0 sha256

Execute the following commands as the gbase user:

gs_guc reload -Z coordinator -N all -I all -c “wal_level = logical”

gs_guc reload -Z coordinator -N all -I all -c “enable_slot_log = on”

gs_guc reload -Z coordinator -N all -I all -h “host replication all 0.0.0.0/0 sha256”

Restart the database for the changes to take effect. For example:

gha_ctl restart -l http://192.168.142.56:2379

(2) Create a logical replication slot

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

(3) List replication slots

select pg_get_replication_slots();select * from pg_replication_slots;

(4) Drop a replication slot

select pg_drop_replication_slot('test_slot');

(5) Decode without advancing the replication slot (the same data can be retrieved again in the next decoding)

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

select pg_logical_slot_peek_changes('test_slot',null,null);

select data from pg_logical_slot_peek_changes('test_slot', null, null, 'pretty-print', '1');

select * from pg_logical_slot_peek_changes('test_slot', null, null, 'include-timestamp', 'on');

(6) Decode and advance the replication slot

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

select pg_logical_slot_get_changes('test_slot', null, null);

select data from pg_logical_slot_get_changes('test_slot', null, null, 'pretty-print', '1');

(7) Replication slot monitoring

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;

select pg_size_pretty(pg_xlog_location_diff('7F9/F7241AA0','76D/3FE8E558'));

The enable_slot_log parameter specifies whether to enable the primary-standby synchronization feature for logical replication slots. The default value is off.

How to verify the effect of enable_slot_log? Suppose in a high availability group, initially, A is the primary and B is the standby.

  • If enable_slot_log = off

When A creates a replication slot, the slot information cannot be found on B, nor can data changes be queried on B.

After a primary-standby switchover, B becomes the primary and A becomes the standby. B still cannot find the replication slot information or perform logical decoding. A can find the replication slot information and perform logical decoding. When data is modified on B, A can also logically decode and obtain the data changes. Therefore, if a failover occurs and A fails to start normally, logical decoding cannot continue, and the replication stream will be interrupted.

  • If enable_slot_log = on

When A creates a replication slot, the slot information can be found on B, and B can also perform logical decoding to obtain data changes, but the slot cannot be advanced on B.

After a primary-standby switchover, B becomes the primary and A becomes the standby. B can find the replication slot information and perform logical decoding. A can find the replication slot information and perform logical decoding. When data is modified on B, A can also logically decode and obtain the data changes, but A cannot advance the slot.

(8) Set the replica identity level

alter table t1 replica identity full;

select a.relname, b.nspname, a.relreplident

from pg_catalog.pg_class a join pg_catalog.pg_namespace b on a.relnamespace = b.oid

where a.relname = 't1' and b.nspname = 'public' and a.relkind = 'r';

In a logical replication scenario, this specifies the level of recording old tuple values for UPDATE and DELETE operations on the table.

  • DEFAULT records the old values of the primary key columns; if no primary key exists, nothing is recorded.

  • USING INDEX records the old values of columns covered by the specified index; these must be unique, not partial, not deferrable, and include only columns marked NOT NULL.

  • FULL records the old values of all columns in the row.

  • NOTHING records no information about the old row.

In a logical replication scenario, when parsing UPDATE and DELETE operations for the table, the parsed old tuple is composed of the information recorded by this method. For tables with a primary key, this option can be set to DEFAULT or FULL. For tables without a primary key, this option must be set to FULL; otherwise, the old tuple will be parsed as empty during decoding. In general, NOTHING is not recommended, as the old tuple will always be parsed as empty.

Even if DEFAULT or USING INDEX is specified, for ustore tables the old values might still include all columns of the row; the configuration option only takes effect for old values involving TOAST. Additionally, for ustore tables, the NOTHING option is ineffective and behaves equivalently to FULL.