pg_dump vs pg_dumpall？哪一个用于数据库备份？ [英] pg_dump vs pg_dumpall? which one to use to database backups?

问题描述

我尝试了 pg_dump ，然后在另一台计算机上尝试导入sql并填充数据库，我看到了

I tried pg_dump and then on a separate machine I tried to import the sql and populate the database, I see

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

这意味着我的用户和角色和 grant 信息不在 pg_dump

which means my user and roles and grant information is not in pg_dump

另一方面，我们有 pg_dumpall ，我读了对话，这不会把我引向任何地方吗？

On the other hand we have pg_dumpall, I read conversation, and this does not lead me anywhere?

问题 < br>
-我应该使用哪一个进行数据库备份？ pg_dump 或 pg_dumpall ？

-要求我可以进行备份，应该

Question
- Which one should I be using for database backups? pg_dump or 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_dumpall --globals-only 获取用户/角色/等


• pg_dump -Fc 每个数据库，以获得一个不错的压缩转储，适用于 pg_restore 。

• pg_dumpall --globals-only to get users/roles/etc
• pg_dump -Fc for each database to get a nice compressed dump suitable for use with pg_restore.

是的，这种糟透了。我真的很想教 pg_dump 将 pg_dumpall 的输出嵌入到 -Fc 转储，但是现在不幸的是它不知道您该怎么做。

Yes, this kind of sucks. I'd really like to teach pg_dump to embed pg_dumpall output into -Fc dumps, but right now unfortunately it doesn't know how so you have to do it yourself.

直到PostgreSQL 11为止，还有一个令人讨厌的警告这种方法：中的 pg_dump 和 pg_dumpall 都不是-仅限全局变量模式将在 DATABASE 上转储用户访问 GRANT s。因此，您几乎不得不从目录中提取它们或过滤 pg_dumpall 。这已在PostgreSQL 11中修复；请参见发行说明。

Up until PostgreSQL 11 there was also a nasty caveat with this approach: Neither pg_dump, nor pg_dumpall in --globals-only mode 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.

使pg_dump转储数据库的属性，而不仅仅是其内容（Haribabu Kommi）

Make pg_dump dump the properties of a database, not just its contents (Haribabu Kommi)

以前，数据库的属性本身，例如数据库级别的 GRANT / REVOKE 权限和 ALTER DATABASE SET 变量设置仅由 pg_dumpall 转储。现在 pg_dump --create 和 pg_restore --create 除了数据库中的对象之外，还将还原这些数据库属性。 pg_dumpall -g 现在仅转储与角色和表空间相关的属性。 pg_dumpall 的完整输出（不带-g）保持不变。

Previously, attributes of the database itself, such as database-level GRANT/REVOKE permissions and ALTER DATABASE SET variable settings, were only dumped by pg_dumpall. Now pg_dump --create and pg_restore --create will restore these database properties in addition to the objects within the database. pg_dumpall -g now only dumps role- and tablespace-related attributes. pg_dumpall's complete output (without -g) is unchanged.

---

您还应该了解物理备份- pg_basebackup ，PgBarman和WAL归档，PITR等。这些提供了很多细粒度的恢复，直到分钟或个别交易。缺点是它们占用更多空间，只能还原到同一平台上的同一PostgreSQL版本，并且备份所有数据库中的所有表，而不能排除任何内容。

---

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_dump vs pg_dumpall？哪一个用于数据库备份？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！