Re: Configuring Standby Server in PostgreSQL 9.3.3

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.
>
> 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@1.2.3.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