Re: BUG #15401: pg_dump error

On Tue, Sep 25, 2018 at 9:24 PM, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 15401
> Logged by: prakash
> Email address: prakash(dot)ramakrishnan(dot)ap(at)nielsen(dot)com
> PostgreSQL version: 9.6.6
> Operating system: Red Hat Enterprise Linux Server release 7.4 (Maipo
> Description:
>
> Hi Team ,
>
> While am connecting the standby server to taking the backup getting below
> error.
>
> ==> pg_dump -h LHRRHENPPP002.enterprisenet.org -p 5432 -d AIUK01PR -U
> postgres -Fd -f /PGDATA/prakash_backup/AIUK01PR.sql
> Password:
> pg_dump: Dumping the contents of table "cmt_attributes" failed:
> PQgetResult() failed.
> pg_dump: Error message from server: ERROR: canceling statement due to
> conflict with recovery
> DETAIL: User was holding a relation lock for too long.
> pg_dump: The command was: COPY cmtroi_owner.cmt_attributes (id, cmt_id,
> user_id, insert_date, last_change_date, height, width, duration, dpi,
> filesize, codec) TO stdout;
>

Seems like the startup process on the standby need to lock the
relation in a conflicting mode which is already held by the pg_dump
process so it got cancelled due to recovery conflict.

I think you can change these parameters to control how long the
startup process will wait before cancelling the conflicting query.

#max_standby_archive_delay = 30s # max delay before canceling queries
# when reading WAL from archive;
# -1 allows indefinite delay
#max_standby_streaming_delay = 30s # max delay before canceling queries
# when reading streaming WAL;
# -1 allows indefinite delay

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com