Re: pg_dump on a standby for a very active master

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(dot)Kreienkamp(at)la-z-boy(dot)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(dot)Kreienkamp(at)la-z-boy(dot)com
>
> *From:* Arjun Ranade [mailto:ranade(at)nodalexchange(dot)com]
> *Sent:* Tuesday, February 12, 2019 11:33 AM
> *To:* pgsql-general(at)lists(dot)postgresql(dot)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.
>