服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - PostgreSQL - PostgreSQL主从复制-逻辑复制

PostgreSQL主从复制-逻辑复制

2023-05-07 03:01未知服务器之家 PostgreSQL

1、物理复制及逻辑复制对比 前文做了PostgreSQL物理复制的部署,其有如下主要优点 物理层面完全一致,是主要的复制方式,其类似于Oracle的DG。 延迟低,事务执行过程中产生REDO record,实时的在备库apply,事务结束时,备库立马能见

PostgreSQL主从复制-逻辑复制

1、物理复制及逻辑复制对比

前文做了PostgreSQL物理复制的部署,其有如下主要优点

  • 物理层面完全一致,是主要的复制方式,其类似于Oracle的DG。
  • 延迟低,事务执行过程中产生REDO record,实时的在备库apply,事务结束时,备库立马能见到数据。
  • 物理复制的一致性、可靠性高,不必担心数据逻辑层面不一致。

但是其又在实际使用的场景中存在一些无法满足的需求,例如:

  • 无法满足指定库或部分表的复制需求。
  • 将多个数据库实例的数据汇聚到同一个目标库或将一个库的数据分发到多个不同的库。
  • 不同的版本之间的复制。
  • 不同库名之间的不同步。

对于以上场景,物理复制是无法满足的,因此逻辑复制应运而生了。

逻辑复制的复制架构图如下:

PostgreSQL主从复制-逻辑复制

图片来源于《PostgreSQL实战》

逻辑复制是基于逻辑解析,其核心原理是逻辑主库将Publication中表的WAL日志解析成一定格式并发送给逻辑备库,逻辑备库Subscription接收到解析后的WAL日志后进行重做,从而实现表数据同步。

2、逻辑复制的部署

PS:以下的逻辑从库可以在新的机器上部署,如在原先的从库上修改,需停止原实例,并将recovery.conf文件删除或重命名(如修改为recovery.conf.done)

(1)修改主库的postgresql.conf

可以基于原先的物理复制的配置文件进行修改,配置逻辑复制主要需调整如下参数

wal_level = logical
max_wal_senders = 10
max_replication_slots = 8

参数简要说明如下

  • wal_level:设置成logical才支持逻辑复制.
  • max_wal_senders:由于每个订阅节点和流复制备库在主库上都会占用主库上一个WAL发送进程,因此此参数设置值需大于max_replication_slots参数值加上物理备库数量。
  • max_replication_slots:设置值需大于订阅节点的数量。

(2)修改逻辑从库的postgresql.conf

逻辑从库的postgresql.conf也可以在物理复制的基础上修改,与主库不同的是主要修改如下参数

wal_level = logical
max_replication_slots = 8
max_logical_replication_workers = 8

参数简要说明:

  • wal_level:设置成logical才支持逻辑复制,逻辑从库可以视情况设置。
  • max_replication_slots:设置数据库复制槽数量,应大于订阅节点的数量。
  • max_logical_replication_workers:设置逻辑复制进程数,应大于订阅节点的数量,并且给表同步预留一些进程数量,此参数默认值为4。

(3)创建逻辑复制账号

postgres=# CREATE USER logical_repl REPLICATION LOGIN CONNECTION LIMIT 8 ENCRYPTED PASSWORD 'logical_repl'; 
CREATE ROLE

逻辑复制用户需要REPLICATION权限即可,可以不需要SUPERUSER权限,之后需要在发布节点上将需要同步的表赋权给logical_repl用户,使logical_repl账号具有对这些表的读权限。

(4)在逻辑主库上创建库及表

/** 创建用于逻辑复制的主库 */ 
[postgres@PG32 data]$ createdb sourcedb
[postgres@PG32 data]$ psql -d sourcedb
psql (11.4)
Type "help" for help.
sourcedb=# create table logical_tb1(id int primary key,name varchar(20));

(5)逻辑从库上创建库及表

/** 在逻辑从库上创建不同的库 */
[postgres@PG33 data]$ createdb desdb
[postgres@PG33 data]$ psql -d desdb
psql (11.4)
Type "help" for help.
desdb=# create table logical_tb1(id int primary key,name varchar(20));
CREATE TABLE
desdb=#

