How to use a replication slot for CDC-ApsaraDB RDS(RDS)-阿里云帮助中心

Prerequisites

You have an ApsaraDB RDS for PostgreSQL instance. For more information, see Create an ApsaraDB RDS for PostgreSQL instance.
You have configured a whitelist to allow client access to the ApsaraDB RDS for PostgreSQL instance. For more information, see Configure an IP address whitelist.
You have installed the PostgreSQL command-line client. For more information, see the official PostgreSQL documentation.

Usage notes

You can enable CDC and consume data changes only on the primary instance. This feature is not supported on read-only instances.
ApsaraDB RDS for PostgreSQL supports Logical Replication Slot Failover. A primary-secondary switchover does not affect CDC. For more information, see Logical Replication Slot Failover.
Before you enable CDC, you must modify a parameter for your ApsaraDB RDS for PostgreSQL instance. This action triggers an instance restart. To avoid service interruptions, perform this operation during off-peak hours.
When using CDC on an ApsaraDB RDS for PostgreSQL instance, note the following:
- CDC requires more storage space for WAL logs. If data consumption is abnormal or stops, the instance does not clean up WAL logs automatically. This can cause logs to accumulate and consume all available disk space, potentially locking the instance. A locked instance becomes read-only and rejects write operations.
- Retaining old data rows for an extended period can lead to a transaction ID wraparound problem, which blocks all write operations on the instance. The error log may contain the following messages:
```
“HINT: Close open transactions soon to avoid wraparound problems. You might also need to commit or roll back old prepared transactions, or drop stale replication slots.”
“WARNING: oldest xmin is far in the past.”
```
You can manually delete the replication slot to allow the PostgreSQL kernel to automatically clean up old WAL logs and data rows. For more information, see Disable CDC.

Note
Regularly monitor the WAL log size and disk space usage of your instance and configure relevant alerts. For more information, see View Enhanced Monitoring data and Manage alert rules.

Enable CDC

Step 1: Create a test database

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the navigation pane on the left, choose Databases.
Click Create Database. After the testdb database is created, its status changes to Running and its character set is UTF8.

Note
In this example, a database named testdb is created. For detailed instructions, see Create a database.

Step 2: Create test accounts and configure permissions

In the navigation pane on the left, choose Accounts.
Click Create Account to create a privileged account (db_admin) and a standard account for CDC (cdc_user).

Note
The account names used in this topic are for demonstration purposes only. You can specify custom names. For more information about how to create an account, see Create an account.
Use the db_admin account to connect to the ApsaraDB RDS for PostgreSQL instance.
```
psql -h <endpoint of the ApsaraDB RDS for PostgreSQL instance> -p 5432 -U db_admin -d testdb
```
Note
For more information about how to obtain the endpoint of an instance, see View or change the endpoint and port number.
Run the following command to add the cdc_user account to the replication role:
```
ALTER USER cdc_user WITH REPLICATION;
```
You can run the following command to check the result:
```
SELECT rolreplication FROM  pg_roles WHERE rolname='cdc_user';
```
Sample output:
```
 rolreplication
----------------
 t
(1 row)
```
Run the following command to grant permissions to the cdc_user account:
```
GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC to cdc_user;
```

Step 3: Modify instance parameters

Run the following command to query the current parameter settings:
```
SELECT name,
       setting,
       short_desc,
       source
FROM pg_settings
WHERE name ='wal_level';
```
Sample output:
```
         name          | setting |                               short_desc                                |       source
-----------------------+---------+-------------------------------------------------------------------------+--------------------
 wal_level             | replica | Sets the level of information written to the WAL.                       | configuration file
(1 rows)
```
The wal_level parameter controls the amount of information PostgreSQL writes to the WAL log. The default value is replica. This parameter can be set only at server startup. Valid values:
- minimal: Writes only the information required to recover from a crash or an immediate shutdown. At this level, you cannot restore a database from a base backup and WAL logs.
- replica: Writes enough information to support WAL archiving and replication, including running read-only queries on a standby server.
- logical: Adds information required for logical decoding.
Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the navigation pane on the left, choose Parameters.
Set the wal_level parameter to logical.
Note
- For more information about how to modify instance parameters, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.
- After you submit the parameter change, your ApsaraDB RDS for PostgreSQL instance restarts. To avoid service interruptions, perform this operation during off-peak hours.

Step 4: Create a logical replication slot

Note

The parameter modification in Step 3 restarts your ApsaraDB RDS for PostgreSQL instance. Proceed with this step only after the instance has restarted and its status is Running.

Use the db_admin account to connect to the ApsaraDB RDS for PostgreSQL instance.

psql -h <endpoint of the ApsaraDB RDS for PostgreSQL instance> -p 5432 -U db_admin -d testdb

Run the following command to create a replication slot named cdc_replication_slot using the test_decoding output plugin:

SELECT pg_create_logical_replication_slot('cdc_replication_slot', 'test_decoding');

