PG原生解码工具pg_recvlogical的使用-在脑裂时帮我们找回丢失的数据

[postgres@db1 ~]$ cd pgsql/postgresql-11.3/contrib/test_decoding/
[postgres@db1 test_decoding]$ make && make install
复制

[postgres@db1 share]$ pg_recvlogical --create-slot -S logicslot -d test
[postgres@db1 share]$ psql
psql (11.3)
Type "help" for help.


postgres=# select * from pg_replication_slots;
 slot_name |    plugin     | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-----------+---------------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
 logicslot | test_decoding | logical   |  24760 | test     | f         | f      |            |      |    110980560 | 7F/B0178EB8 | 7F/B0178EF0
(1 row)
复制

[postgres@db1 pginst1]$ pg_recvlogical --start -S logicslot -d test -f logical_decoding.log &
复制

[postgres@db1 pginst1]$ psql test
psql (11.3)
Type "help" for help.


test=# create table test(id int);
CREATE TABLE
test=# insert into test values(1);
INSERT 0 1


[postgres@db1 pginst1]$ cat logical_decoding.log
BEGIN 110980560
COMMIT 110980560
BEGIN 110980561
table public.test: INSERT: id[integer]:1
COMMIT 110980561
复制

postgres=# alter system set synchronous_standby_names='';
ALTER SYSTEM
postgres=# \q
[postgres@HWFBS01 ~]$ pg_ctl reload
server signaled
[postgres@HWFBS01 ~]$ psql
psql (11.3)
Type "help" for help.


postgres=# show synchronous_standby_names;
 synchronous_standby_names
---------------------------


(1 row)
复制

postgres=# select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 4C/47000060
(1 row)
复制

[postgres@HWFBS02 ~]$ pg_ctl promote
waiting for server to promote.... done
server promoted
复制

test=# delete from test;
DELETE 32
test=# insert into test values(1);
INSERT 0 1
复制

postgres=# select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 4C/470044E0
(1 row)
复制

[postgres@HWFBS01 ~]$ pg_recvlogical --start -S logicslot -d test -I 4C/47000060 -E 4C/470044E0 -f 1.log
复制

[postgres@HWFBS01 ~]$ cat 1.log
BEGIN 493416304
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
table public.test: DELETE: (no-tuple-data)
COMMIT 493416304
BEGIN 493416305
table public.test: INSERT: id[integer]:1
COMMIT 493416305
BEGIN 493416306
COMMIT 493416306
复制