Postgresql PITR备份：处理多个数据库的最佳实践？ [英] Postgresql PITR backup: best practices to handle multiple databases?

问题描述

伙计们，我有一个带有许多数据库的Postgresql 8.3服务器。

Hy guys, i have a postgresql 8.3 server with many database.

实际上，我打算使用一个脚本将这些数据库存储在一个数据库中，以备份这些数据库。与数据库同名的文件夹，例如：

Actually, im planning to backup those db with a script that will store all the backup in a folder with the same name of the db, for example:

/mypath/backup/my_database1/
/mypath/backup/my_database2/
/mypath/backup/foo_database/

每天我进行1次转储每2个小时，每天覆盖一次文件...例如，在my_database1文件夹中，我有：

Every day i make 1 dump each 2 hours, overwriting the files every day... for example, in the my_database1 folder i have:

my_database1.backup-00.sql  //backup made everyday at the 00.00 AM
my_database1.backup-02.sql  //backup made everyday at the 02.00 AM
my_database1.backup-04.sql  //backup made everyday at the 04.00 AM
my_database1.backup-06.sql  //backup made everyday at the 06.00 AM
my_database1.backup-08.sql  //backup made everyday at the 08.00 AM
my_database1.backup-10.sql  //backup made everyday at the 10.00 AM
[...and so on...]

这是我实际上向自己保证能够恢复丢失至少2小时数据的每个数据库的方式。

This is how i actually assure myself to be able to restore everydatabase loosing at least 2 hours of data.

2小时看上去还是

我看过Postgresql pitr处理了WAL文件，但是这些文件似乎包含全部数据， 全部我的数据库。

I've got a look to the postgresql pitr trought the WAL files, but, those files seem to contain all the data about all my database.

我需要分离这些文件，就像我分离转储文件一样。

I'll need to separate those files, in the same way i do separate the dump files.

如何？

否则，还有一个易于安装的备份程序可以让我还原早10秒备份1次，但是每10秒没有创建转储文件吗？

Otherwise, there is another easy-to-install to have a backup procedure that allo me to restore just 1 backup at 10 seconds earlier, but without creating a dump file every 10 seconds?

推荐答案

为什么要分离数据库？

PITR的工作方式是不可能的，因为它可以在整个群集上工作。
在这种情况下，您可以为每个数据库创建一个数据目录和一个单独的群集（尽管不建议这样做，因为它将需要不同的端口和postmaster实例）。

The way the PITR works, it is not possible to do since it works on the complete cluster. What you can do in that case is to create a data directory and a separate cluster for each of those databases (not recommended though since it will require different ports, and postmaster instances).

我相信使用PITR代替常规转储的好处胜过为每个数据库单独备份，因此也许您可以重新考虑为什么需要将其分开。

I believe that the benefits of using PITR instead of regular dumps outweigh having separate backups for each database, so perhaps you can re-think the reasons for why you need to separate it.

另一种方法可能是使用 Slony-I建立一些复制，但这将需要一台单独的计算机（或实例）来接收数据。另一方面，这样一来，您将拥有几乎实时的复制系统。

Another way could be to set up some replication with Slony-I but that would require a separate machine (or instance) that receives the data. On the other hand, that way you would have a replicated system in near real-time.

更新评论：

要从错误中恢复，例如删除表，因为您可以重播到特定时间，所以PITR是完美的选择。但是，对于500个数据库，我知道这可能会产生很多开销。 Slony-我可能无法正常工作，因为它正在复制。不确定如何处理表删除。

To recover from mistakes, like deleting a table, PITR would be perfect since you can replay to a specific time. However, for 500 databases I understand that can be a lot of overhead. Slony-I would probably not work, since it is replicating. Not sure how it handles table deletions.

我不知道可以采取任何其他方式。我要做的可能仍然是PITR，只是不会犯任何错误;）。除了玩笑，根据犯错误的频率，这可能是解决方案：

I am not aware of any other ways you can go. What I would do would probably still be going for PITR and just not do any mistakes ;). Jokes aside, depending how frequently mistakes are being made this could be a solution:

• 为PITR设置


• 有另一个备用实例。


• 发生错误时，将还原重放到第二个实例上的时间点。


• 从该实例执行受影响数据库的pg_dump


• 在该数据库的生产实例上执行pg_restore。

• Set it up for PITR
• have a second instance ready on standby.
• When a mistake happens, replay the restore to the point in time on the second instance.
• Do a pg_dump of the affected database from that instance.
• Do a pg_restore on the production instance for that database.

但是要求您准备好第二个实例，无论是在同一服务器上还是在其他服务器上（建议使用不同的实例）。另外，还原时间会更长一些，因为它需要您执行一次额外的转储并还原。

However, it would require you to have a second instance ready, either on the same server or a different one (different is recommended). Also, the restore time would be a bit longer since it would require you to do one extra dump and restore.

这篇关于Postgresql PITR备份：处理多个数据库的最佳实践？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！