pg_dump on a standby for a very active master

From: Arjun Ranade <ranade(at)nodalexchange(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: pg_dump on a standby for a very active master
Date: 2019-02-12 16:32:54
Message-ID: CANrrCRyGHDkRo+phd-3nyKRBcHtNAHPwtkJi1RDD8yhRCQzVpQ@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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Scot Kreienkamp 2019-02-12 17:36:08 RE: pg_dump on a standby for a very active master
Previous Message David G. Johnston 2019-02-12 16:30:41 Re: insert data with invalid input syntax for integer:"42P01"

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2019-02-12 16:39:52 Re: Out of memory: Kill process nnnn (postmaster) score nn or sacrifice child
Previous Message pabloa98 2019-02-12 16:31:11 Re: postgresql v11.1 Segmentation fault: signal 11: by running SELECT... JIT Issue?