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

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

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