MK
摩柯社区 - 一个极简的技术知识社区
AI 面试

PostgreSQL逻辑复制起点设置与策略

2022-06-147.0k 阅读

一、PostgreSQL 逻辑复制概述

PostgreSQL 的逻辑复制是一种强大的数据复制机制,它基于逻辑层面的数据变化,如 SQL 语句或事务,而非物理层面的块或页。这使得它在异构环境和应用特定需求的场景中表现出色。逻辑复制允许将数据从一个 PostgreSQL 数据库(发布端)复制到一个或多个其他 PostgreSQL 数据库(订阅端)。

在逻辑复制中,发布端将数据更改以逻辑日志的形式记录下来,这些日志包含了对表的增、删、改操作等逻辑信息。订阅端通过读取这些逻辑日志,将相应的操作应用到本地数据库,从而实现数据的同步。

(一)逻辑复制的应用场景

  1. 数据分发:将数据从中心数据库分发到多个分支机构或分布式系统中的不同节点,以支持本地查询和处理,减少网络流量和提高响应速度。例如,大型连锁企业将总部数据库中的商品信息复制到各个门店的本地数据库,门店可在本地快速查询商品库存等信息,而无需频繁访问总部数据库。
  2. 数据备份与恢复:逻辑复制可作为一种数据备份手段,订阅端数据库可作为备用数据库。当主数据库出现故障时,可迅速切换到订阅端数据库,确保业务连续性。比如金融系统中,核心交易数据库通过逻辑复制将数据同步到灾备中心的数据库,一旦生产数据库出现问题,灾备数据库能快速接管业务。
  3. 异构系统集成:由于逻辑复制基于逻辑数据变化,可将 PostgreSQL 数据复制到其他类型的数据库或数据存储系统(在一定程度上),实现不同系统间的数据集成。例如,将 PostgreSQL 中的业务数据复制到 Elasticsearch 中用于全文搜索,或者复制到 NoSQL 数据库以满足特定的大数据处理需求。

二、逻辑复制起点设置基础

(一)逻辑复制槽

逻辑复制槽是逻辑复制中的关键概念。它是发布端为订阅端保留逻辑复制相关信息的一种机制。当创建一个逻辑复制槽时,发布端会记录从哪个位置开始的逻辑日志需要保留,以便订阅端可以消费这些日志进行数据同步。

  1. 创建逻辑复制槽 在 PostgreSQL 中,可以使用 pg_create_logical_replication_slot 函数来创建逻辑复制槽。以下是一个简单的示例:
SELECT * FROM pg_create_logical_replication_slot('my_slot','mppdb_decoding');

在上述示例中,my_slot 是逻辑复制槽的名称,mppdb_decoding 是用于解码逻辑日志的插件。不同的插件可根据具体需求选择,如 wal2json 插件可将逻辑日志解码为 JSON 格式,方便处理和分析。

  1. 查看逻辑复制槽 可以使用以下 SQL 语句查看当前数据库中的逻辑复制槽:
SELECT slot_name, plugin, slot_type, database, active, xmin, catalog_xmin, restart_lsn
FROM pg_replication_slots;

此查询将返回逻辑复制槽的名称、使用的插件、槽类型、关联的数据库、是否处于活动状态、最早未提交事务的 XID(xmin)、最早未提交事务在系统目录中的 XID(catalog_xmin)以及重启日志序列号(restart_lsn)等信息。这些信息对于监控和管理逻辑复制过程非常重要。

  1. 删除逻辑复制槽 当不再需要某个逻辑复制槽时,可以使用 pg_drop_replication_slot 函数删除它:
SELECT pg_drop_replication_slot('my_slot');

删除逻辑复制槽时需谨慎,因为这将导致与该槽相关的所有未消费的逻辑日志被删除,可能会影响到相关订阅端的数据同步。

(二)日志序列号(LSN)

日志序列号(LSN)是 PostgreSQL 事务日志中的一个重要概念,用于标识事务日志中的位置。在逻辑复制中,LSN 用于确定逻辑复制的起点和记录同步进度。

  1. 获取当前 LSN 可以使用 pg_current_wal_lsn() 函数获取当前的 WAL(Write - Ahead Log)日志序列号:
SELECT pg_current_wal_lsn();

