13.PG14逻辑复制总结（一）

前言

一、PG14逻辑复制

二、发布和订阅

1.publication(发布)–主库

create publication 名称
[for table [only] 表名[*][,…]
|for all tables]
[with (publication_parameter [=值][,…])]

for table:表示要复制的表，可以通过’,'定义多个表。
for all tables:表示数据库的所有表都要复制。
with:表示DML操作行为，忽略表示全部DML操作。
only:忽略，则继承该复制表的所有表也会被复制。

--创建一个发布，发布两个表中所有更改:
create publication mypub1 for table users,departments;

--创建一个发布，发布所有表中的所有更改:
create publication mypub2 for all tables;

--创建一个发布，只发布一个表中的insert操作:
create publication mypub_insert_only for table mydata with (publish = 'insert');

--将发布修改为只发布删除和更新:
alter publication noinsert set (publish = 'update,delete');

--给发布添加一些表:
alter publication mypub1 add table emp,dept;

--查看发布:
select * from pg_publication;

2.subscription(订阅)–从库

create subscription subscription_name
connection ‘conninfo’
publication publication_name[,…]
[with (subscription_parameter[= 值][,…])]

connection:表示连接master节点的字符串信息。
publication:表示master节点的publication对象。
with:表示DML操作，忽略表示全部DML操作。

--创建一个到远程服务器订阅，复制发布mypub1和insert_only中的表，并在提交时立即开始复制:
create subscription mysub1
connection 'host=192.168.66.91 port=5432 user=replhc dbname=lhcdb'
publication mypub1,insert_only;

--创建一个到远程服务器订阅，复制insert_only发布中的表，并且不开始复制直到稍后启用复制:
create subscription mysub2
connection 'host=192.168.66.91 port=5432 user=replhc dbname=lhcdb'
publication insert_only
with (enabled = false);

--将订阅的发布更改为insert_only
alter subscription mypub1 set publication insert_only;

--禁用(停止)订阅:
alter subscription mypub1 disable;

3.replication slots(复制槽)

--postgresql.conf关联配置
wal_level = logical
max_replication_slots = 10
注意:参数设置后，重启数据后生效

--创建复制槽
select * from pg_create_logical_replication_slot('log_slot1','test_decoding');

--查看复制槽信息
select * from pg_replication_slots where slot_name='log_slot1';

--查看复制槽解析记录，记录只能查询一次，之后查询为空，如果想重复查询到日志，
--需使用pg_logical_slot_peek_changes()函数
select * from pg_logical_slot_get_changes('log_slot1',NULL,NULL);

--使用pg_recvlogical接收数据变化
pg_recvlogical -h 127.0.0.1 -d postgres --slot log_slot1 --start -f -

4.逻辑复制示例

--发布节点:
10.206.132.196:5432

--订阅节点:
10.206.132.168:5432

--测试远程登录:
psql -U postgres -h 10.206.132.196 -p 5432
psql -U postgres -h 10.206.132.168 -p 5432

--发布节点参数配置
pg_hba.conf:host    all    repuser      订阅段IP/32       trust

cd /data/postgresql14/pgdata
[postgres@lhc_hdp01 pgdata]$vim pg_hba.conf
#TYPE   DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    all             repuser         10.206.132.168/32       trust
host    all             all             0.0.0.0/0               md5
host    replication     all             0.0.0.0/0               md5

--发布端配置归档
cd /data/postgresql14/pgdata
wal_level = logical
listen_addresses = '*'
max_replication_slots = 10

[postgres@lhc_hdp01 pgdata]$vim postgresql.conf
wal_level = logical
listen_addresses = '*'
port = 5432
unix_socket_directories = '/data/postgresql14/pgdata'
logging_collector = on
log_directory = 'pg14_log'
log_filename = 'postgresql14-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on

--重启发布端
pg_ctl stop
pg_ctl status
pg_ctl start
pg_ctl status

select * from pg_settings
where name in ('wal_level','archive_mode','archive_command','listen_addresses','max_replication_slots','max_wal_senders');

"archive_command"	"(disabled)"
"archive_mode"	"off"
"listen_addresses"	"*"
"max_replication_slots"	"10"
"max_wal_senders"	"10"
"wal_level"	"replica"

发布节点创建复制用户和表

--用于逻辑复制用户必须至少是replication角色
create user repuser replication login connection limit 10 encrypted password 'lhc';

grant connect on database postgres to repuser;

create table test(id int4 primary key,name text);
insert into test values (1,'a');

grant select on test to repuser;
grant usage on schema public to repuser;

\dp+ test;
postgres=# \dp+ test
                                Access privileges
 Schema | Name | Type  |     Access privileges     | Column privileges | Policies
--------+------+-------+---------------------------+-------------------+----------
 public | test | table | postgres=arwdDxt/postgres+|                   |
        |      |       | repuser=r/postgres        |                   |
		

发布节点创建发布

--为表创建发布
create publication mypub1 for table test;
注意:如果需发布多张表，则表名间用逗号(,)分隔，
如果需发布所有库，则将for table调整为for all tables。

--查看创建的发布
select * from pg_publication;

postgres=# select * from pg_publication;
  oid  | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
-------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
 16392 | mypub1  |       10 | f            | t         | t         | t         | t           | f
(1 row)

