The wal2sql is an output extension for logical decoding. It means that the extension have access to tuples produced by INSERT and UPDATE. Also, UPDATE/DELETE old row versions can be accessed depending on the configured replica identity. Changes can be consumed using the streaming protocol (logical replication slots) or by a special SQL API.
format version 1 produces a JSON object per transaction.All of the new/old tuples are available in the JSON object. Also, there are options to include properties such as transaction timestamp, schema-qualified, data types, and transaction ids.
format version 2 produces a JSON object per tuple. Optional JSON object for beginning and end of transaction. Also, there are a variety of options to include properties.
You need to set up at least parameters at lightdb.conf
# lightdb.conf wal_level = logical
include-transaction
(boolean
)
emit records denoting the start and end of each transaction. Default is true.
include-xids
(boolean
)
add xid to each changeset. Default is false.
include-timestamp
(boolean
)
add timestamp to each changeset. Default is false.
include-pk
(boolean
)
add primary key information as pk. Column name and data type is included. Default is false.
include-origin
(boolean
)
add origin of a piece of data. Default is false.
include-schemas
(boolean
)
add schema to each change. Default is true.
include-types
(boolean
)
add type to each change. Default is true.
include-type-oids
(boolean
)
add type oids. Default is false.
include-typmod
(boolean
)
add modifier to types that have it (eg. varchar(20) instead of varchar). Default is true.
include-domain-data-type
(boolean
)
replace domain name with the underlying data type. Default is false.
include-column-positions
(boolean
)
add column position (pg_attribute.attnum). Default is false.
include-not-null
(boolean
)
add not null information as columnoptionals. Default is false.
include-default
(boolean
)
add default expression. Default is false.
pretty-print
(boolean
)
dd spaces and indentation to JSON structures. Default is false.
write-in-chunks
(boolean
)
write after every change instead of every changeset. Only used when format-version is 1. Default is false.
include-lsn
(boolean
)
add nextlsn to each changeset. Default is false.
actions
(boolean
)
efine which operations will be sent. Default is all actions (insert, update, delete, and truncate). However, if you are using format-version 1, truncate is not enabled (backward compatibility).
filter-origins
(boolean
)
exclude changes from the specified origins. Default is empty which means that no origin will be filtered. It is a comma separated value.
filter-tables
(boolean
)
xclude rows from the specified tables. Default is empty which means that no table will be filtered. It is a comma separated value. The tables should be schema-qualified. *.foo means table foo in all schemas and bar.* means all tables in schema bar. Special characters (space, single quote, comma, period, asterisk) must be escaped with backslash. Schema and table are case-sensitive. Table "public"."Foo bar" should be specified as public.Foo\ bar.
add-tables
(boolean
)
include only rows from the specified tables. Default is all tables from all schemas. It has the same rules from filter-tables.
filter-msg-prefixes
(boolean
)
exclude messages if prefix is in the list. Default is empty which means that no message will be filtered. It is a comma separated value.
add-msg-prefixes
(boolean
)
include only messages if prefix is in the list. Default is all prefixes. It is a comma separated value. wal2sql applies filter-msg-prefixes before this parameter.
format-version
(integer
)
defines which format to use. Default is 1.
There are two ways to obtain the changes (JSON objects) from wal2sql plugin: calling functions via ltdts_recvlogical or SQL.
Besides the configuration above, it is necessary to configure a replication connection to use ltdts_recvlogical.
Add a replication connection rule at lt_hba.conf
local mydatabase myuser trust
Also, set max_wal_senders at lightdb.conf:
max_wal_senders = 1
A restart is necessary if you changed max_wal_senders. You are ready to try wal2sql. In one terminal:
# create a logical repliction slot name as test_oracle_slot ltdts_recvlogical -d postgres --slot=test_oracle_slot --create-slot -P wal2sql # delete a logical repliction slot ltdts_recvlogical -d postgres --slot=test_oracle_slot --drop-slot # print output in the first terminal ltdts_recvlogical -d postgres --slot test_oracle_slot --start -o pretty-print=1 -o add-msg-prefixes=wal2sql -f - # forward output to oracle。 ltdts_recvlogical --tnsname=TEST --oracle-username=scott --oracle-password=tiger --start --slot=test_oracle_slot -o include-types=false -o include-type-oids=true -d postgres -h 10.20.30.193 -p 9999 -f debug.dat
Save the following code and execute it
CREATE TABLE table3_with_pk (a SERIAL, b VARCHAR(30), c TIMESTAMP NOT NULL, PRIMARY KEY(a, c)); CREATE TABLE table3_without_pk (a SERIAL, b NUMERIC(5,2), c TEXT); SELECT 'init' FROM pg_create_logical_replication_slot('test_slot', 'wal2sql'); BEGIN; INSERT INTO table3_with_pk (b, c) VALUES('Backup and Restore', now()); INSERT INTO table3_with_pk (b, c) VALUES('Tuning', now()); INSERT INTO table3_with_pk (b, c) VALUES('Replication', now()); SELECT pg_logical_emit_message(true, 'wal2sql', 'this message will be delivered'); DELETE FROM table3_with_pk WHERE a < 3; SELECT pg_logical_emit_message(false, 'wal2sql', 'this non-transactional message will be delivered even if you rollback the transaction'); INSERT INTO table3_without_pk (b, c) VALUES(2.34, 'Tapir'); -- it is not added to stream because there isn't a pk or a replica identity UPDATE table3_without_pk SET c = 'Anta' WHERE c = 'Tapir'; COMMIT; SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'format-version', '2', 'add-msg-prefixes', 'wal2sql'); SELECT 'stop' FROM pg_drop_replication_slot('test_slot'); DROP TABLE table3_with_pk; DROP TABLE table3_without_pk;
wal2sql
is not supported on
LoongArch.
In high availability
please start ltdts_logicalrepl_copier.sh
to
copy slot from primary to standby.
In high availability when faiover occur, please restart new primary LightDB to let slot work.