postgres 数据备份和还原

左手的ㄟ右手 2022-12-17 07:14 263阅读 0赞

[PgSql备份pg\_dump与还原手记pg\_restore][PgSql_pg_dump_pg_restore]

真没有想到，以前一直是PostgreSQL使用者，突然需要库移植又成了头一招了！原来它与mysql命令行操作区别还挺大。  
不用怕，但绝对要细心，因为数据库操作是网站的核心，一旦出现损坏或丢失，后果就非常严重了。  
我先写了步骤，然后按计划进行，虽然也出现了错误，但最终还是安全移植了。这里记录在案，以备后用。  
备份还原方法：**pg\_dump和pg\_restore**，先仔细说明这两个命令，再记录我的操作方法。

**pg\_dump --  将一个PostgreSQL数据库抽出到一个脚本文件或者其它归档文件中  
pg\_dump \[option...\] \[dbname\]**  
**选项option...**  
下面的命令行参数控制输出的内容和格式。  
  
dbname  
声明将要转储的数据库名。 如果没有声明这个参数，那么使用环境变量 PGDATABASE。 如果那个环境变量也没声明，那么用发起连接的用户名。  
  
\-a  
\--data-only  
只输出数据，不输出模式（数据定义）。  
  
这个选项只是对纯文本格式有意义。对于归档格式，你可以在调用 pg\_restore 的时候声明选项。  
  
\-b  
\--blobs  
在转储中包含大对象。必须选择一种非文本输出格式。  
  
\-c  
\--clean  
输出在创建数据库创建命令之前先清理（删除）该数据库对象的命令。  
  
这个选项只是对纯文本格式有意义。对于归档格式，你可以在调用 pg\_restore 的时候声明选项。  
  
\-C  
\--create  
以一条创建该数据库本身并且与这个数据库联接等命令开头进行输出。 （如果是这种形式的脚本，那么你在运行脚本之前和哪个数据库联接就不重要了。）  
  
这个选项只对纯文本格式有意义。对于归档格式，你可以在调用 pg\_restore 的时候声明该选项。  
  
\-d  
\--inserts  
将数据输出为的INSERT命令（而不是 COPY）。 这样会导致恢复非常缓慢。 这个选项主要用于制作那种可以用于其它非 PostgreSQL 数据库的转储。 请注意，如果你重新排列了字段顺序，那么恢复可能会完全失败。 -D 更安全，但是也更慢。  
  
\-D  
\--column-inserts  
\--attribute-inserts  
把数据转储为带有明确字段名的 INSERT 命令。 （INSERT INTO table(column, ...) VALUES ...）。 这样会导致恢复非常缓慢，它主要用于制作那种可以用于其它非 PostgreSQL 数据库的转储。  
  
\-f file  
\--file=file  
把输出发往指定的文件。如果忽略这些，则使用标准输出。  
  
\-F format  
\--format=format  
选择输出的格式。format可以是下列之一：  
  
p  
输出纯文本SQL脚本文件（缺省）  
  
t  
输出适合输入到 pg\_restore 里的tar归档文件。 使用这个归档允许在恢复数据库时重新排序和/或把数据库对象排除在外。 同时也可能可以在恢复的时候限制对哪些数据进行恢复。  
  
c  
输出适于给 pg\_restore 用的客户化归档。 这是最灵活的格式，它允许对装载的数据和对象定义进行重新排列。 这个格式缺省的时候是压缩的。  
  
\-i  
\--ignore-version  
忽略在 pg\_dump 和数据库服务器之间的版本差别。  
  
pg\_dump 可以处理来自以前版本的PostgreSQL 的数据库，但是太老的版本则不被支持了（目前是支持到 7.0）。 如果你需要跨越版本检查时才使用这个选项（ 而且如 pg\_dump 失效，别说我没警告你）。  
  
\-n namespace  
\--schema=schema  
只转储 schema 的内容。 如果没有声明这个选项，所有目标数据库中的非系统模式都会被转储出来。  
  
注意: 在这个模式里，pg\_dump 并不试图转储任何其它选定模式可能依赖的数据库对象。 因此，系统不保证单一的一个模式的转储就可以成功地恢复到一个干净的数据库中去。  
  
\-o  
\--oids  
作为数据的一部分，为每个表都输出对象标识（OID）。 如果你的应用在某种程度上引用了OID字段的话，（比如，在外键约束中用到）。 那么使用这个选项。否则，不应该使用这个选项。  
  
