Re: Backup Method

On 7/3/15 8:08 AM, howardnews(at)selestial(dot)com wrote:
> Thanks everyone.
>
> I am trying to move away from pg_dump as it is proving too slow. The
> size of the database clusters are approaching 1TB (with multiple
> individual compressed pg_dumps of around 100GB each, but the pace of
> change is relatively glacial compared to the size so I am hoping that
> WAL backups will prove to be much more efficient. As you all point out
> it looks like I will need to test the various methods to find the best
> solution for me.

PgBackRest might be a good fit for you:
https://github.com/pgmasters/backrest

It supports compressed full, differential, and incremental backups and
can do local backups to an NFS mount without a lot of configuration.

> A supplementary question would be: would rsync be a viable alternative
> to pg_basebackup when performing the file system copy. I have seen a
> few posts on this subject which suggest rsync is more prone to
> mistakes but is potentially the faster option. Love to hear all your
> thoughts on this subject before I risk trying rsync.

Rsync is potentially dangerous for an incremental (or resumed) backup.
PgBackRest is not based on rsync and is safe for incrementals. It also
offers destination compression which rsync cannot do, though of course
you could backup to a compressed file system.

More importantly you still need to deal with the WAL and PgBackRest does
that for you. If you have a large database with a small rate of change
then a weekly/biweekly full backup with daily incremental/differential
should be very efficient.

--
- David Steele
david(at)pgmasters(dot)net