pg_dump on a standby for a very active master

From: Arjun Ranade <ranade(at)nodalexchange(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: pg_dump on a standby for a very active master
Date: 2019-02-12 00:44:17
Message-ID: CANrrCRwwUOj4d6iaXtbmOk02NBKdjLF2XuqBNyZRRttT1iBsgA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

I have a Production machine which is having objects
dropped/created/truncated at all hours of the day (Read: No zero activity
window). I have multiple standbys (repmgr streaming replication) for this
machine including a cascading standby. Each night, I am attempting to take
a logical backup on the standby databases via pg_dump of key schemas.

Recently, due to the activity on the primary, pg_dump is failing on the
standby usually with "ERROR: could not obtain lock on relation."

I've had the following settings set in postgresql.conf which gave me
successful backups for a while:

hot_standby = on # "off" disallows queries during
recovery
max_standby_archive_delay = -1 # max delay before canceling queries
max_standby_streaming_delay = -1 # max delay before canceling queries
hot_standby_feedback = on # send info from standby to prevent
wal_receiver_timeout = 300s # time that receiver waits for

I have it set up this way because I don't mind any replication lag on the
standbys during the logical backup. However, recently logical backups have
been failing either due to a table dropped/truncated on the master.

Also, I use pg_dump with the parallel option in directory format. However,
even single threaded pg_dump fails when a table is truncated on the primary.

Is there any way to guarantee consistent logical backups on a standby
server with a master that has constant DDL/activity?

I am on Postgres 10.3; RHEL 7; 128gb RAM

Thanks,
Arjun

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Shreeyansh Dba 2019-02-12 06:02:13 Re: installation of postgres 10.6
Previous Message Peter Tormey 2019-02-11 22:49:35 Re: Calculating mem requirements for parallel queries and Docker and coredumps!

Browse pgsql-general by date

  From Date Subject
Next Message Michael Paquier 2019-02-12 03:17:33 Re: Promote replica before being able to accept connections
Previous Message Ron 2019-02-12 00:42:44 Re: Blank, nullable date column rejected by psql