PostgreSQL备份恢复(pg_dump)

tigerdog 2024-02-04

2256

PostgreSQL数据库的逻辑备份方式有：pg_dump、pg_dumpall、copy。

1、pg_dump 概述

1.1、pg_dump 介绍
pg_dump是用于PostgreSQL数据库的逻辑备份工具。即使数据库在运行中，它也能创建一致性的备份，备份过程中不阻塞其他用户访问数据库(读取或写入)。

pg_dump只转储单个数据库。要备份一个集簇或者集簇中对于所有数据库公共的全局对象（例如角色和表空间），应使用 pg_dumpall。

可以选择一个数据库或部分表进行备份，恢复过程可以跨平台迁移，可以在数据库正在使用时进行完整一致的备份，并不阻塞其它用户对数据库的访问，只能备份单个数据库，不会导出角色和表空间相关的信息。

1.2、pg_dump四种转储格式

支持四种备份格式：plain、custom、directory、tar

plain是纯文本格式跟pg_dumpall转储一样。可以用psql加载它，如果转储很大，提取部分可能会很复杂。

使用pg_restore程序恢复所有其他格式（custom，directory和tar）。

pg_dump四种转储格式:

-F t tar格式的转储输出格式，不支持压缩，在早期版本有单表8G限制，目前版本已经改善

-F d 以目录的格式创建备份，备份出来的是一个包含若干个目录的备份文件，可以指定并行备份-j

-F c 备份为二进制格式, 压缩存储. 并且可被pg_restore用于精细还原，输出输入IO比较稳定

-F p 备份为文本, 大库不推荐，文本方式，对 IO 不可控，不稳定

转储格式
	plain	cluster	tar	directory
并行转储	×	×	×	√
并行恢复	×	√	×	√
部分恢复	×	√	√	√

2、pg_dump 使用

pg_dump --help

2.1、命令语法

pg_dump [OPTION]... [DBNAME]

2.2、一般选项(General options)

-f, --file=FILENAME 输出文件或目录名

-F, --format=c|d|t|p 输出文件格式 (custom,directory,tar,plain(默认值))

-j, --jobs=NUM 执行多个并行任务进行备份转储工作

-v, --verbose 详细模式

-V, --version 输出版本信息，然后退出

-Z, --compress=0-9 被压缩格式的压缩级别

--lock-wait-timeout=TIMEOUT 在等待表锁超时后操作失败

--no-sync 不需要等待，执行刷盘

-?, --help 显示此帮助, 然后退出

2.3、控制输出内容选项(Options controlling the output content)

-a, --data-only 只转储数据,不包括模式

-b, --blobs 在转储中包括大对象

-B, --no-blobs 在转储中排除大对象

-c, --clean 在重新创建之前，先清除（删除）数据库对象

-C, --create 在转储中包括命令,以便创建数据库

-e, --extension=PATTERN 仅转储指定的扩展

-E, --encoding=ENCODING 转储以ENCODING形式编码的数据

-n, --schema=PATTERN 只转储指定名称的模式

-N, --exclude-schema=PATTERN 不转储指定名称的模式

-O, --no-owner 在明文格式中, 忽略恢复对象所属者

-s, --schema-only 只转储模式, 不包括数据

-S, --superuser=NAME 在明文格式中使用指定的超级用户名

-t, --table=PATTERN 只转储指定名称的表(转储多个表时需使用多个-t选项指定)

-T, --exclude-table=PATTERN 不转储指定名称的表

-x, --no-privileges 不要转储权限 (grant/revoke)

--binary-upgrade 仅供升级实用程序使用

--column-inserts 以带有列名的INSERT命令形式转储数据

--disable-dollar-quoting 取消美元 (符号) 引号, 使用 SQL 标准引号

--disable-triggers 在恢复数据的过程中禁用触发器

--enable-row-security 启用行安全性（只转储用户能够访问的内容）

--exclude-table-data=PATTERN 不转储指定名称的表中的数据

--extra-float-digits=NUM 覆盖 extra_float_digits 的默认设置

--if-exists 当删除对象时使用IF EXISTS

--include-foreign-data=PATTERN 包括外部服务器上的外部数表数据

--inserts 以INSERT命令，而不是COPY命令的形式转储数据

--load-via-partition-root 通过根表加载分区

--no-comments 不转储注释内容

--no-publications 不转储发布对象

--no-security-labels 不转储安全标签分配

--no-subscriptions 不转储订阅对象

--no-synchronized-snapshots 在并行作业中不使用同步快照

--no-tablespaces 不转储表空间对象

--no-unlogged-table-data 不转储未记录的表数据

--on-conflict-do-nothing 添加 ON CONFLICT DO NOTHING 到 INSERT 命令

--quote-all-identifiers 引用所有标识符，即使不是关键字

--rows-per-insert=NROWS 每个插入行包含的INSERT记录数

--section=SECTION 转储命名节（前数据、数据或后数据）

--serializable-deferable 等到转储可以无异常运行

--snapshot=SNAPSHOT 使用给定的快照进行转储

--strict-names 要求表和/或模式包含模式以匹配每个至少一个实体

--use-set-session-authorization 使用 SET SESSION AUTHORIZATION 命令而不是 ALTER OWNER 命令来设置所有权

2.4、连接选项(Connection options)

-d, --dbname=DBNAME 对数据库 DBNAME备份

-h, --host=HOSTNAME 数据库服务器的主机名或套接字目录

-p, --port=PORT 数据库服务器的端口号

-U, --username=NAME 以指定的数据库用户联接

