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

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 ?

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

On Mon, Jun 30, 2025 at 3:36 AM 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.

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!

On 6/30/25 08:36, Achilleas Mantzios 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,
>
> 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 ?
>
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)
>
>