如何横向扩展 PostgreSQL?


水平扩展是在不影响数据完整性、事务安全性和查询性能的情况下跨多个服务器分布数据的艺术和科学。

只读副本
只读副本通常指的是“备用”服务器,它冗余地复制主服务器上的所有数据,持续与主服务器保持同步,并允许客户端查询(但不更新)它包含的数据。在 Postgres 世界中,这种类型的服务器称为热备用。

一个PostgreSQL服务器通常启动时,会通过SQL命令接受来自客户端的变化,并以一系列WAL(提前写入日志)记录的形式产生变化。(这些记录随后被检点为实际的行/列数据。) PostgreSQL的备用服务器启动并保持在所谓的恢复模式
在恢复模式下,Postgres服务器不接受通过SQL命令进行的修改,而只接受由另一个服务器产生的WAL记录作为输入。
这些WAL记录然后被重放,因此,在主服务器上发生的同样的变化在备用服务器上被重新创建。

收集新生成的 WAL 文件,将它们复制到备用服务器,并将它们放置在备用服务器数据目录中的适当位置的过程,在 Postgres 术语中称为日志传送。最流行的日志传送技术是流式复制

简单备用服务器不允许常规客户端连接(“简单”备用服务器在 Postgres 世界中称为热备用):

$ psql -h /tmp -p 6001 postgres
psql: error: connection to server on socket "/tmp/.s.PGSQL.6001" failed: FATAL:  the database system is not accepting connections
DETAIL:  Hot standby mode is disabled.

但按理说,不以任何方式更新数据的只读查询可能是允许的,这确实是热备所允许的。要使常规备用成为热备用,请更改此配置并重新启动 Postgres 服务器:

hot_standby = on

客户端现在可以连接到热备服务器:

$ psql -h /tmp -p 6001 postgres psql (15.1 (Debian 15.1-1.pgdg110+1)) Type "help" for help. postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row)

关于 Postgres 的热备功能有一些注意事项:

  • 备用服务器复制主服务器上的全部数据——在 Postgres 术语中称为物理复制。“完整数据”包括主服务器上的所有数据库。物理复制是一个孤注一掷的交易,没有办法使用这种方法只复制数据库的一个子集。
  • 无法创建临时表——创建表,即使是临时表,也需要更新系统目录
  • 不允许需要行锁和其他一些类型锁的查询
  • 不允许准备好的交易
  • 主数据库上的某些更改(如 vacuum)可能会导致取消在热备用数据库上运行的查询(需要刚刚在主数据库中被 vacuum 的行)
  • 更多内容在 PostgreSQL 文档中

也就是说,Postgres 的物理复制功能已经存在了很长时间,在各地的生产中大量使用,坚如磐石且有据可查。
它在标准 PostgreSQL 安装中可用,不需要其他扩展或工具。
它有陷阱。

热备用不仅可以用作只读副本,还可以用作 HA 设置中的故障转移候选者。

多个只读副本
多个只读副本通常用于使数据更接近其使用位置,以减少应用程序查询延迟。使用 PostgreSQL,您可以为单个主服务器设置多个备用服务器,或者从另一个备用服务器级联备用服务器。

将多个备用数据库连接到一个主数据库,或从现有备用数据库级联另一个备用数据库不需要额外的设置。但是,请检查连接限制、wal 发件人进程限制和系统资源是否足够高以支持多个备用数据库。

在这里阅读更多。


延迟复制
拥有一个比当前主数据库状态晚几个小时甚至几天的副本,可以有效地减少灾难性查询的结果("哎呀,我在prod上运行时忘记了删除的where子句")。丢失或覆盖的数据可以从延迟的副本中手动查询出来,并且可以采取适当的纠正措施。

如果没有延迟复制,要解决这样的问题就需要恢复最新的备份,通过PITR-ing到命令执行前的时间,然后从该点获取数据。不过,从坏的方面看,为了从这种错误中恢复,拥有一个完全可操作的服务器可能太昂贵了。

然而,如果你确实需要它,将你的备用服务器配置成一个延迟的副本是相当简单的:

# add this to your standby to make it lag behind the primary by 4 hours
recovery_min_apply_delay = '4h'

更多文档在这里

逻辑复制
物理复制的目的是复制主服务器数据文件的 "物理 "磁盘布局,通过转发WAL记录来实现,WAL记录基本上是对文件内容进行编码的。

另一种复制方法是记录和重放SQL级别的事务:
例如,如果我们有兴趣将一个单独的表复制到另一台服务器上,那么记录和重放所有的INSERT/UPDATE/DELETE命令就足够了,这些命令是以可序列化的顺序在上面运行。

