On Thu, Feb 14, 2013 at 7:21 AM, Sergey Konoplev <gray.ru@xxxxxxxxx> wrote: > On Wed, Feb 13, 2013 at 12:53 AM, Stuart Bishop <stuart@xxxxxxxxxxxxxxxx> 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 = 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: User query might have needed to see row versions that must be removed. DETAIL: User 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 <=9.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. -- Stuart Bishop <stuart@xxxxxxxxxxxxxxxx> http://www.stuartbishop.net/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
