Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uW9gF-0091nC-8u for pgsql-admin@arkaria.postgresql.org; Mon, 30 Jun 2025 08:15:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uW9gC-00FE8k-KA for pgsql-admin@arkaria.postgresql.org; Mon, 30 Jun 2025 08:15:57 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uW9gC-00FE8c-8s for pgsql-admin@lists.postgresql.org; Mon, 30 Jun 2025 08:15:56 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by makus.postgresql.org with smtp (Exim 4.96) (envelope-from ) id 1uW9gA-004j8a-0H for pgsql-admin@lists.postgresql.org; Mon, 30 Jun 2025 08:15:56 +0000 Content-Type: multipart/alternative; boundary="------------czUWy0AFQVWt1tP5LRW97f6p" Message-ID: <96800d42-3399-4c29-aa4b-63a2e7f9156c@cloud.gatewaynet.com> Date: Mon, 30 Jun 2025 09:15:50 +0100 MIME-Version: 1.0 Subject: Re: Fast Logical replication setup, via VM clone , PostgreSQL 16.9 To: pgsql-admin@lists.postgresql.org References: <7bcef1b5-6e29-4eea-8c85-23a80dab4e2c@cloud.gatewaynet.com> Content-Language: en-US Cc: Achilleas Mantzios From: Achilleas Mantzios In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------czUWy0AFQVWt1tP5LRW97f6p Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable On 6/30/25 08:47, Ron Johnson wrote: > On Mon, Jun 30, 2025 at 3:36=E2=80=AFAM Achilleas Mantzios=20 > wrote: > > Hi, > > I gotta provide again a logical repl subscriber for our devs, we ar= e > 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=C2=A0VM=20 > (including all mount points) every day. > > About 5 years ago, "OMG we dropped a table, and need it restored ASAP,=20 > but can't stop other production." > > Because we use PgBackRest, it's not possible to restore one table in=20 > one database,=C2=A0and since it's a 5TB=C2=A0instance, restoring to a n= ew=C2=A0disk=20 > would take time.=C2=A0 The simplest solution was to restore the appropr= iate=20 > VM snapshot to a new VM. > > That worked like a charm.=C2=A0 "pg_ctl start -wt9999" on the new VM=20 > 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. If the VM snapshot is atomic on all filesystems, then postgresql on=20 starting up will see this as a crash and perform crash recovery, we've=20 done the same for years. But the question here is about setting up=20 logical replication as fast as possible, not disaster recovery. > > It's not too dissimilar from a crash and restart. > > --=20 > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! --------------czUWy0AFQVWt1tP5LRW97f6p Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

On 6/30/25 08:47, Ron Johnson wrote:

On Mon, Jun 30, 2025 at 3:36=E2=80=AFAM Achillea= s Mantzios <a.ma= ntzios@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=C2=A0VM (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,=C2=A0and since it's a 5TB=C2=A0instance,= =C2=A0 restoring to a new=C2=A0disk would take time.=C2=A0 The simples= t solution was to restore the appropriate VM snapshot to a new VM.

That worked like a charm.=C2=A0 "pg_ctl start -wt9999" on th= e 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.
If the VM snapshot is atomic on all filesystems, then postgresql on starting up will see this as a crash and perform crash recovery, we've done the same for years. But the question here is about setting up logical replication as fast as possible, not disaster recovery.

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

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