Re: pg_dump vs pg_basebackup

On Tue, Mar 25, 2014 at 12:22 PM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> On 03/25/2014 08:18 AM, Ilya Kosmodemiansky wrote:
>>
>>
>> Joshua,
>>
>> that is really good point: an alternative is to use pg_basebackup
>> through ssh tunnel with compression, but rsync is much simpler.
>
>
> Or rsync over ssh. The advantage is that you can create backups that don't
> have to be restored, just started. You can also use the differential
> portions of rsync to do it multiple times a day without much issue.

rsync's delta transfer isn't relly very effective with postgres. You
don't save any I/O, just network traffic, and in general the
bottleneck is I/O (unless you have a monster I/O subsys or a snail of
a network one).

There were some musing about making delta transfer more efficient in
pg in hackers, but I don't think anything tangible came out of that,
so it's basically equivalent to a full transfer. The only reason to
leverage rsync's delta transfer would be to decrease the time between
pg_start_backup and pg_stop_backup, which could only matter if you're
low on WAL space, but the reduction, in my experience, isn't stellar.