pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFrom: Viral Shah <viralshah009@gmail.com>
To: Rene Romero Benavides <rene.romero.b@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: pg_dump fails when a table is in ACCESS SHARE MODE
Date: Tue, 25 Feb 2020 10:45:30 -0500
Message-ID: <CAEVFvu3UgFD919fW+XdfFMUq_LofD_1EhACd_CL6rt6eNqGg0w@mail.gmail.com> (raw)
In-Reply-To: <CANaGW086GF5qioi8=R0p6-Hh=Y-p4gsxr4FCYi9eHC5TzCgr+A@mail.gmail.com>
References: <CAEVFvu0aY4qgxVtMJvPTqty0pmL-jqsbsY9DkmA8REDbQtwKcw@mail.gmail.com>
<CANaGW086GF5qioi8=R0p6-Hh=Y-p4gsxr4FCYi9eHC5TzCgr+A@mail.gmail.com>
Hello Rene,
Since I am using pg_wal_replay_pause before my pg_dump, it doesn't throw
any error. Instead, it waits for the resource to release the lock so that
it can proceed with taking the dump. the hot_standby_feedback is set to *on*
on my replica and primary.
Thanks,
Viral Shah
Data Analyst, Nodal Exchange LLC
viralshah009@gmail.com | (240) 645 7548
On Tue, Feb 25, 2020 at 10:36 AM Rene Romero Benavides <
rene.romero.b@gmail.com> wrote:
>
> On Tue, Feb 25, 2020 at 9:11 AM Viral Shah <viralshah009@gmail.com> wrote:
>
>> Hello All,
>>
>> I am facing some problems while taking logical backup of my database
>> using pg_dump. We have set up our infrastructure such that the logical
>> backups are taken on our DR. Our intention here is to avoid substantial
>> load on our primary server. We also pause and play wal files using
>> pg_wal_replay_pause and pg_wal_replay_resume before and after pg_dump.
>> However, at the time of pausing the wal replay, if there is a table under
>> ACCESS SHARE MODE, the pg_dump cannot complete the backup.
>>
>> Can anyone suggest any better solution to take a logical backup using
>> pg_dump where the table lock doesn't result in failure of the logical
>> backup?
>>
>> PS: we are using PostgreSQL 10.12
>>
>> Thanks,
>> Viral Shah
>> Data Analyst, Nodal Exchange LLC
>> viralshah009@gmail.com | (240) 645 7548
>>
>
> Hi Viral, what's the error pg_dump is throwing at you ? what's the current
> setting of standby_feedback in your replica?
> --
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
>
>
>
view thread (11+ messages) latest in thread
Message-ID: <CAEVFvu3UgFD919fW+XdfFMUq_LofD_1EhACd_CL6rt6eNqGg0w@mail.gmail.com>
Permalink: ../CAEVFvu3UgFD919fW+XdfFMUq_LofD_1EhACd_CL6rt6eNqGg0w@mail.gmail.com/
Also on: postgresql.org/message-id/CAEVFvu3UgFD919fW+XdfFMUq_LofD_1EhACd_CL6rt6eNqGg0w@mail.gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: viralshah009@gmail.com, rene.romero.b@gmail.com
Subject: Re: pg_dump fails when a table is in ACCESS SHARE MODE
In-Reply-To: <CAEVFvu3UgFD919fW+XdfFMUq_LofD_1EhACd_CL6rt6eNqGg0w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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