From: | Arjun Ranade <ranade(at)nodalexchange(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | pg_dump on a standby for a very active master |
Date: | 2019-02-12 16:32:54 |
Message-ID: | CANrrCRyGHDkRo+phd-3nyKRBcHtNAHPwtkJi1RDD8yhRCQzVpQ@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 | Scot Kreienkamp | 2019-02-12 17:36:08 | RE: pg_dump on a standby for a very active master |
Previous Message | David G. Johnston | 2019-02-12 16:30:41 | Re: insert data with invalid input syntax for integer:"42P01" |
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2019-02-12 16:39:52 | Re: Out of memory: Kill process nnnn (postmaster) score nn or sacrifice child |
Previous Message | pabloa98 | 2019-02-12 16:31:11 | Re: postgresql v11.1 Segmentation fault: signal 11: by running SELECT... JIT Issue? |