MIME-Version: 1.0
References: <7bcef1b5-6e29-4eea-8c85-23a80dab4e2c@cloud.gatewaynet.com>
In-Reply-To: <7bcef1b5-6e29-4eea-8c85-23a80dab4e2c@cloud.gatewaynet.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Mon, 30 Jun 2025 03:47:17 -0400
Message-ID: 
 <CANzqJaAyOdxH=SArXzfD5gWKmMqsKB8VPi0d=XnOg_QJnS4bLw@mail.gmail.com>
Subject: Re: Fast Logical replication setup, via VM clone , PostgreSQL 16.9
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000004813dc0638c53d40"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaAyOdxH%3DSArXzfD5gWKmMqsKB8VPi0d%3DXnOg_QJnS4bLw%40mail.gmail.com>
Precedence: bulk

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

On Mon, Jun 30, 2025 at 3:36=E2=80=AFAM Achilleas Mantzios <
a.mantzios@cloud.gatewaynet.com> wrote:

> Hi,
>
> I gotta provide again a logical repl subscriber for our devs, we are
> running PostgreSQL 16.9 .
>
> Instead of going the traditional logical replication way (which involves
> long running COPY, catchup, etc), I am thinking of doing something along
> the lines :
>
> 1) @publisher (master) create repl slot, create publication
>
> 2) shutdown postgresql ,
>
> 3) clone the VM,
>

"We" (not me, but the ESX Admin team) takes a snapshot of the VM (including
all mount points) every day.

About 5 years ago, "OMG we dropped a table, and need it restored ASAP, but
can't stop other production."

Because we use PgBackRest, it's not possible to restore one table in one
database, and since it's a 5TB instance,  restoring to a new disk would
take time.  The simplest solution was to restore the appropriate VM
snapshot to a new VM.

That worked like a charm.  "pg_ctl start -wt9999" on the new VM recovered
all open transactions, and I could access the relevant table.

IOW, you might just need to:
1) Take a snapshot of the primary VM.
2) Restore that snapshot to a new VM.

It's not too dissimilar from a crash and restart.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Jun 30, 2025 at 3:36=E2=80=AFAM A=
chilleas Mantzios &lt;<a href=3D"mailto:a.mantzios@cloud.gatewaynet.com">a.=
mantzios@cloud.gatewaynet.com</a>&gt; wrote:</div><div class=3D"gmail_quote=
 gmail_quote_container"><blockquote class=3D"gmail_quote" style=3D"margin:0=
px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">H=
i,<br>
<br>
I gotta provide again a logical repl subscriber for our devs, we are <br>
running PostgreSQL 16.9 .<br>
<br>
Instead of going the traditional logical replication way (which involves <b=
r>
long running COPY, catchup, etc), I am thinking of doing something along <b=
r>
the lines :<br>
<br>
1) @publisher (master) create repl slot, create publication<br>
<br>
2) shutdown postgresql ,<br>
<br>
3) clone the VM,<br></blockquote></div><div><br></div><div>&quot;We&quot; (=
not me, but the ESX Admin team) takes a snapshot of the=C2=A0VM (including =
all mount points) every day.</div><div><br></div><div>About 5 years ago, &q=
uot;OMG we dropped a table, and need it restored ASAP, but can&#39;t stop o=
ther production.&quot;</div><div><br></div><div>Because we use PgBackRest, =
it&#39;s not possible to restore one table in one database,=C2=A0and since =
it&#39;s a 5TB=C2=A0instance,=C2=A0 restoring to a new=C2=A0disk would take=
 time.=C2=A0 The simplest solution was to restore the appropriate VM snapsh=
ot to a new VM.</div><div><br></div><div>That worked like a charm.=C2=A0 &q=
uot;pg_ctl start -wt9999&quot; on the new VM recovered all open transaction=
s, and I could access the relevant table.</div><div><br></div><div>IOW, you=
 might just need to:</div><div>1) Take a snapshot of the primary VM.</div><=
div>2) Restore that snapshot to a new VM.</div><div><br></div><div>It&#39;s=
 not too dissimilar from a crash and restart.</div><div><br></div><span cla=
ss=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail=
_signature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<d=
iv>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lo=
bster!</div></div></div></div></div></div>

--0000000000004813dc0638c53d40--