pubname:发布的名称。
pubowner:发布的属主。
puballtables:是否发布数据库中所有的表。
pubinsert:t表示仅发布表上的insert操作。
pubupdate:t表示仅发布表上的update操作。
pubdelete:t表示仅发布表上的delete操作。
pubtruncate:t表示仅发布表上的truncate操作。

--无主键或者唯一性约束时开启补全日志，此时会导致复制效率很低
alter table test replica identity full;

订阅节点

--订阅节点创建接受表及订阅
create table test(id int4 primary key,name text);
create subscription mysub1 connection 'host=10.206.132.196 port=5432 dbname=postgres user=repuser password=lhc' publication mypub1;

select * from pg_subscription;
postgres=# select * from pg_subscription;
  oid  | subdbid | subname | subowner | subenabled | subbinary | substream |subconninfo|                                                               subslotname | subsynccommit | subpublications
-------+---------+---------+----------+------------+-----------+-----------+-------------------------------------------------------------------------+-------------+---------------+-----------------
 16391 |   13892 | mysub1  |       10 | t          | f         | f         | host=10.206.132.196 port=5432 dbname=postgres user=repuser password=lhc | mysub1      | off           | {mypub1}
(1 row)


--此时在主库可以查询到复制槽
select slot_name,plugin,slot_type,database,active,restart_lsn from pg_replication_slots;
postgres=# select slot_name,plugin,slot_type,database,active,restart_lsn from pg_replication_slots;
 slot_name |  plugin  | slot_type | database | active | restart_lsn
-----------+----------+-----------+----------+--------+-------------
 mysub1    | pgoutput | logical   | postgres | t      | 0/26DB290
(1 row)


测试同步

--发布节点分别向表中插入、删除数据
--插入
insert into test values (2,'lhc');
insert into test values (3,'lhq');
insert into test values (4,'lhl');
insert into test values (5,'ljq');

--删除
delete from test where id = 1;

--订阅节点查看逻辑复制效果
select * from test;

大表同步
--1.发布节点修改发布，测试插入10万数据的逻辑同步
create table big_test
(
id int4 primary key,
create_time timestamp without time zone default clock_timestamp(),
name character varying(32)
);

insert into big_test (id,name) select n,n*random()*10000 from generate_series(1,100000) n;
grant select on big_test to repuser;
alter publication mypub1 add table big_test;

--2.订阅节点创建同步表并手动执行刷新命令
create table big_test
(
id int4 primary key,
create_time timestamp without time zone default clock_timestamp(),
name character varying(32)
);
alter subscription mysub1 refresh publication;

--3.订阅节点创建逻辑同步结果
select count(*) from big_test;

结论:10万数据用了不到30秒。

5.运维监控

--订阅节点需要刷新才能看到新添加表
alter publication mypub1 add table test2;

--删除发布节点的表
alter publication mypub1 drop table test2;

--刷新订阅
alter subscription mysub1 refresh publication;

--删除发布
drop publication mypub1;

--查询发布
select * from pg_publication;
postgres=# select * from pg_publication;
  oid  | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
-------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
 16392 | mypub1  |       10 | f            | t         | t         | t         | t           | f
(1 row)

--发布哪些表
select * from pg_publication_tables;
postgres=# select * from pg_publication_tables;
 pubname | schemaname | tablename
---------+------------+-----------
 mypub1  | public     | test
 mypub1  | public     | big_test
(2 rows)



--所有的订阅者
select * from pg_stat_replication;
postgres=# select * from pg_stat_replication;
 pid  | usesysid | usename | application_name |  client_addr   | client_hostname | client_port |     backend_start             | backend_xmin |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
 8314 |    16384 | repuser | mysub1           | 10.206.132.168 |                 |       34034 | 2021-11-11 16:26:17.080998+08 |              | streaming | 0/26BB7C8 | 0/26BB7C8 | 0/26BB7C8 | 0/26BB7C8  |           |           |            |             0 | async      | 2021-11-12 08:19:31.615763+08
(1 row)


--复制进度
select * from pg_stat_subscription;
postgres=# select * from pg_stat_subscription;
 subid | subname | pid  | relid | received_lsn |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |        latest_end_time
-------+---------+------+-------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------
 16391 | mysub1  | 7619 |       | 0/26BB7C8    | 2021-11-12 08:30:12.512939+08 | 2021-11-12 08:30:12.547975+08 | 0/26BB7C8      | 2021-11-12 08:30:12.512939+08
(1 row)

--订阅
select * from pg_subscription;
postgres=# select * from pg_subscription;
  oid  | subdbid | subname | subowner | subenabled | subbinary | substream |                               subconninfo                               | subslotname | subsynccommit | subpublications
-------+---------+---------+----------+------------+-----------+-----------+-------------------------------------------------------------------------+-------------+---------------+-----------------
 16391 |   13892 | mysub1  |       10 | t          | f         | f         | host=10.206.132.196 port=5432 dbname=postgres user=repuser password=lhc | mysub1      | off           | {mypub1}
(1 row)

--订阅哪些表
select *,srrelid::regclass from pg_subscription_rel;
postgres=# select *,srrelid::regclass from pg_subscription_rel;
 srsubid | srrelid | srsubstate | srsublsn  | srrelid
---------+---------+------------+-----------+----------
   16391 |   16384 | r          | 0/170E338 | test
   16391 |   16400 | r          | 0/26BB6E0 | big_test
(2 rows)

--删除订阅
drop subscription mysub1;