decoderbufs is a logical decoding output plugin that converts Write-Ahead Log (WAL) changes to Protocol Buffers format. It streams row-level change events from PolarDB for PostgreSQL (Compatible with Oracle) to downstream Change Data Capture (CDC) consumers.
Prerequisites
Before you begin, make sure that you have:
A PolarDB for PostgreSQL (Compatible with Oracle) 2.0 cluster with revision version 2.0.14.1.0 or later To check the revision version, run:
SHOW polar_version;The following library dependencies met on the cluster:
Library Minimum version Purpose PostgreSQL 9.6 Database engine protobuf-c 1.2 Data serialization PostGIS 2.1 PostgreSQL Geo type support
Configuration
decoderbufs is automatically loaded by default.
To enable logical decoding, set the wal_level parameter to logical in the console. For more information, see Configure cluster parameters.
The cluster restarts after you modify the wal_level parameter. Proceed with caution.
The following postgresql.conf parameters control logical replication behavior:
# MODULES
shared_preload_libraries = 'decoderbufs'
# REPLICATION
wal_level = logical # minimal, archive, hot_standby, or logical (change requires restart)
max_wal_senders = 8 # max number of walsender processes (change requires restart)
wal_keep_segments = 4 # in logfile segments, 16MB each; 0 disables
#wal_sender_timeout = 60s # in milliseconds; 0 disables
max_replication_slots = 4 # max number of replication slots (change requires restart)Set up logical replication
After you set the wal_level parameter, complete the following steps.
Step 1: Create a replication slot
Create a logical replication slot with the decoderbufs output plugin:
SELECT * FROM pg_create_logical_replication_slot('decoderbufs_demo', 'decoderbufs');Step 2: Modify the published table
Insert, update, or delete rows in the target table. These changes are captured in the WAL and decoded by decoderbufs.
Step 3: View WAL changes in debug mode
Run pg_logical_slot_peek_changes to view decoded WAL changes without consuming them. This does not advance the replication slot position.
SELECT data FROM pg_logical_slot_peek_changes('decoderbufs_demo', NULL, NULL, 'debug-mode', '1');Step 4: Consume WAL changes
Run pg_logical_slot_get_changes to retrieve decoded WAL changes and advance the replication slot position.
SELECT data FROM pg_logical_slot_get_changes('decoderbufs_demo', NULL, NULL, 'debug-mode', '1');Step 5: Check replication slot positions
Query pg_replication_slots to check the current WAL position of logical replication slots:
SELECT * FROM pg_replication_slots WHERE slot_type = 'logical';Data type mappings
The following table lists how PostgreSQL type OIDs map to decoderbuf fields.
| PostgreSQL type OID | Decoderbuf field |
|---|---|
| BOOLOID | datum_boolean |
| INT2OID | datum_int32 |
| INT4OID | datum_int32 |
| INT8OID | datum_int64 |
| OIDOID | datum_int64 |
| FLOAT4OID | datum_float |
| FLOAT8OID | datum_double |
| NUMERICOID | datum_double |
| CHAROID | datum_string |
| VARCHAROID | datum_string |
| BPCHAROID | datum_string |
| TEXTOID | datum_string |
| JSONOID | datum_string |
| XMLOID | datum_string |
| UUIDOID | datum_string |
| TIMESTAMPOID | datum_string |
| TIMESTAMPTZOID | datum_string |
| BYTEAOID | datum_bytes |
| POINTOID | datum_point |
| PostGIS geometry | datum_point |
| PostGIS geography | datum_point |