Re: WAL Log Shipping - Warm Standby not working under 8.3.7

On Jan 9, 2010, at 7:24 AM, Keaton Adams wrote:

> Restored from last evening's backup from the master DB to make the rsync across the network finish sooner.
> >>
> >> Shut down the PG instance on the slave machine
> >>
> >> Ran a script that does the following:
> >>
> >> select pg_start_backup('Master_Backup');
> >> rsync -rvlpogtz ${masterdb}/* ${slave_dbuser}@${slave_host}:${slavedb}
> >> select pg_stop_backup();
> >> ssh ${slave_dbuser}@${slave_host} rm ${slavedb}/postmaster.pid 2>/dev/null
> >> ssh ${slave_dbuser}@${slave_host} rm ${slave_backup_path}/0* 2>/dev/null
> >> ssh ${slave_dbuser}@${slave_host} ${PSQL_BIN}/pg_ctl -D ${slavedb} -l logfile start
> >
> > Is ${slave_backup_path} your archive directory?  Why are you deleting all of you archives there?
>
> Also, what are the contents of your recovery.conf file?  Are you using pg_standby?  The typical setup is to clear
<datadir>/pg_xlogon your standby and use pg_standby to recovery files directly from your archive directory? 

> We are still on PG 8.3.7 and I don’t see pg_standby.  It looks like it was added with 8.3.8.  I will put in a request
toupdate to 8.3.9 as a part of our next software upgrade (internal product we write) but for now  I need to try to
figureout why this isn’t working under 8.3.7.  I did manage to bring up replication under 8.3.7 in the test environment
justfine, so the question is now why the standby PG instance won’t come up in recovery mode and why it is looking so
farback for the next WAL log file. 
>
> -K

Keaton,

Please, don't top post (I've fixed it in this reply), it makes it difficult for others to join the thread and know
what'scurrently going on. 

Anyways, with regards to pg_standby, it should be included in all versions >= 8.3.0.  Regardless, it will work with
anythingversion since at least 8.2 so you can just grab it from a source install and build it against your built source
treeor use your package manager of choice to get it. 

Regardless:

> >> ssh ${slave_dbuser}@${slave_host} rm ${slave_backup_path}/0* 2>/dev/null

You are doing that *after* you run pg_start_backup() so you're deleting WAL files that are needed for the standby -- it
needsall WAL files generated from the start_backup() call on. 


Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k