Note

The slot name cdc_replication_slot is used for demonstration purposes. You can specify a custom name.
PostgreSQL provides the test_decoding output plugin natively, so no changes are required.

Sample output:

 pg_create_logical_replication_slot
------------------------------------
 (cdc_replication_slot,1/14003428)
(1 row)

You can run the following command to check whether the slot was created:

SELECT * FROM pg_replication_slots;

Sample output:

      slot_name       |    plugin     | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size  | two_phase
----------------------+---------------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+----------------+-----------
 cdc_replication_slot | test_decoding | logical   |  18822 | testdb   | f         | f      |            |      |        22356 | 1/140033F0  | 1/14003428          | reserved   |                | f
(1 row)

Step 5: Create test data

Run the following commands to create test data to simulate a production environment:

CREATE TABLE public.tb_test(
    id int NOT NULL PRIMARY KEY
);
ALTER TABLE public.tb_test ADD name varchar(1) NULL;
INSERT INTO public.tb_test SELECT 1, 'A';

Step 6: Read data from the slot

Use the cdc_user account to connect to the ApsaraDB RDS for PostgreSQL instance.

psql -h <endpoint of the ApsaraDB RDS for PostgreSQL instance> -p 5432 -U cdc_user -d testdb

Run the following command to read the changes from the replication slot:

SELECT * FROM pg_logical_slot_peek_changes('cdc_replication_slot', null, null);

Sample output:

    lsn     |  xid  |                                  data
------------+-------+-------------------------------------------------------------------------
 1/14003D90 | 22376 | BEGIN 22376
 1/1401DDE8 | 22376 | COMMIT 22376
 1/1401DDE8 | 22377 | BEGIN 22377
 1/1401E100 | 22377 | COMMIT 22377
 1/1401E2A8 | 22382 | BEGIN 22382
 1/1401E2A8 | 22382 | table public.tb_test: INSERT: id[integer]:1 name[character varying]:'A'
 1/1401E3C0 | 22382 | COMMIT 22382
(7 rows)

Step 7: Consume data changes

Run the \q command to exit the database connection.

Run the following command to consume the data changes:

Note

You must run the pg_recvlogical command as the postgres user. You can switch users by running the su - postgres command. If you encounter the -bash: pg_recvlogical: command not found error, see the FAQ section for a solution.

pg_recvlogical -h <endpoint of the ApsaraDB RDS for PostgreSQL instance> -U <privileged account> -d <test database> --create-slot --if-not-exists --slot=cdc_replication_slot --plugin=test_decoding --start -f -

Command example:

pg_recvlogical -h pgm-*****.pgsql.singapore.rds.aliyuncs.com -U db_admin -d testdb --create-slot --if-not-exists --slot=cdc_replication_slot --plugin=test_decoding --start -f -

Sample output:

BEGIN 22376
COMMIT 22376
BEGIN 22377
COMMIT 22377
BEGIN 22382
table public.tb_test: INSERT: id[integer]:1 name[character varying]:'A'
COMMIT 22382

Disable CDC

If data consumption is abnormal or stops, WAL logs can accumulate and consume all available disk space, eventually locking the instance. To resolve this, you can manually delete inactive replication slots, which allows the PostgreSQL kernel to clean up the old WAL logs.

You can delete inactive replication slots from the console, through an API call, or by running an SQL command:

In the console: Manage WAL logs.
Using the API: DeleteSlot.

Using an SQL command:

Use the db_admin account to connect to the ApsaraDB RDS for PostgreSQL instance.

psql -h <endpoint of the ApsaraDB RDS for PostgreSQL instance> -p 5432 -U db_admin -d testdb

Run the following command to view the names and information about inactive slots:

SELECT slot_name, slot_type, database, active, safe_wal_size
FROM pg_replication_slots
WHERE active = 'f';

Sample output:

      slot_name       | slot_type | database | active | safe_wal_size
----------------------+-----------+----------+--------+---------------
 cdc_replication_slot | logical   | testdb   | f      |
(1 row)

Run the following command to delete the logical replication slot:
```
SELECT pg_drop_replication_slot('cdc_replication_slot');
```

FAQ

Q: What should I do if I get the -bash: pg_recvlogical: command not found error when consuming data?

A: pg_recvlogical is a native logical decoding tool for PostgreSQL that uses the default test_decoding output plugin. This plugin is in the contrib/test_decoding directory of the PostgreSQL source code package. Compile and install PostgreSQL from its source code. After installation, the pg_recvlogical tool is in the /bin directory of the client installation path. For more information about how to install PostgreSQL from source, see Installation from Source Code.
Q: I manually deleted a replication slot, but the system did not automatically clean up the WAL logs and they are still consuming disk space. What should I do?

A: You can change the wal_keep_segments parameter to its default value of 128 to reduce the number of retained log files. For more information about how to modify instance parameters, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.