程序员经验分享-hwhale

Postgresql DB 备份理想实践

2024-01-20

• 使用pg_dump 进行PostgreSQL 逻辑备份的理想做法是什么？

• 从备用/从属节点进行备份是否理想？如果复制延迟小于 200 毫秒

• 从备用/从属节点进行备份是否理想？是否需要更改任何特定配置？

• 哪种备份方法是逻辑备份或物理备份的好方法？ DB 经常更新。当备份用于灾难恢复时，哪种方法是更快、更好的备份和灾难恢复（恢复）。

updated
我们当前的数据库大小为 5GB，并且复制已开启hot standby mode.
我们在从节点上运行备份脚本，但它每 30 分钟从主节点进行一次远程备份。
我创建这个问题的原因是为了了解备份何时运行COPY语句需要 6 分钟才能完成，即使它不会影响数据库上的其他事务，如果语句花费更多时间，是否会出现任何其他问题。


我考虑了你写的内容，这里有一些想法供你参考：

  1. 如果您需要真正与某个时间点保持一致的备份，那么您必须使用 pg_basebackup 或 pg_barman （内部使用 pg_basebackup） - 解释在下面的 1. 链接中。最新的 pg_basebackup 10 流式传输 WAL 日志，因此您还可以备份备份期间完成的所有更改。当然，这个备份只需要整个PG实例。另一方面，它不锁定任何表。如果您从远程实例执行此操作，那么它只会在 PG 实例上造成较小的 CPU 负载，并且磁盘 IO 不会像某些文本建议的那么大。有关我的经历，请参阅链接 4。恢复非常简单 - 请参阅链接 5。
  2. 如果您使用 pg_dump，您必须明白，您无法保证您的备份与时间点确实一致 - 再次参见链接 1。可以使用数据库快照（参见链接 2 和 3），但即使使用它你不能指望 100% 的一致性。我们仅在分析数据库上使用 pg_dump，该数据库每天仅加载 1 次新数据（昨天来自生产数据库的分区）。您可以使用并行选项加速它（仅适用于目录备份格式）。但缺点是 PG 实例的负载要高得多 - CPU 使用率更高，磁盘 IO 更高。即使您远程运行 pg_dump - 在这种情况下，您也只节省磁盘 IO 来保存备份文件。另外 pg_dump 需要在表上放置读锁，这样它就可以与新插入或复制（当在副本上进行时）发生冲突。但是，当您的数据库达到数百 GB 时，即使并行转储也可能需要几个小时，此时您无论如何都需要切换到 pg_basebackup。
  3. pg_barman 是 pg_basebackup 的“舒适版本”+它可以让你防止数据丢失，即使你的 PG 实例崩溃得很严重。让它发挥作用需要更多的改变，但这绝对是值得的。你必须设置WAL日志归档（参见链接6），如果你的PG

您的数据库有 5GB 大，因此任何备份方法都会很快。但您必须决定是否需要时间点恢复和几乎零数据丢失 - 因此您是否愿意花时间设置 pg-barman。

Links:

  1. PostgreSQL、备份以及您需要了解的一切 https://www.compose.com/articles/postgresql-backups-and-everything-you-need-to-know/
  2. 论文评论：PostgreSQL 中的 14-可序列化快照隔离 https://web.eecs.umich.edu/~mozafari/fall2015/eecs584/reviews/summaries/summary14.html- 关于快照
  3. 数据库并行转储 https://www.depesz.com/2013/03/05/parallel-dumping-of-databases/- 示例如何使用快照
  4. pg_basebackup 经验 http://postgresql.freeideas.cz/pg_basebackup-experiences/
  5. pg_basebackup - 恢复 tar 备份 http://postgresql.freeideas.cz/pg_basebackup-pgbarman-restore-tar-backup/
  6. 使用脚本归档 WAL 日志 http://postgresql.freeideas.cz/streaming-replication-pg-barman-archiving-wal-logs-using-script/
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

postgresql

pgdump

