pg部署逻辑复制

环境:
OS:Centos 7
DB:pg12

主库:192.168.1.102
从库:192.168.1.103

1.主从库修改参数
## 修改发布节点参数
##wal_level值要为logical,修改该参数需要重启服务
vi /opt/pg12/data/postgresql.conf

wal_level = logical # minimal, replica, or logical
max_replication_slots = 10 # max number of replication slots

## 修改从库节点参数(系统默认就是如下值,可以不用修改)
max_replication_slots = 10 # max number of replication slots
max_logical_replication_workers = 4 # taken from max_worker_processes

2.重启主库
[root@localhost ~]# systemctl stop postgresql-12
[root@localhost ~]# systemctl start postgresql-12

3.主库创建用户
postgres=# create user logical_user replication login connection limit 10 encrypted password 'logical_user';
CREATE ROLE

4.主库准备发布的表
##建库
postgres=# create database db_rep;
CREATE DATABASE

##建表
postgres=# c db_rep
You are now connected to database "db_rep" as user "postgres".

db_rep=# create table tb_aa(id int,name varchar(32));
CREATE TABLE

db_rep=# insert into tb_aa values(1,'name1'),(2,'name2'),(3,'name3');
INSERT 0 3

5.创建发布

[postgres@localhost ~]$ psql
psql (12.13)
Type "help" for help.

postgres=# c db_rep;
You are now connected to database "db_rep" as user "postgres".
db_rep=# create publication pub_test for table tb_aa;
CREATE PUBLICATION

##查看发布信息
db_rep=# select * from pg_publication;
  oid  | pubname  | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate 
-------+----------+----------+--------------+-----------+-----------+-----------+-------------
 18735 | pub_test |       10 | f            | t         | t         | t         | t
(1 row)

##授权
db_rep=# grant usage on schema public to logical_user;
GRANT

db_rep=# grant select on tb_aa to logical_user;
GRANT

###################从库上操作############################

1.创建与主库一致名称的数据库和表(因为逻辑复制不会同步主库的ddl)

[postgres@localhost ~]$ psql
psql (12.13)
Type "help" for help.

postgres=# create database db_rep;
CREATE DATABASE


postgres=# c db_rep;
You are now connected to database "db_rep" as user "postgres".

db_rep=#create table tb_aa(id int,name varchar(32));
CREATE TABLE

2.创建订阅

db_rep=# create subscription sub_test connection 'host=192.168.1.102 port=5432 dbname=db_rep user=logical_user password=logical_user' publication pub_test;
NOTICE:  created replication slot "sub_test" on publisher
CREATE SUBSCRIPTION

3.查看订阅信息

db_rep=# select * from pg_subscription;
  oid  | subdbid | subname  | subowner | subenabled |                                    subconninfo                 
                    | subslotname | subsynccommit | subpublications 
-------+---------+----------+----------+------------+----------------------------------------------------------------
--------------------+-------------+---------------+-----------------
 18734 |   18730 | sub_test |       10 | t          | host=192.168.1.102 port=5432 dbname=db_rep user=logical_user pa
ssword=logical_user | sub_test    | off           | {pub_test}
(1 row)

4.验证数据
从库查询:
db_rep=# select * from tb_aa;
id | name
----+-------
1 | name1
2 | name2
3 | name3
(3 rows)

发现数据同步过来了

################################新加一个表####################

1.在主库上原来的库上创建新的表

[postgres@localhost ~]$ psql
psql (12.13)
Type "help" for help.
postgres=# postgres=# c db_rep
You are now connected to database "db_rep" as user "postgres".

db_rep=# create table tb_aa01(id int,name varchar(32));
CREATE TABLE

insert into tb_aa01 values(1,'name1');
insert into tb_aa01 values(2,'name2');
insert into tb_aa01 values(3,'name3');
insert into tb_aa01 values(4,'name4');
insert into tb_aa01 values(5,'name5');

授权(在db_rep用户下授权)
db_rep=#grant usage on schema public to logical_user;
GRANT
db_rep=#grant select on tb_aa01 to logical_user;
GRANT

2.从库上一样的表
postgres=# c db_rep;
You are now connected to database "db_rep" as user "postgres".
db_rep=# create table tb_aa01(id int,name varchar(32));
CREATE TABLE

3.修改主库的发布
db_rep=# alter publication pub_test add table tb_aa01;
ALTER PUBLICATION

4.刷新从库上的订阅
[postgres@localhost ~]$ psql
psql (12.13)
Type "help" for help.

postgres=# c db_rep
You are now connected to database "db_rep" as user "postgres".
db_rep=# alter subscription sub_test refresh publication;
ALTER SUBSCRIPTION

然后查询数据
db_rep=# select * from tb_aa01;
id | name
----+-------
1 | name1
2 | name2
3 | name3
4 | name4
5 | name5
(5 rows)

####################日常维护########################
-- 添加发布表(主库操作)
alter publication pub_test add table bb;

-- 刷新订阅信息(从库操作)
alter subscription sub_test refresh publication;

-- 停止逻辑复制(主库操作)
alter subscription pub_test disable;

-- 启动逻辑复制(主库操作)
alter subscription pub_test enable;

主库查看复制的表

db_rep=# select * from pg_publication_tables ;
pubname | schemaname | tablename
----------+------------+-----------
pub_test | public | tb_aa
pub_test | public | tb_aa01
(2 rows)