PostgreSQL逻辑复制槽 pg_recvlogical test_decoding wal2json

?

1 2 3 4

Schema   |                Name                 | Result data type |                            Argument data types                            |  Type ------------+-------------------------------------+------------------+---------------------------------------------------------------------------+--------  pg_catalog | pg_create_logical_replication_slot  | record           | slot_name name, plugin name, OUT slot_name text, OUT xlog_position pg_lsn | normal  pg_catalog | pg_create_physical_replication_slot | record           | slot_name name, OUT slot_name name, OUT xlog_position pg_lsn              | normal

 

1.需求

遇到一种需要将一部分表通过logical_tool用逻辑复制槽的方式同步到kaffka，之前没有使用过，因此对逻辑复制槽进行了了解。

 

2.资料

pg_create_logical_replication_slot方法配置两个参数:slot_name、plugin_name，例如：

pg_create_logical_replication_slot('test_logical_slot', 'wal2json');

这样就可以通过wal2json这个工具来解析wal日志，也可以用test_decoding自带的解析工具来做，有时间进行详细了解····

 

--201906027有时间了：

test_decoding自带的，直接在psql中安装插件：create extension test_decoding;

wal2json需要下载编译安装：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64

安装： $ git clone https://github.com/eulerto/wal2json.git $ cd wal2json # Make sure your path includes the bin directory that contains the correct `pg_config` $ PATH=/path/to/pg/bin:$PATH $ USE_PGXS=1 make $ USE_PGXS=1 make install   修改wal日志级别： wal_level = logical max_replication_slots = 5 max_wal_senders = 5   参数： include-xids: add xid to each changeset. Default is false. include-timestamp: add timestamp to each changeset. Default is false. include-schemas: add schema to each change. Default is true. include-types: add type to each change. Default is true. include-typmod: add modifier to types that have it (eg. varchar(20) instead of varchar). Default is true. include-type-oids: add type oids. Default is false. include-not-null: add not null information as columnoptionals. Default is false. pretty-print: add spaces and indentation to JSON structures. Default is false. write-in-chunks: write after every change instead of every changeset. Default is false. include-lsn: add nextlsn to each changeset. Default is false. include-unchanged-toast (deprecated): add TOAST value even if it was not modified. Since TOAST values are usually large, this option could save IO and bandwidth if it is disabled. Default is true. filter-tables: exclude 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: include only rows from the specified tables. Default is all tables from all schemas. It has the same rules from filter-tables. format-version: defines which format to use. Default is 1.   常用参数是，pretty-print 1   修改pg访问权限，可选： local    replication     myuser                     trust   启动： $ pg_recvlogical -d postgres --slot test_slot --create-slot -P wal2json $ pg_recvlogical -d postgres --slot test_slot --start -o pretty-print=1 -f -   停止直接ctrl+c   删除： pg_recvlogical -d postgres --slot test_slot --drop-slot     测试脚本： $ cat /tmp/example2.sql 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');   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'); -- it is not added to stream because there isn't a pk or a replica identity UPDATE table2_without_pk SET c = 'Anta' WHERE c = 'Tapir'; COMMIT;   SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1'); SELECT 'stop' FROM pg_drop_replication_slot('test_slot');

 

 

3.类似工具

另外关于逻辑槽的工具，在阿里云中看到他们用逻辑复制槽的方式进行数据库迁移：
1.安装他们的ali_decoding工具；
2.创建逻辑复制槽：SELECT * FROM pg_create_logical_replication_slot('replication_slot_test', 'ali_decoding');
然后配置连接参数，开始迁移。


 

4.应对集群切换

 

而客户这边有一些不一样的地方，为了适应集群的切换，他们将逻辑槽所在的目录，pglogical设置为共享目录，当主节点被切换走了，在新的主节点重新启动逻辑复制槽（通过守护进程实现，同时，他们还需要重启数据库才能让共享目录被识别，不能简单的promote方式提示从节点为主节点）。