事实上,传输 "UPDATE t SET a=a+1 "比复制磁盘上的一百万行变化要快。

这种类型的复制被称为逻辑复制,在PostgreSQL的核心版本中被引入,它已经被证明对各种使用情况很有帮助,最重要的可能是在不停机的情况下升级Postgres的主要版本。自从它在v10中被引入后的改进(特别是在v14和v15中),使它变得更加强大,适合更多的使用情况。在PostgreSQL官方文档中阅读更多关于逻辑复制的信息。

Postgres的逻辑复制是这样工作的。在源服务器上设置一个具有唯一名称的发布对象。这个发布对象指的是一个现有表的列表,并作为这些表发生变化的 "发布者"。在另一台服务器上,设置了一个订阅对象,它将从特定服务器上的特定发布对象中读取变化信息,并将这些变化重放至具有相同名称和列规格的本地表。

下面是它在实践中的样子(注意wal_level必须首先在两个服务器上设置为逻辑的)。

在源服务器上:

postgres=# create table tbl1 (a int primary key);
CREATE TABLE

postgres=# insert into tbl1 (a) select generate_series(1, 100);
INSERT 0 100

postgres=# create publication pub1 for table tbl1;
CREATE PUBLICATION

在目标服务器上:

postgres=# create table tbl1 (a int primary key);
CREATE TABLE

postgres=# create subscription sub1 connection 'host=/tmp port=6000 dbname=postgres' publication pub1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

postgres=# select count(*) from tbl1;
 count
-------
   100
(1 row)

然而,逻辑复制也有一些限制,比如缺少 DDL 复制。您可以在此处阅读有关其中一些限制的更多信息。错误地设置逻辑复制也可能是有问题的,因为可以使 Postgres 复制出违反源服务器中的事务原子性和引用完整性的数据。


只复制一些表
可以设置发布以发布对所有表的更改(创建发布时存在的表列表,之后创建的表不会自动包括在内),或指定的表列表:

-- include all tables present at time of creation of publication
create publication pub2 for all tables;

-- include only specified list of tables
create publication pub3
    for table tbl3, tbl4, tbl5;

PostgreSQL v15 也增加了在一个或多个模式中指定所有表的能力:

-- Postgres v15+ only
create publication pub4
    for tables in schema schema1, schema2;

稍后可以使用ALTER PUBLICATION更改发布中的表列表。

在此处此处阅读有关出版物的更多信息。


仅复制表的某些列
再次从 Postgres v15 开始,可以只复制表的列的子集。除了表名,列的列表也可以这样指定:

postgres=# create table tbl3 (a int primary key, b text, c text);
CREATE TABLE

postgres=# create publication pub3 for table tbl3 (a, c);
CREATE PUBLICATION

postgres=# insert into tbl3 (a, b, c) values (1, 'hello', 'world');
INSERT 0 1

在订阅端,接收复制数据的表只有列的子集:

postgres=# create table tbl3 (a int primary key, c text);
CREATE TABLE

postgres=# create subscription sub3 connection 'host=/tmp port=6000 dbname=postgres' publication pub3;
NOTICE:  created replication slot "sub3" on publisher
CREATE SUBSCRIPTION

postgres=# select * from tbl3;
 a |   c
---+-------
 1 | world
(1 row)

可以为发布一起指定行过滤器和列列表,以便仅复制表的行和列的子集。所有这一切只需几个简单的命令,无需外部工具!


仅复制表上的某些操作
可以插入、更新、删除表的行,并且可以截断表本身。表的发布者可以配置为仅将这 4 个操作中的一个或多个包含到复制数据中。

例如,这里是如何设置一个只复制对表的插入和更新的发布者:

postgres=# create table tbl4 (a int primary key, b text);
CREATE TABLE

postgres=# create publication pub4 for table tbl4 with (publish = 'insert, update');
CREATE PUBLICATION

postgres=# select * from tbl4;
 a | b
---+---
(0 rows)

现在让我们设置并启动订阅者:

postgres=# create table tbl4 (a int primary key, b text);
CREATE TABLE

postgres=# create subscription sub4 connection 'host=/tmp port=6000 dbname=postgres' publication pub4;
NOTICE:  created replication slot "sub4" on publisher
CREATE SUBSCRIPTION
然后让我们在发布者上插入、更新和删除一些行:

postgres=# select * from tbl4;
 a | b
---+---
(0 rows)

