I have a 20 Tb database in PostgreSQL 9.2 and I have no idea how to efficiently create backups. It's a database that grows a little bit every day and on the 1 of each month it grows about 200 Gb.

I was thinking of doing a pg_dump, but it takes too much to do it every month, then I think about doing it one time, and then backup just the new or changed tables with copy, but I can't stop using the database in order to create the first dump, so I won't exactly know exactly which data does the backup contains.

How can I do an incremental backup without stop using the database?

  • pg_dump produces a consistent backup even when the db is being written to. See the description section from its manpage. In theory your custom incremental backup should work even with the huge dataset you have. – Daniel Vérité Oct 18 '13 at 0:37

Backups are extensively covered in the PostgreSQL manual.

To achieve a continuous backup, archive the write-ahead log. Suggested settings are:

archive_mode = on
wal_level = hot_standby
archive_command = '/usr/bin/rsync --archive --ignore-existing "%p" "/backup-dest/%p"'

# Ensures that a log file is written at least once every 30 minutes even if little
# activity has occurred
archive_timeout = 30min

Instead of doing a pg_dump for your baseline, you can do pg_basebackup, which does not require you to freeze the database. However, if you do not already have archive_mode on, you'll need to restart the database to change that setting.

| improve this answer | |

Personally, I don't know of an incremental backup solution, but I can think of two ways to achieve what you are asking.

You could take a look at barman which runs pg_basebackup (if i'm not mistaken) and then streams the WAL records to the barman server. That has the disadvantage that you need a server running barman constantly, and not just on the first of each month. However, it has the advantage of being able to create a database based on a specific date and time.

The other solution that I can think of, is to try using a snapshotting filesystem. I only have experience with ZFS, but BTRFS should work too. Then on the first of each month you could just create an incremental snapshot of the filesystem that hosts the database, and restore in case something goes wrong. However, given that you already have a running database, that would be an issue, unless you use it on a read-only replica that you spin up for this reason.

| improve this answer | |

If you are using AWS to host your Postgres db, you can try using tool created by Heroku for making backup from EC2 to S3 called Wal-E

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.