| PostgreSQL 9.6.0 锟街诧拷
| |||
|---|---|---|---|
| 锟斤拷一页 | 锟斤拷一锟斤拷 | 锟斤拷 47. 锟竭硷拷锟斤拷锟斤拷 | 锟斤拷一页 |
锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟绞撅拷锟绞癸拷锟� SQL 锟接口匡拷锟斤拷锟竭硷拷锟斤拷锟诫。
锟斤拷锟斤拷锟斤拷使锟斤拷锟竭硷拷锟斤拷锟斤拷之前锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟�wal_level为 logical锟斤拷锟斤拷锟斤拷max_replication_slots 锟斤拷锟斤拷锟斤拷锟劫憋拷锟斤拷锟斤拷为 1锟斤拷然锟斤拷锟斤拷应锟斤拷锟斤拷为一锟斤拷锟斤拷锟斤拷锟矫伙拷锟斤拷锟接碉拷目锟斤拷锟斤拷锟捷库（锟斤拷锟斤拷锟斤拷 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷postgres锟斤拷锟斤拷
postgres=# -- 使锟斤拷锟斤拷锟斤拷锟斤拷'test_decoding'锟斤拷锟斤拷一锟斤拷锟斤拷为'regression_slot'锟侥诧拷
postgres=# SELECT * FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');
slot_name | xlog_position
-----------------+---------------
regression_slot | 0/16B1970
(1 row)
postgres=# SELECT slot_name, plugin, slot_type, database, active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots;
slot_name | plugin | slot_type | database | active | restart_lsn | confirmed_flush_lsn
-----------------+---------------+-----------+----------+--------+-------------+-----------------
regression_slot | test_decoding | logical | postgres | f | 0/16A4408 | 0/16A4440
(1 row)
postgres=# -- 目前锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷
postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
location | xid | data
----------+-----+------
(0 rows)
postgres=# CREATE TABLE data(id serial primary key, data text);
CREATE TABLE
postgres=# -- DDL 没锟叫憋拷锟斤拷锟狡ｏ拷锟斤拷锟斤拷憬拷锟斤拷锟斤拷亩锟斤拷锟街伙拷锟斤拷锟斤拷锟�
postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
location | xid | data
-----------+-----+------------
0/16D5D48 | 688 | BEGIN 688
0/16E0380 | 688 | COMMIT 688
(2 rows)
postgres=# -- 一锟斤拷锟斤拷锟斤拷锟斤拷锟侥ｏ拷锟斤拷锟角会被锟斤拷锟窖碉拷锟斤拷锟揭诧拷锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟叫憋拷锟斤拷锟斤拷锟斤拷
postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
location | xid | data
----------+-----+------
(0 rows)
postgres=# BEGIN;
postgres=# INSERT INTO data(data) VALUES('1');
postgres=# INSERT INTO data(data) VALUES('2');
postgres=# COMMIT;
postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
location | xid | data
-----------+-----+-----------------------------------------------
0/16E0478 | 689 | BEGIN 689
0/16E0478 | 689 | table public.data: INSERT: id[integer]:1 data[text]:'1'
0/16E0580 | 689 | table public.data: INSERT: id[integer]:2 data[text]:'2'
0/16E0650 | 689 | COMMIT 689
(4 rows)
postgres=# INSERT INTO data(data) VALUES('3');
postgres=# -- 锟斤拷也锟斤拷锟皆诧拷锟斤拷锟窖革拷锟侥讹拷锟节革拷锟斤拷锟斤拷锟斤拷锟饺匡拷一锟斤拷
postgres=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL);
location | xid | data
-----------+-----+-----------------------------------------------
0/16E09C0 | 690 | BEGIN 690
0/16E09C0 | 690 | table public.data: INSERT: id[integer]:3 data[text]:'3'
0/16E0B90 | 690 | COMMIT 690
(3 rows)
postgres=# -- 锟斤拷锟斤拷锟斤拷锟斤拷 pg_logical_slot_peek_changes() 锟侥碉拷锟斤拷锟劫次凤拷锟斤拷锟斤拷同锟侥革拷锟斤拷
postgres=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL);
location | xid | data
-----------+-----+-----------------------------------------------
0/16E09C0 | 690 | BEGIN 690
0/16E09C0 | 690 | table public.data: INSERT: id[integer]:3 data[text]:'3'
0/16E0B90 | 690 | COMMIT 690
(3 rows)
postgres=# -- 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷选锟斤拷锟斤拷影锟斤拷锟绞斤拷锟�
postgres=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL, 'include-timestamp', 'on');
location | xid | data
-----------+-----+-----------------------------------------------
0/16E09C0 | 690 | BEGIN 690
0/16E09C0 | 690 | table public.data: INSERT: id[integer]:3 data[text]:'3'
0/16E0B90 | 690 | COMMIT 690 (at 2014-02-27 16:41:51.863092+01)
(3 rows)
postgres=# -- 锟斤拷锟斤拷锟斤拷锟斤拷要一锟斤拷锟桔猴拷锟阶★拷锟斤拷锟斤拷锟斤拷锟酵Ｖ癸拷锟斤拷姆锟斤拷锟斤拷锟斤拷锟皆达拷锟�
postgres=# SELECT pg_drop_replication_slot('regression_slot');
pg_drop_replication_slot
-----------------------
(1 row)锟斤拷锟斤拷锟斤拷锟斤拷锟秸故撅拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷协锟斤拷锟斤拷使锟斤拷 PostgreSQL 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟侥筹拷锟斤拷pg_recvlogical锟斤拷锟斤拷锟斤拷锟竭硷拷锟斤拷锟诫。锟斤拷要锟斤拷锟斤拷锟矫客伙拷锟斤拷锟斤拷证锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟接ｏ拷锟斤拷锟斤拷 26.2.5.1 锟斤拷锟斤拷锟斤拷锟斤拷锟揭帮拷max_wal_senders锟斤拷锟矫筹拷锟姐够锟斤拷锟斤拷锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷印锟�
$ pg_recvlogical -d postgres --slot test --create-slot
$ pg_recvlogical -d postgres --slot test --start -f -
Control+Z
$ psql -d postgres -c "INSERT INTO data(data) VALUES('4');"
$ fg
BEGIN 693
table public.data: INSERT: id[integer]:4 data[text]:'4'
COMMIT 693
Control+C
$ pg_recvlogical -d postgres --slot test --drop-slot