This topic describes how to use the wal2json extension that is provided by ApsaraDB RDS for PostgreSQL to export logical log records of your ApsaraDB RDS for PostgreSQL instance as a file in the JSON format.
Prerequisites
- Your RDS instance runs PostgreSQL 10 or later.
- The wal_level parameter is set to logical. For more information, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.
Background information
wal2json is a logical decoding extension. It has access to tuples that are generated by INSERT and UPDATE statements and can parse log records exported by write-ahead logging (WAL).
The wal2json extension produces a JSON object for each transaction. All new and old tuples are available in the JSON object. Other options that include properties such as transaction timestamp, schema-qualified, data type, and transaction ID are also available in the JSON object. For more information, see Execute SQL statements to obtain JSON objects.
Execute SQL statements to obtain JSON objects
- Use DMS to log on to an ApsaraDB RDS for PostgreSQL instance.
- Execute the following statements to create a table and initialize the wal2json extension:
CREATE TABLE table2_with_pk (a SERIAL, b VARCHAR(30), c TIMESTAMP NOT NULL, PRIMARY KEY(a, c)); CREATE TABLE table2_without_pk (a SERIAL, b NUMERIC(5,2), c TEXT); SELECT 'init' FROM pg_create_logical_replication_slot('test_slot', 'wal2json');
- Execute the following statements to change data:
BEGIN; INSERT INTO table2_with_pk (b, c) VALUES('Backup and Restore', now()); INSERT INTO table2_with_pk (b, c) VALUES('Tuning', now()); INSERT INTO table2_with_pk (b, c) VALUES('Replication', now()); DELETE FROM table2_with_pk WHERE a < 3; INSERT INTO table2_without_pk (b, c) VALUES(2.34, 'Tapir'); UPDATE table2_without_pk SET c = 'Anta' WHERE c = 'Tapir'; COMMIT;
- Execute the following statement to export logical log records as a file in the JSON format:
SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1');
Note If you want to stop exporting logical log records and release the resources that are used, execute the following statement:SELECT 'stop' FROM pg_drop_replication_slot('test_slot');