最近，在irc上，有人使用各种pg_dump/pg_dumpall调用来获取数据库的转储。
我表达了我的想法，但觉得这可能是一个很好的博客主题。
首先，我们有两个可用的工具。
pg_dumpall
pg_dump
在给定的PostgreSQL安装（集群）中，pg_dumpall转储了所有的数据库，并将其保存到plain文本文件中，一切都在那里。另外，它转储全局 的东西——角色和表空间，这些不能被pg_dump转储。
pg_dumpall的主要好处是它是单个命令，你可以获得结果。
但也存在很多缺点：
转储很大，因为它未压缩
转储非常慢，因为它是顺序完成的，只有一个工作程序
仅恢复部分转储很难
要加载这样的转储，通常可以运行：
=$ psql -f dump.file或者，更好的是：
=$ psql -f dump.file -v ON_ERROR_STOP=1这样它将在出现第一个错误后立即停止——使跟踪错误更简单。
另一方面，pg_dump不能转储全局，并且一次只能转储一个数据库。但是它可以使用四种转储格式：
plain
custom
directory
tar
Plain是纯文本格式，就像pg_dumpall转储一样。你可以用psql加载它，如果转储很大，提取部分可能会很复杂。
使用pg_restore程序恢复所有其他格式（custom，directory和tar）。
为了更清楚的看到差异，让我们使用所有格式进行数据库转储：
=$ for a in p c t ddo echo "Format: $a" time pg_dump -F $a -C -f dump-$a depesz_explaindoneFormat: p real 0m17.604suser 0m1.102ssys 0m4.646sFormat: c real 1m47.669suser 1m45.056ssys 0m1.641sFormat: t real 0m22.308suser 0m1.381ssys 0m8.648sFormat: d real 1m50.406suser 1m47.875ssys 0m1.473s
这看起来不太直观，但是主要是因为压缩。Plain转储使用了7.2GB（跟tar一样），但dir和custom仅各使用了970MB。
当我使用-Z0选项从custom格式和dir格式中删除压缩时，我得到了非常相近的时间：-Fc在18.442s中完成，-Fd在18.732s中完成。
但是，这些格式也有好处。
它们（除了plain）都可以生成转储的内容列表，然后只还原转储的一部分。
例如：
=$ pg_restore -l dump-c | head -n 20;; Archive created at 2019-12-10 18:20:59 CET; dbname: depesz_explain; TOC Entries: 723; Compression: -1; Dump Version: 1.14-0; Format: CUSTOM; Integer: 4 bytes; Offset: 8 bytes; Dumped from database version: 13devel; Dumped by pg_dump version: 13devel;;; Selected TOC Entries:;8; 2615 370778 SCHEMA - plans depesz_explain592; 1247 370781 TYPE public register_plan_return depesz_explain257; 1255 370782 FUNCTION public get_random_string(integer) depesz_explain258; 1255 370783 FUNCTION public register_plan(text, text, boolean, boolean, text) depesz_explain259; 1255 370784 FUNCTION public register_plan(text, text, boolean, boolean, text, text) depesz_explain
你可以将 -l 的输出捕获到文件中，进行编辑，然后pg_restore将只还原你列出的元素。
例如：
=$ pg_restore -l dump-c | grep get_random_string > edited.list =$ cat edited.list257; 1255 370782 FUNCTION public get_random_string(integer) depesz_explain =$ pg_restore -L edited.list -f partial.restore dump-c 18:37:27 pgdba@korsarz var/tmp/dump.test=$ cat partial.restore---- PostgreSQL database dump-- -- Dumped from database version 13devel-- Dumped by pg_dump version 13devel SET statement_timeout = 0;SET lock_timeout = 0;SET idle_in_transaction_session_timeout = 0;SET client_encoding = 'UTF8';SET standard_conforming_strings = on;SELECT pg_catalog.set_config('search_path', '', false);SET check_function_bodies = false;SET xmloption = content;SET client_min_messages = warning;SET row_security = off; ---- Name: get_random_string(integer); Type: FUNCTION; Schema: public; Owner: depesz_explain-- CREATE FUNCTION public.get_random_string(string_length integer) RETURNS text LANGUAGE plpgsql AS $$DECLARE possible_chars TEXT = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'; output TEXT = ''; i INT4; pos INT4;BEGIN FOR i IN 1..string_length LOOP pos := 1 + cast( random() * ( length(possible_chars) - 1) as INT4 ); output := output || substr(possible_chars, pos, 1); END LOOP; RETURN output;END;$$; ALTER FUNCTION public.get_random_string(string_length integer) OWNER TO depesz_explain; ---- PostgreSQL database dump complete--
更重要的是——通过custom/dir转储，我们可以使用多个并行工作程序加载它们。
例如：
=$ dropdb --force depesz_explain ; time psql -qAtX -v ON_ERROR_STOP=1 -f dump-preal 2m13.950suser 0m2.817ssys 0m2.537s
在8路并行中加载custom时：
=$ dropdb --force depesz_explain ; time pg_restore -j 8 -C -d postgres dump-creal 0m35.152suser 0m21.316ssys 0m1.788s
dir转储的并行加载时间是相同的。
所以，最后——有一个很大的 不同以支持DIR格式——我们可以并行地转储 数据库。例如：
=$ time pg_dump -F d -j 8 -C -f dump-j8-dir depesz_explainreal 0m24.928suser 2m30.755ssys 0m2.125s
24秒仅比plain文本转储多出7秒，但是转储更小（~970MB），我们可以并行还原，也可以还原部分内容。
如果速度至关重要：
=$ time pg_dump -F d -j 8 -C -Z 0 -f dump-j8-z0-dir depesz_explainreal 0m8.090suser 0m1.849ssys 0m7.780s
因此，总而言之：
| 转储格式 | ||||
|---|---|---|---|---|
| plain | custom | tar | dir | |
| 并行转储 | ✗ | ✗ | ✗ | ✓ |
| 并行恢复 | ✗ | ✓ | ✗ | ✓ |
| 部分恢复 | ✗ | ✓ | ✓ | ✓ |
那么，我们如何利用这些知识来转储所有数据库和全局？
我将重复以前的博客文章中的观点。这个脚本，用新的知识给出：
#!/usr/bin/env bash # Unofficial Bash Strict Mode# http://redsymbol.net/articles/unofficial-bash-strict-mode/set -euo pipefailIFS=$'\n\t'# End of Unofficial Bash Strict Mode # configtop_level_backup_dir="/var/tmp/backups-pg"backup_keep_days="3"concurrent_dumps="2"dump_workers="5"# config cd "${top_level_backup_dir}" backup_dir="$( date '+%Y-%m-%d' )"mkdir "${backup_dir}"cd "$backup_dir" # Make actual backup files # First globalspg_dumpall -r -f roles.dumppg_dumpall -t -f tablespaces.dump # And now per-database dumpspsql -qAtX -c "select datname from pg_database where datallowconn order by pg_database_size(oid) desc" | \ xargs -d '\n' -P "${concurrent_dumps}" -I % pg_dump -F d -C -j "${dump_workers}" -f pg-%.dump % # Add marker so that we'd know if the backup has finished correctlytouch backup.done # Retention policycd "$top_level_backup_dir"cutoff_date="$( date -d "${backup_keep_days} days ago" '+%Y-%m-%d' )" # Iterate over all backupsfor backup in [0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]do # We need to remove - characters from dates, because otherwise we'd get: # >> 2019-12-08: value too great for base (error token is "08") [[ "${backup//-/}" -ge "${cutoff_date//-/}" ]] && continue # Backup is not within backup_keep_days, remove it. rm -rf "${backup}"done # All done.
exit 0
这就是全部内容。
希望对你有所帮助。
本文翻译自：https://www.depesz.com/2019/12/10/how-to-effectively-dump-postgresql-databases/