该函数返回的 LSN 值表示当前 WAL 日志写入的位置。

  1. LSN 与逻辑复制起点 逻辑复制的起点可以基于特定的 LSN 来设置。例如,当订阅端首次连接到发布端进行数据同步时,发布端会告知订阅端从某个 LSN 开始读取逻辑日志。这个 LSN 可能是创建逻辑复制槽时的当前 LSN,也可能是根据特定需求指定的某个历史 LSN。假设发布端在创建逻辑复制槽 my_slot 时记录的当前 LSN 为 16/B0000028,那么订阅端就从这个 LSN 开始消费逻辑日志,以确保获取到从该点之后发生的所有数据变化。

三、逻辑复制起点设置策略

(一)基于时间的起点设置

基于时间的起点设置策略是根据特定的时间点来确定逻辑复制的起点。这种策略在某些场景下非常有用,例如,希望从昨天某个业务低谷时间段开始复制数据,以减少初始同步的数据量。

  1. 确定基于时间的 LSN 首先,需要找到与特定时间对应的 LSN。可以通过查询 pg_wal_inspect 系统视图来实现。以下是一个示例查询,用于查找在 2023 - 10 - 01 08:00:00 之后的第一个 WAL 日志记录的 LSN:
SELECT start_lsn
FROM pg_wal_inspect.pg_lsn_time_mapping
WHERE mapping_time > '2023 - 10 - 01 08:00:00'::timestamptz
ORDER BY mapping_time
LIMIT 1;

上述查询利用 pg_lsn_time_mapping 视图,该视图存储了 WAL 日志记录的时间和对应的 LSN 映射关系。通过筛选出大于指定时间的记录,并按时间排序取第一条记录,即可得到所需的 LSN。

  1. 设置逻辑复制起点为基于时间的 LSN 在获取到基于时间的 LSN 后,例如 16/C0000040,可以在创建逻辑复制槽或配置订阅端时将该 LSN 设置为逻辑复制的起点。在创建逻辑复制槽时,可以通过向 pg_create_logical_replication_slot 函数传递额外参数来指定起点 LSN:
SELECT * FROM pg_create_logical_replication_slot('time_based_slot','mppdb_decoding', '{"start_lsn": "16/C0000040"}');

在订阅端配置时,也可以指定从该 LSN 开始同步,确保订阅端从指定时间点之后的逻辑日志开始消费。

(二)基于事务 ID(XID)的起点设置

基于事务 ID(XID)的起点设置策略是根据特定的事务 ID 来确定逻辑复制的起点。这种策略在需要精确控制数据同步范围,例如从某个特定事务完成后开始复制数据时非常有用。

  1. 查找特定事务的 XID 要基于 XID 设置逻辑复制起点,首先需要找到特定事务的 XID。可以通过查询系统表 pg_catalog.pg_stat_activity 来获取当前活动事务的 XID。假设我们知道某个事务执行了关键的初始化操作,并且在 pg_stat_activity 中可以通过查询条件(如 query 字段匹配特定 SQL 语句)来定位该事务:
SELECT xact_start, xid, query
FROM pg_catalog.pg_stat_activity
WHERE query LIKE '%关键初始化操作的 SQL 语句%';

从上述查询结果中获取到对应的 XID,例如 12345

  1. 设置逻辑复制起点为基于 XID 的位置 获取到 XID 后,可以通过查找与该 XID 对应的 LSN 来设置逻辑复制起点。这可以通过查询 pg_catalog.pg_clog 系统表(事务日志记录)以及相关的 WAL 日志信息来实现。虽然直接查询 pg_clog 比较复杂,通常可以借助一些工具或扩展来简化操作。例如,某些第三方工具可以根据 XID 快速定位到对应的 LSN。假设通过工具获取到与 XID 12345 对应的 LSN 为 16/D0000060,则可以在创建逻辑复制槽或配置订阅端时将该 LSN 设置为逻辑复制的起点,方法与基于时间设置 LSN 类似:
SELECT * FROM pg_create_logical_replication_slot('xid_based_slot','mppdb_decoding', '{"start_lsn": "16/D0000060"}');

(三)基于全量数据初始化后的起点设置

在许多情况下,尤其是在首次进行逻辑复制时,需要先进行全量数据的初始化,然后再基于初始化完成后的状态设置逻辑复制起点,以确保后续只同步增量数据。

  1. 全量数据初始化 通常可以使用 pg_dumppg_restore 工具来进行全量数据的初始化。例如,在发布端使用 pg_dump 导出数据库:
pg_dump -U my_user -Fc my_database > my_database.dump

然后将导出的文件传输到订阅端,并使用 pg_restore 进行恢复:

pg_restore -U my_user -d my_database my_database.dump

这样就在订阅端完成了全量数据的初始化。

  1. 确定全量初始化后的逻辑复制起点 在全量数据初始化完成后,需要确定从哪个位置开始同步增量数据。一种常见的方法是在发布端记录全量导出完成时的 LSN。可以在执行 pg_dump 之前获取当前 LSN,然后在全量导出完成后再次获取 LSN,以确定导出期间 WAL 日志的变化范围。假设在全量导出前获取的 LSN 为 16/E0000080,导出完成后获取的 LSN 为 16/E0000100,则可以将 16/E0000100 设置为逻辑复制的起点。在订阅端配置逻辑复制时,将该 LSN 作为起点进行设置,确保订阅端从全量初始化完成后的第一个增量变化开始同步数据。

四、不同场景下逻辑复制起点设置的实践

(一)单表逻辑复制起点设置

  1. 场景描述 假设我们有一个电商数据库,其中有一个 products 表,由于业务调整,需要将该表的数据从某个特定时间点开始复制到另一个数据库用于数据分析。

  2. 起点设置步骤

    • 首先,按照基于时间的起点设置策略,确定 products 表在特定时间点对应的 LSN。假设我们希望从 2023 - 11 - 15 12:00:00 开始复制数据,通过查询 pg_wal_inspect.pg_lsn_time_mapping 视图获取对应的 LSN,例如 16/F0000020
    • 在发布端创建逻辑复制槽时,指定该 LSN 为起点:
SELECT * FROM pg_create_logical_replication_slot('products_slot','mppdb_decoding', '{"start_lsn": "16/F0000020"}');
- 在订阅端配置逻辑复制时,同样指定从该 LSN 开始同步:
CREATE SUBSCRIPTION products_subscription
CONNECTION 'host=发布端主机 port=发布端端口 user=my_user password=my_password dbname=my_database'
PUBLICATION products_publication
WITH (create_slot = false, slot_name = 'products_slot', synchronize_structure = true, start_lsn = '16/F0000020');

通过以上步骤,即可实现从特定时间点开始对 products 表进行逻辑复制。

(二)多表逻辑复制起点设置

  1. 场景描述 在一个企业级 ERP 系统中,有多个相关联的表,如 customersordersorder_items 等,需要将这些表的数据从某个特定事务之后复制到另一个数据库,以支持异地办公的数据分析和报表生成。

  2. 起点设置步骤

    • 先找到特定事务的 XID。假设该事务执行了一些关键的业务初始化操作,通过查询 pg_catalog.pg_stat_activity 视图获取到该事务的 XID,例如 54321
    • 通过工具或复杂查询确定与该 XID 对应的 LSN,假设为 16/G0000040
    • 在发布端创建逻辑复制槽时,指定该 LSN 为起点,并且将多个表添加到发布中:
-- 创建逻辑复制槽
SELECT * FROM pg_create_logical_replication_slot('erp_slots','mppdb_decoding', '{"start_lsn": "16/G0000040"}');
-- 创建发布并添加表
CREATE PUBLICATION erp_publication FOR TABLE customers, orders, order_items;
- 在订阅端配置逻辑复制,指定从该 LSN 开始同步,并订阅相应的发布:
CREATE SUBSCRIPTION erp_subscription
CONNECTION 'host=发布端主机 port=发布端端口 user=my_user password=my_password dbname=my_database'
PUBLICATION erp_publication
WITH (create_slot = false, slot_name = 'erp_slots', synchronize_structure = true, start_lsn = '16/G0000040');

这样就实现了从特定事务之后对多个相关表进行逻辑复制。

(三)跨版本逻辑复制起点设置

  1. 场景描述 随着 PostgreSQL 版本的升级,需要将旧版本(如 9.6 版本)数据库中的数据复制到新版本(如 14 版本)的数据库中。由于版本差异,在设置逻辑复制起点时需要特别注意兼容性。

  2. 起点设置步骤

    • 首先在旧版本数据库中按照常规方法确定逻辑复制起点,例如基于时间获取 LSN。假设获取到的 LSN 为 16/H0000060
    • 在旧版本数据库中创建逻辑复制槽,并使用兼容新版本的解码插件(确保插件在新旧版本中都可用或有合适的替代方案):