\-O  
\--no-owner  
不 把对象的所有权设置为对应源数据库。 通常， pg\_dump 发出（psql特有的） ALTER OWNER 或者 SET SESSION AUTHORIZATION 语句以设置创建的数据库对象的所有权。 又见 -R 和 -X use-set-session-authorization 选项。 请注意 -O 并不防止所有对数据库的重新联接， 只是防止那些为调整权限进行的排它联接。  
  
这个选项只是对纯文本格式有意义。对于归档格式，在你调用 pg\_restore 的时候你可以声明该选项。  
  
\-R  
\--no-reconnect  
这个选项已经过时，但是出于向下兼容的考虑，仍然接受这个选项。  
  
\-s  
\--schema-only  
只输出对象定义（模式），不输出数据。  
  
\-S username  
\--superuser=username  
声明关闭触发器时需要用到的超级用户名。 它只有使用了 --disable-triggers 的时候才有关系。 （通常，我们最好不要输入这个参数，而是用超级用户启动生成的脚本。）  
  
\-t table  
\--table=table  
只输出表 table的数据。 很可能是在不同模式里面有多个同名表；如果这样，那么所有匹配的表都将被转储出来。 同时声明 --schema 和 --table 则只选择一个表。  
  
注意: 在这个模式里，pg\_dump 并不试图转储任何其它选定表可能依赖的数据库对象。 因此，系统不保证单一的一个表的转储就可以成功地恢复到一个干净的数据库中去。  
  
\-v  
\--verbose  
声明冗余模式。 这样将令 pg\_dump 输出详细的对象评注以及转储文件的启停时间和进度信息到标准输出上。  
  
\-x  
\--no-privileges  
\--no-acl  
避免输出 ACL（赋予/撤消 命令）和表的所有者关系信息。  
  
\-X disable-dollar-quoting  
\--disable-dollar-quoting  
这个选项关闭使用美元符包围函数体。强制它们用 SQL 标准的字串语法的引号包围。  
  
\-X disable-triggers  
\--disable-triggers  
这个选项只是和创建仅有数据的转储相关。它告诉 pg\_dump 包含在恢复数据时，临时关闭目标表上面的触发器的命令。 如果你在表上有参考完整性检查或者其它触发器，而恢复数据的时候你不想重载他们，那么你就应该使用这个选项。  
  
目前，为 --disable-triggers 发出的命令必须用超级用户来做。 因此，你应该同时用 -S 声明一个超级用户名，或者最好是用一个超级用户的身份来启动这个生成的脚本。  
  
这个选项只对纯文本格式有意义。对于归档格式，你可以在调用 pg\_restore 的时候声明这个选项。  
  
\-X use-set-session-authorization  
\--use-set-session-authorization  
输出 SQL 标准 SET SESSION AUTHORIZATION 命令而不是 OWNER TO 命令。 这样的转储结果更加复合标准，但是依赖转储中的对象的历史，可能不能正确恢复。  
  
\-Z 0..9  
\--compress=0..9  
声明在那些支持压缩的格式中使用的压缩级别。 （目前只有客户化格式支持压缩）。  
  
下面的命令行参数控制数据库为联接参数。  
\-h host  
\--host=host  
声明运行服务器的机器的主机名。 如果数值以斜杠开头，则它被用做到 Unix 域套接字的路径。 缺省是从 PGHOST 环境变量中取得的，如果设置了这个环境变量的话，否则，尝试一个 Unix 域套接字连接。  
  
\-p port  
\--port=port  
声明服务器正在侦听并等待联接的 TCP 端口或本地 Unix 主控套接字文件句柄。 缺省时使用环境变量 PGPORT 的值（如果存在），或者是编译时的缺省值。  
  
\-U username  
以给出用户身分联接。  
  
\-W  
强制口令提示。如果服务器需要口令认证，那么这个动作应该自动发生。  
  
pg\_restore --  从一个由 pg\_dump 创建的备份文件中恢复 PostgreSQL 数据库。  
pg\_restore 接受下列命令行参数。  
  
filename  
声明要恢复的备份文件的位置。如果没有声明，则使用标准输入。  
  
\-a  
\--data-only  
只恢复数据，而不恢复表模式（数据定义）。  
  
