From: | Arjun Ranade <ranade(at)nodalexchange(dot)com> |
---|---|
To: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | pg_dump on a standby for a very active master |
Date: | 2019-02-12 00:44:17 |
Message-ID: | CANrrCRwwUOj4d6iaXtbmOk02NBKdjLF2XuqBNyZRRttT1iBsgA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Shreeyansh Dba | 2019-02-12 06:02:13 | Re: installation of postgres 10.6 |
Previous Message | Peter Tormey | 2019-02-11 22:49:35 | Re: Calculating mem requirements for parallel queries and Docker and coredumps! |
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2019-02-12 03:17:33 | Re: Promote replica before being able to accept connections |
Previous Message | Ron | 2019-02-12 00:42:44 | Re: Blank, nullable date column rejected by psql |