SELECT * FROM pg_create_logical_replication_slot('cross_version_slot','mppdb_decoding', '{"start_lsn": "16/H0000060"}');
- 在新版本数据库中配置订阅时,指定正确的连接信息和逻辑复制槽名称,并确保 `synchronize_structure` 参数根据实际需求进行设置。如果需要同步表结构,可以设置为 `true`,如果表结构已经提前创建好,则可以设置为 `false`:
CREATE SUBSCRIPTION cross_version_subscription
CONNECTION 'host=旧版本数据库主机 port=旧版本数据库端口 user=my_user password=my_password dbname=my_database'
PUBLICATION cross_version_publication
WITH (create_slot = false, slot_name = 'cross_version_slot', synchronize_structure = true, start_lsn = '16/H0000060');

在跨版本逻辑复制过程中,还需要注意检查数据类型兼容性、函数和操作符的变化等,确保数据能够正确同步。

五、逻辑复制起点设置的注意事项与优化

(一)注意事项

  1. LSN 与数据一致性 在设置逻辑复制起点时,确保选择的 LSN 能够保证数据的一致性。如果选择的 LSN 不正确,可能导致订阅端的数据不完整或与发布端不一致。例如,如果在一个事务中间选择 LSN 作为起点,可能会导致订阅端只同步了部分事务操作,从而破坏数据完整性。因此,在基于时间或 XID 确定 LSN 时,要仔细分析事务边界和数据状态。

  2. 逻辑复制槽的管理 逻辑复制槽会占用发布端的资源,包括 WAL 日志空间等。如果长时间不使用某个逻辑复制槽,应及时删除,以避免资源浪费。同时,在删除逻辑复制槽之前,要确保相关的订阅端已经完成数据同步或不再需要该槽。否则,可能导致订阅端无法继续同步数据。

  3. 版本兼容性 在跨版本进行逻辑复制时,要充分考虑 PostgreSQL 版本之间的差异。不同版本的逻辑日志格式、解码插件等可能有所不同。要选择兼容的解码插件,并对可能出现的兼容性问题进行测试和处理。例如,某些函数或数据类型在新版本中有变化,可能需要在同步过程中进行转换或调整。

(二)优化策略

  1. 批量同步优化 为了提高逻辑复制的效率,可以采用批量同步的方式。订阅端在获取逻辑日志时,可以设置合理的批量大小,一次性处理多个事务或数据变更,减少与发布端的交互次数。在配置订阅时,可以通过参数调整批量大小,例如在 CREATE SUBSCRIPTION 语句中设置 batch_size 参数:
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=发布端主机 port=发布端端口 user=my_user password=my_password dbname=my_database'
PUBLICATION my_publication
WITH (create_slot = false, slot_name ='my_slot', synchronize_structure = true, batch_size = 1000);

这里将批量大小设置为 1000,表示订阅端每次从发布端获取 1000 个逻辑日志记录进行处理。

  1. 网络优化 逻辑复制涉及发布端和订阅端之间的数据传输,网络性能对复制效率有重要影响。可以通过优化网络配置,如增加带宽、减少网络延迟等,来提高逻辑复制的速度。此外,使用压缩技术对传输的数据进行压缩也是一种有效的优化手段。在 PostgreSQL 中,可以通过配置参数启用逻辑日志传输的压缩功能,例如在发布端和订阅端的配置文件中设置 wal_sender_compression = onwal_receiver_compression = on,以减少网络传输的数据量。

  2. 索引优化 在订阅端,合理的索引设计可以提高逻辑复制过程中数据应用的效率。对于频繁更新、插入或删除的表,创建适当的索引可以加快数据操作的速度。例如,如果逻辑复制涉及到一个频繁更新的 users 表,并且更新操作经常基于 user_id 字段,那么在订阅端的 users 表上创建 user_id 字段的索引可以提高更新操作的性能:

CREATE INDEX idx_users_user_id ON users (user_id);

通过以上注意事项的遵循和优化策略的实施,可以确保逻辑复制起点设置的正确性和逻辑复制过程的高效性。