-w, --no-password 永远不提示输入口令

-W, --password 强制口令提示 (自动)

--role=ROLENAME 在转储前运行SET ROLE

3、pg_dump 示例

3.1、默认的文本方式

# 备份数据库

pg_dump -h 127.0.0.1 -p 5432 -U postgres -F p -E UTF8 testdb > /data/pgbackup/testdb.sql

或者

pg_dump -h 127.0.0.1 -p 5432 -U postgres -F p -E UTF8 testdb -f /data/pgbackup/testdb.sql

#恢复数据库(如果备份时没有-C选项，则恢复前需手动创建数据库)

#create database testdb;

psql -h 127.0.0.1 -p 5432 -U postgres testdb < /data/pgbackup/testdb.sql

3.2、二进制备份文件

#二进制格式备份文件，指定类开 -F c

pg_dump -h 127.0.0.1 -p 5432 -U postgres -F c -E UTF8 testdb > /data/pgbackup/testdb.dmp

#解析二进制格式的备份文件, 查看二进制文件的备份内容

pg_restore /data/pgbackup/testdb.dmp -f /data/pgbackup/testdb.log

#注：二进制格式，tar格式，目录格式, 都需要用pg_restore 来进行恢复

pg_restore -d testdb1 /data/pgbackup/testdb.dmp #需要先创建目标库，还原恢复

#选项：-l 生成备份集的toc例表，可以编辑该toc文件，注释其中的表，恢复部份对象

pg_restore -l -f /tmp/a.toc /tmp/testdb.dmp

3.3、备份常用示例

1、备份指定数据库下的某个模式所有的表

#备份

pg_dump -h 127.0.0.1 -p 5432 -U postgres -t 'schema1.t*' testdb1 > testdb1_schema1.sql

#恢复

psql -h 127.0.0.1 -p 5432 -U postgres testdb1 < testdb1_schema1.sql

2、备份单个数据表

#备份

pg_dump -h 127.0.0.1 -p 5432 -U postgres testdb1 -t t1 > testdb1_t1.sql

#恢复

psql -h 127.0.0.1 -p 5432 -U postgres testdb1 < testdb1_t1.sql

3、导出多个表数据

pg_dump -h 127.0.0.1 -p 5432 -U postgres -W db -t t1 -t t2 –-inserts > bak.sql

4、导出整个数据库

pg_dump -h 127.0.0.1 -p 5432 -U postgres -W db –-inserts > bak.sql

5、只导出表结构，不导出数据

pg_dump -h 127.0.0.1 -p 5432 -U postgres -W db -s > bak.sql

6、只导出数据，不导出表结构

pg_dump -h 127.0.0.1 -p 5432 -U postgres -W db –-inserts -a > bak.sql

4、pg_dump 扩展

4.1、生成toc文件进行选择性恢复

1）使用二进制备份数据库testdb2(包含t1和t2两张表)

pg_dump -h 127.0.0.1 -p 5432 -U postgres -F c -E UTF8 testdb2 > /data/pgbackup/testdb2.dmp

2）根据二进制备份文件生成toc文件

pg_restore /data/pgbackup/testdb2.dmp -l -f /data/pgbackup/testdb2.toc

3）修改 toc文件，以首行加分号“;”的方式注释掉不用还原的内容

vi /data/pgbackup/testdb2.toc #注释掉t2表相关信息

4）以toc文件列表做恢复

pg_restore -Fc -L /data/pgbackup/testdb2.toc /data/pgbackup/testdb2.dmp -d testdb2

4.2、使用unix管道备份恢复

1、压缩与解压

pg_dump testdb1 | gzip > /data/pgbackup/testdb1.sql.gz #操作系统压缩

gunzip -c /data/pgbackup/testdb1.sql.gz | psql testdb1 #解压缩恢复

2、备份恢复数据不落盘

pg_dump testdb1 | psql testdb2 #备份testdb1的同时还原到testdb2,testdb2库要先创建, 数据不落盘的备份恢复方式，一般适用于小数据进行测试的场景。

3、分割备份文件

备份： pg_dump dbname | split -b1m- filename

恢复： cat filename* | psql dbname

4.3、并行处理

pg_dump -Fd -j4 -f /data/pgbackup/testdb1.dirbak testdb1 #-F d 以目录的格式创建备份

pg_restore -d testdb3 -j4 /data/pgbackup/db.dirbak

注：-j 参数指定同时几个进程来同时执行，每个进程同时只处理一个表的数据。

5、pg_dumpall

相对于pg_dump只能备份单个库，pg_dumpall可以备份整个PostgreSQL实例中所有的数据，包括角色和表空间定义。

pg_dumpall需要多次连接到PostgreSQL服务器（每个数据库一次）。如果你使用口令认证，可能每次都会要求口令。这种情况下使用一个~/.pgpass会比较方便。

示例如下：

#备份

pg_dumpall -h 127.0.0.1 -U postgres -p 5432 -W –-inserts > dumpall.sql

#恢复

psql -h 127.0.0.1 -p 5432 -U postgres < dumpall.sql

6、copy

对于小表的备份恢复还可以使用copy命令实现。

copy命令在平时日常维护中使用较为广泛，一方面是数据CSV的导出，另一方面是单表数据（特别是数据量不大时）的转移或者导出，都有很多的应用。

copy与\copy 差异：
--权限：
    copy需要superuser或pg_execute_server_program角色的用户；
    \copy一般用户即可，只要对表有查询权限
--位置
    copy是去服务器端寻找或者导出
    \copy 是在客户端进行寻找或者导出