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

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

  1. Use DMS to log on to an ApsaraDB RDS for PostgreSQL instance.
  2. 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');
  3. 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;
  4. 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');
    Parse logical log records
    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');