PostgreSQL学习之pg_recvlogical与pgoutput的使用

       参考：

        pg内功修炼：逻辑复制_pgoutput-CSDN博客        PG原生解码工具pg_recvlogical的使用-在脑裂时帮我们找回丢失的数据-腾讯云开发者社区-腾讯云 (tencent.com)

        postgresql数据库的原生解码插件pg_recvlogical可以将wal日志解码保存到指定文件，准确的说是将复制槽通过指定的逻辑解码插件(test_decoding、pgoutput等)解码后的数据保存为文件。

        pg_recvlogical使用

#创建复制槽
./pg_recvlogical --create-slot -S my_slot_test -d test_pgoutput

#启动复制槽
./pg_recvlogical --start -S my_slot_test  -d test_pgoutput -f test_pgoutput.log

        假如启动复制槽后，在另一终端登录数据库test_pgoutput 并执行insert等操作，则会在文件test_pgoutput.log中看到如下内容：

[postgres@localhost bin]$ cat ./test_pgoutput.log
BEGIN 740
table public.test_table: INSERT: id[integer]:9 name[character varying]:'Alice3' age[integer]:33
table public.test_table: INSERT: id[integer]:10 name[character varying]:'Bob3' age[integer]:253
COMMIT 740

        上述复制槽使用的是默认插件 test_decoding。

        pg_recvlogical使用pgoutput

        首先创建使用pgoutput做解码插件的复制槽，然后启动复制槽，但会出现如下错误：

        client sent proto_version=0 but we only support protocol 1 or higher

[postgres@localhost bin]$ ./pg_recvlogical --create-slot -S my_slot_pgoutput -d test_pgoutput --plugin=pgoutput
[postgres@localhost bin]$ ./pg_recvlogical --start -S my_slot_pgoutput -d test_pgoutput -f test_pgoutput.log
pg_recvlogical: error: could not send replication command "START_REPLICATION SLOT "my_slot_pgoutput" LOGICAL 0/0": ERROR:  client sent proto_version=0 but we only support protocol 1 or higher
CONTEXT:  slot "my_slot_pgoutput", output plugin "pgoutput", in the startup callback
pg_recvlogical: disconnected; waiting 5 seconds to try again
^Cpg_recvlogical: error: could not send replication command "START_REPLICATION SLOT "my_slot_pgoutput" LOGICAL 0/0": ERROR:  client sent proto_version=0 but we only support protocol 1 or higher
CONTEXT:  slot "my_slot_pgoutput", output plugin "pgoutput", in the startup callback

        上述错误是加密插件pgoutput的错误，具体是期望的版本与接受到的pg_recvlogical的发送版本不一致，这时需要使用pg_recvlogical的- o参数配置pgoutput版本

[postgres@localhost bin]$ ./pg_recvlogical --start -S my_slot_pgoutput  -d test_pgoutput -f test_pgoutput.log -o proto_version=1
pg_recvlogical: error: could not send replication command "START_REPLICATION SLOT "my_slot_pgoutput" LOGICAL 0/0 ("proto_version" '1')": ERROR:  publication_names parameter missing
CONTEXT:  slot "my_slot_pgoutput", output plugin "pgoutput", in the startup callback
pg_recvlogical: disconnected; waiting 5 seconds to try again
^Cpg_recvlogical: error: could not send replication command "START_REPLICATION SLOT "my_slot_pgoutput" LOGICAL 0/0 ("proto_version" '1')": ERROR:  publication_names parameter missing
CONTEXT:  slot "my_slot_pgoutput", output plugin "pgoutput", in the startup callback

        增加了参数-o proto_version=1，版本错误问题没有了，但又出现了新的错误：

 publication_names parameter missing

        登录数据库，创建发布

[postgres@localhost bin]$ ./psql -d test_pgoutput
psql (14.7)
Type "help" for help.

test_pgoutput=# CREATE PUBLICATION pgoutput_pub FOR ALL TABLES;
ERROR:  publication "pgoutput_pub" already exists
test_pgoutput=# SELECT * FROM pg_publication;
  oid  |   pubname    | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
-------+--------------+----------+--------------+-----------+-----------+-----------+-------------+------------
 16392 | pgoutput_pub |       10 | t            | t         | t         | t         | t           | f
(1 row)

test_pgoutput=#

        增加参数再次测试：

[postgres@localhost bin]$ ./pg_recvlogical --start -S my_slot_pgoutput  -d test_pgoutput -f test_pgoutput.log -o proto_version=1 -o publication_names=pgoutput_pub

        运行成功。

        另一终端做insert等操作，然后查看文件test_pgoutput.log内容：

[postgres@localhost bin]$ cat ./test_pgoutput.log
Bp▒▒▒%▒M▒
R@publictest_tabledid▒▒▒▒nameage▒▒▒▒
I@Nt13tAlice5t66
I@Nt14tBob5t256
Cp▒p▒▒▒▒%▒M