Postgresql DB 备份理想实践 的相关文章

  • 如何在 PostgreSQL 中克隆记录

    我想循环查询 但也保留下一个循环的实际记录 这样我就可以比较两个相邻的行 CREATE OR REPLACE FUNCTION public test RETURNS void AS body DECLARE previous RECORD

  • PostgreSQL regexp_matches 只返回匹配的行？

    这是我第一次使用 regexp matches 我发现使用它只会返回与 SELECT 子句中的所有 regexp matches 匹配的行 例如 SELECT parameters regexp matches parameters a d

  • JSON 值的模式匹配

    运行 Postgres 12 5 的本地 docker 实例 4MBwork mem 我正在实施这个图案 https dba stackexchange com q 108447 3684搜索 json 中的任意字段 目标是搜索并返回 JS

  • Postgres where 子句比较时间戳

    我有一个表 其中列的数据类型timestamp 其中包含一天的多条记录 我想选择与日期对应的所有行 我该怎么做 Assuming you actually mean timestamp because there is no datetim

  • PostgreSQL：有效地将 JSON 数组拆分为行

    我有一个表 表 A 其中包含一个包含 JSON 编码数据的文本列 JSON 数据始终是一个包含一到几千个普通对象的数组 我有另一个表 表 B 其中有几列 包括数据类型为 JSON 的列 我想从表 A 中选择所有行 将 json 数组拆分为其

  • Postgres 平均值计算忽略 null

    这是我的 postgres 表 name revenue John 100 Will 100 Tom 100 Susan 100 Ben 5 rows 在这里 当我计算平均收入时 它返回 100 这显然不是这种情况 而总和 计数 即 400

  • postgresql中数组的区别

    我有两个数组 1 2 3 4 7 6 and 2 3 7 在 PostgreSQL 中可能有共同的元素 我想做的是从第一个数组中排除第二个数组中存在的所有元素 到目前为止我已经取得了以下成果 SELECT array SELECT unne

  • PostgreSQL 仅当列存在时才重命名该列

    我在中找不到PostgreSQL 文档 https www postgresql org docs 12 sql altertable html如果有办法运行 ALTER TABLE tablename RENAME COLUMN IF E

  • PostgreSQL - 根据另一个单元格值设置默认单元格值

    如果我有一个专栏说column a任何给定值 我想要另一列column b有一个default value根据 的值column a 换句话说 if column a peter then column b default value do

  • Postgres：显示继承的字段

    我应该实现什么查询来获取继承的列 读过this http www alberton info postgresql meta info html综合帖子没有找到解决办法 如果我理解正确的话 您想知道作为表之间继承的一部分的列的名称 SELE

  • 最近邻居的 Postgis SQL

    我正在尝试计算最近的邻居 为此 我需要传递一个参数来限制与邻居的最大距离 例如 半径1000米内最近的邻居是哪些 我做了以下事情 我用数据创建了表 id name latitude longitude 之后 我执行了以下查询 SELECT

  • PostgreSQL：使用for循环迭代表行，根据当前行检索列值

    我有以下2张表 CREATE TABLE salesperson t salespersonid numeric 4 0 NOT NULL salespersonname character varying 25 salespersonte

  • PostgreSQL 在递归查询中找到所有可能的组合（排列）

    输入是一个长度为 n 的数组 我需要生成数组元素的所有可能组合 包括输入数组中元素较少的所有组合 IN j A B C OUT k A AB AC ABC ACB B BA BC BAC BCA 随着重复 所以AB BA 我尝试过这样的事情

  • java -postgresql 最后插入的 id 插入时未获取

    我有一个插入功能postgresql如下 CREATE OR REPLACE FUNCTION insert orderhead order id integer order dt text customer id integer rout

  • Npgsql 参数化查询输出与 PostGIS 不兼容

    我在 Npgsql 命令中有这个参数化查询 UPDATE raw geocoding SET the geom ST Transform ST GeomFromText POINT longitude latitude 4326 3081

  • pg_dump 没有对象注释？

    有没有办法执行 pg dump 并排除表 视图和列的 COMMENT ON 我广泛使用 COMMENT ON 命令来描述所有对象 并且经常在其中包含换行符以获得更清晰的描述 例如 COMMENT ON TABLE mytable1 IS M

  • 带有可变 WHERE 子句的批量 UPDATE 表

    我有一堆值对 foo1 bar1 foo2 bar2 我想做一堆更新 将 foo 列设置为 foo1 其中 bar 列为 bar1 我正在使用 psycopg2 在 Python 中执行此操作 我可以executemany与查询UPDATE

  • postgresql：插入...（选择*...）

    我不确定它是否是标准 SQL INSERT INTO tblA SELECT id time FROM tblB WHERE time gt 1000 我正在寻找的是 如果 tblA 和 tblB 位于不同的数据库服务器中怎么办 Postg

  • 使用 PostGIS 拥有通用 GEOGRAPHY 列

    在 PostgreSQL 9 PostGIS 1 5 中 有没有办法创建 通用 GEOGRAPHY 列 我指的是接受 POINT 以及 POLYGON LINESTRING 等的列 到目前为止 我只在 Internet 上看到过像 GEOG

  • TimescaleDB 查询选择列值较上一行发生更改的行

    最近刚刚开始使用 TimescaleDB 和 Postgres 来处理大多数数据请求 然而 我遇到了一个问题 即我对时间序列数据的请求效率极低 它是一个可以是任意时间长度 具有特定整数值的数据系列 大多数时候 除非出现异常 否则该值将是相同

随机推荐

热门标签