pg_standby/warm standby questions

From: mikeee <mike(dot)engelhart(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: pg_standby/warm standby questions
Date: 2008-04-01 14:29:10
Message-ID: 55b8de94-92c3-43cd-8475-2f108ec966d6@b1g2000hsg.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all -

I've setup a warm standby using WAL archiving and pg_standby and it
seems to be mostly working. I do have a few questions though:

Here's my archive_comand and restore_command for reference
(postgres_db_2 is a hosts entry in case you were wondering).

archive_command = 'scp -q %p postgres_db_2:/opt/wal_archives/%f </dev/
null'

restore_command = 'pg_standby -s 30 -t /tmp/psql.trigger.5432 /opt/
wal_archives %f %p %r 2>>standby.log'

1) The %r macro in pg_standby doesn't remove files right after they
are consumed - is that normal? In fact I haven't seen it remove any
files from my WAL archive directory at all. Is there a time counter
on top of the fact that the file has already been restored?

2) How does one confirm that everything is working properly? While in
continuous recovery mode, you can't log into the database with psql so
what is the correct procedure for this?

My initial thoughts where that I would do a nightly dump of my
production database using the standard pg_dump tools as a fail safe in
case the warm standby gets corrupt for some reason but I'm wondering
how/when I can safely get rid of old WAL files. Both on the
production and standby servers. Do I write a shell script for my
archive and restore commands that deletes the files? Seems risky but
I couldn't find any documentation on what the proper way of handling
this is.

Thanks for any help with these questions

Best Regards
Mike

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2008-04-01 14:29:34 Re: Using tables in other PostGreSQL database
Previous Message Tom Lane 2008-04-01 14:27:06 Re: simple update queries take a long time - postgres 8.3.1