环境:
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)
标签：逻辑,postgres,rep,db,复制,pg,test,tb,user From： https://www.cnblogs.com/hxlasky/p/16924619.html