pg_dump on a standby for a very active master

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

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

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@la-z-boy.com> wrote:

Greetings,

* Arjun Ranade (ranade@nodalexchange.com) wrote:

Will barman automatically do a delta restore assuming the postgres server
is stopped and the old cluster exists at the same location it's restoring
to?

I don't know if barman supports that today, it might. I do know that
pgbackrest supports delta restores with the --delta option.

Thanks!

Stephen

Will barman automatically do a delta restore assuming the postgres server
is stopped and the old cluster exists at the same location it's restoring
to?

On Tue, Feb 12, 2019 at 12:59 PM Stephen Frost <sfrost@snowman.net> wrote: