How to do Hot Backups correctly?

Question

PostgreSQL

The documentation says that it would be correct to do something like this:

psql -c "select pg_start_backup('hot_backup');"
tar -cf $BACKUP_DIR/$BACKUP_FILENAME /var/lib/pgsql/9.1/data
psql -c "select pg_stop_backup();"

I did something like this, only tar was replaced with rsync. It works great on small bases without load, but problems appear with large ones under loads. The backup is done in about an hour. WAL logs are copied last and rsync correctly notices that file has been vanished.

As a result, the backup is completely without WAL logs. In principle, the backups are working, they are repaired using pg_resetxlog. But still it is interesting how to be, so that you do not have to reset the logs?

Answer 1

What the manual thinks about this:
Those. wal-logs are needed, but you can ignore their changes during the backup process. Check rsync's mana to see if it can ignore file modifications.
In the years since this answer, I have successfully retrained to postgresql DBA. And I can say that vyegorov 's answer is correct . Use the regular pg_basebackup with --xlog (for 9.1 + attention to the warning in the manual) or --xlog-method=stream for newer ones.
But I strongly do not recommend "repairing" anything through pg_resetxlog. As a result, you will get something incomprehensible instead of a consistent state of the database. Since it is on xlog that the base comes to a consistent transactional state at startup.

Answer 2

If there is a need to manage a fleet of databases / servers, then you should look at ready-made solutions, like http://www.pgbarman.org/

If the backup is done occasionally, then it would be optimal to use the standard utility pg_basebackup . It can also prepare a Standby instance.

Answer 3

Have you tried, for example, taking a snapshot of the file system, copying it to a backup, and then deleting the snapshot?