On 6/30/25 08:47, Ron Johnson 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.