\-c  
\--clean  
创建数据库对象前先清理（删除）它们。  
  
\-C  
\--create  
在恢复数据库之前先创建它。（如果出现了这个选项，和 -d 在一起的数据库名只是用于发出最初的CREATE DATABASE命令。 所有数据都恢复到名字出现在归档中的数据库中去。）  
  
\-d dbname  
\--dbname=dbname  
与数据库 dbname 联接并且直接恢复到该数据库中。  
  
\-e  
\--exit-on-error  
如果在向数据库发送 SQL 命令的时候碰到错误，则退出。 缺省是继续执行并且在恢复结束时显示一个错误计数。  
  
\-f filename  
\--file=filename  
声明生成的脚本的输出文件，或者出现-l 选项时用于列表的文件，缺省是标准输出。  
  
\-F format  
\--format=format  
声明备份文件的格式。因为pg\_restore 会自动判断格式，所以如果声明了，它可以是下面之一：  
  
t  
备份是一个 tar 归档。 使用这个格式允许在恢复数据库的时候重新排序和/或把表模式元素排除出去。 同时还可能在恢复的时候限制装载的数据。  
  
c  
备份的格式是来自pg\_dump的客户化格式。 这是最灵活的格式，因为它允许重新对数据排序，也允许重载表模式元素。 缺省时这个格式是压缩的。  
  
  
\-i  
\--ignore-version  
忽略数据库版本检查。  
  
\-I index  
\--index=index  
只恢复命名的索引。  
  
\-l  
\--list  
列出备份的内容。这个操作的输出可以用 -L 选项限制和重排所恢复的项目。  
  
\-L list-file  
\--use-list=list-file  
只恢复在 list-file 里面的元素，以它们在文件中出现的顺序。 你可以移动各个行并且也可以通过在行开头放 ';' 的方式注释。（见下文获取例子。）  
  
\-O  
\--no-owner  
不 要输出设置对象的权限，以便与最初的数据库匹配的命令。 缺省时，pg\_restore 发出 ALTER OWNER 或 SET SESSION AUTHORIZATION 语句设置创建出来的模式元素的所有者权限。 如果最初的数据库连接不是由超级用户（或者是拥有所有创建出来的对象的同一个用户）发起的，那么这些语句将失败。 使用 -O，那么任何用户都可以用于初始的连接，并且这个用户将拥有所有创建出来的对象。  
  
\-P function-name(argtype \[, ...\])  
\--function=function-name(argtype \[, ...\])  
只恢复指定的命名函数。请注意仔细拼写函数名及其参数，应该和转储的内容列表中的完全一样。  
  
\-R  
\--no-reconnect  
这个选项已经废弃了，但是为了保持向下兼容仍然接受。  
  
\-s  
\--schema-only  
只恢复表结构（数据定义）。不恢复数据，序列值将重置。  
  
\-S username  
\--superuser=username  
设置关闭触发器时声明超级用户的用户名。 只有在设置了 --disable-triggers 的时候才有用。  
  
\-t table  
\--table=table  
只恢复表指定的表的定义和/或数据。  
  
\-T trigger  
\--trigger=trigger  
只恢复指定的触发器。  
  
\-v  
\--verbose  
声明冗余模式。  
  
\-x  
\--no-privileges  
\--no-acl  
避免 ACL 的恢复（grant/revoke 命令）。  
  
\-X use-set-session-authorization  
\--use-set-session-authorization  
输出 SQL 标准的 SET SESSION AUTHORIZATION 命令，而不是 OWNER TO 命令。 这样令转储与标准兼容的更好，但是根据转储中对象的历史，这个转储可能不能恰当地恢复。  
  
\-X disable-triggers  
\--disable-triggers  
这个选项只有在执行仅恢复数据的时候才相关。它告诉 pg\_restore 在装载数据的时候执行一些命令临时关闭在目标表上的触发器。 如果你在表上有完整性检查或者其它触发器， 而你又不希望在装载数据的时候激活它们，那么可以使用这个选项。  
  
目 前，为 --disable-triggers 发出的命令必须以超级用户发出。 因此，你应该也要用 -S 声明一个超级用户名，或者更好是设置 --use-set-session-authorization 并且以 PostgreSQL 超级用户身份运行 pg\_restore。  
  
pg\_restore 还接受下面的命令行参数做为联接参数：  
  
