postgresql 有谁知道备份 postgres 数据库的好方法？

Does anyone know of a good way to back up postgres databases?

提问by Stavros Korokithakis

I have a script that will produce daily rotated backups for mysql, but I can't find anything similar for postgres. I have also discovered that it has an online backup capability, which should come in handy since this is a production site.

我有一个脚本可以为 mysql 生成每日轮换备份，但我找不到任何与 postgres 类似的内容。我还发现它具有在线备份功能，这应该会派上用场，因为这是一个生产站点。

Does anyone know of a program/script that will help me, or even a way to do it?

有谁知道可以帮助我的程序/脚本，或者甚至是一种方法？

Thanks.

谢谢。

回答by Adam Batkin

One way is to usepg_dumpto generate a flat sql dump, which you can gzip or whatever. This is certainly the easiest option, as the results can be piped back in to psqlto re-load a database, and since it can also export as plain text, you can look through or edit the data prior to restore if necessary.

一种方法是使用pg_dump生成平面 sql 转储，您可以对其进行 gzip 或其他任何操作。这当然是最简单的选择，因为结果可以通过管道psql重新加载到数据库中，并且由于它也可以导出为纯文本，如果需要，您可以在恢复之前查看或编辑数据。

The next method is to temporarily shut down your database (or if your filesystem supports atomic snapshots, in theory that might work) and backupyour PostgreSQL datadirectory.

下一个方法是暂时关闭您的数据库（或者如果您的文件系统支持原子快照，理论上可能可行）并备份您的 PostgreSQLdata目录。

Thispage from the PostgreSQL site also explains how to do online backups and point-in-time recovery, which is definitely the most difficult to configure, but also the optimal method. The idea is that you perform a base backup (which you might do every day, couple of days or week) by running some special SQL (pg_start_backupand pg_stop_backup) and make a (filesystem-level) copy of your database directory. The database doesn't go offline during this time, and everything still works as normal. From then on, the database generates a Write Ahead Log (WAL) of any changes, which can then be pushed (automatically, by the database) to wherever you want. To restore, you take the base backup, load it into another database instance, then just replay all the WAL files. This way you can also do point-in-time recovery by not replaying all of the logs.

PostgreSQL 站点的这个页面还解释了如何进行在线备份和时间点恢复，这绝对是最难配置的，但也是最佳方法。这个想法是通过运行一些特殊的 SQL（pg_start_backup和pg_stop_backup) 并制作数据库目录的（文件系统级）副本。数据库在此期间不会脱机，一切仍然正常。从那时起，数据库会生成任何更改的预写日志 (WAL)，然后可以将这些更改（由数据库自动）推送到您想要的任何位置。要恢复，您需要获取基本备份，将其加载到另一个数据库实例中，然后重放所有 WAL 文件。通过这种方式，您还可以通过不重放所有日志来进行时间点恢复。

回答by Kevin

Here the scripts which you can use for backup postgresql by crontab

这里是您可以通过 crontab 用于备份 postgresql 的脚本

http://ithelpblog.com/os/linux/debian/best-way-to-backup-postgresql-database/

http://ithelpblog.com/os/linux/debian/best-way-to-backup-postgresql-database/

Regards, Kevin

问候， 凯文

回答by Vitaly Kushner

For automated backups of both MySQL AndPostrgres check out astrails-safe on github(or just "gem install astrails-safe --source=http://gems.github.com"). It uses mysqldump to backup MySQL and pg_dump to backup Postgres. It also knows how to backup plain files with tar and encrypt everything with GnuPG and upload to S3, or any Unix server with SFTP.

对于 MySQL和Postrgres 的自动备份，请查看github 上的 astrails-safe（或只是“ gem install astrails-safe --source=http://gems.github.com”）。它使用 mysqldump 来备份 MySQL 和 pg_dump 来备份 Postgres。它还知道如何使用 tar 备份纯文件，并使用 GnuPG 加密所有内容并上传到 S3 或任何带有 SFTP 的 Unix 服务器。

回答by chotchki

Since you specified databaseS, pg_dumpallwill be far more useful to you. It dumps all databases and users to a sql file instead of just a single one.

既然你指定了 databaseS，pg_dumpall对你来说会更有用。它将所有数据库和用户转储到一个 sql 文件中，而不仅仅是一个。

回答by chotchki

Generally theway to do backups is to use pg_dump.

通常进行备份的方法是使用 pg_dump。

You shouldn't "copy files from postgresql directory, just like in mysql" - because chances are you will not be able to use them (these files are architecture, operating system, and compile-options dependent).

您不应该“从 postgresql 目录复制文件，就像在 mysql 中一样”-因为您可能无法使用它们（这些文件取决于体系结构、操作系统和编译选项）。

Unless pg_dump is provento be insufficient - this is what you should use. After you will be in situation that pg_dump cannot be used - you should ask yourself: why it can't be used, and what can you do to use it again :)

除非 pg_dump 被证明是不够的 - 这就是你应该使用的。在您将处于无法使用 pg_dump 的情况之后-您应该问自己：为什么不能使用它，以及如何再次使用它:)

When using pg_dump you might choose plain SQL file dump (-F p), or custom format (-F c). SQL dump is easier to modify/change, but the custom format is much more powerful, and (since 8.4) faster to load, because you can load it in many parallel workers instead of sequentially.

使用 pg_dump 时，您可以选择普通 SQL 文件转储 (-F p) 或自定义格式 (-F c)。SQL 转储更容易修改/更改，但自定义格式更强大，并且（自 8.4 起）加载速度更快，因为您可以在许多并行工作程序中而不是按顺序加载它。

回答by Randell

You can also dump your PostgreSQL database using phpPgAdminor pgAdmin III.

您还可以使用phpPgAdmin或pgAdmin III转储 PostgreSQL 数据库。

回答by Vladimir Dyuzhev

I've just stumbled upon this little neat utility:

我刚刚偶然发现了这个小巧的实用工具：

http://code.google.com/p/pg-rman/

http://code.google.com/p/pg-rman/

From the documentation it looks promising, but I yet have to try it.

从文档来看，它看起来很有希望，但我还得尝试一下。

回答by Techie

This is what I would do to backup my old database and restore

这就是我要备份旧数据库并恢复的方法

To back up your database

备份您的数据库

pg_dump --format=c olddb_name > db_dump_file.dump

To restore that backup

恢复该备份

pg_restore -v -d newdb_name db_dump_file.dump

Read more on pg_dumpand pg_restore

阅读有关pg_dump和pg_restore 的更多信息