Stuart Bishop | 14 Aug 13:34 2012
Picon

pg_dump on hot standby canceled despite hot_standby_feedback=on

Hi.

I've found a situation on one of my PG 9.1 servers where pg_dump
running on a hot standby gets terminated when a tble on the master is
vacuumed. This is PostgreSQL 9.1.4, and purely streaming replication.

pg_dump: Error message from server: ERROR:  canceling statement due to
conflict with recovery
DETAIL:  User was holding shared buffer pin for too long.
pg_dump: The command was: COPY public.webcatalog_machine (id,
owner_id, uuid, hostname, packages_checksum, package_list,
logo_checksum) TO stdout;
pg_dump: *** aborted because of error

hot_standby_feedback is on, and my understanding is that this should
instruct the master that there is still an open transaction and vacuum
should not clean stuff up that is still in use on the hot standby.
Replication is otherwise working flawlessly, and I've confirmed that
the walstreamer has been alive the whole time.

The pg_dump works when no vacuum kicks in, but I have reproduced the
fault by manually running vacuum on the master once the pg_dump has
started on this larger table.

I think I must be missing something, as I don't see this on my other
servers. This database isn't particularly large, with pg_dump
finishing in a few minutes. I'm successfully using pg_dump on other
hot standbys that take half a day to dump with tables active enough
that they certainly should have triggered autovacuums.

(Continue reading)

Stuart Bishop | 7 Sep 08:39 2012
Picon

Re: pg_dump on hot standby canceled despite hot_standby_feedback=on

I'm still getting my pg_dumps on the 9.1 hot standby cancelled
occasionally, despite hot_standby_feedback being set.
pg_stat_replication tells me the replication connection is not being
reset or anything.

The last one was:
pg_dump: Error message from server: ERROR:  canceling statement due to
conflict with recovery
DETAIL:  User was holding a relation lock for too long.

Can anyone shed some insight? My understanding of hot_standby_feedback
is that it should make this sort of query cancellation never happen.

On Tue, Aug 14, 2012 at 6:34 PM, Stuart Bishop <stuart <at> stuartbishop.net> wrote:
> Hi.
>
> I've found a situation on one of my PG 9.1 servers where pg_dump
> running on a hot standby gets terminated when a tble on the master is
> vacuumed. This is PostgreSQL 9.1.4, and purely streaming replication.
>
> pg_dump: Error message from server: ERROR:  canceling statement due to
> conflict with recovery
> DETAIL:  User was holding shared buffer pin for too long.
> pg_dump: The command was: COPY public.webcatalog_machine (id,
> owner_id, uuid, hostname, packages_checksum, package_list,
> logo_checksum) TO stdout;
> pg_dump: *** aborted because of error
>
> hot_standby_feedback is on, and my understanding is that this should
> instruct the master that there is still an open transaction and vacuum
(Continue reading)


Gmane