注:逻辑复制的表结构需要手动在从库创建

(6)在逻辑主库上创建发布

/** 在发布主库上创建发布pub1,注意实在sourcedb库下执行 */
sourcedb=# CREATE PUBLICATION pub1 FOR TABLE logical_tb1;
CREATE PUBLICATION

如果需发布多张表 则表名间用逗号(,)分割,如果需发布所有库,则将FOR TABLE 调整为FOR ALL TABLES。

此时可以查看到如下信息。

sourcedb=# SELECT * FROM pg_publication;
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate
---------+----------+--------------+-----------+-----------+-----------+-------------
pub1 | 10 | f | t | t | t | t
(1 row)

字段说明如下:

  • pubname:指发布的名称。
  • pubowner:指发布的属主,可以和pg_user视图的usesysid字段关联查询得到属主具体信息。
  • puballtables:是否发布数据库中的所有表,t表示发布数据库中所有已存在的表和以后新建的表。
  • pubinsert:t表示仅发布表上的INSERT操作。
  • pubupdate:t表示仅发布表上的UPDATE操作。
  • pubdelete:t表示仅发布表上的DELETE操作。

(7)在逻辑从库上创建订阅

desdb=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=192.168.56.32 port=5432 user=logical_repl dbname=sourcedb password=logical_repl' PUBLICATION pub1;
NOTICE: created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

创建成功后,可以在逻辑主库上查询到如下信息:

sourcedb=# 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 | sourcedb | t | 0/6022D30
(1 row)

在逻辑从库上可以查询到如下信息:

desdb=# SELECT * FROM pg_subscription;
subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublications
---------+---------+----------+------------+---------------------------------------------------------------------------------------+-------------+---------------+-----------------
24995 | sub1 | 10 | t | host=192.168.56.32 port=5432 user=logical_repl dbname=sourcedb password=logical_repl | sub1 | off | {pub1}
(1 row)

(8)给复制账号授权

因为步骤2.3中只创建了复制账号并未对需要复制的表进行授权,因此,此时逻辑从库的日志中有如下错误

2019-10-10 15:57:21.847 CST [27443] ERROR: could not start initial contents copy for table "public.logical_tb1": ERROR: permission denied for table logical_tb1
2019-10-10 15:57:21.848 CST [24722] LOG: background worker "logical replication worker" (PID 27443) exited with exit code 1

因此 ,现在需要对复制用户授权。

sourcedb=# GRANT USAGE ON SCHEMA public TO logical_repl;
GRANT
sourcedb=# GRANT SELECT ON logical_tb1 TO logical_repl;
GRANT

授权后,显示正常。

2019-10-10 16:00:25.959 CST [28204] LOG: logical replication table synchronization worker for subscription "sub1", table "logical_tb1" has started
2019-10-10 16:00:25.967 CST [28204] LOG: logical replication table synchronization worker for subscription "sub1", table "logical_tb1" has finished

(9)测试数据同步

在逻辑主库插入数据。

/** 在主库插入数据 */ 
sourcedb=# insert into logical_tb1(id,name) values(1,'a'),(2,'bca');
INSERT 0 2

在逻辑从库查看结果。

/** 查看数据是否同步完成 */ 
desdb=# select * from logical_tb1;
id | name
----+------
1 | a
2 | bca
(2 rows)

由此可见数据已同步完成。

(10)添加复制所需的表

在逻辑主库和逻辑从库均添加一张新表,并添加到发布列表中。

/** 主库上创建表结构 */ 
sourcedb=# create table logical_tb2(id int primary key ,addr varchar(100));
CREATE TABLE
sourcedb=#
/** 从库上创建表结构 */
desdb=# create table logical_tb2(id int primary key ,addr varchar(100));
CREATE TABLE
/** 在主库上给逻辑复制账号授权 */
sourcedb=# GRANT SELECT ON logical_tb2 TO logical_repl;
GRANT
/** 添加新表至发布列表 */
sourcedb=# ALTER PUBLICATION pub1 ADD TABLE logical_tb2;
ALTER PUBLICATION
/** 在主库查看发布列表中的表名 */
sourcedb=# SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename
---------+------------+-------------
pub1 | public | logical_tb1
pub1 | public | logical_tb2
(2 rows)

