public inbox for [email protected]
help / color / mirror / Atom feedFrom: Achilleas Mantzios <[email protected]>
To: [email protected]
Cc: Achilleas Mantzios <[email protected]>
Subject: Re: Fast Logical replication setup, via VM clone , PostgreSQL 16.9
Date: Mon, 30 Jun 2025 09:15:50 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CANzqJaAyOdxH=SArXzfD5gWKmMqsKB8VPi0d=XnOg_QJnS4bLw@mail.gmail.com>
References: <[email protected]>
<CANzqJaAyOdxH=SArXzfD5gWKmMqsKB8VPi0d=XnOg_QJnS4bLw@mail.gmail.com>
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!
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Fast Logical replication setup, via VM clone , PostgreSQL 16.9
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox