Re: Configuring Standby Server in PostgreSQL 9.3.3

On Apr 8, 2014, at 3:16 AM, Heikki Linnakangas <hlinnakangas@vmware.com> =
wrote:

> On 04/08/2014 01:25 AM, fburgess@radiantblue.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.
>>=20
>> 1.) Running from the slave.  1.2.3.4 is the ip of master database.
>>=20
>> basebackup -D /u01/fiber/postgreSQL_data -F p -x stream -c fast -P -v =
-h 1.2.3.4
>> -p 5432 -U replication
>>=20
>> 2.) compared to ...
>>=20
>> psql -c "select pg_start_backup('initial_backup');"
>> rsync -cva --inplace --exclude=3D*pg_xlog* =
/u01/fiber/postgreSQL_data/
>> postgres@1.2.3.4:/u01/fiber/postgreSQL_data/
>> psql -c " select pg_stop_backup () ;"
>>=20
>> 3.) or this ...
>>=20
>> psql =96c =93select pg_start_backup(=91hot backup=92)=94
>> cp =96pr /u01/fiber/postgreSQL_data =
1.2.3.4:/u01/fiber/postgreSQL_data
>> psql =96c =93select pg_stop_backup(=91hot backup=92)=94
>=20
> 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-benchma=
rk.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.