Thread: pg_dump on a standby for a very active master

How about pausing replication while you’re running the backup?  I have a mirror dedicated to backups, it pauses replication by cron job every night before the backup, then resumes midday after I’ve had enough time to find out if the backup was successful. 

 

Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 7349151444 | Email: Scot.Kreienkamp@la-z-boy.com

From: Arjun Ranade [mailto:ranade@nodalexchange.com]
Sent: Tuesday, February 12, 2019 11:33 AM
To: pgsql-general@lists.postgresql.org
Subject: pg_dump on a standby for a very active master

 

ATTENTION:   This email was sent to La-Z-Boy from an external source.     Be vigilant when opening attachments or clicking links.

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

 

This message is intended only for the individual or entity to which it is addressed.  It may contain privileged, confidential information which is exempt from disclosure under applicable laws.  If you are not the intended recipient, you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information.  If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.

Greetings,

* Arjun Ranade (ranade@nodalexchange.com) wrote:
> Yeah, that was one thing I was planning to try.  The other potential
> solution is to use barman (we are using barman on all db servers including
> standbys) to restore the latest backup to a VM and then take the pg_dump
> from there.  But I was hoping there would be a way in the settings to
> prevent such a workaround.

Performing a file-level backup and then restoring that and then
taking a pg_dump of restored cluster works quite well as a solution, in
my experience, even better is when you can do a delta restore over top
of the prior restore, updating just the files which were different, as
that can be much faster.

Thanks!

Stephen