postgres=# insert into tbl4 (a, b) values (1, 'jello');
INSERT 0 1
postgres=# insert into tbl4 (a, b) values (2, 'world');
INSERT 0 1
postgres=# update tbl4 set b='hello' where a=1;
UPDATE 1
postgres=# delete from tbl4 where a=2;
DELETE 1
postgres=# select * from tbl4;
 a |   b
---+-------
 1 | hello
(1 row)

看看订阅者会发生什么:

postgres=# select * from tbl4;
 a |   b
---+-------
 2 | world
 1 | hello
(2 rows)

订阅者的表包含从发布者删除的行,因为删除操作不包括在发送的更改中。

这也说明了为什么不正确地使用逻辑复制会破坏事务的原子性:如果在发布者端的单个事务中创建然后删除了一行,订阅者仍然会得到一行,而该行从未被发布者端的任何其他事务看到发布方。

请注意,最初启动订阅时,源中的所有行都被复制到目标中。进一步的复制受指定的限制。


多主控和双向复制
术语“多主机”、“多写入器”和“双向复制”指的是具有两个或多个链接服务器的部署,其中任何一个都可以持续更新一组共享的数据库。客户端应用程序将能够从任何服务器执行更新,并且更改将被复制到其他服务器,同时保持整个数据的一致性和完整性。

因此,核心 PostgreSQL 中没有“多主”复制模式。有实现通用多主复制的第三方扩展和工具,但它们不像 PostgreSQL 本身那样广泛可用或流行。

逻辑复制对于以特定于模式的方式实现您希望使用通用多主解决方案实现的目标大有帮助。可以在任何现有的 PostgreSQL 服务器上设置发布和订阅,并且可以轻松实现任何方向的复制。冲突解决必须在架构设计和应用程序级别处理,这与通用多主解决方案提供的任何功能相反。


同步复制
默认情况下,当在 PostgreSQL 中提交事务时,它会写出所需的 WAL 记录并等待将此数据刷新到磁盘。当服务器是主服务器复制到一个或多个备用服务器时,可以进一步加强此行为以进一步降低数据丢失的风险。这可以通过配置设置synchronous_commit和synchronous_standby_names来实现。

synchronous_commit的remote_write的值使 Postgres 在回复提交已完成的应用程序之前等待备用数据库将 WAL 记录写入其磁盘。必须接收 WAL 记录的备用数据库的application_name由synchronous_standby_names指定,如下所示:

synchronous_commit = remote_write
synchronous_standby_names = mystandby1

remote_write并不一定意味着在提交完成后立即在备用数据库上启动的查询可以看到该提交中包含的更改。如果需要这样的保证,请为synchronous_commit使用remote_apply的值:

synchronous_commit = remote_apply
synchronous_standby_names = mystandby1

甚至可以设置一个仲裁提交,也就是说,n 个节点中至少有k个节点必须已经收到 WAL 记录,主节点的提交才能成功:

synchronous_commit = remote_write
synchronous_standby_names = ANY 2 (mystandby1, mystandby2, mystandby3)

synchronous_commit最有趣的地方可能是能够在事务级别而不是全局设置中指定它。仅为某些交易启用它,例如更新账户余额的交易,在需要时为操作带来额外的安全性。

BEGIN;
SET synchronous_commit = 'remote_write';
-- use the global setting for synchronous_standby_names
-- update data here
COMMIT;


跨服务器访问表
并非所有跨服务器分布的数据都需要实时复制或更新。数据可能大部分是静态的,用于参考、查找或历史等。可以使用外部数据包装器(FDW) 从主 OLTP/OLAP 服务器访问此类数据。

FDW 允许您使用“外部数据”,这些数据可能驻留在 Postgres 服务器外部的任何地方。

使用来自另一个 Postgres 服务器的数据的能力是由postgres_fdw实现的,它是核心 PostgreSQL 中可用的扩展。

Postgres FDW
这是 Postgres FDW 在实践中的样子。假设有一个源数据库,其中有一个表,如下所示:

srcdb=# create table srct (a int primary key);
CREATE TABLE

srcdb=# insert into srct (a) select generate_series(1, 100);
INSERT 0 100

在目标服务器上,您可以设置一个外部表 srct,它充当srct我们源数据库中实际表的代理表:

destdb=# create extension postgres_fdw;
CREATE EXTENSION

destdb=# create server src foreign data wrapper postgres_fdw options (host '/tmp', port '6000', dbname 'srcdb');
CREATE SERVER

destdb=# create user mapping for current_user server src;
CREATE USER MAPPING

destdb=# import foreign schema public limit to (srct) from server src into public;
IMPORT FOREIGN SCHEMA

destdb=# select count(*) from srct;
 count
-------
   100
(1 row)

