在 PostgreSQL 中使用快照

由 John Doe 十二月 17, 2024

摘要：在本文中，我们将学习如何在 PostgreSQL 中使用快照。

目录

介绍

快照导出最初是在版本 9.2 中实现的，它允许多个事务共享相同数据库状态的同一视图。那时，该功能并没有实际的应用场景，直到 PostgreSQL 版本 9.3 出现，当多线程并行导出被引入 pg_dump 时，它提供了一个真实的应用案例。

pg_dump 如何使用快照？

这是一个简单的例子；让我们先创建两个表，导入大量的数据：

begin;
create table t1(
    c1 serial primary key,
    c2 text default lpad('',500,md5(random()::text)),
    c3 timestamp default clock_timestamp()
);

create table t2(like t1 including all);

insert into t1 select generate_series(1,5e6);
insert into t2 select generate_series(1,5e6);

analyze;
commit;

=> \d
                    List of relations
 Schema | Name | Type  |  Owner   |  Size   | Description
--------+------+-------+----------+---------+-------------
 public | t1   | table | postgres | 2791 MB |
 public | t2   | table | postgres | 2791 MB |

提示：可以根据您的硬件能力，更改generate_series函数中的参数，填充最适合的数据量。

传统的调用一次逻辑转储，会使用单个 CPU/线程一次归档一个表。命令行工具 time 可生成计时的统计信息，返回使用 “compressed” 格式执行转储所需的时间：

# execution time: 60 seconds
time pg_dump -Fc db01 > db01.db

real    1m0.322s
user    0m50.203s
sys     0m1.309s

生成转储的另一种调用方式是，使用 “directory” 格式：

# execution time: 52 seconds
time pg_dump -Fd db01 -f db01_dirA

real    0m52.304s
user    0m50.908s
sys     0m1.152s

到目前为止，执行都是使用单个 CPU。现在来看一下指定 ‘-j’ 或 ‘--jobs’ 选项时的执行时间，这样会使用多个连接来生成转储：

# execution time: 31 seconds
time pg_dump -Fd db01 -f db01_dirB -j 2

real    0m31.115s
user    1m0.242s
sys     0m1.377s

请注意在后台运行的进程数。第三个连接是一个领导进程，用于协调两个工作进程：

$ ps aux | grep -v grep | grep pg_dump
postgres 1872809  0.1  0.0  14444  5968 pts/1    S+   08:52   0:00 pg_dump -Fd db01 -f db01_dirB -j 2
postgres 1872811  100  0.0  14804  4480 pts/1    R+   08:52   0:12 pg_dump -Fd db01 -f db01_dirB -j 2
postgres 1872812  100  0.0  14804  4488 pts/1    R+   08:52   0:12 pg_dump -Fd db01 -f db01_dirB -j 2

如果没有同步快照的功能，则无法保证不同工作进程的作业在各自的连接中看到相同的数据，这可能会导致产生不一致的备份。

需要记住的一个重要警告是，pg_dump 提供的 jobs 选项仅适用于 “directory” 格式。尝试将 jobs 选项用于单个数据库的转储会失败：

$ pg_dump -Fc db01 -j 2 > db01.db
pg_dump: error: parallel backup only supported by the directory format

PostgreSQL 文档中提供了一份完整描述，说明使用 jobs 选项时的其他注意事项。

我们已经展示了快照是如何与 pg_dump 配合使用的，其实，我们还能更进一步地使用它。

使用快照

我们可以使用快照同步函数 pg_export_snapshot()，来复制 pg_dump 的行为。

继续使用之前创建的两个表，让我们创建另一个场景。请查看下表中的每个步骤：

  • 步骤 1：登录三个 psql 会话，并与表 t1 和 t2 同步交互。
  • 步骤 2：会话 1 每 5 秒插入一次表。会话 2 查看数据的方式与会话 3 不同，请注意会话 3 中查询的 3 秒延迟，因此无法同时看到相同的数据集。
  • 步骤 3：会话 1 继续更新表 t1，但现在会话 2 和 3 使用相同的快照会话。
  • 步骤 4：当会话 1 继续更新表 t1、t2 时，会话 2 和 3 看到相同的数据集，即它们已同步了。
  • 步骤 5：会话 2 和 3 同时主动复制数据，无需担心不一致。
  • 步骤 6：COPY 已完成，因此让我们通过提交事务来完成。
步骤 会话 1 会话 2 会话 3
1 psql db01 psql db01 psql db01
2 WITH a(x) AS (SELECT max(c1) FROM t1),
b AS (INSERT INTO t1 SELECT generate_series(x + 1, x + 10) FROM a returning *),
c AS (INSERT INTO t2 SELECT generate_series(x + 1, x + 10) FROM a returning *)
SELECT x AS increment FROM a;
watch 5		 WITH a(x) AS (SELECT max(c1) FROM t1)
SELECT c1, md5(c2), c3 FROM t1, a WHERE c1 = x
UNION ALL
SELECT c1, md5(c2), c3 FROM t2, a WHERE c1 = x;		 WITH a(x) AS (SELECT max(c1) FROM t1)
SELECT c1, md5(c2), c3 FROM t1, a WHERE c1 = x
UNION ALL
SELECT c1, md5(c2), c3 FROM t2, a, pg_sleep(3) WHERE c1 = x;
3 步骤 2 处于活动状态 -- 让我们来导出快照 00000003-000021CE-1
begin transaction isolation level repeatable read;
SELECT pg_export_snapshot();		 -- 让我们来导入快照
begin transaction isolation level repeatable read;
set transaction snapshot ‘00000003-000021CE-1’;
4 步骤 2 处于活动状态 WITH a(x) AS (SELECT max(c1) FROM t1)
SELECT c1, md5(c2), c3 FROM t1, a WHERE c1 = x
UNION ALL
SELECT c1, md5(c2), c3 FROM t2, a WHERE c1 = x;		 WITH a(x) AS (SELECT max(c1) FROM t1)
SELECT c1, md5(c2), c3 FROM t1, a WHERE c1 = x
UNION ALL
SELECT c1, md5(c2), c3 FROM t2, a, pg_sleep(3) WHERE c1 = x;
5 步骤 2 处于活动状态 COPY t1 TO PROGRAM ‘gzip > t1.gz’ COPY t2 TO PROGRAM ‘gzip > t2.gz’
6 步骤 2 终止 -- 可选择提交或回滚
COMMIT		 -- 可选择提交或回滚
COMMIT

好了，您已经学习到了一个导出快照的巧妙技巧！当然，除了以多线程方式复制表之外，您还可以做更多的事情，但希望这个小小的用例能带来更多可能性，能简化您在使用 PostgreSQL 时的工作。

搜索

分类

标签