此时已加入一张表进入发布列表中。

此时在主库写入数据,查看从库情况如下:

/** 主库插入一条记录 */ 
sourcedb=# insert into logical_tb2(id,addr) values(1,'beijing');
INSERT 0 1
/** 此时在逻辑从库查看,结果却没有数据 */
desdb=# select * from logical_tb2;
id | addr
----+------
(0 rows)

因为还需要在从库刷新一下订阅。

/** 此时在从库刷新订阅 */ 
desdb=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
/** 刷新完成后再查数据已经有数据了 */
ALTER SUBSCRIPTION
desdb=# select * from logical_tb2;
id | addr
----+---------
1 | beijing
(1 row)

至此,PostgreSQL的逻辑复制也部署完毕。

3、物理复制与逻辑复制特点和应用场景

PostgreSQL的逻辑复制与物理复制的差异比较突出,在使用中可以根据其特点选择使用哪种复制方式。

  • 逻辑订阅,适合于发布端与订阅端都有读写的情况。
  • 逻辑订阅,更适合于小事务,或者低密度写(轻度写)的同步。如果有大事务、高密度写,逻辑订阅的延迟相比物理复制更高。
  • 逻辑订阅,适合于双向,多向同步。
  • 物理复制,适合于单向同步。
  • 物理复制,适合于任意事务,任意密度写(重度写)的同步。
  • 物理复制,适合于HA、容灾、读写分离。
  • 物理复制,适合于备库没有写,只有读的场景。

延伸 · 阅读

精彩推荐
  • PostgreSQLPostgreSQL标准建表语句分享

    PostgreSQL标准建表语句分享

    这篇文章主要介绍了PostgreSQL标准建表语句分享,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...

    码上得天下7962021-02-27
  • PostgreSQL分布式 PostgreSQL之Citus 架构

    分布式 PostgreSQL之Citus 架构

    节点 Citus 是一种 PostgreSQL 扩展,它允许数据库服务器(称为节点)在“无共享(shared nothing)”架构中相互协调。这些节点形成一个集群,允许 PostgreSQL 保存比单...

    未知802023-05-07
  • PostgreSQLRDS PostgreSQL一键大版本升级技术解密

    RDS PostgreSQL一键大版本升级技术解密

    一、PostgreSQL行业位置 (一)行业位置 在讨论PostgreSQL(下面简称为PG)在整个数据库行业的位置之前,我们先看一下阿里云数据库在全球的数据库行业里的...

    未知1192023-05-07
  • PostgreSQLPostgresql开启远程访问的步骤全纪录

    Postgresql开启远程访问的步骤全纪录

    postgre一般默认为本地连接,不支持远程访问,所以如果要开启远程访问,需要更改安装文件的配置。下面这篇文章主要给大家介绍了关于Postgresql开启远程...

    我勒个去6812020-04-30
  • PostgreSQLpostgresql 数据库中的数据转换

    postgresql 数据库中的数据转换

    postgres8.3以后,字段数据之间的默认转换取消了。如果需要进行数据变换的话,在postgresql数据库中,我们可以用"::"来进行字段数据的类型转换。...

    postgresql教程网12482021-10-08
  • PostgreSQLpostgresql 中的to_char()常用操作

    postgresql 中的to_char()常用操作

    这篇文章主要介绍了postgresql 中的to_char()常用操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...

    J符离13432021-04-12
  • PostgreSQLPostgresql查询效率计算初探

    Postgresql查询效率计算初探

    这篇文章主要给大家介绍了关于Postgresql查询效率计算的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Postgresql具有一定的参考学习价...

    轨迹4622020-05-03
  • PostgreSQL深入理解PostgreSQL的MVCC并发处理方式

    深入理解PostgreSQL的MVCC并发处理方式

    这篇文章主要介绍了深入理解PostgreSQL的MVCC并发处理方式,文中同时介绍了MVCC的缺点,需要的朋友可以参考下 ...

    PostgreSQL教程网3622020-04-25