Horton

Re: speed up a logical replica setup

Amit Kapila <amit.kapila16@gmail.com>

View thread
From: Amit Kapila <amit.kapila16@gmail.com>
To: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>
Cc: Euler Taveira <euler@eulerto.com>, Alexander Lakhin <exclusion@gmail.com>, "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>, "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>
Date: 2024-07-18T02:37:44Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. pg_createsubscriber: Remove obsolete comment

  2. pg_createsubscriber: Fix an unpredictable recovery wait time.

  3. Fix unstable test in 040_pg_createsubscriber.

  4. Fix the testcase introduced in commit 81d20fbf7a.

  5. Further weaken new pg_createsubscriber test on Windows.

  6. Temporarily(?) weaken new pg_createsubscriber test on Windows.

  7. Make pg_createsubscriber warn if publisher has two-phase commit enabled.

  8. Make pg_createsubscriber more wary about quoting connection parameters.

  9. pg_createsubscriber: Remove failover replication slots on subscriber

  10. pg_createsubscriber: Remove replication slot check on primary

  11. pg_createsubscriber: Only --recovery-timeout controls the end of recovery process

  12. pg_createsubscriber: creates a new logical replica from a standby server

  13. Add some const decorations

  14. Add option force_initdb to PostgreSQL::Test::Cluster:init()

  15. Remove MSVC scripts

On Wed, Jul 17, 2024 at 5:28 PM Hayato Kuroda (Fujitsu)
<kuroda.hayato@fujitsu.com> wrote:
>
> > Your analysis sounds correct to me.
>
> Okay, so we could have a same picture...
>
> > > IIUC, the root cause is that pg_create_logical_replication_slot() returns a LSN
> > > which is not generated yet. So, I think both mine [1] and Euler's approach [2]
> > > can solve the issue. My proposal was to add an extra WAL record after the final
> > > slot creation, and Euler's one was to use a restart_lsn as the
> > recovery_target_lsn.
> > >
> >
> > I don't think it is correct to set restart_lsn as consistent_lsn point
> > because the same is used to set replication origin progress. Later
> > when we start the subscriber, the system will use that LSN as a
> > start_decoding_at point which is the point after which all the commits
> > will be replicated. So, we will end up incorrectly using restart_lsn
> > (LSN from where we start reading the WAL) as start_decoding_at point.
> > How could that be correct?
>
> I didn't say we could use restart_lsn as consistent point of logical replication,
> but I could agree the approach has issues.
>
> > Now, even if we use restart_lsn as recovery_target_lsn and the LSN
> > returned by pg_create_logical_replication_slot() as consistent LSN to
> > set replication progress, that also could lead to data loss because
> > the subscriber may never get data between restart_lsn value and
> > consistent LSN value.
>
> You considered the case, e.g., tuples were inserted just after the restart_lsn
> but before the RUNNING_XACT record?

I am thinking of transactions between restart_lsn and "consistent
point lsn" (aka the point after which all commits will be replicated).
You conclusion seems correct to me that such transactions won't be
replicated by streaming replication and would be skipped by logical
replication. Now, if we can avoid that anyway, we can consider that.

-- 
With Regards,
Amit Kapila.