外部表不占用空间也不包含任何数据——它只是作为一个占位符来引用其他地方的实际表。目标 Postgres 服务器的 postgres_fdw 扩展将建立并维护与源 Postgres 服务器的连接,将涉及外部表的每个查询转换为适当的网络调用。

外部表可以与常规本地表无缝工作,就像在这个连接中一样:

destdb=# create table destt (b int primary key, c text);
CREATE TABLE

destdb=# insert into destt (b,c) values (10,'foo'), (20,'bar');
INSERT 0 2

destdb=# select a,b,c from srct join destt on srct.a = destt.b;
 a  | b  |  c
----+----+-----
 10 | 10 | foo
 20 | 20 | bar
(2 rows)

FDW的主要工作是尽可能把工作下推到远程服务器,尽量减少两台服务器之间来回发送的数据量。例如,您希望远程服务器处理 LIMIT 子句,而不是获取所有行然后在本地应用 LIMIT 子句。然而,鉴于 SQL 的复杂性以及 PostgreSQL 的查询计划器和执行器,这并非易事。每个版本的效率都在不断提高,但有些查询可能会花费您预期的太多时间或 work_mem。


物化视图 + 外部数据包装器
根据您的用例,将物化视图与 FDW 相结合可以在复制完整表和完全远程(外部)之间提供合理的平衡。实体化视图可以有效地充当本地缓存,而本地缓存又可以与仅限本地的表一起使用,以实现本地级性能。

destdb=# create materialized view destmv as select a,b,c from srct join destt on srct.a = destt.b;
SELECT 2

destdb=# select * from destmv;
 a  | b  |  c
----+----+-----
 10 | 10 | foo
 20 | 20 | bar
(2 rows)

使用常规的“REFRESH MATERIALIZED VIEW”命令可以随时刷新“缓存”,定期或以其他方式刷新。作为奖励,可以在视图上定义(本地)索引以进一步加快查询速度。


跨服务器分布表的行
跨多个服务器对单个表的行进行分片,同时向 SQL 客户端呈现常规表的统一接口可能是处理大表最抢手的解决方案。这种方法使应用程序更简单,并让 DBA 更加努力地工作!

将表拆分成多个部分,以便查询和只处理相关的行,希望是并行的,这是分片背后的核心原则。PostgreSQL v10 引入了分区功能,此后有了很多改进并得到广泛采用。在此处阅读有关分区的更多信息。

使用分区进行垂直扩展涉及在不同的表空间(在不同的磁盘上)中创建分区。水平缩放涉及结合分区和 FDW。


分区+ FDW
继续 Postgres文档中的示例,让我们创建具有一个本地分区表和一个外部分区表的分区根表测量:

destdb=# CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);
CREATE TABLE

destdb=# CREATE TABLE measurement_y2023
PARTITION OF measurement
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE

destdb=# CREATE FOREIGN TABLE measurement_y2022
PARTITION OF measurement
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01')
SERVER src;
CREATE FOREIGN TABLE

外部表只是一个代理,所以实际的表本身必须存在于外部服务器上:

srcdb=# CREATE TABLE measurement_y2022 (
    city_id         int not null,
    logdate         date not null
        CHECK (logdate >= '2022-01-01' and logdate <= '2023-01-01'),
    peaktemp        int,
    unitsales       int
);
CREATE TABLE

我们现在可以将行插入根表并将其路由到适当的分区。您可以看到 SELECT 查询执行了本地扫描和外部扫描并将结果组合在一起。

destdb=# insert into measurement (city_id, logdate, peaktemp, unitsales)
values (1, '2022-01-03', 66, 100), (1, '2023-01-03', 67, 300);
INSERT 0 2

destdb=# select * from measurement;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2022-01-03 |       66 |       100
       1 | 2023-01-03 |       67 |       300
(2 rows)

destdb=# explain select * from measurement;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Append  (cost=100.00..219.43 rows=3898 width=16)
   ->  Foreign Scan on measurement_y2022 measurement_1  (cost=100.00..171.44 rows=2048 width=16)
   ->  Seq Scan on measurement_y2023 measurement_2  (cost=0.00..28.50 rows=1850 width=16)
(3 rows)

但是,分区表和外部表在 PostgreSQL 中仍然存在实现限制,这意味着此设置仅适用于简单表和基本查询。


几个月前,微软发布了 Citus 的完全开源版本v11 。它作为常规 Postgres 扩展开发和提供,可以安装到常规 Postgres 部署中。
除了分区和 FDW 可以实现的功能之外,它还提供了许多功能,包括相关表的共置分区和并发分片重新平衡。在这里找到更多