如何有效转储PostgreSQL数据库

最近，有人使用各种pg_dump / pg_dumpall调用以获取数据库的转储。

首先，我们有两个可用的工具：

• pg_dumpall
• pg_dump

pg_dumpall转储给定PostgreSQL安装（群集）中的所有数据库，并将其保存到纯文本文件中。另外，它可以转储全局的东西–角色和表空间，而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程序恢复所有其他格式（自定义，目录和tar）。

为了更清楚地看到差异，让我使用所有格式进行数据库转储：

=$ for a in p c t d
do
    echo "Format: $a"
    time pg_dump -F $a -C -f dump-$a depesz_explain
done
Format: p
 
real    0m17.604s
user    0m1.102s
sys     0m4.646s
Format: c
 
real    1m47.669s
user    1m45.056s
sys     0m1.641s
Format: t
 
real    0m22.308s
user    0m1.381s
sys     0m8.648s
Format: d
 
real    1m50.406s
user    1m47.875s
sys     0m1.473s

这看起来不太好，但是主要是由于压缩。普通转储使用了7.2GB（就像tar）一样，但dir和custom仅使用了970MB。

当我使用-Z0选项从自定义格式和目录格式中删除压缩时，我得到了更多相似的时间：-Fc在18.442s中完成，-Fd在18.732s中完成。

但是，这些格式有很多好处。

所有这些文件（普通文件除外）都可以生成转储的内容列表，然后仅还原转储的一部分。

例如：

=$ 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_explain
592; 1247 370781 TYPE public register_plan_return depesz_explain
257; 1255 370782 FUNCTION public get_random_string(integer) depesz_explain
258; 1255 370783 FUNCTION public register_plan(text, text, boolean, boolean, text) depesz_explain
259; 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.list
257; 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

更重要的是–通过自定义/目录转储，我们可以使用多个并行工作程序加载它们。

例如：

=$ dropdb --force depesz_explain ; time psql -qAtX -v ON_ERROR_STOP=1 -f dump-p
real    2m13.950s
user    0m2.817s
sys     0m2.537s

在8路并行中加载自定义时：

=$ dropdb --force depesz_explain ; time pg_restore -j 8 -C -d postgres dump-c
real    0m35.152s
user    0m21.316s
sys     0m1.788s

目录转储的并行加载时间相同。

所以，最后-有一个大赞成DIR格式的差异-我们可以转储并行数据库。例如：

=$ time pg_dump -F d -j 8 -C -f dump-j8-dir depesz_explain
real    0m24.928s
user    2m30.755s
sys     0m2.125s

24秒仅比纯文本转储多出7秒，但是转储较小（〜970MB），我们可以并行还原，也可以还原部分内容。

如果速度至关重要：

=$ time pg_dump -F d -j 8 -C -Z 0 -f dump-j8-z0-dir depesz_explain
real    0m8.090s
user    0m1.849s
sys     0m7.780s

因此，总而言之：

转储格式

那么，我们如何利用这些知识来转储所有数据库和全局变量？具有新知识的该脚本可提供：

#!/usr/bin/env bash
 
# Unofficial Bash Strict Mode
# http://redsymbol.net/articles/unofficial-bash-strict-mode/
set -euo pipefail
IFS=$'\n\t'
# End of Unofficial Bash Strict Mode
 
# config
top_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 globals
pg_dumpall -r -f roles.dump
pg_dumpall -t -f tablespaces.dump
 
# And now per-database dumps
psql -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 correctly
touch backup.done
 
# Retention policy
cd "$top_level_backup_dir"
cutoff_date="$( date -d "${backup_keep_days} days ago" '+%Y-%m-%d' )"
 
# Iterate over all backups
for 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

这就是全部。

希望您会发现它有用。

来源：如何有效转储PostgreSQL数据库