PostgreSQL as a Source Database

Supported Version

Database version: 9.6, 10, 11, 12, 13, 14

Dependency plug-in: decoderbufs, wal2json, pgoutput (recommendation)

Database Configuration

Install plug-in

pgoutput is a built-in plug-in, no need to install, the other two types of plug-ins please refer to:

decoderbufs,

wal2json

Modify the WAL Parameter of the Source Database

Modify the parameter configuration file postgresql.conf of the source database. The parameter file is located in the data/ directory of the database.

Use psql to log in to the PostgreSQL database. Run the following command to query the data directory:

psql -d postgres -U $USER -c 'show data_directory'

Modify the postgresql.conf file in the data/ directory.

# Relace <data_directory> with the actual data directory
vi <data_directory>/postgresql.conf

Find the following parameters in the postgresql.conf file and set them to specified values.

1. wal_level=logical # use logical (change requires restart)
2. max_replication_slots = 4 # max number of replication slots (change requires restart)
3. wal_sender_timeout = 0s # in milliseconds; 0 disables
4. max_wal_senders = 8 # max number of walsender processes (change requires restart)
5. shared_preload_libraries = 'decoderbufs' （If the plug-in is wal2json, ignore it）

Modify the HBA Configuration of the Source Database

In the data directory, find the pg_hba.conf configuration file, add the following content to add the replication permission.

Note: mdb_user indicates the user for using the replication function. It needs to be modified based on actual requirement.

# Replace <data_directory> with the actual data directory. 
cat >> <data_directory>/pg_hba.conf <"EOF"

# Add for MDB replication
host  replication  mdb_user   0.0.0.0/0  sha256
EOF

Set User Permission

Create the mdb_user user for logging in to and replicate source PostgreSQL data. The username and password need to be modified based on the actual requirement.

• The login permission is used for logging in to the database.
• The replication permission is used for synchronizing incremental data.
• The superuser permission is used for initializing objects and synchronizing full data.

create user mdb_user with superuser login replication PASSWORD 'Enmo@123';

Enable the REPLICA IDENTITY Mode of the Table

For all tables to be synchronized, enable the REPLICA IDENTITY mode.

Note: The following is the command example.

ALTER TABLE public.customers REPLICA IDENTITY FULL;

Enable Heartbeat Configuration

Sending a heartbeat message enables the connector to send the latest retrieved LSN to the database slot, allowing the database to reclaim disk space used by WAL files that are no longer needed.

To enable the heartbeat configuration, see Channel-related Advanced Parameters. You need to set heartbeatIntervalMs to a positive integer, and run the following command on the source node.

Note: The following is only an example. The table name heartbeat needs to be consistent with the heartbeatTableName value.

CREATE TABLE heartbeat (ts TIMESTAMP WITH TIME ZONE);
INSERT INTO heartbeat (ts) VALUES (NOW());

Character Set Mapping