Thread: pg_dump on a standby for a very active master

pg_dump on a standby for a very active master

From
Arjun Ranade
Date:
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

RE: pg_dump on a standby for a very active master

From
Scot Kreienkamp
Date:

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.

Re: pg_dump on a standby for a very active master

From
Stephen Frost
Date:
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

Attachment

Re: pg_dump on a standby for a very active master

From
Arjun Ranade
Date:
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:

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.

Re: pg_dump on a standby for a very active master

From
Stephen Frost
Date:
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

Attachment

Re: pg_dump on a standby for a very active master

From
Arjun Ranade
Date:
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:
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