From: Benjamin Krajmalnik <kraj@servoyant.com>
To: Kevin Grittner <kgrittn@mail.com>
To: pgsql-admin@postgresql.org
Subject: Re: Question concerning replicated server using streaming replication used as a read-only reporting server
Date: Thu, 17 Jan 2013 19:46:05 -0700
Message-ID: <F4E6A2751A2823418A21D4A160B6898892B85F@fletch.stackdump.local> (raw)
In-Reply-To: <20130117233212.134360@gmx.com>
References: <20130117233212.134360@gmx.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
It is ok if I am a little bit behind. What setting do I need to tweak to allow it to get further behind?
-----Original Message-----
From: Kevin Grittner [mailto:kgrittn@mail.com]
Sent: Thursday, January 17, 2013 4:32 PM
To: Benjamin Krajmalnik; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Question concerning replicated server using streaming replication used as a read-only reporting server
Benjamin Krajmalnik wrote:
> I have 2 servers which are using streaming replication (pg 9.0.4).
>
> The secondary server is there primarily as a disaster recovery server,
> but we are also using it for reporting, so as not to place undue load
> on the primary server.
>
> As I review the logs on the secondary server, I frequently see the
> following:
> 2013-01-17 06:05:47 MST [local]ERROR: canceling statement due to
> conflict with recovery
> 2013-01-17 06:05:47 MST [local]DETAIL: User query might have needed to
> see row versions that must be removed.
> 2013-01-17 06:05:47 MST [local]STATEMENT: Select statement goes here
> 2013-01-17 06:05:47 MST [local]FATAL: terminating connection due to
> conflict with recovery
> 2013-01-17 06:05:47 MST [local]DETAIL: User query might have needed to
> see row versions that must be removed.
> 2013-01-17 06:05:47 MST [local]HINT: In a moment you should be able to
> reconnect to the database and repeat your command.
> Is there anything that can be done to mitigate this situation?
You need to decide how "stale" you're willing to let the hot standby get. To preserve an image of the database which can allow the query to keep running, the standby server might need to pause replay of transactions. To allow long transactions, you need to allow it to pause the transaction stream for a long time, but that means that it's getting out of date for disaster recovery purposes.
It might be worthwhile to keep two standby clusters, one that is aggressive about applying the latest transactions, and another which allows long-running queries.
-Kevin
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: kraj@servoyant.com, kgrittn@mail.com
Subject: Re: Question concerning replicated server using streaming replication used as a read-only reporting server
In-Reply-To: <F4E6A2751A2823418A21D4A160B6898892B85F@fletch.stackdump.local>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox