Обсуждение: pg_dump on Hot standby : clarification on how to
I would just like to get some clarification from the list on how to do a pg_dump on the slave in the face of "canceling statement due to conflict with recovery".
The following links seem to indicate that If I start an idle transaction on the master I should be able to do the pg_dump, but I tried this in psql on the master "start transaction", and was still unable to do a pg_dump on the slave at the same time.
Is there something special about using dblink that would make this all work?
---->
http://postgresql.1045698.n5.nabble.com/Hot-Standby-ERROR-canceling-statement-due-to-conflict-with-recovery-td3402417.html
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.
The following links seem to indicate that If I start an idle transaction on the master I should be able to do the pg_dump, but I tried this in psql on the master "start transaction", and was still unable to do a pg_dump on the slave at the same time.
Is there something special about using dblink that would make this all work?
---->
http://postgresql.1045698.n5.nabble.com/Hot-Standby-ERROR-canceling-statement-due-to-conflict-with-recovery-td3402417.html
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.
On Thu, May 12, 2011 at 11:26:38AM -0700, bubba postgres wrote: > I would just like to get some clarification from the list on how to do a > pg_dump on the slave in the face of "canceling statement due to conflict > with recovery". > The following links seem to indicate that If I start an idle transaction on > the master I should be able to do the pg_dump, but I tried this in psql on > the master "start transaction", and was still unable to do a pg_dump on the > slave at the same time. > Is there something special about using dblink that would make this all work? Could you define what you mean by "unable to do pg_dump on the slave"? I don't see why dblink would be the special thing. I think what you want is to hold a transaction open on the master so that the WAL can't get recycled. At least, that's what I understood from the post. I haven't actually tried it yet, but to me it sounded like it ought to work. A -- Andrew Sullivan ajs@crankycanuck.ca
What I mean is if I do pg_dump on slave I get the " ERROR: canceling statement due to conflict with recovery".
So I googled and tried the solution listed in the linked thread.
I did a "start transaction" via psql on the master but I continued to get the error.
Wondered if there was more to it than that.
So I googled and tried the solution listed in the linked thread.
I did a "start transaction" via psql on the master but I continued to get the error.
Wondered if there was more to it than that.
On Thu, May 12, 2011 at 5:08 PM, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
On Thu, May 12, 2011 at 11:26:38AM -0700, bubba postgres wrote:Could you define what you mean by "unable to do pg_dump on the slave"?
> I would just like to get some clarification from the list on how to do a
> pg_dump on the slave in the face of "canceling statement due to conflict
> with recovery".
> The following links seem to indicate that If I start an idle transaction on
> the master I should be able to do the pg_dump, but I tried this in psql on
> the master "start transaction", and was still unable to do a pg_dump on the
> slave at the same time.
> Is there something special about using dblink that would make this all work?
I don't see why dblink would be the special thing. I think what you
want is to hold a transaction open on the master so that the WAL can't
get recycled. At least, that's what I understood from the post. I
haven't actually tried it yet, but to me it sounded like it ought to
work.
A
--
Andrew Sullivan
ajs@crankycanuck.ca
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
What is your "max_standby_streaming_delay" set at?
If your pg_dump takes longer than your "max_standby_streaming_delay" (which is likely since the default is 30s), you might get that error as well. This setting tells your standby how long it should wait to apply conflicting WAL files to finish a particular transaction.
-Dan
On Fri, May 13, 2011 at 11:28 AM, bubba postgres <bubba.postgres@gmail.com> wrote:
What I mean is if I do pg_dump on slave I get the " ERROR: canceling statement due to conflict with recovery".
So I googled and tried the solution listed in the linked thread.
I did a "start transaction" via psql on the master but I continued to get the error.
Wondered if there was more to it than that.On Thu, May 12, 2011 at 5:08 PM, Andrew Sullivan <ajs@crankycanuck.ca> wrote:On Thu, May 12, 2011 at 11:26:38AM -0700, bubba postgres wrote:Could you define what you mean by "unable to do pg_dump on the slave"?
> I would just like to get some clarification from the list on how to do a
> pg_dump on the slave in the face of "canceling statement due to conflict
> with recovery".
> The following links seem to indicate that If I start an idle transaction on
> the master I should be able to do the pg_dump, but I tried this in psql on
> the master "start transaction", and was still unable to do a pg_dump on the
> slave at the same time.
> Is there something special about using dblink that would make this all work?
I don't see why dblink would be the special thing. I think what you
want is to hold a transaction open on the master so that the WAL can't
get recycled. At least, that's what I understood from the post. I
haven't actually tried it yet, but to me it sounded like it ought to
work.
A
--
Andrew Sullivan
ajs@crankycanuck.ca
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general