PostgreSQL部署逻辑复制过程详解

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

1.环境准备

角色主机名IP端口数据库名用户名版本发布端postgresql192.168.80.2395432pubdbreplicpostgresql 15订阅端postgresql2192.168.80.2405432subdbreplicpostgresql 15

2.发布端配置参数

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

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_slots=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 ----+------ 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部署逻辑复制的文章就介绍到这了,