public inbox for [email protected]help / color / mirror / Atom feed
Fast Logical replication setup, via VM clone , PostgreSQL 16.9 4+ messages / 2 participants [nested] [flat]
* Fast Logical replication setup, via VM clone , PostgreSQL 16.9 @ 2025-06-30 07:36 Achilleas Mantzios <[email protected]> 0 siblings, 2 replies; 4+ messages in thread From: Achilleas Mantzios @ 2025-06-30 07:36 UTC (permalink / raw) To: [email protected] 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 ? ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Fast Logical replication setup, via VM clone , PostgreSQL 16.9 @ 2025-06-30 07:47 Ron Johnson <[email protected]> parent: Achilleas Mantzios <[email protected]> 1 sibling, 1 reply; 4+ messages in thread From: Ron Johnson @ 2025-06-30 07:47 UTC (permalink / raw) To: Pgsql-admin <[email protected]> On Mon, Jun 30, 2025 at 3:36 AM Achilleas Mantzios < [email protected]> 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! ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Fast Logical replication setup, via VM clone , PostgreSQL 16.9 @ 2025-06-30 08:15 Achilleas Mantzios <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Achilleas Mantzios @ 2025-06-30 08:15 UTC (permalink / raw) To: [email protected]; +Cc: Achilleas Mantzios <[email protected]> On 6/30/25 08:47, Ron Johnson wrote: > On Mon, Jun 30, 2025 at 3:36 AM Achilleas Mantzios > <[email protected]> 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! ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Fast Logical replication setup, via VM clone , PostgreSQL 16.9 @ 2025-06-30 15:08 Achilleas Mantzios <[email protected]> parent: Achilleas Mantzios <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Achilleas Mantzios @ 2025-06-30 15:08 UTC (permalink / raw) To: [email protected]; +Cc: Achilleas Mantzios <[email protected]> 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) > > ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-06-30 15:08 UTC | newest] Thread overview: 4+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-06-30 07:36 Fast Logical replication setup, via VM clone , PostgreSQL 16.9 Achilleas Mantzios <[email protected]> 2025-06-30 07:47 ` Ron Johnson <[email protected]> 2025-06-30 08:15 ` Achilleas Mantzios <[email protected]> 2025-06-30 15:08 ` Achilleas Mantzios <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox