《PostgreSQL 开发指南》第 08 篇 备份与恢复
pg_dump，逻辑备份工具，支持单个数据库（可以指定模式、表）的导出，可以选择导出的格式。
pg_dumpall，逻辑备份工具，用于导出整个数据库集群，包括公用的全局对象。
pg_basebackup，物理备份工具，为数据库集群创建一个基准备份。它也可以用于时间点恢复（point-in-time recovery）的基准备份，或者设置基于日志传输或流复制的从节点的初始化。
psql，PostgreSQL 交互式命令行工具，也可以用于导入逻辑备份产生的 SQL 文件。
pg_restore，逻辑还原工具，用于还原 pg_dump 导出的归档格式的备份文件。
COPY，PostgreSQL 专有的 SQL 语句，将表中的数据复制到文件，或者将文件中的数据复制到表中。
此外，还可以通过第三方工具执行备份与恢复操作。
pgAdmin（开源）
Barman（开源）
pg_probackup（开源）
pgBackRest（开源）
-
BART（商业）
plain，文本格式，输出一个纯文本形式的 SQL 脚本，默认值。还原时直接使用 psql 导入。
custom，自定义格式，输出一个自定义格式的归档文件，还原时使用 pg_restore 工具。与目录导出格式结合使用时，提供了最灵活的输出格式，它允许在恢复时手动选择和排序已归档的项。这种格式在默认情况还会进行文件的压缩。
directory，目录格式，输出一个目录格式的归档，还原时使用 pg_restore 工具。这种格式将会创建一个目录，为每个导出的表和大对象都创建一个文件，另外再创建一个内容目录文件，该文件使用自定义格式存储关于导出对象的描述。这种格式在默认情况还会进行文件的压缩，并且支持并行导出。
tar，打包格式，输出一个 tar 格式的归档，还原时使用 pg_restore 工具。这种格式与目录格式兼容，解压一个 tar 格式的归档将会产生一个目录格式的归档。但是，tar 格式不支持压缩。另外，在使用 tar 格式归档进行还原时，表数据项的相对顺序不能进行改动。
-bash-4.2$ whoami
postgres
-bash-4.2$ pg_dump testdb > testdb.sql
-bash-4.2$ psql newdb -f testdb.sql
SET
SET
SET
...
pg_dump -h host1 dbname | psql -h host2 dbname
-bash-4.2$ pg_dump -Fc testdb -f testdb.dmp
-bash-4.2$ pg_restore -d newdb testdb.dmp
-bash-4.2$ pg_dump -Fd testdb -f testdb_dir
-bash-4.2$ pg_restore -d newdb testdb_dir
-
bash-4.2$ pg_dump -Ft testdb -f testdb.tar
-bash-4.2$ pg_restore -d newdb testdb.tar
-bash-4.2$ pg_dumpall -f cluster.sql
-bash-4.2$ psql -f cluster.sql postgres
使用 pg_dump 中导出表的选项 -t 和 -T；
使用COPY命令复制数据
-bash-4.2$ pg_dump -a -t
'emp*' -T employees testdb > testdb_table.sql
postgres=
# \c testdb;
You are now connected to database
"testdb" as user
"postgres".
testdb=
# COPY products to '/var/lib/pgsql/products.dat';
COPY 1
testdb=
# COPY products FROM '/var/lib/pgsql/products.dat';
-bash-4.2$ time pg_dumpall > cluster.sql
real2m18.435s
user0m32.315s
sys0m49.526s
-bash-4.2$ time pg_dumpall | gzip > cluster.sql.gz
real6m20.461s
user6m38.507s
sys0m47.674s
-bash-4.2$ ls -lh
total 23G
drwx------ 6 postgres postgres 81 Dec 5 11:18 11
drwx------. 2 postgres postgres 6 Dec 8 2017 backups
-rw-r--r-- 1 postgres postgres 22G Mar 6 04:00 cluster.sql
-rw-r--r-- 1 postgres postgres 1.8G Mar 6 04:33 cluster.sql.gz
drwx------. 2 postgres postgres 6 Dec 8 2017 data
-bash-4.2$ gunzip -c cluster.sql.gz | psql newdb
-bash-4.2$ pg_dumpall | split -b 1G - cluster
-bash-4.2$ cat cluster* | psql newdb
COPY products to PROGRAM
'gzip > /var/lib/pgsql/products.dat.gz';
COPY products FROM PROGRAM
'gunzip < /var/lib/pgsql/products.dat.gz';
-bash-4.2$ time pg_dump -F d -f out.dir testdb
real10m22.901s
user10m13.333s
sys0m8.830s
-bash-4.2$ time pg_dump -j 8 -F d -f out.dir testdb
real2m31.607s
user2m27.522s
sys0m4.010s
-
关闭 PostgreSQL 服务； -bash-4.2$ pg_ctl stop -D /var/lib/pgsql/14/data/
-
执行操作系统命令，拷贝数据目录； -bash-4.2$ tar czf backup.tar.gz /var/lib/pgsql/14/data
-
也可以使用cp或者rsync等命令复制数据目录。
-
完成备份之后，重新启动 PostgreSQL 服务。
-bash-4.2$ pg_ctl start -D /var/lib/pgsql/14/data/
-
关闭 PostgreSQL 服务（也可以启动另一个实例服务管理备份的数据目录）； -bash-4.2$ pg_ctl stop -D /var/lib/pgsql/14/data/
-
使用备份的数据目录启动实例服务； -bash-4.2$ pwd
/var/lib/pgsql
-bash-4.2$ tar xzf backup.tar.gz
-bash-4.2$ /usr/pgsql-14/bin/pg_ctl start -D ./var/lib/pgsql/14/data/
wal_level，决定写入 WAL 的信息量。可选值为 minimal、replica 以及 logical，默认值为 replica。启用 WAL 归档需要设置为 replica 或更高配置。在 PostgreSQL 9.6 之前的版本中，还允许设置为 archive 和 hot_standby。仍然可以设置这两个值， 但它们直接映射到 replica。
archive_mode，是否启动日志归档。默认值为 off。如果设置为 on 或者 always，可以通过设置下面的归档命令参数 archive_command 执行已完成的 WAL 段文件的归档操作。
archive_command，执行日志归档操作的脚本命令。例如操作系统的 cp 命令。
wal_level = replica
archive_mode = on
archive_command =
'test ! -f /var/lib/pgsql/wal_archive/%f && cp %p /var/lib/pgsql/wal_archive/%f'
archive_timeout = 300
test ! -f /var/lib/pgsql/wal_archive/000000010000000000000001 && cp pg_wal/000000010000000000000001 /var/lib/pgsql/wal_archive/000000010000000000000001
-bash-4.2$ pg_ctl restart
postgres=
# select * from pg_switch_wal();
pg_switch_wal
---------------
0/40001C8
(1 row)
postgres=
# select * from pg_stat_archiver;
archived_count | last_archived_wal | last_archived_time | failed_count | last_failed_wal | last_failed_time | stats_rese
t
----------------+--------------------------+-------------------------------+--------------+--------------------------+-------------------------------+--------------------
-----------
4 | 000000010000000000000004 | 2019-03-25 22:36:16.462352-04 | 0 | | | 2019-01-04 16:57:12.471428-05
postgres=
# SELECT pg_start_backup('basebackup20190328');
pg_start_backup
-----------------
0/2E000060
(1 row)
-bash-4.2$ cp -r /var/lib/pgsql/14/data/ /var/lib/pgsql/14/backups/basebackup20190328/
-bash-4.2$ ls /var/lib/pgsql/14/backups/basebackup20190328/
backup_label global pg_dynshmem pg_logical pg_replslot pg_stat pg_tblspc pg_wal postgresql.conf tablespace_map
base
log pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_twophase pg_xact postmaster.opts
current_logfiles pg_commit_ts pg_ident.conf pg_notify pg_snapshots pg_subtrans PG_VERSION postgresql.auto.conf postmaster.pid
postgres=
# SELECT pg_stop_backup();
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
pg_stop_backup
----------------
0/2E000168
(1 row)
postgres=
# \c testdb
You are now connected to database
"testdb" as user
"postgres".
testdb=
# create table t1(id int,v varchar(10));
CREATE TABLE
testdb=
# insert into t1(id, v) values(1, 'pg');
INSERT 0 1
-bash-4.2$ ps -ef|grep /usr/pgsql-14/bin/postgres
postgres 6471 32086 0 02:46 pts/1 00:00:00 grep --color=auto /usr/pgsql-14/bin/postgres
postgres 32313 1 0 Mar25 pts/1 00:00:16 /usr/pgsql-14/bin/postgres -D /var/lib/pgsql/14/data/
-bash-4.2$
kill -9 32313
-bash-4.2$ ps -ef|grep postgres
postgres 7058 32086 0 02:48 pts/1 00:00:00 ps -ef
postgres 7059 32086 0 02:48 pts/1 00:00:00 grep --color=auto postgres
root 32083 7880 0 Mar25 pts/1 00:00:00 su - postgres
postgres 32086 32083 0 Mar25 pts/1 00:00:01 -bash
-bash-4.2$ mkdir /var/lib/pgsql/14/data_old
-bash-4.2$ mv /var/lib/pgsql/14/data/* /var/lib/pgsql/14/data_old
-bash-4.2$ pg_ctl start
pg_ctl: directory
"/var/lib/pgsql/14/data" is not a database cluster directory
-bash-4.2$ cp -R /var/lib/pgsql/14/backups/basebackup20190328/* /var/lib/pgsql/14/data/
-bash-4.2$ ls /var/lib/pgsql/14/data/pg_tblspc/ -l
total 0
lrwxrwxrwx. 1 postgres postgres 23 Mar 28 03:21 16558 -> /var/lib/pgsql/tony_tbs
-bash-4.2$ rm -rf /var/lib/pgsql/14/data/pg_wal/*
-bash-4.2$ cp -R /var/lib/pgsql/14/data_old/pg_wal/* /var/lib/pgsql/14/data/pg_wal/
restore_command =
'cp /var/lib/pgsql/wal_archive/%f %p'
#recovery_target_time = "2019-3-28 12:05 GMT"
-bash-4.2$ pg_ctl start
waiting
for server to start....2019-03-28 22:21:26.231 EDT [31505] LOG: listening on IPv4 address
"192.168.56.103", port 5432
2019-03-28 22:21:26.234 EDT [31505] LOG: listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5432"
2019-03-28 22:21:26.238 EDT [31505] LOG: listening on Unix socket
"/tmp/.s.PGSQL.5432"
2019-03-28 22:21:26.252 EDT [31505] LOG: redirecting
log output to logging collector process
2019-03-28 22:21:26.252 EDT [31505] HINT: Future
log output will appear
in directory
"log".
done
server started
testdb=
# select * from t1;
id | v
----+-----
1 | pg
(1 rows)
新闻|Babelfish使PostgreSQL直接兼容SQL Server应用程序
中国PostgreSQL分会入选工信部重点领域人才能力评价机构
更多新闻资讯，行业动态，技术热点，请关注中国PostgreSQL分会官方网站
https://www.postgresqlchina.com
中国PostgreSQL分会生态产品
https://www.pgfans.cn
中国PostgreSQL分会资源下载站
https://www.postgreshub.cn
点击此处阅读原文
↓↓↓
本文分享自微信公众号 - 开源软件联盟PostgreSQL分会（kaiyuanlianmeng）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
