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.kreienk...@la-z-boy.com> wrote: > 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.kreienk...@la-z-boy.com > > *From:* Arjun Ranade [mailto:ran...@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. >