\-h host  
\--host=host  
声明服务器运行的机器的主机名。 如果数值以斜杠开头，那么它被用做 Unix 域套接字的目录。 缺省是从 PGHOST 环境变量中获取的（如果设置了）， 否则将尝试进行 Unix 域套接字。  
  
\-p port  
\--port=port  
声明服务器侦听的 TCP 端口或者本地的 Unix 域套接字文件扩展。 缺省是环境变量 PGPORT 的值（如果设置了的话）， 否则就说编译的缺省。  
  
\-U username  
以给出用户身分联接。  
  
\-W  
强制给出口令提示。如果服务器要求口令认证，那么这个应该自动发生。  
  
**理论说完了，有了上面的知识下面进行实战变得容易：**  
  
DBコッピ  
/usr/local/pgsql/bin/pg\_dump -Ft -b zhoz > /home/zhoz/db\_zhoz\_081121.tar  
  
移動  
scp -v /home/zhoz/db\_zhoz\_081121.tar zhoz@zhoz.com:/home/zhoz/  
  
SCP也是新学到的，很强大！参数也收集了一下：  
\-v 和大多数linux命令中的-v意思一样,用来显示进度.可以用来查看连接,认证,或是配置错误.  
\-C 使能压缩选项.  
\-r 复制文件夹  
\-P 选择端口.注意-p已经被rcp使用.  
\-4 强行使用IPV4地址.  
\-6 强行使用IPV6地址.  
  
エクスポート  
/usr/local/pgsql/bin/pg\_restore -d zhoz -U zhoz -W /home/zhoz/logs/db\_zhoz\_081121.tar  
这里如果不指定-U会提示数据库不存在或导入非指定的库中，有危险性。  
  
至此，打完收工！又掌握了一种实战技术。  
  
「2009/06/23补充：」  
pg\_dumpall > outfile  
生成的转储可以用 psql 恢复：  
  
psql template1 < infile  
（实际上，你可以声明任意现有的数据库进行连接，但是如果你是向一个空的数据库装载，那么 template1 是你唯一的选择。） 恢复pg\_dumpall的转储的时候通常需要数据库超级用户权限，因为我们需要它来恢复用户和组信息。  
  
   **处理大数据库**  
  
     因为 PostgreSQL 允许表的大小大于你的系统允许的最大文件大小， 可能把表转储到一个文件会有问题，因为生成的文件很可能比你的系统允许的最大文件大。 因为 pg\_dump 输出到标准输出，你可以用标准的 Unix 工具绕开这个问题：  
  
使用压缩的转储. 使用你熟悉的压缩程序，比如说 gzip。

pg\_dump dbname | gzip > filename.gz  
用下面命令恢复：  
  
createdb dbname  
gunzip -c filename.gz | psql dbname  
或者  
  
cat filename.gz | gunzip | psql dbname

split 命令允许你 你用下面的方法把输出分解成操作系统可以接受的大小。 比如，让每个块大小为 1 兆字节：  
  
pg\_dump dbname | split -b 1m - filename  
用下面命令恢复：  
  
createdb dbname  
cat filename\* | psql dbname  
  
    使 用客户化转储格式. 如果PostgreSQL是在一个安装了zlib 压缩库的系统上制作的，那么客户化转储格式将在写入输出文件的时候压缩数据。 它会生成和使用 gzip 类似大小的转储文件，但是还附加了一个优点：你可以有选择地恢复库中的表。 下面的命令用客户化转储格式转储一个数据库：  
  
pg\_dump -Fc dbname > filename  
客户化格式的转储不是脚本，不能用于 psql， 而是需要使用 pg\_restore 转储。 请参考 pg\_dump 和 pg\_restore 的手册获取细节。

**备份：pg\_dump -h localhost -p 5432 -U tradesns -W -F c -b -v -f "/home/tradeworkwangbin/us2010.backup" us2010  
恢复：pg\_restore -h 192.168.0.100 -p 5432 -U postgres -W -d us2011 -v "/root/us2010.backup"**

**注意**  
处于向下兼容的考虑，缺省的时候 pg\_dump 并不转储大对象。 要转储大对象，你必须使用客户化或者 tar输出格式， 并且在 pg\_dump 中使用-b选项。

[PgSql_pg_dump_pg_restore]: https://www.cnblogs.com/chjbbs/p/6480642.html