Subject: Fast Logical replication setup, via VM clone , PostgreSQL 16.9

From: Achilleas Mantzios <[email-masked]>
Date: Mon, 30 Jun 2025 08:36:13 +0100

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,

4) boot the clone (subscriber),

5) @subscriber start postgresql , drop publication, drop replication 
slot, create the subscription using repl slot of 1)

6) @master start postgresql .

or a version with less downtime for the publisher (aka master , primary) :

1) @publisher (master) create repl slot, create publication

2) shutdown postgresql ,

3) clone the VM,

4) start master,

5) boot the clone (subscriber),

6) @subscriber start postgresql , drop the publication, drop the 
replication slot, create the subscription using repl slot of 1)

do you find any gotchas in the above ?

---

From: Ron Johnson <[email-masked]>
Date: Mon, 30 Jun 2025 03:47:17 -0400

> 1) @publisher (master) create repl slot, create publication

"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.

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

---

From: Achilleas Mantzios <[email-masked]>
Date: Mon, 30 Jun 2025 09:15:50 +0100

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

> On Mon, Jun 30, 2025 at 3:36 AM Achilleas Mantzios 
> <[email-masked]> wrote:
>
>     Hi,
>
> "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.
>
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.

---

From: Achilleas Mantzios <[email-masked]>
Date: Mon, 30 Jun 2025 16:08:52 +0100

On 6/30/25 08:36, Achilleas Mantzios wrote:

> Instead of going the traditional logical replication way (which 
> involves long running COPY, catchup, etc), I am thinking of doing 
> something along the lines :
>
It seems I missed the fact that between 1) and 2) there could be INSERTs 
that are both logged in the slot and also in the data files. This will 
create conflicts upon creating the SUBSCRIPTION on steps 5) or 6) 
respectively. Unless I prohibit any connections before step 1)