MIME-Version: 1.0
References: <CAG4TxrgqofFj1_LGT29DUwzn_LFLJO7MUO5sCWk620ctin00jg@mail.gmail.com>
 <CAKAnmmKsgLmBOJQQouOFUHeEMjSCouK_dqx+xhdUNZ58UsUq3w@mail.gmail.com>
In-Reply-To: <CAKAnmmKsgLmBOJQQouOFUHeEMjSCouK_dqx+xhdUNZ58UsUq3w@mail.gmail.com>
From: Radu Radutiu <rradutiu@gmail.com>
Date: Mon, 10 Nov 2025 17:49:00 +0200
Message-ID: <CAG4TxrjyMA9G4GtVWGLhQOTX_yHTYQig+Xj=ytxOmA_Liph7QA@mail.gmail.com>
Subject: Re: Pgbackrest setup for two sites with limited bandwidth
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000008de0706433f79d6"
Archived-At: <https://www.postgresql.org/message-id/CAG4TxrjyMA9G4GtVWGLhQOTX_yHTYQig%2BXj%3DytxOmA_Liph7QA%40mail.gmail.com>
Precedence: bulk

--00000000000008de0706433f79d6
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Thank you. I was thinking along the same lines, but I am not sure I
understand why the setup will fall apart on failover. If I also have
archive_mode always on the primary site, I think that I just need to
pg_rewind (or PITR restore) old primary and configure it as a secondary to
the new master followed by a local backup. Am I missing something obvious?

Regards,
Radu

On Mon, Nov 10, 2025 at 5:33=E2=80=AFPM Greg Sabino Mullane <htamfids@gmail=
.com>
wrote:

> What you are asking for is tricky, but possible. You can setup two repos,
> one for each server. On the first server (in datacenter A), you have a
> "normal" pgbackrest configuration, with an explicit backup-standby=3Dn. p=
g1-*
> points to the local server, pg2-* points to the replica. The repo is in
> datacenter A.Backups run from PG server in datacenter A, to the backrest
> repo in datacenter A. WAL is archived from A -> A
>
> On datacenter B, you have a separate local repo, a different stanza name,
> and within that stanza, you have backup-standby=3Dy, pg1 pointing to the
> local pg, pg2 pointing back to A. In the postgresql.conf, your
> archive-command pushes to this second repo, and archive_mode is "always".
> Backups run from the PG server in datacenter A, but 99% of the files are
> actually copied from the replica in datacenter B, and goes to the backres=
t
> server in datacenter B. WAL is archived from B -> B.
>
> That's the basic idea. It falls apart rapidly if this second data center
> ever fails over.
> Cheers,
> Greg
>
> --
> Crunchy Data - https://www.crunchydata.com
> Enterprise Postgres Software Products & Tech Support
>
>

--00000000000008de0706433f79d6
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Thank you. I was thinking along=C2=A0the same lines, =
but I am not sure I understand why the setup will fall apart on failover. I=
f I also have archive_mode always on the primary site, I think that I just =
need to pg_rewind (or PITR restore) old primary and configure it as a secon=
dary to the new master followed by a local backup. Am I=C2=A0missing someth=
ing obvious?</div><div dir=3D"ltr"><div><br></div><div>Regards,</div><div>R=
adu</div></div></div><br><div class=3D"gmail_quote gmail_quote_container"><=
div dir=3D"ltr" class=3D"gmail_attr">On Mon, Nov 10, 2025 at 5:33=E2=80=AFP=
M Greg Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfids@gm=
ail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"=
margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-lef=
t:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><div>What you are asking for is tr=
icky, but possible. You can setup two repos, one for each server. On the fi=
rst server (in datacenter A), you have a &quot;normal&quot; pgbackrest conf=
iguration, with an explicit backup-standby=3Dn. pg1-* points to the local s=
erver, pg2-* points to the replica. The repo is in datacenter A.Backups run=
 from PG server in datacenter A, to the backrest repo in datacenter A. WAL =
is archived=C2=A0from A -&gt; A</div><div><br></div><div>On datacenter B, y=
ou have a separate=C2=A0local repo, a different stanza name, and within tha=
t stanza, you have backup-standby=3Dy, pg1 pointing to the local pg, pg2 po=
inting back to A. In the postgresql.conf, your archive-command pushes to th=
is second repo, and archive_mode is &quot;always&quot;. Backups run=C2=A0fr=
om the PG server in datacenter A, but 99% of the files are actually=C2=A0co=
pied from the replica in datacenter B, and goes to the backrest server in d=
atacenter B. WAL is archived from B -&gt; B.</div><div><br></div><div>That&=
#39;s the basic idea. It falls apart rapidly if this second data center eve=
r fails over.</div><div>Cheers,</div></div><div dir=3D"ltr" class=3D"gmail_=
signature"><div dir=3D"ltr"><div>Greg</div><div><br></div><div>--</div><div=
>Crunchy Data - <a href=3D"https://www.crunchydata.com" target=3D"_blank">h=
ttps://www.crunchydata.com</a></div><div>Enterprise Postgres Software Produ=
cts &amp; Tech Support</div><div><br></div></div></div></div>
</blockquote></div>

--00000000000008de0706433f79d6--