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 = 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.
Yo could try increasing max_standby_streaming_delay that will "pause"
applying WAL data on replica but it still does not guarantee
successful result and moreover could cause lagging far behind that is
not your goal.
> My goal is to reliably make pg_dump backups from a hot standby without
> the hot standby lagging far behind the master.
Also slave does not guarantee that it is always up-to-date. There
could be issues like network problems, etc.
Personally I recommend to do pg_dump on master at least on <=9.2.
--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp
Phones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com