Thread: Hot Standby - ERROR: canceling statement due to conflict with recovery
On Sonntag, 27. Februar 2011, Sean Laurent wrote: > Unfortunately, most queries against the hot standby fail. Worse > yet, pg_dump fails: ... > I'm not entirely certain I understand why I'm seeing this. Nor do > I understand how to fix or work around this. Any advice or > suggestions would be greatly appreciated. Long running queries on the standby are a bit tricky, because they might need to see row versions that are already removed on the master. It's well documented: http://www.postgresql.org/docs/9.0/static/hot-standby.html HTH, Jens
On Sonntag, 27. Februar 2011, Sean Laurent wrote:...
> Unfortunately, most queries against the hot standby fail. Worse
> yet, pg_dump fails:> I'm not entirely certain I understand why I'm seeing this. Nor doLong running queries on the standby are a bit tricky, because they
> I understand how to fix or work around this. Any advice or
> suggestions would be greatly appreciated.
might need to see row versions that are already removed on the
master.
It's well documented:
http://www.postgresql.org/docs/9.0/static/hot-standby.html
On Sunday, February 27, 2011 11:57:35 am Sean Laurent wrote:
> On Sun, Feb 27, 2011 at 1:04 PM, Jens Wilke <jens@wilke.org> wrote:
> > On Sonntag, 27. Februar 2011, Sean Laurent wrote:
> > > Unfortunately, most queries against the hot standby fail. Worse
> >
> > > yet, pg_dump fails:
> > ...
> >
> > > I'm not entirely certain I understand why I'm seeing this. Nor do
> > > I understand how to fix or work around this. Any advice or
> > > suggestions would be greatly appreciated.
> >
> > Long running queries on the standby are a bit tricky, because they
> > might need to see row versions that are already removed on the
> > master.
> > It's well documented:
> > http://www.postgresql.org/docs/9.0/static/hot-standby.html
>
> Right. I read all of that. I guess I just assumed it was possible to create
> a snapshot on the standby so that a longer running on the standby could
> complete. In particular, I was really hoping to run database dumps against
> the standby, not the master.
>
> Thanks.
>
> Sean
From the above link:
"The most common reason for conflict between standby queries and WAL replay is "early cleanup". Normally, PostgreSQL allows cleanup of old row versions when there are no transactions that need to see them to ensure correct visibility of data according to MVCC rules. However, this rule can only be applied for transactions executing on the master. So it is possible that cleanup on the master will remove row versions that are still visible to a transaction on the standby. "
Below that it goes into some possible solutions, the easiest of which to test would seem to be:
"Another option is to increase vacuum_defer_cleanup_age on the primary server, so that dead rows will not be cleaned up as quickly as they normally would be. This will allow more time for queries to execute before they are cancelled on the standby, without having to set a high max_standby_streaming_delay. However it is difficult to guarantee any specific execution-time window with this approach, since vacuum_defer_cleanup_age is measured in transactions executed on the primary server. "
--
Adrian Klaver
adrian.klaver@gmail.com
On Sonntag, 27. Februar 2011, Sean Laurent wrote: > In particular, I was really hoping to > run database dumps against the standby, not the master. One solution is to begin idle transactions on the master by using e.g. dblink from the standby to the master before you start pg_dump on the standby and end them after pg_dump (or whatever) is finished. Jens
On 28/02/11 03:57, Sean Laurent wrote: > Right. I read all of that. I guess I just assumed it was possible to create > a snapshot on the standby so that a longer running on the standby could > complete. In particular, I was really hoping to run database dumps against > the standby, not the master. This makes me wonder if a possible extension to HS might (one day) be a side log for rows that are still needed for transactions running on the slave, but have been overwritten on the master. When a log replay overwrites a page containing tuples still needed by a transaction running on the slave, it could be copied to a temporary heap that's deleted when the last slave transaction needing those tuples finishes. That'd be a LOT of work and would introduce differences in query execution that'd make the standby more different to the master (increasing testing burden) so it's not exactly an obvious no-brainer thing to do. I'm sure it'd also be seriously complicated to get right, and isn't anything I'd ever ask anybody to do unless I was paying them well for their time. Maybe someone who really needs functionality like this will fund it at some point - personally, I don't presently even use HS, so it's well beyond my needs. -- Craig Ringer
Re: Hot Standby - ERROR: canceling statement due to conflict with recovery
On Sun, Feb 27, 2011 at 1:57 PM, Sean Laurent <sean@studyblue.com> wrote: > Right. I read all of that. I guess I just assumed it was possible to create > a snapshot on the standby so that a longer running on the standby could > complete. In particular, I was really hoping to run database dumps against > the standby, not the master. > Thanks. It is possible to pull dumps from a hot standby, at least in theory (I've never tried it). You can delay the timeout period via max_standby_archive_delay and max_standby_streaming_delay to block WAL application until the snapshot resolves. You can also use the dblink approach mentioned by others. This is obviously you have to think about very carefully before doing and some experimentation may be in order. Curious: has anybody set up 2+ hot standby reading from a primary, with one set up as HA-ish config and one with a config set up for long running queries, dumps etc? Has anyone successfully pulled off dumps from a hot standby on a busy server? merlin
One solution, for backups anyway, is to pause the replication on the slave machines with the command: pg_xlog_replay_pause() and pg_xlog_replay_resume(). We still don't have a solution for long running queries, mayber have a mechanism to retry them when they fail with that specific error. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Hot-Standby-ERROR-canceling-statement-due-to-conflict-with-recovery-tp3402417p5636744.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
One solution, for backups anyway, is to pause the replication on the slave
machines with the command: pg_xlog_replay_pause() and
pg_xlog_replay_resume(). We still don't have a solution for long running
queries, mayber have a mechanism to retry them when they fail with that
specific error.
Thanks & Regards,
Raghu Ram
EnterpriseDB: http://www.enterprisedb.com