Re: pg_dump vs pg_basebackup

On Tue, Mar 25, 2014 at 4:39 AM, David Johnston <polobo(at)yahoo(dot)com> wrote:
>> Hai,
>>
>> Can anyone tell me the difference and performance between pgdump and
>> pg_basebackup if I want to backup a large database.
>>
>> Thanks
>
> Yes. And many of their words have been written down in the documentation in
> a chapter named "Backup and Restore". Do you have a specific question about
> what is written there?
>
> I'll add that comparing the performance of both is relatively meaningless.
> You need to understand how each works then choose the correct tool for your
> situation.

I don't know if meaningless is the right word here. I have a ~450G
database, and the difference is quite meaningful to me, as it is
measured in days.

The difference being, pg_basebackup is dumber and using it is harder,
but its performance is only limited by sequential I/O capacity (which
is usually quite high). It is also used in conjunction with PITR to
get not only that, but also incremental backups, which is something
you really want for big databass. pg_dump, on the other hand, will
only do full dumps and it will be limited both by I/O and CPU power,
because the reformatting involved in making a dump is considerable. In
my experience, a base backup takes hours, while a dump takes days.