postgresql pg_dump vs pg_dumpall？使用哪个数据库备份？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16618627/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pg_dump vs pg_dumpall? which one to use to database backups?
提问by daydreamer
I tried pg_dumpand then on a separate machine I tried to import the sql and populate the database, I see
我试过pg_dump然后在一台单独的机器上我尝试导入 sql 并填充数据库，我看到
CREATE TABLE
ERROR: role "prod" does not exist
CREATE TABLE
ERROR: role "prod" does not exist
CREATE TABLE
ERROR: role "prod" does not exist
CREATE TABLE
ERROR: role "prod" does not exist
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
WARNING: no privileges could be revoked for "public"
REVOKE
ERROR: role "postgres" does not exist
ERROR: role "postgres" does not exist
WARNING: no privileges were granted for "public"
GRANT
which means my userand rolesand grantinformation is not in pg_dump
这意味着我user和roles和grant信息不在pg_dump
On the other hand we have pg_dumpall, I read conversation, and this does not lead me anywhere?
另一方面，我们有pg_dumpall，我阅读了对话，这并没有让我走到任何地方？
Question
- Which one should I be using for database backups? pg_dumpor pg_dumpall?
- the requirement is that I can take the backup and should be able to import to any machine and it should work just fine.
问题
- 我应该使用哪一个进行数据库备份？pg_dump或者pg_dumpall？
- 要求是我可以进行备份并且应该能够导入到任何机器并且它应该可以正常工作。
回答by Craig Ringer
The usual process is:
通常的流程是：
pg_dumpall --globals-onlyto get users/roles/etcpg_dump -Fcfor each database to get a nice compressed dump suitable for use withpg_restore.
pg_dumpall --globals-only获取用户/角色/等pg_dump -Fc为每个数据库获得适合与pg_restore.
Yes, this kind of sucks. I'd really like to teach pg_dumpto embed pg_dumpalloutput into -Fcdumps, but right now unfortunately it doesn't know how so you have to do it yourself.
是的，这种方式很糟糕。我真的很想教pg_dump如何将pg_dumpall输出嵌入到-Fc转储中，但不幸的是现在它不知道如何做，所以你必须自己做。
Up until PostgreSQL 11 there was also a nasty caveat with this approach: Neither pg_dump, nor pg_dumpallin --globals-onlymode would dump user access GRANTs on DATABASEs. So you pretty much had to extract them from the catalogs or filter a pg_dumpall. This is fixed in PostgreSQL 11; see the release notes.
在 PostgreSQL 11 之前，这种方法还有一个令人讨厌的警告：无论是pg_dump, 还是pg_dumpallin --globals-onlymode都不会将用户访问GRANTs转储到DATABASEs 上。因此，您几乎必须从目录中提取它们或过滤pg_dumpall. 这在 PostgreSQL 11 中已修复；请参阅发行说明。
Make pg_dump dump the properties of a database, not just its contents (Haribabu Kommi)
Previously, attributes of the database itself, such as database-level
GRANT/REVOKEpermissions andALTER DATABASE SETvariable settings, were only dumped bypg_dumpall. Nowpg_dump --createandpg_restore --createwill restore these database properties in addition to the objects within the database.pg_dumpall -gnow only dumps role- and tablespace-related attributes.pg_dumpall's complete output (without -g) is unchanged.
使 pg_dump 转储数据库的属性，而不仅仅是其内容 (Haribabu Kommi)
以前，数据库本身的属性，例如数据库级别
GRANT/REVOKE权限和ALTER DATABASE SET变量设置，仅由pg_dumpall. 现在pg_dump --create，pg_restore --create除了数据库中的对象外，还将恢复这些数据库属性。pg_dumpall -g现在只转储与角色和表空间相关的属性。pg_dumpall的完整输出（不带 -g）不变。
You should also know about physical backups - pg_basebackup, PgBarman and WAL archiving, PITR, etc. These offer much "finer grained" recovery, down to the minute or individual transaction. The downside is that they take up more space, are only restoreable to the same PostgreSQL version on the same platform, and back up all tables in all databases with no ability to exclude anything.
您还应该了解物理备份 - pg_basebackup、PgBarman 和 WAL 归档、PITR 等。这些提供了更“细粒度”的恢复，精确到分钟或单个事务。缺点是它们占用更多空间，只能在同一平台上恢复到相同的 PostgreSQL 版本，并且备份所有数据库中的所有表，无法排除任何内容。
