pg部署逻辑复制

环境:
OS:Centos 7
DB:pg12

主库:192.168.1.102
从库:192.168.1.103

 

逻辑复制可以支持不同版本的复制,pg14->pg15验证了没有问题 

 

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

## 修改从库节点参数(系统默认就是如下值,从库可以不用修改wal_level参数)

wal_level = logical # minimal, replica, or logical
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.主库创建用户

-bash-4.2$ psql -h localhost -U postgres -p5432
psql (15.6, server 14.11)
Type "help" for help.

postgres=# create user logical_user replication login connection limit 10 encrypted password 'logical_user';
CREATE ROLE

 

4.主库准备发布的表

##创建开发用户

 

postgres=# create user hxl with password 'postgres';
CREATE ROLE

##创建数据库
postgres=# create database db_rep owner hxl;
CREATE DATABASE

 

##开发用户登录建表

 

-bash-4.2$ psql -h localhost -U hxl -p5432 -d db_rep
psql (15.6, server 14.11)
Type "help" for help.

 

db_rep=>

 

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.创建发布

创建发版需要在上面创建的库db_rep下创建

[postgres@localhost ~]$ psql -h localhost -U hxl -p5432 -d db_rep

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)

 

##授权

-bash-4.2$ psql -h localhost -U postgres -p5432
psql (15.6, server 14.11)
Type "help" for help.

postgres=# \c db_rep
psql (15.6, server 14.11)
You are now connected to database "db_rep" as user "postgres".
db_rep=# grant usage on schema public to logical_user;
GRANT
db_rep=# grant select on tb_aa to logical_user;
GRANT

 

 

 

 

 

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

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

 

##创建开发用户和数据库

 

-bash-4.2$ psql -h localhost -U postgres -p5432
psql (15.6, server 14.11)
Type "help" for help.

postgres=# create user hxl with password 'postgres';
CREATE ROLE
postgres=# create database db_rep owner hxl;
CREATE DATABASE
postgres=# 

 

 

 

##开发账户登录创建表

 

-bash-4.2$ psql -h localhost -U hxl -p5432 -d db_rep
psql (15.6, server 14.11)
Type "help" for help.

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

 

 

 

 

 

2.创建订阅　　　　

 

-bash-4.2$ psql -h localhost -U hxl -p5432 -d db_rep
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;
ERROR:  must be superuser to create subscriptions

 

##需要使用超级用户登录创建

-bash-4.2$ psql -h localhost -U postgres -p5432
psql (15.6, server 14.11)
Type "help" for help.

postgres=# \c db_rep
psql (15.6, server 14.11)
You are now connected to database "db_rep" as user "postgres".
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)

发现之前主库写入的数据也会同步过来了

 

主库写入数据

 

db_rep=> insert into tb_aa values(4,'name4'),(5,'name5'),(6,'name6');
INSERT 0 3

 

 

从库也同步过来了

db_rep=# select * from tb_aa;
 id | name  
----+-------
  1 | name1
  2 | name2
  3 | name3
  4 | name4
  5 | name5
  6 | name6
(6 rows)

 

############################主库添加字段###########################

主库添加字段:

 

-bash-4.2$ psql -h localhost -U hxl -p5432 -d db_rep
psql (15.6, server 14.11)
Type "help" for help.

db_rep=> alter table tb_aa add column name1 varchar(32);
ALTER TABLE

然后写入数据
db_rep=> insert into tb_aa values(7,'name7','name77'),(8,'name8','name88'),(9,'name9','name99');
INSERT 0 3

 

 

从库查看:

db_rep=# select * from tb_aa;
 id | name  
----+-------
  1 | name1
  2 | name2
  3 | name3
  4 | name4
  5 | name5
  6 | name6
(6 rows)

这个时候从库是没有同步的.
可以看到从库有报错信息:

2024-05-20 16:45:31 CST [8035]: [2-1] user=,db=,app=,client=ERROR:  logical replication target relation "public.tb_aa" is missing replicated column: "name1"
2024-05-20 16:45:31 CST [5152]: [28-1] user=,db=,app=,client=LOG:  background worker "logical replication worker" (PID 8035) exited with exit code 1

 

从库添加字段

-bash-4.2$ psql -h localhost -U postgres -p5432
psql (15.6, server 14.11)
Type "help" for help.

postgres=# \c db_rep
db_rep=> alter table tb_aa add column name1 varchar(32);

再次查看从库同步过来了
db_rep=# select * from tb_aa;
 id | name  | name1  
----+-------+--------
  1 | name1 | 
  2 | name2 | 
  3 | name3 | 
  4 | name4 | 
  5 | name5 | 
  6 | name6 | 
  7 | name7 | name77
  8 | name8 | name88
  9 | name9 | name99
(9 rows)

 

 

删除字段
主库删除字段并写入数据
alter table tb_aa drop column name1;
insert into tb_aa values(10,'name10'),(11,'name11'),(12,'name12');
发现新增字段是可以同步的,但是从库的字段需要手工删除

 

db_rep=# select * from tb_aa;
 id |  name  | name1  
----+--------+--------
  1 | name1  | 
  2 | name2  | 
  3 | name3  | 
  4 | name4  | 
  5 | name5  | 
  6 | name6  | 
  7 | name7  | name77
  8 | name8  | name88
  9 | name9  | name99
 10 | name10 | 
 11 | name11 | 
 12 | name12 | 
(12 rows)

从库需要手工删除字段

db_rep=# alter table tb_aa drop column name1;
ALTER TABLE

 

修改字段长度

 

alter table tb_aa alter column name type varchar(35);

 

写入超过32个字符的数据
db_rep=> insert into tb_aa values(13,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
INSERT 0 1

 

从库查看

db_rep=# select * from tb_aa;
 id |  name  
----+--------
  1 | name1
  2 | name2
  3 | name3
  4 | name4
  5 | name5
  6 | name6
  7 | name7
  8 | name8
  9 | name9
 10 | name10
 11 | name11
 12 | name12
(12 rows)

数据没有同步过来,查看日志:

2024-05-20 17:24:39 CST [9264]: [2-1] user=,db=,app=,client=ERROR:  value too long for type character varying(32)
2024-05-20 17:24:39 CST [9264]: [3-1] user=,db=,app=,client=CONTEXT:  processing remote data for replication target relation "public.tb_aa" column "name"

 

从库需要手工修改字段类型,数据马上同步过来了

db_rep=# alter table tb_aa alter column name type varchar(35);
ALTER TABLE
db_rep=# select * from tb_aa;
 id |                name                 
----+-------------------------------------
  1 | name1
  2 | name2
  3 | name3
  4 | name4
  5 | name5
  6 | name6
  7 | name7
  8 | name8
  9 | name9
 10 | name10
 11 | name11
 12 | name12
 13 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
(13 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 ;
db_rep=>drop publication pub_test;

 

删除订阅(从库上操作)
-bash-4.2$ psql -h localhost -U postgres -p5432
postgres=# \c db_rep
db_rep=# select * from pg_subscription;
db_rep=# drop subscription sub_test;

 

 

 

主库查看复制的表

 

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