草庐IT

PostgreSQL主从复制-逻辑复制

数据库干货铺 2023-03-28 原文

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

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

  • 物理层面完全一致，是主要的复制方式，其类似于Oracle的DG。
  • 延迟低，事务执行过程中产生REDO record，实时的在备库apply，事务结束时，备库立马能见到数据。
  • 物理复制的一致性、可靠性高，不必担心数据逻辑层面不一致。
但是其又在实际使用的场景中存在一些无法满足的需求，例如：

  • 无法满足指定库或部分表的复制需求。
  • 将多个数据库实例的数据汇聚到同一个目标库或将一个库的数据分发到多个不同的库。
  • 不同的版本之间的复制。
  • 不同库名之间的不同步。
对于以上场景,物理复制是无法满足的，因此逻辑复制应运而生了。

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

图片来源于《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、容灾、读写分离。
  • 物理复制，适合于备库没有写，只有读的场景。
PostgreSQL复制spansectionstyle数据库$逻辑复制

有关PostgreSQL主从复制-逻辑复制的更多相关文章

  1. java - 我的模型类或其他类中应该有逻辑吗 - 2

    我只想对我一直在思考的这个问题有其他意见，例如我有classuser_controller和classuserclassUserattr_accessor:name,:usernameendclassUserController//dosomethingaboutanythingaboutusersend问题是我的User类中是否应该有逻辑user=User.newuser.do_something(user1)oritshouldbeuser_controller=UserController.newuser_controller.do_something(user1,user2)我

  2. ruby-on-rails - 如何让 datamapper 与 postgresql 数据库一起工作？ - 2

    我已经找到了几个使用datamapper的示例，并且能够让它们正常工作。不过，所有这些示例都是针对sqlite数据库的。我正在尝试将数据映射器与postgresql一起使用。我将datamapper中的调用从sqlite3更改为postgres，并且我已经安装了dm-postgres-adapter。但它仍然不起作用。我还需要做什么？ 最佳答案 与SQLite不同，PostgreSQL不将数据库存储在单个文件中。在你拥有createdyourdatabase之后，尝试这样的事情:DataMapper.setup:default,{:

  3. ruby - 如何在 ruby​​ 中复制目录结构，不包括某些文件扩展名 - 2

    我想编写一个ruby​​脚本来递归复制目录结构，但排除某些文件类型。因此，给定以下目录结构:folder1folder2file1.txtfile2.txtfile3.csfile4.htmlfolder2folder3file4.dll我想复制这个结构，但不包含.txt和.cs文件。因此，生成的目录结构应如下所示:folder1folder2file4.htmlfolder2folder3file4.dll 最佳答案 您可以使用查找模块。这是一个代码片段:require"find"ignored_extensions=[".cs"

  4. sql - 在 Rails Console for PostgreSQL 的表中显示数据 - 2

    我找到了这样的东西:Rails:Howtolistdatabasetables/objectsusingtheRailsconsole?这一行没问题:ActiveRecord::Base.connection.tables并返回所有表但是ActiveRecord::Base.connection.table_structure("users")产生错误:ActiveRecord::Base.connection.table_structure("projects")我认为table_structure不是Postgres方法。如何列出Postgres数据库的Rails控制台中表中的所有

  5. ruby - 在两个 ActiveRecord 类之间合并/复制属性的好方法？ - 2

    之前有人问过这个问题，我发现了以下clip关于如何一次设置一个类对象的所有属性，但由于批量分配保护，这在Rails中是不可能的。(例如，您不能Object.attributes={})有没有一种很好的方法可以将一个类的属性合并到另一个类中？object1.attributes=object2.attributes.inject({}){|h,(k,v)|h[k]=vifObjectModel.column_names.include?(k);h}谢谢。 最佳答案 利用assign_attributes使用:without_prote

  6. ruby-on-rails - Rails 迁移中的 PostgreSQL 点类型 - 2

    我想使用PostgreSQL中的point类型。我已经完成了:railsgmodelTestpoint:point最终的迁移是:classCreateTests当我运行时:rakedb:migrate结果是:==CreateTests:migrating====================================================--create_table(:tests)rakeaborted!Anerrorhasoccurred,thisandalllatermigrationscanceled:undefinedmethod`point'for#/hom

  7. ruby-on-rails - Ruby on Rails 单表继承(STI)和单元测试问题(使用 PostgreSQL) - 2

    我正在使用带有单个“帐户”表的STI模型来保存用户和技术人员的信息(即用户...8)错误:test_the_truth(用户测试):ActiveRecord::StatementInvalid:PGError:ERROR:关系“技术人员”不存在:从“技术人员”中删除...从本质上讲，标准框架不承认Technicians和Users表(或PostgreSQL称它们为“关系”)不存在，事实上，应该别名为Accounts。有什么想法吗？我对RoR比较陌生，不知道如何解决这个问题而又不完全删除STI。 最佳答案 原来问题是由于存在:./te

  8. ruby - rails 3.2.2(或 3.2.1)+ Postgresql 9.1.3 + Ubuntu 11.10 连接错误 - 2

    我正在使用PostgreSQL9.1.3(x86_64-pc-linux-gnu上的PostgreSQL9.1.3，由gcc-4.6.real(Ubuntu/Linaro4.6.1-9ubuntu3)4.6.1，64位编译)和在ubuntu11.10上运行3.2.2或3.2.1。现在，我可以使用以下命令连接PostgreSQLsupostgres输入密码我可以看到postgres=#我将以下详细信息放在我的config/database.yml中并执行“railsdb”，它工作正常。开发:adapter:postgresqlencoding:utf8reconnect:falsedat

  9. Ruby:我怎样才能复制这个数组？ - 2

    (跟进我之前的问题，Ruby:howcanIcopyavariablewithoutpointingtothesameobject?)我正在编写一个简单的Ruby程序来在.svg文件中进行一些替换。第一步是从文件中提取信息并将其放入数组中。为了避免每次调用此函数时都从磁盘读取文件，我尝试使用memoize设计模式-在第一次调用后的每次调用中都使用缓存结果。为此，我使用了一个在函数之前定义的全局变量。但是，即使我在返回局部变量之前将该变量.dup为局部变量，调用该变量的函数仍在修改全局变量。这是我的实际代码:#memoizetokeepfromhavingtoreadoriginalfi

  10. Linux磁盘分区中物理卷（PV）、卷组（VG）、逻辑卷（LV）创建和（LVM）管理 - 2

    文章目录一基础定义二创建逻辑卷2-1准备物理设备2-2创建物理卷2-3创建卷组2-4创建逻辑卷2-5创建文件系统并挂载文件三扩展卷组和缩减卷组3-1准备物理设备3-2创建物理卷3-3扩展卷组3-4查看卷组的详细信息以验证3-5缩减卷组四扩展逻辑卷4-1检查卷组是否有可用的空间4-2扩展逻辑卷4-3扩展文件系统五删除逻辑卷5-1备份数据5-2卸载文件系统5-3删除逻辑卷5-4删除卷组5-5删除物理卷六LVM逻辑卷缩容6-1缩容注意事项6-2标准缩容步骤一基础定义LVM，LogicalVolumeManger，逻辑卷管理，Linux磁盘分区管理的一种机制，建立在硬盘和分区上的一个逻辑层，提高磁盘分

随机推荐