Re: Backup Method - Mailing list pgsql-general

On 03/07/2015 12:23, Bill Moran wrote:
> On Fri, 03 Jul 2015 13:16:02 +0200
> Jan Lentfer <Jan.Lentfer@web.de> wrote:
>
>> Am 2015-07-03 13:00, schrieb howardnews@selestial.com:
>>> On 03/07/2015 11:39, Guillaume Lelarge wrote:
>>>>> In that case is there any recommendation for how often to make
>>>> base backups in relation to the size of the cluster and the size of
>>>> the WAL?
>>>> Nope, not really. That depends on a lot of things. Our customers
>>>> usually do one per day.
>>>>
>>>>
>>> Excuse my ignorance... Is the base backup, in general, faster than
>>> pg_dump?
>> It is a different approach. With the base backup you are actually
>> backing up files from the filesystem ($PGDATA directory), whereas with
>> pg_dump your saving the SQL commands to reload and rebuild the database.
>> "Usually" a file based backup will be faster, both on backup and
>> restore, but it is - as mentioned - a different approach and it might
>> also not serve all your purposes.
> One of the things that makes a lot of difference is the amount of
> redundant data in the database. For example, indexes are completely
> redundant. They sure do speed things up, but they're storing the same
> data 2x for each index you have. When you do a base backup, you have
> to copy all that redundancy, but when you do a pg_dump, all that
> redundant data is reduced to a single CREATE INDEX command. The
> result being that if your database has a lot of indexes, the pg_dump
> might actually be faster.
>
> But the only way to know is to try it out on your particular system.
>

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.

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.