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

On Mon, Jun 30, 2025 at 3:36 AM Achilleas Mantzios <
a(dot)mantzios(at)cloud(dot)gatewaynet(dot)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.

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