Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1j6cPF-0002or-Ks for pgsql-sql@arkaria.postgresql.org; Tue, 25 Feb 2020 15:45:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j6cPD-0001s4-AD for pgsql-sql@arkaria.postgresql.org; Tue, 25 Feb 2020 15:45:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j6cPD-0001q6-0l for pgsql-sql@lists.postgresql.org; Tue, 25 Feb 2020 15:45:55 +0000 Received: from mail-ed1-x541.google.com ([2a00:1450:4864:20::541]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1j6cOz-0005qm-T1 for pgsql-sql@postgresql.org; Tue, 25 Feb 2020 15:45:54 +0000 Received: by mail-ed1-x541.google.com with SMTP id t7so16746236edr.4 for ; Tue, 25 Feb 2020 07:45:41 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=KOZtv4icLI+w4vEhe5oTp9Tg89AZoXyeSjQBochwpuQ=; b=vg4fsMKC8Nhd5/6quIjfI4Y9kuJF32jnHWXoqBE6HS4YVzrDXaP4jwzkIX1qO036PU x+BSqEGx8+mtW0/bnO31hdRoE8h4aHMNLsOPeXwO6tERyhu/RwMUv/5G6IQsLLuZIwkb AwcnJl70o4VkW6MYRE78v11JFNT7spPfeNnjh8n2QLeONscXeE7LaVEdNdRL8Bda/0uw E1cDwviRK6G3eOpo5PG1+G2rmgzg0AaYf1DVe1PA8H15sDwVfpS60kF4smta9SgVSCcJ ISkYH41vErdVj8csfUgueKwBh3GZKiIujw9ipB+vkO+11de1wQNEVDwfWOdwmo/Y34kc xX6Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=KOZtv4icLI+w4vEhe5oTp9Tg89AZoXyeSjQBochwpuQ=; b=WfjwKIx290kjEgN8Rmq8dkT589TIjvX8cLfIPseGYu5E5jI/cF7lhtI0cbyUijd5se gnjV0hOHjlg7/IV+/WXCjX8VMI/vY8cKj8s+z1/esuTH8ngQy9nXj5x01EH8m5QsLl0A JChH2iELsMSOSnG83U8xSXvFXCeyE/kaqRoDRKbwvxQ7UPwufPBO47QS8jFLrkGu304U 7mXHXq17/GVF9bdwFwkUAGFKLGzfPK/Tk/XQFh4CuJSwDqVQy/8YA0c/48l5NePtPniA Z6MCXJzynZ/XD65m+/6p1VV9f7oHUetsedhEK0hEZp7cXxh5jfKSeJMhosUQ+jyrg18D oKRg== X-Gm-Message-State: APjAAAVkowrqE9yYR+70dSr9zcd4okBsunwAWbOdsjirr8D8LbQ6Cr9D YGFrDjz4AOLNMLX/Zc8YgWh9b73dhzTbMOXiylk= X-Google-Smtp-Source: APXvYqyNof5pGkmMHpA16snya1kPAuFCe03aH9RzY+nhZn+eIahSyz0YJcT+WwDiW9Knozrv3Z1DeUZDRTTmawxKLZE= X-Received: by 2002:a50:ce56:: with SMTP id k22mr53017481edj.34.1582645541214; Tue, 25 Feb 2020 07:45:41 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Viral Shah Date: Tue, 25 Feb 2020 10:45:30 -0500 Message-ID: Subject: Re: pg_dump fails when a table is in ACCESS SHARE MODE To: Rene Romero Benavides Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary="0000000000004a1d2a059f6861c1" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --0000000000004a1d2a059f6861c1 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 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 unde= r >> 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 curren= t > setting of standby_feedback in your replica? > -- > El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n. > Thomas Alva Edison > > > --0000000000004a1d2a059f6861c1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello Rene,

Since I am using pg_wal_replay_pause b= efore 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=C2=A0on
on my replica a= nd primary.

Thanks,
Viral Shah
Data Analyst, Nodal Exchang= e LLC
viralshah009@gmail.com |=C2=A0(240) 64= 5 7548

<= /div>
O= n Tue, Feb 25, 2020 at 10:36 AM Rene Romero Benavides <rene.romero.b@gmail.com> wrote:
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">

On Tue, Feb 25, 2020 at 9:11 AM Viral Shah <viralshah009@gmail.com> wro= te:
Hello All,

I am facing some problems while taking l= ogical backup of my database using pg_dump. We have set up our infrastructu= re 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 wa= l 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 tab= le under ACCESS SHARE MODE, the pg_dump cannot complete the backup.=C2=A0

Can anyone suggest any better solution to take a lo= gical backup using pg_dump where the table lock doesn't result in failu= re of the logical backup?

PS: we are using Postgre= SQL 10.12

Thanks,=C2=A0
Viral Shah
Data Analyst, No= dal Exchange LLC
viralshah009@gmail.com |=C2=A0(240) 645 7548

Hi Viral, what's the error pg= _dump is throwing at you ? what's the current setting of standby_feedba= ck in your replica?=C2=A0
--
El genio es 1% inspi= raci=C3=B3n y 99% transpiraci=C3=B3n.
Thomas Alva Edison


--0000000000004a1d2a059f6861c1--