合 pg_restore导入数据报警告 pg_restore: WARNING: column "foobar" has type "unknown" DETAIL: Proceeding with relation creation anyway.
Tags: PG故障处理GreenPlumPostgreSQLpg_restore
逻辑备份恢复总结
pg_dump支持指定所要备份的对象
:可以单独备份表、schema或者database;pg_dumpall仅支持导出全库数据。pg_dump可以将数据备份为SQL文本文件格式,也支持备份为用户自定义的压缩格式或者TAR包格式。
在恢复数据时,对压缩格式和TAR包格式的备份文件可以实现并行恢复,该特性是从8.4版开始支持的。
pg_dumpall仅可以将当前PostgreSQL服务实例中所有database的数据导出为SQL文本(pg_dumpall不支持导出SQL文本以外的其他格式),也可以同时导出表空间和角色的全局对象。 在pg12中,pg_dumpall新增了--exclude-database选项可以排除不想导出的数据库。
PostgreSQL支持以下两种数据恢复方法:
1、使用psql恢复pg_dump或pg_dumpall工具生成的SQL文本格式的数据备份。
2、使用pg_restore工具来恢复由pg_dump工具生成的自定义压缩格式、TAR包格式或者目录格式备份。
生成测试数据:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | postgres=# create database sbtest owner lhr; CREATE DATABASE sysbench /usr/share/sysbench/oltp_common.lua --db-driver=pgsql --pgsql-host=192.168.66.35 --pgsql-port=15433 \ --pgsql-user=postgres --pgsql-password=lhr --pgsql-db=sbtest \ --time=300 --table-size=1000 --tables=10 --threads=16 \ --events=999999999 prepare postgres=# \l+ sbtest List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description --------+-------+----------+------------+------------+-------------------+---------+------------+------------- sbtest | lhr | UTF8 | en_US.utf8 | en_US.utf8 | | 9389 kB | pg_default | (1 row) |
pg_dump:客户端工具
导出sbtest数据库:
1 2 3 4 5 6 7 8 9 10 11 | -- 备份 pg_dump sbtest > /bk/sbtest.sql pg_dump sbtest --file=/bk/sbtest.sql pg_dump --username=postgres --host=192.168.66.35 --port=15433 --dbname=sbtest --format=plain --file=/bk/sbtest.sql -- 恢复 drop database sbtest; create database sbtest; psql --username=postgres --host=192.168.66.35 --port=15433 --dbname=sbtest --file=/bk/sbtest.sql |
导出大文件(并行+压缩)
在不指定文件格式时,默认导出的文件为SQL文本文件,不会压缩,生成的文件较大,但导出较快。
若数据库较大,可通过 -Fc
或--format=custom
指定为custom文件,会默认进行5级压缩( --compress=5
),生成的文件较小,可以按照缩8倍进行计算,但导出稍慢。在还原二进制文件,需要用 pg_restore 还原。
注意:
1、虽然是custom格式,但是,仍然是文本格式的,可以用vi或txt打开查看。
2、custom格式的文件本身就是压缩文件,若再进行OS基本的压缩(
tar -zcvf aa.tar.gz lhrdb.dmp
)作用不大,文件不会变化太大。3、某生产环境备份(350GB,备份后41G,用时2个半小时,平均每小时130GB,每秒15MB);
某生产环境备份(350GB,备份后41G,开32个并行,用时15分钟,每秒50MB)
4、导出和导入的库名可以不一样
5、串行备份,也可以并行恢复
1 2 3 4 5 6 7 8 9 10 11 12 | -- 串行备份并压缩 pg_dump --username=postgres --host=192.168.66.35 --port=15433 --dbname=sbtest --format=custom --file=/bk/sbtest.dmp -- 恢复(导出不是并行,但导入依然可以并行) drop database sbtest; create database sbtest; pg_restore --username=postgres --host=192.168.66.35 --port=15433 --dbname=sbtest /bk/sbtest.dmp -j 32 -- 并行导出导入(推荐,只打包不压缩:tar -cvf sbtest.tar /bk/ --remove-files 解压: tar -xvf bb.tar ) pg_dump --dbname=sbtest --format=directory --jobs=16 --file=/bk/ pg_restore -d db4 /bk/ -j 32 |
pg_dumpall:客户端工具
1 2 3 | pg_dumpall > /bk/pg_all.sql pg_dumpall --username=postgres --host=192.168.66.35 --port=15433 --file=/bk/pg_all.sql |
👉 注意:
1、若是远程导出,则需要多次输入密码,若不想多次输入密码,则可以配置环境变量PGPASSWORD
1 export PGPASSWORD=lhr
如下三种写法均可以将testdb和testdc排除:
1 2 3 | pg_dumpall --exclude-database='testd[bc]' -f alldump.sql pg_dumpall --exclude-database='testdb' --exclude-database='testdc' -f alldump_3.sql pg_dumpall --exclude-database=testdb --exclude-database=testdc -f alldump_4.sql |
特别注意:两个--exclude-database选项之间不能用逗号分隔!!!
pg_dump
pg_dump — 把PostgreSQL数据库抽取为一个脚本文件或其他归档文件
大纲
pg_dump
[connection-option
...] [option
...] [dbname
]
描述
pg_dump是用于备份一种PostgreSQL数据库的工具。即使数据库正在被并发使用,它也能创建一致的备份。pg_dump不阻塞其他用户访问数据库(读取或写入)。
pg_dump只转储单个数据库。要备份一个集簇或者集簇中 对于所有数据库公共的全局对象(例如角色和表空间),应使用 pg_dumpall。
转储可以被输出到脚本或归档文件格式。脚本转储是包含 SQL 命令的纯文本文件,它们可以用来重构数据库到它被转储时的状态。要从这样一个脚本恢复,将它喂给psql。脚本文件甚至可以被用来在其他机器和其他架构上重构数据库。在经过一些修改后,甚至可以在其他 SQL 数据库产品上重构数据库。
另一种可选的归档文件格式必须与pg_restore配合使用来重建数据库。它们允许pg_restore能选择恢复什么,或者甚至在恢复之前对条目重排序。归档文件格式被设计为在架构之间可移植。
当使用归档文件格式之一并与pg_restore组合时,pg_dump提供了一种灵活的归档和传输机制。pg_dump可以被用来备份整个数据库,然后pg_restore可以被用来检查归档并/或选择数据库的哪些部分要被恢复。最灵活的输出文件格式是“自定义”格式(-Fc
)和“目录”格式(-Fd
)。它们允许选择和重排序所有已归档项、支持并行恢复并且默认是压缩的。“目录”格式是唯一一种支持并行转储的格式。
当运行pg_dump时,我们应该检查输出中有没有任何警告(打印在标准错误上),特别是考虑到下面列出的限制。
选项
下列命令选项控制输出的内容和格式。
dbname
指定要被转储的数据库名。如果没有指定,将使用环境变量
PGDATABASE
。如果环境变量也没有设置,则使用指定给该连接的用户名。-a
--data-only
只转储数据,而不转储模式(数据定义)。表数据、大对象和序列值都会被转储。这个选项类似于指定
--section=data
,但是由于历史原因又不完全相同。-b
--blobs
在转储中包括大对象。这是当
--schema
、--table
或--schema-only
被指定时的默认行为。因此,只有在请求转储一个特定方案或者表的情况中,-b
开关才对向转储中加入大对象有用。注意blobs是被考虑的数据,因此在使用--data-only
时将被包括在内,但在使用--schema-only
时则不会包括。-B
--no-blobs
在转储中排除大对象。当同时给定
-b
和-B
时,行为是在数据被转储时输出大对象,请参考-b
文档。-c
--clean
在输出创建数据库对象的命令之前输出清除(删除)它们的命令 (除非也指定了
--if-exists
,如果任何对象不存在于 目的数据库中,恢复可能会产生一些伤害性的错误消息)。这个选项只对纯文本格式有意义。对于归档格式,你可以在调用pg_restore
时指定该选项。-C
--create
使得在输出的开始是一个创建数据库本身并且重新连接到被创建的数据库的命令(通过这种形式的一个脚本,在运行脚本之前你连接的是目标安装中的哪个数据库都没有关系)。如果也指定了
--clean
,脚本会在重新连接到目标数据库之前先删除它然后再重建。通过--create
,输出还会包括数据库的注释(如果有)以及与这个数据库相关的任何配置变量设置,也就是任何提到了这个数据库的ALTER DATABASE ... SET ...
命令和ALTER ROLE ... IN DATABASE ... SET ...
命令。该数据库本身的访问特权也会被转储,除非指定有--no-acl
。这个选项只对纯文本格式有意义。对于归档格式,你可以在你调用pg_restore
时指定这个选项。-E *
encoding*
--encoding=*
encoding*
以指定的字符集编码创建转储。在默认情况下,该转储会以该数据库的编码创建(另一种得到相同结果的方式是将
PGCLIENTENCODING
环境变量设置成想要的转储编码)。-f *
file*
--file=*
file*
将输出发送到指定文件。对于基于输出格式的文件这个参数可以被忽略,在那种情况下将使用标准输出。不过对于目录输出格式必须给定这个参数,在目录输出格式中指定的是一个目录而不是一个文件。在这种情况中,该目录会由
pg_dump
创建并且不需要以前就存在。-F *
format*
--format=*
format*
选择输出的格式。
format
可以是下列之一:p
plain
输出一个纯文本形式的SQL脚本文件(默认值)。c
custom
输出一个适合于作为pg_restore输入的自定义格式归档。和目录输出格式一起,这是最灵活的输出格式,它允许在恢复时手动选择和排序已归档的项。这种格式在默认情况还会被压缩。d
directory
输出一个适合作为pg_restore输入的目录格式归档。这将创建一个目录,其中每个被转储的表和大对象都有一个文件,外加一个所谓的目录文件,该文件以一种pg_restore能读取的机器可读格式描述被转储的对象。一个目录格式归档能用标准 Unix 工具操纵,例如一个未压缩归档中的文件可以使用gzip工具压缩。这种格式默认情况下是被压缩的并且也支持并行转储。t
tar
输出一个适合于输入到pg_restore中的tar
-格式归档。tar 格式可以兼容目录格式,抽取一个 tar 格式的归档会产生一个合法的目录格式归档。不过,tar 格式不支持压缩。还有,在使用 tar 格式时,表数据项的相对顺序不能在恢复过程中被更改。-j *
njobs*
--jobs=*
njobs*
通过同时归档
njobs
个表来运行并行转储。这个选项可能会减少执行转储所需的时间,但也会增加数据库服务器上的负载。你只能和目录输出格式一起使用这个选项,因为这是唯一一种让多个进程能在同一时间写其数据的输出格式。pg_dump将打开njobs
+ 1 个到该数据库的连接,因此确保你的max_connections设置足够高以容纳所有的连接。在运行一次并行转储时请求数据库对象上的排他锁可能导致转储失败。其原因是,pg_dump主控进程会在工作者进程将要稍后转储的对象上请求共享锁,以便确保在转储运行时不会有人删除它们并让它们出错。如果另一个客户端接着请求一个表上的排他锁,那个锁将不会被授予但是会被排入队列等待主控进程的共享锁被释放。因此,任何其他对该表的访问将不会被授予或者将排在排他锁请求之后。这包括尝试转储该表的工作者进程。如果没有任何防范措施,这可能会是一种经典的死锁情况。要检测这种冲突,pg_dump工作者进程使用NOWAIT
选项请求另一个共享锁。 如果该工作者进程没有被授予这个共享锁,其他某人必定已经在同时请求了一个排他锁并且没有办法继续转储,因此pg_dump除了中止转储之外别无选择。对于一个一致的备份,数据库服务器需要支持同步的快照,在PostgreSQL 9.2的主服务器和10的后备服务器中引入了一种特性。有了这种特性,即便数据库客户端使用不同的连接,也可以保证他们看到相同的数据集。pg_dump -j
使用多个数据库连接,它用主控进程连接到数据一次,并且为每一个工作者任务再一次连接数据库。如果没有同步快照特征,在每一个连接中不同的工作者任务将不能被保证看到相同的数据,这可能导致一个不一致的备份。如果你希望运行一个 9.2 之前服务器的并行转储,你需要确保数据库内容从主控进程连接到数据库一直到最后一个工作者任务连接到数据库之间不会改变。做这些最简单的方法是在开始备份之前停止任何访问数据库的数据修改进程(DDL 以及 DML)。当对一个 9.2 之前的PostgreSQL服务器运行pg_dump -j
时,你还需要指定--no-synchronized-snapshots
参数。-n *
pattern*
--schema=*
pattern*
只转储匹配
pattern
的模式,这会选择模式本身以及它所包含的所有对象。当没有指定这个选项时,目标数据库中所有非系统模式都将被转储。多个模式可以通过书写多个-n
开关来选择。另外,pattern
参数可以被解释为一种根据psql's\d
命令所用的相同规则(请参见下面的Patterns)编写的模式,这样多个模式也可以通过在该模式中书写通配字符来选择。在使用通配符时,如果需要阻止 shell 展开通配符需要小心引用该模式,请参见下面的Examples。注意当-n
被指定时,pg_dump不会尝试转储所选模式可能依赖的任何其他数据库对象。因此,无法保证一次指定模式转储的结果能够仅凭其本身被成功地恢复到一个干净的数据库中。注意当-n
被指定时,非模式对象(如二进制大对象)不会被转储。你可以使用--blobs
开关将二进制大对象加回到该转储中。-N *
pattern*
--exclude-schema=*
pattern*
不转储匹配
pattern
模式的任何模式。该模式被根据-n
所用的相同规则被解释。-N
可以被给定多次来排除匹配几个模式中任意一个的模式。当-n
和-N
都被给定时,该行为是只转储匹配至少一个-n
开关但是不匹配-N
开关的模式。如果只有-N
而没有-n
,那么匹配-N
的模式会被从一个正常转储中排除。-O
--no-owner
不输出设置对象拥有关系来匹配原始数据库的命令。默认情况下,pg_dump会发出
ALTER OWNER
或SET SESSION AUTHORIZATION
语句来设置被创建的数据库对象的拥有关系。除非该脚本被一个超级用户(或是拥有脚本中所有对象的同一个用户)启动,这些语句都将会失败。要使一个脚本能够被任意用户恢复,但把所有对象的拥有关系都给这个用户,可指定-O
。这个选项只对纯文本格式有意义。对于归档格式,你可以在调用pg_restore
时指定该选项。-R
--no-reconnect
这个选项已经废弃,但是为了向后兼容仍然能被接受。
-s
--schema-only
只转储对象定义(模式),而非数据。这个选项是
--data-only
的逆选项。它和指定--section=pre-data --section=post-data
相似,但是由于历史原因又不完全相同。(不要把这个选项和--schema
选项混淆,后者在“schema”的使用上有不同的含义)。要为数据库中表的一个子集排除表数据,见--exclude-table-data
。-S *
username*
--superuser=*
username*
指定要在禁用触发器时使用的超级用户的用户名。只有使用
--disable-triggers
时,这个选项才相关(通常,最好省去这个选项,而作为超级用户来启动结果脚本来取而代之)。-t *
pattern*
--table=*
pattern*
只转储名字匹配
pattern
的表。通过写多个-t
开关可以选择多个表。另外,pattern
参数可以被解释为一种根据psql's\d
命令所用的相同规则(请参见下面的Patterns)编写的模式,这样多个表也可以通过在该模式中书写通配字符来选择。在使用通配符时,如果需要阻止 shell 展开通配符需要小心引用该模式,请参见下面的Examples。当-t
被使用时,-n
和-N
开关不会有效果,因为被-t
选择的表将被转储而无视那些开关,并且非表对象将不会被转储。注意当-t
被指定时,pg_dump不会尝试转储所选表可能依赖的任何其他数据库对象。因此,无法保证一次指定表转储的结果能够仅凭其本身被成功地恢复到一个干净的数据库中。注意-t
开关的行为不完全向前兼容 8.2 之前的PostgreSQL版本。以前,写-t tab
将转储所有命名为tab
的表,但现在它仅仅转储在你默认搜索路径中可见的那一个。要得到旧的行为,你可以写成-t '*.tab'
。还有,你必须写类似-t sch.tab
的东西来选择一个特定模式中的一个表,而不是用老的惯用语-n sch -t tab
。-T *
pattern*
--exclude-table=*
pattern*
不转储匹配
pattern
模式的任何表。该模式被根据-t
所用的相同规则被解释。-T
可以被给定多次来排除匹配几个模式中任意一个的模式。当-t
和-T
都被给定时,该行为是只转储匹配至少一个-t
开关但是不匹配-T
开关的表。如果只有-T
而没有-t
,那么匹配-T
的表会被从一个正常转储中排除。-v
--verbose
指定冗长模式。这将导致pg_dump向标准错误输出详细的对象注释以及转储文件的开始/停止时间,还有进度消息。
-V
--version
pg_dump版本并退出。
-x
--no-privileges
--no-acl
防止转储访问特权(授予/收回命令)。
-Z *
0..9*
--compress=*
0..9*
指定要使用的压缩级别。零意味着不压缩。对于自定义归档格式,这会指定个体表数据段的压缩,并且默认是进行中等级别的压缩。对于纯文本输出,设置一个非零压缩级别会导致整个输出文件被压缩,就好像它被gzip处理过一样,但是默认是不压缩。tar 归档格式当前完全不支持压缩。
--binary-upgrade
这个选项用于就地升级功能。我们不推荐也不支持把它用于其他目的。这个选项在未来的发行中可能被改变而不做通知。
--column-inserts
--attribute-inserts
将数据转储为带有显式列名的
INSERT
命令(INSERT INTO *
table* (*
column*, ...) VALUES ...
)。这将使得恢复过程非常慢,这主要用于使转储能够被载入到非PostgreSQL数据库中。重新加载期间的任何错误都将导致有问题的INSERT
相关的行将丢失,而不是整个表内容。--disable-dollar-quoting
这个选项禁止在函数体中使用美元符号引用,并且强制它们使用 SQL 标准字符串语法被引用。
--disable-triggers
只有在创建一个只转储数据的转储时,这个选项才相关。它指示pg_dump包括在数据被重新载入时能够临时禁用目标表上的触发器的命令。如果你在表上有引用完整性检查或其他触发器,并且你在数据重新载入期间不想调用它们,请使用这个选项。当前,为
--disable-triggers
发出的命令必须作为超级用户来执行。因此,你还应当使用-S
指定一个超级用户名,或者宁可作为一个超级用户启动结果脚本。这个选项只对纯文本格式有意义。对于归档格式,你可以在调用pg_restore
时指定这个选项。--enable-row-security
只有在转储具有行安全性的表的内容时,这个选项才相关。默认情况下, pg_dump将把 row_security设置为 off 来确保从该表中转储 出所有的数据。如果用户不具有足够能绕过行安全性的特权,那么会抛出 一个错误这个参数指示pg_dump将 row_security设置为 on,允许用户只转储该表中 它们能够访问到的部分内容。注意如果当前你使用了这个选项,你可能还想得到
INSERT
格式的转储,因为恢复期间的COPY FROM
不支持行安全性。--exclude-table-data=*
pattern*
不转储匹配
pattern
模式的任何表中的数据。该模式根据-t
的相同规则被解释。--exclude-table-data
可以被给定多次来排除匹配多个模式的表。当你需要一个特定表的定义但不想要其中的数据时,这个选项就有用了。要排除数据库中所有表的数据,见--schema-only
。--extra-float-digits=*
ndigits*
在转储浮点数据时使用规定的
extra_float_digits
值,而不是最大可用精度。以备份目的生成的常规转储不使用此选项。--if-exists
时间条件性命令(即增加一个
IF EXISTS
子句)来清除数据库和其他对象。 只有同时指定了--clean
时,这个选项才可用。--include-foreign-data=*
foreignserver*
使用与
foreignserver
模式匹配的外部服务器转储任何外部表的数据。可以通过编写 多个--include-foreign-data
开关来选择多个外部服务器。 同样,根据psql's\d
命令使用的相同规则, 将foreignserver
参数解释为模式。 (请参见下面的Patterns),因此也可以通过在模式中写入通配符来选择多个外部服务器。使用通配符时, 如果需要,请小心引用该模式,以防止Shell扩展通配符。请参见下面的Examples。 唯一的例外是不允许使用空模式。注意指定--include-foreign-data
时,pg_dump不会检查外部表是否可写。因此,不能保证可以成功还原外部表转储的结果。--inserts
将数据转储为
INSERT
命令(而不是COPY
)。这将使得恢复非常慢,这主要用于使转储能够被载入到非PostgreSQL数据库中。重新加载期间的任何错误都将导致有问题的INSERT
相关的行将丢失,而不是整个表内容。注意如果你已经重新安排了列序,该恢复可能会一起失败。--column-inserts
选项对于列序改变是安全的,但是会更慢。--load-via-partition-root
在为一个分区表转储数据时,让
COPY
语句或者INSERT
语句把包含它的分区层次的根而不是分区自身作为目标。这导致在数据被装载时,会为每一个行重新确定合适的分区。如果在一台服务器上重新装载数据时会出现行并不是总是落入到和原始服务器上相同的分区中的情况,这个选项就很有用。例如,如果分区列是文本类型并且两个系统中用于排序分区列的排序规则有着不同的定义,就会发生这种情况。在从用这个选项制作的归档恢复时,最好不要使用并行,因为pg_restore将不能准确地知道一个给定的归档数据项将把数据装载到哪个分区中。这会导致效率不高,因为在并行任务见会有锁冲突,或者甚至可能由于在所有的相关数据被装载前建立了外键约束而导致重新装载失败。--lock-wait-timeout=*
timeout*
在转储的开始从不等待共享表锁的获得。而是在指定的
timeout
内不能锁定一个表时失败。超时时长可以用SET statement_timeout
接受的任何格式指定(允许的值根据你从其转出的服务器版本变化,但是从 7.3 以来的所有版本都接受一个整数表示的毫秒数。如果从 7.3 以前的服务器转出,这个选项会被忽略。)。--no-comments
不转储注释。
--no-publications
不转储publication。
--no-security-labels
不转储安全标签。
--no-subscriptions
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!