This topic describes how to use the wal2json plug-in provided by RDS PostgreSQL to export logical log records as a file in JSON format.
- Your RDS instance runs PostgreSQL 11, 12 or 13.
- The wal_level parameter is set to logical. For more information, see Reconfigure parameters for an RDS PostgreSQL instance.
wal2json is a logical decoding plug-in. It has access to tuples generated by INSERT and UPDATE statements and can parse log records produced by write-ahead logging (WAL).
The wal2json plug-in produces a JSON object for each transaction. All new and old tuples are available in the JSON object. In addition, there are options to include properties such as transaction timestamp, schema-qualified, data type, and transaction ID. You can obtain JSON objects by executing SQL statements. 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 plug-in:
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 produce logical log records in JSON format:
SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1');Note If you want to stop producing logical log records and release the resources used, execute the following statement:
SELECT 'stop' FROM pg_drop_replication_slot('test_slot');