pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump on a standby for a very active master
7+ messages / 3 participants
[nested] [flat]
* pg_dump on a standby for a very active master
@ 2019-02-12 00:44 Arjun Ranade <ranade@nodalexchange.com>
0 siblings, 1 reply; 7+ messages in thread
From: Arjun Ranade @ 2019-02-12 00:44 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* pg_dump on a standby for a very active master
@ 2019-02-12 16:32 Arjun Ranade <ranade@nodalexchange.com>
parent: Arjun Ranade <ranade@nodalexchange.com>
0 siblings, 1 reply; 7+ messages in thread
From: Arjun Ranade @ 2019-02-12 16:32 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: pg_dump on a standby for a very active master
@ 2019-02-12 17:36 Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com>
parent: Arjun Ranade <ranade@nodalexchange.com>
0 siblings, 1 reply; 7+ messages in thread
From: Scot Kreienkamp @ 2019-02-12 17:36 UTC (permalink / raw)
To: Arjun Ranade <ranade@nodalexchange.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_dump on a standby for a very active master
@ 2019-02-12 17:59 Stephen Frost <sfrost@snowman.net>
parent: Arjun Ranade <ranade@nodalexchange.com>
0 siblings, 1 reply; 7+ messages in thread
From: Stephen Frost @ 2019-02-12 17:59 UTC (permalink / raw)
To: Arjun Ranade <ranade@nodalexchange.com>; +Cc: Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_dump on a standby for a very active master
@ 2019-02-12 18:02 Arjun Ranade <ranade@nodalexchange.com>
parent: Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com>
0 siblings, 1 reply; 7+ messages in thread
From: Arjun Ranade @ 2019-02-12 18:02 UTC (permalink / raw)
To: Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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.
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_dump on a standby for a very active master
@ 2019-02-12 18:06 Stephen Frost <sfrost@snowman.net>
parent: Arjun Ranade <ranade@nodalexchange.com>
0 siblings, 0 replies; 7+ messages in thread
From: Stephen Frost @ 2019-02-12 18:06 UTC (permalink / raw)
To: Arjun Ranade <ranade@nodalexchange.com>; +Cc: Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_dump on a standby for a very active master
@ 2019-02-12 18:18 Arjun Ranade <ranade@nodalexchange.com>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 7+ messages in thread
From: Arjun Ranade @ 2019-02-12 18:18 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2019-02-12 18:18 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-02-12 00:44 pg_dump on a standby for a very active master Arjun Ranade <ranade@nodalexchange.com>
2019-02-12 16:32 ` Arjun Ranade <ranade@nodalexchange.com>
2019-02-12 17:36 ` Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com>
2019-02-12 18:02 ` Arjun Ranade <ranade@nodalexchange.com>
2019-02-12 17:59 ` Stephen Frost <sfrost@snowman.net>
2019-02-12 18:18 ` Arjun Ranade <ranade@nodalexchange.com>
2019-02-12 18:06 ` Stephen Frost <sfrost@snowman.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox