PostgreSQL部署逻辑复制过程详解

目录

• 1.环境准备
• 2.发布端配置参数
• 3.发布端配置pg_hba.conf
• 4.订阅端配置参数
• 5.发布端创建逻辑复制用户，并具备replication复制权限（可选）
• 6.发布端创建发布
• 7.发布端给复制用户授权
• 8.订阅端创建表
• 9.订阅端创建订阅
• 10.订阅端给复制用户授权
• 11.配置完成，发布端查看信息
• 12.测试逻辑复制

1.环境准备

角色	主机名	IP	端口	数据库名	用户名	版本
发布端	PostgreSQL	192.168.80.239	5432	pubdb	replic	postgresql 15
订阅端	postgresql2	192.168.80.240	5432	subdb	replic	postgresql 15

2.发布端配置参数

## vi postgressql.conf（重启生效）
listen_add编程resses = '*'
wal_level=logical
max_replication_slots=8
max_wal_senders=1http://www.devze.com0
## alter system set
alter system set wal_level=logical;
## 参数说明
wal_level设置为logical，才支持逻辑复制，低于这个级别逻辑复制不能工作。
max_replication_slots设置值必须大于订阅的数量。
max_wal_senders设置值必须大于max_replication_slots参数值加上物理备库数，因为每个订阅在主库上都会占用主库一个wal发送python进程。

3.发布端配置pg_hba.conf

vi pg_hba.conf
host    replication     test       0/0         md5

4.订阅端配置参数

## vi postgresql.conf（重启生效）
listen_addresses = '*'
wal_level=logical
max_replication_http://www.devze.comslots=8
max_logical_replication_workers=8
## alter system set
alter system set wal_level=logical;
## 参数说明
max_replication_slots设置数据库复制槽数量。
max_logical_replication_workers设置逻辑复制进程数，应大于订阅节点的数量，并且给表同步预留一些进程数量。
注意：max_logical_replication_workers会消耗后台进程数，并且从max_worker_processes参数设置的后台进程数中消费，因此max_worker_processes需要设置的大一些。

5.发布端创建逻辑复制用户，并具备replication复制权限（可选）

如不创建，可以使用默认的管理员用户postgres。

postgres=# create user replic replication login connection limit 8 password 'replic';
CREATE ROLE
limit 8：为新用户设置最大数目连接数。默认无限制。

6.发布端创建发布

## 创建复制数据库
postgres=# create database pubdb;
CREATE DATABASE
## 授予复制用户权限
postgres=# \c pubdb postgres
You are now connected to database "pubdb" as user "postgres".
pubdb=# grant all on schema public to replic;
GRANT
## 创建复制表
pubdb=> create table c1 (id int4 primary key,name text);
CREATE TABLE
pubdb=> insert into c1 values (1,'a');
INSERT 0 1
pubdb=> select * from c1;
 id | name 
----+------
  1 | a
(1 row)
## 创建发布
pubdb=> \c pubdb postgres
You are now connected to database "pubdb" as user "postgres".
pubdb=# create publication pub1 for table c1;
CREATE PUBLICATION
注意：如果发布多张表使用逗号隔开，如果发布所有表则将 for table 修改为 for all tables。
##查看创建的发布
pubdb=# select * from pg_publication;
  oid  | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot 
-------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
 33177 | pub1    |       10 | f            | t         | t         | t         | t           | f
(1 row)
参数说明：
pubname：发布名称。
pubowner：发布的属主，可以和pg_user视图的usesysid字段关联查询属主的具体信息。
puballtables：是否发布数据库中的所有表，t 表示发布数据库中所有已存在的表和以后新建的表。
pubinsert：t 表示仅发布表上的insert操作。
pubupdate：t 表示仅发布表上的update操作。
pubdelete：t 表示仅发布表上的delete操作。
pubtruncate：t 表示仅发布表上的truncate操作。

7.发布端给复制用户授权

pubdb=# grant connect on database pubdb to replic;
GRANT
pubdb=# grant usage on schema public to replic;
GRANT
pubdb=# grant select on c1 to replic;
GRANT

8.订阅端创建表

postgres=# create database subdb;
CREATE DATABASE
postgres=# create user replic replication login connection limit 8 password 'replic';
CREATE ROLE
subdb=> \c subdb postgres
You are now connected to database "subdb" as user "postgres".
subdb=# grant all on schema public to replic;
GRANT
subdb=> create table c1 (id int4 primary key,name text);
CREATE TABLE

9.订阅端创建订阅

subdb=> \c subdb postgres
You are now connected to database "subdb" as user "postgres".
subdb=# create subscription sub1 connection 'host=192.168.80.239 port=5432 dbname=pubdb user=replic password=replic' publication pub1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
## 查看创建的订阅
subdb=# \x
Expanded display is on.
subdb=# select * from pg_subscription;
-[ RECORD 1 ]----+-----------------------------------------------------------------------
oid              | 41374
subdbid          | 41361
subskiplsn       | 0/0
subname          | sub1
subowner         | 10
subenabled       | t
subbinary        | f
substream        | f
subtwophasestate | d
subdisableonerr  | f
subconninfo      | host=192.168.80.239 port=5432 dbname=pubdb user=replic password=replic
subslotname      | sub1
subsynccommit    | off
subpublications  | {pub1}

10.订阅端给复制用户授权

subdb=# grant connect on database subdb to replic;
GRANT
subdb=# grant usage on schema public to replic;
GRANT
subdb=# grant select on c1 to replic;
GRANT

11.配置完成，发布端查看信息

postgres=# select slot_name,plugin,slot_type,database,active,restart_lsn from pg_replication_slots where slot_name='sub1';
 slot_name |  plugin  | slot_type | database | active | restart_lsn 
-----------+----------+-----------+----------+--------+-------------
 sub1      | pgoutput | logical   | pubdb    | t      | 0/3F45C840
(1 row)

12.测试逻辑复制

## 发布端向表中插入数据
pubdb=> insert into c1 values (2,'tt');
INSERT 0 1
pubdb=> select * from c1;
 id | name 
----+------
python  1 | a
  2 | tt
(2 rows)
pubdb=> delete from c1 where id=1;
DELETE 1
pubdb=> select * from c1;
 id | name 
----+------
  2 | tt
(1 row)
## 订阅端查看结果
subdb=# select * from c1;
 id | name 
----+------
  2 | tt
(1 row)
## 添加新表测试，发布端创建表结构
pubdb=> create table c2 (id int primary key,addr varchar(100));
CREATE TABLE
## 订阅端创建表结构
subdb=> create table c2 (id int primary key,addr varchar(100));
CREATE TABLE
## 发布端授权
pubdb=> grant select on c2 to replic;
GRANT
## 将新表c2，添加到发布列表中
pubdb=> \c pubdb postgres 
You are now connected to database "pubdb" as user "postgres".
pubdb=# alter publication pub1 add table c2;
ALTER PUBLICATION
## 发布端查看发布列表
pubdb=# select * from pg_publication_tables;
 pubname | schemaname | tablename | attnames  | rowfilter 
---------+------------+-----------+-----------+-----------
 pub1    | public     | c1        | {id,name} | 
 pub1    | public     | c2        | {id,addr} | 
(2 rows)
## 如果没有看到新表，可在订阅端刷新订阅
subdb=> \c subdb postgres
You are now connected to database "subdb" as user "postgres".
subdb=# alter subscription sub1 refresh publication;
ALTER SUBSCRIPTION
## 删除复制设置
drop subscription sub1;

到此这篇关于PostgreSQL部署逻辑复制的文章就介绍到这了,更多相关PostgreSQL部署内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！