Subject: Re: High Availability: Hot Standby vs. Warm Standby
From: Brad Nicholson <bnichols@ca.afilias.info>
To: Thomas Kellerer <spam_eater@gmx.net>
Cc: pgsql-admin@postgresql.org
In-Reply-To: <i17itq$g11$1@dough.gmane.org>
References: <i172f8$kug$1@dough.gmane.org>
	<AANLkTilFPekuBaypffaEgBxjreDdusVhqz0mSE_paJ8g@mail.gmail.com>
	<i17itq$g11$1@dough.gmane.org>
Content-Type: text/plain; charset="UTF-8"
Date: Fri, 09 Jul 2010 13:18:40 -0400
Message-ID: <1278695920.1914.55.camel@bnicholson-desktop>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Fri, 2010-07-09 at 18:31 +0200, Thomas Kellerer wrote:
> Hi,
> 
> Rob Wultsch wrote on 09.07.2010 18:14:
> >> I am aware that I can use the 9.0 standby server for read only queries, but
> >> that is (currently) not something we need
> >>
> >
> > Taking SQL backups without impacting the master might be something to consider.
> 
> Interesting point. Thanks for mentioning that.

There is an issue with running your backup from a standby that you need
to be aware of. 

There is potential that a long running query on the standby can conflict
with the application of wal records.  In this case, you have a choice to
either terminate the query and let wal records continue to be applied,
or delay the application of the wal until the query completes.

Considering that you are looking at HA and asking about the difference
in lost transactions between streaming replication and 8.4 PITR, I doubt
that letting the standby lag for the duration of the pg_dump is going to
be something that interests you.
 
Full details are here:
http://www.postgresql.org/docs/9.0/static/hot-standby.html#HOT-STANDBY-CONFLICT
 
-- 
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.