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@xxxxxxxxxxxxFrom: Arjun Ranade [mailto:ranade@xxxxxxxxxxxxxxxxx]
Sent: Tuesday, February 12, 2019 11:33 AM
To: pgsql-general@xxxxxxxxxxxxxxxxxxxx
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 preventwal_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.
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.
On Tue, Feb 12, 2019 at 12:36 PM Scot Kreienkamp <Scot.Kreienkamp@xxxxxxxxxxxx> wrote:
