Management requires that we implement a master server with two standby database servers. Is it recommended we keep the archives logs in separate directories in each VM, or that we have one directory on a shared drive that each of the three VM's can access? We would like to leverage the archive_cleanup_command = ‘pg_archivecleanup /sharedata/postgres_archive_master %r’ in our recovery.conf configuration on both standby servers.

-------- Original Message --------
Subject: Re: [BUGS] Configuring Standby Server in PostgreSQL 9.3.3
From: <fburgess@radiantblue.com>
Date: Tue, April 08, 2014 12:34 pm
To: "Jeff Frost" <jeff@pgexperts.com>, "Heikki Linnakangas"
<hlinnakangas@vmware.com>
Cc: "Michele" <michele.mariani@databtech.com>, pgsql-bugs@postgresql.org

Our standby server sits on a separate VM,. Should archive_mode = on be set only on the master and not the slave? My colleague is convinced that the archive log directory should sit on a shared drive that is accessible to both VM's (master and Slave)

-------- Original Message --------
Subject: Re: [BUGS] Configuring Standby Server in PostgreSQL 9.3.3
From: Jeff Frost <jeff@pgexperts.com>
Date: Tue, April 08, 2014 9:39 am
To: Heikki Linnakangas <hlinnakangas@vmware.com>
Cc: fburgess@radiantblue.com, Michele <michele.mariani@databtech.com>,
pgsql-bugs@postgresql.org


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

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.