On Thu, Feb 14, 2013 at 7:21 AM, Sergey Konoplev <gray.ru@gmail.com> wrote:
> On Wed, Feb 13, 2013 at 12:53 AM, Stuart Bishop <stuart@stuartbishop.net>=
wrote:
>> I'm unable to offload my backups to one of my PG 9.1 hot standbys
>> using purely streaming replication. After a few hours, usually on the
>> same large table, pg_dump is failing with 'ERROR: canceling statement
>> due to conflict with recovery'.
>>
>> From my reading from the documentation, this should not be possible as
>> my hot standby has 'hot_standby_feedback =3D on' in its postgresql.conf.
>
> hot_standby_feedback affects VACUUM only to prevent it from removing
> dead rows on master that might cause the cleanup conflict. It has no
> deal with other hard conflicts like in case of DROP TABLE etc.
I can confirm that no DDL is being run (apart from temporary tables).
I can also confirm that the replication connection does not drop out.
I can't think of what else would be causing problems apart from
vacuum, and I used vacuum to trigger the problem in the bug report
test case ( http://postgresql.1045698.n5.nabble.com/BUG-7546-Backups-on-hot=
-standby-cancelled-despite-hot-standby-on-td5724284.html
)
Something that might be interesting that I neglected to mention, the
DETAIL of the error message is random; on production my failures end
up with one of these three:
DETAIL: =C2=A0User query might have needed to see row versions that must be=
removed.
DETAIL: =C2=A0User was holding a relation lock for too long.
DETAIL: User was holding shared buffer pin for too long.
> Personally I recommend to do pg_dump on master at least on <=3D9.2.
Anything in particular in 9.2? I've been seeing a lot of replication
related fixes in 9.1 patch releases and had been planning on sticking
with 9.1 for the next 18 months.
I'm still unsure if this is supposed to work, and this is a bug in
PostgreSQL or Ubuntu, or if I'm just misreading the documentation.
--=20
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/