Re: Configuring Standby Server in PostgreSQL 9.3.3

On Apr 8, 2014, at 3:16 AM, Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> wrote:

> On 04/08/2014 01:25 AM, fburgess(at)radiantblue(dot)com wrote:
>> Did you guys benchmark the basebackup utility? The master database will have to
>> remain online during this backup/restore process, since it is receiving real
>> time data feeds. Not sure which technique is better for our 7TB db.
>>
>> 1.) Running from the slave. 1.2.3.4 is the ip of master database.
>>
>> basebackup -D /u01/fiber/postgreSQL_data -F p -x stream -c fast -P -v -h 1.2.3.4
>> -p 5432 -U replication
>>
>> 2.) compared to ...
>>
>> psql -c "select pg_start_backup('initial_backup');"
>> rsync -cva --inplace --exclude=*pg_xlog* /u01/fiber/postgreSQL_data/
>> postgres(at)1(dot)2(dot)3(dot)4:/u01/fiber/postgreSQL_data/
>> psql -c " select pg_stop_backup () ;"
>>
>> 3.) or this ...
>>
>> psql –c “select pg_start_backup(‘hot backup’)”
>> cp –pr /u01/fiber/postgreSQL_data 1.2.3.4:/u01/fiber/postgreSQL_data
>> psql –c “select pg_stop_backup(‘hot backup’)”
>
> I bet the rsync or cp method is faster. Dunno how much, though, that depends on what the bottleneck is; the network, or the disk, or something else. You'll have to measure it yourself, to know how it is in your environment.
> - Heikki

FWIW, I benchmarked a few methods here:

http://frosty-postgres.blogspot.com/2011/12/postgresql-base-backup-benchmark.html

admittedly, the base backup was much smaller, but I suspect you would see similar performance with a larger base backup.

You might also try running the rsync once, before issuing pg_start_backup() and then doing a normal base backup so that rsync can take advantage of partial file copy.

If you use rsync over ssh, make sure to use the arcfour cipher for best performance.