Re: BUG #15401: pg_dump error

On Tue, Sep 25, 2018 at 9:24 PM, PG Bug reporting form
<noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference:      15401
> Logged by:          prakash
> Email address:      prakash.ramakrishnan.ap@nielsen.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