PostgreSQL（七）备份还原

原创

JeesonHuang 2023-01-10 11:22:44 ©著作权

©著作权归作者所有：来自51CTO博客作者JeesonHuang的原创作品，请联系作者获取转载授权，否则将追究法律责任

postgresql 官方给出以下三种方法进行数据备份：

1. SQL dump（SQL 转储）
2. File system level backup（文件系统级别备份）
3. Continuous archiving（连续归档）

【备份】

pg_dump dbname > outfile

pg_dump 是客户端应用，可以备份任何本地或远程ps数据库。若要备份整个数据库，最好拥有数据库最大权限superuser。

当然也可设置选项 -n schema 或 -t table 备份部分数据。使用 pg_dump 备份数据库时，连接到数据库的选项与 psql 一样，可连接到指定的主机和端口（ -h host and -p port）。 pg_dump 当前是主要的备份方法。对于转移数据来说还是用pg_dump，相比其他方法对原服务器的依赖性没那么强。 pg_dump 备份在内部是一致的。dump 出的数据为当时数据库的快照，并不堵塞其他对数据库的操作（除了那些需要排它锁的操作，如 ALTER TABLE）。

示例：

pg_dump testdb > /home/postgres/testdb.dmp
pg_dump -h 127.0.0.1 -Upostgres testdb > /home/postgres/testdb2.dmp

【还原】

psql dbname < infile

这里的 infile 为 pg_dump 的导出文件，此处数据库不会被创建，创建数据库需要从模板库 template0 创建。

createdb -T template0 dbname

在恢复数据库前，那些在转储数据库中拥有对象或权限的用户，必须已经存在，否则还原会失败，因为恢复过程相当于重新执行一遍SQL操作。默认情况下，psql 脚本在执行遇到SQL错误时仍然继续。可设置 ON_ERROR_STOP 中断。

psql --set ON_ERROR_STOP=on dbname < infile

若要保证数据的完整性，还原时可指定 -1 或 --single-transaction 参数。使用该参数，即使遇到一个微小的错误，还原都会回滚。

示例：

dropdb -h 127.0.0.1 -Upostgres testdb
createdb -h 127.0.0.1 -Upostgres  -T template0 testdb
psql -h 127.0.0.1 -Upostgres testdb < /home/postgres/testdb.dmp

--还原为另一个新的数据库 newdb
createdb -h 127.0.0.1 -Upostgres  -T template0 newdb
psql -h 127.0.0.1 -Upostgres newdb < /home/postgres/testdb.dmp

可以使用管道直接复制数据库到其他服务器上：

pg_dump -h host1 dbname | psql -h host2 dbname

示例：

createdb -h 127.0.0.1 -Upostgres  -T template0 testdb2
pg_dump -h 127.0.0.1 -Upostgres testdb | psql -h 127.0.0.1 -Upostgres testdb2

还原好数据库库后，应该刷新统计信息。ANALYZE 收集表内容的统计信息，然后把结果保存在系统表 pg_statistic 里。

ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]

【备份所有：pg_dumpall】

pg_dump 只能备份单个数据库，也没有 dump 相关的角色和表空间信息，因为这些信息是群集级别的，不是数据库级别内的。postgresql 提供了完整信息的备份命令 pg_dumpall。

转储备份：
pg_dumpall > outfile
pg_dumpall -h 127.0.0.1 -Upostgres -f /home/postgres/dumpall.sql

导入备份：
psql -f infile postgres
psql -h 127.0.0.1 -Upostgres -f /home/postgres/dumpall.sql

可以使用选项 --globals-only 备份部分实例级别的数据。

导出角色和表空间创建脚本
pg_dumpall -h 127.0.0.1 -Upostgres -f /home/postgres/globals.sql --globals-only

导出角色创建脚本
pg_dumpall -h 127.0.0.1 -Upostgres -f /home/postgres/roles.sql --roles-only

执行导入：
psql -h 127.0.0.1 -Upostgres -f /home/postgres/globals.sql
psql -h 127.0.0.1 -Upostgres -f /home/postgres/roles.sql

数据量或数据库较大的时候，还是使用 pg_dump 备份还原快一些。同时使用 pg_dumpall 备份角色和表空间脚本。数据量大时使用管道进一步压缩文件。

转储并压缩：pg_dump dbname | gzip > filename.gz
解压并导入：gunzip -c filename.gz | psql dbname
指定块大小：pg_dump dbname | split -b 1m - filename

并行导出：pg_dump -j num -F d -f out.dir dbname
并行导入：pg_restore -j -d dbname filename

并行job参数 "-j" 指定并行度,并行只支持目录格式(format = d)，可指定一个不存在的目录。
示例：
pg_dump -h 127.0.0.1 -Upostgres -j 2 -F d -E UTF8 -f /home/postgres/demodb demodb
pg_restore -h 127.0.0.1 -Upostgres -j 2 -d demodb /home/postgres/demodb/toc.dat

【归档日志】

数据库预写日志 write ahead log (WAL) 记录数据库的变更记录，日志文件存储在目录 /usr/local/pgsql/data/pg_wal 中。

归档设置，主要三个参数：'wal_level','archive_mode','archive_command'

#查看默认配置
SELECT name,setting,vartype,boot_val,min_val,max_val,reset_val
FROM pg_settings WHERE name in('wal_level','archive_mode','archive_command');
-----------------+------------+---------+----------+---------+---------+-----------
      name       |  setting   | vartype | boot_val | min_val | max_val | reset_val 
-----------------+------------+---------+----------+---------+---------+-----------
 archive_command | (disabled) | string  |          |         |         | 
 archive_mode    | off        | enum    | off      |         |         | off
 wal_level       | replica    | enum    | replica  |         |         | replica
-----------------+------------+---------+----------+---------+---------+-----------

数据库默认关闭归档，启用归档，需要编辑参数文件 postgresql.conf。

# vim /usr/local/pgsql/data/postgresql.conf

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /usr/local/pgsql/data/pg_wal/%f && cp %p /home/postgres/%f'

wal_level：日志级别

minimal：bulk 操作则逃过记录，如 CREATE TABLE AS 、CREATE INDEX、CLUSTER、COPY 等

replica(默认)：使用WAL归档和流复制

logical：与replica记录同样的信息，对于表设置 REPLICA IDENTITY FULL时则没有影响。

（在10.0版本以前，可以使用archive 和 hot_standby，在10.0版本使用该参数则映射为 replica）

archive_mode ：on / always 。启用归档时，命令archive_command 才会被执行。两种模式平时无差别，只有在还原或备用模式时才需要用到 always。当 wal_level=minimal 时，archive_mode 不会被激活。

# 其他 wal 配置
SELECT name,setting,vartype,boot_val,min_val,max_val,reset_val
FROM pg_settings WHERE name like '%wal%';


# 手动切换日志，即使用新的文件
select pg_switch_wal();

select pg_ls_logdir();   --错误日志文件
select pg_ls_waldir();  --归档日志文件
select pg_current_logfile();  --当前归档日志文件

# 10.0 以前版本
select pg_switch_xlog();

select * from pg_log;   --错误日志
select * from pg_xlog;  --事务日志
select * from pg_clog;  --事务日志

备份与恢复：http://www.yiibai.com/manual/postgresql/backup.html

Backup and Restore ：https://www.postgresql.org/docs/10/static/backup.html

PostgreSQL 10 版本的变更：https://www.postgresql.org/docs/10/static/release-10.html