博客

系统表说明:

http://postgres.cn/docs/11/view-pg-replication-slots.html

————————————————————————–逻辑复制wal2json安装配置———————————————-

1.1、PostgreSQL安装WAL2JSON插件；

$ wget -c –ftp-user=ftp –ftp-password=ftp ftp://192.168.1.1/pub/installpkg/database/postgresql/wal2json-master.zip

$ unzip  wal2json-master.zip

$ chown -R postgres:postgres wal2json-master

$ cd wal2json-master

# Make sure your path includes the bin directory that contains the correct `pg_config`

$ PATH=/home/postgres/pgsql/bin:$PATH

$ USE_PGXS=1 make

$ USE_PGXS=1 make install

1.2 修改参数，然后重启数据库

$vi postgresql.conf

wal_level = logical

备注：详情请参考https://github.com/eulerto/wal2json

1.3、创建PostgreSQL逻辑复制槽；

select * from pg_create_logical_replication_slot(‘risk_logical_slot_81_72′,’wal2json’);

slot_name：逻辑复制槽名称

test_decoding：逻辑复制解码器，使用wal2json

备注：根据逻辑复制的原理，一个库原则上只需创建一个槽即可，切记请使用wal2json解码器

新建逻辑复制账号

create user riskdsc_logical login replication encrypted password ‘*******’;

创建复制槽

每个复制槽都有一个名字，名字可以包含小写字母、数字和下划线字符。

master设置几个参数

max_replication_slots = 10 –最大复制槽数，即pg_replication_slots条数不能超过10

hot_standby = on

wal_level = replica 或者 logical

hot_standby_feedback = on

max_wal_senders = 10

//复制槽视图
select * from pg_replication_slots;

//查看复制槽
select * from pg_stat_replication;

select * from pg_publication_tables;

//创建物理复制槽
SELECT * FROM pg_create_physical_replication_slot('test_slot');

//创建逻辑复制槽
select * from pg_create_logical_replication_slot('test_logical_slot_81_72','wal2json');

//删除复制槽
SELECT * FROM pg_drop_replication_slot('test_slot');

参考：

https://blog.csdn.net/ctypyb2002/article/details/81207837