逻辑复制( Logical decoding) 是 PostgreSQL9.4 的关键特性之一，Logical decoding 允许读取 WAL 日志，并将数据变化解析成目标格式， 这块内容很多，下面仅做一些简单的分享。
在开启逻辑复制之前，需要设置 wal_level 和 max_replication_slots 参数.
创建逻辑 Slot
设置 postgresql.conf 参数
1
2
| wal_level = logical
max_replication_slots = 2
|
备注： max_replication_slots 值最少需设置成 1，设置后重启数据库生效。
创建逻辑 slot
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| [pg94@db1 ~]$ psql
psql (9.4beta1)
Type "help" for help.
postgres=# SELECT * FROM pg_create_logical_replication_slot('log_slot1', 'test_decoding');
slotname | xlog_position
-----------+---------------
log_slot1 | 0/540001E8
(1 row)
postgres=# select * from pg_replication_slots where slot_name='log_slot1';
slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn
-----------+---------------+-----------+--------+----------+--------+------+--------------+-------------
log_slot1 | test_decoding | logical | 12993 | postgres | f | | 1902 | 0/540001B4
(1 row)
|
DDL 操作不被记录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| postgres=# SELECT * FROM pg_logical_slot_get_changes('log_slot1', NULL, NULL);
location | xid | data
----------+-----+------
(0 rows)
postgres=# create table test_logical(id int4);
CREATE TABLE
postgres=# SELECT * FROM pg_logical_slot_get_changes('log_slot1', NULL, NULL);
location | xid | data
------------+------+-------------
0/540003F8 | 1902 | BEGIN 1902
0/54013CE4 | 1902 | COMMIT 1902
(2 rows)
postgres=# SELECT * FROM pg_logical_slot_get_changes('log_slot1', NULL, NULL);
location | xid | data
----------+-----+------
(0 rows)
|
备注：记录只能查询一次，之后查询为空。
DML 操作被解析
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| postgres=# insert into test_logical (id) values (1);
INSERT 0 1
postgres=# SELECT * FROM pg_logical_slot_get_changes('log_slot1', NULL, NULL);
location | xid | data
------------+------+--------------------------------------------------
0/54013DE0 | 1903 | BEGIN 1903
0/54013DE0 | 1903 | table public.test_logical: INSERT: id[integer]:1
0/54013E58 | 1903 | COMMIT 1903
(3 rows)
postgres=# insert into test_logical (id) values (2),(3),(4);
INSERT 0 3
postgres=# SELECT * FROM pg_logical_slot_get_changes('log_slot1', NULL, NULL);
location | xid | data
------------+------+--------------------------------------------------
0/54013F38 | 1905 | BEGIN 1905
0/54013F38 | 1905 | table public.test_logical: INSERT: id[integer]:2
0/54013F74 | 1905 | table public.test_logical: INSERT: id[integer]:3
0/54013FB0 | 1905 | table public.test_logical: INSERT: id[integer]:4
0/5401403C | 1905 | COMMIT 1905
(5 rows)
|
查询解析日志
重复查询解析日志
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| postgres=# insert into test_logical (id) values (7);
INSERT 0 1
postgres=# select * from pg_logical_slot_peek_changes('log_slot1',NULL,NULL);
location | xid | data
------------+------+--------------------------------------------------
0/54014380 | 1908 | BEGIN 1908
0/54014380 | 1908 | table public.test_logical: INSERT: id[integer]:7
0/540143F8 | 1908 | COMMIT 1908
(3 rows)
postgres=# select * from pg_logical_slot_peek_changes('log_slot1',NULL,NULL);
location | xid | data
------------+------+--------------------------------------------------
0/54014380 | 1908 | BEGIN 1908
0/54014380 | 1908 | table public.test_logical: INSERT: id[integer]:7
0/540143F8 | 1908 | COMMIT 1908
(3 rows)
|
备注:如果想重复查询到日志，需使用 pg_logical_slot_peek_changes() 函数。接下来使用工具接收数据变化，这里使用 pg_recvlogical。
使用 pg_recvlogical 接收数据变化
1
| pg_recvlogical -h 127.0.0.1 -d postgres --slot log_slot1 --start -f -
|
会话1: 创建表并插入数据
1
2
3
4
5
6
7
8
| postgres=# create table test_1(id int4);
CREATE TABLE
postgres=# insert into test_1 values (1);
INSERT 0 1
postgres=# insert into test_1 values (2);
INSERT 0 1
postgres=# delete from test_1 where id=1;
DELETE 1
|
日志被解析
1
2
3
4
5
6
7
8
9
10
11
12
13
| [pg94@db1 ~]$ pg_recvlogical -h 127.0.0.1 -d postgres
BEGIN 1909
COMMIT 1909
BEGIN 1910
table public.test_1: INSERT: id[integer]:1
COMMIT 1910
BEGIN 1911
table public.test_1: INSERT: id[integer]:2
COMMIT 1911
BEGIN 1912
table public.test_1: DELETE: (no-tuple-data)
COMMIT 1912
|
备注： 数据日志被解析出来，以上仅是对逻辑复制简单实验，更多内容还需探索。
参考
原创文章，作者：506227337，如若转载，请注明出处：https://blog.ytso.com/238070.html
