public inbox for [email protected]
help / color / mirror / Atom feedFrom: Justin <[email protected]>
To: Koen De Groote <[email protected]>
Cc: Adrian Klaver <[email protected]>
Cc: PostgreSQL General <[email protected]>
Subject: Re: Questions on logical replication
Date: Sat, 8 Jun 2024 16:33:34 -0400
Message-ID: <CALL-XeOZ36D2z5qcxhF0qf-m9+ArioRVXhN9KEUCgMM69UjMLA@mail.gmail.com> (raw)
In-Reply-To: <CAGbX52GF=n7-vi5VnfnMXKkf=CfbX_nAgmy3=eezLU6XWXqjfw@mail.gmail.com>
References: <CAGbX52FxL9eR=jmS3ACgRC=tEm=xj_xMmFoOxO+wVA+oURW=kA@mail.gmail.com>
<[email protected]>
<CAGbX52HTgb2jYYO5eVDu=xB5-bjAra+34=AUFg4Xk0NpCRjnyg@mail.gmail.com>
<[email protected]>
<CAGbX52EMG+d-BR5SzDP+rQsrdxT32=CXD5XCT2YCmkTpr3jesw@mail.gmail.com>
<[email protected]>
<CAGbX52GF=n7-vi5VnfnMXKkf=CfbX_nAgmy3=eezLU6XWXqjfw@mail.gmail.com>
On Sat, Jun 8, 2024 at 1:41 PM Koen De Groote <[email protected]> wrote:
> What I'm trying to do is upgrade a PG11 database to PG16, using logical
> replication.
>
> The PG11 has an active and a standby, there are a handful of databases. On
> particular one has a few tables just over 100GB, then a few 100 tables near
> 1GB.
>
> What I'd do is start a publication with no tables and add them 1 at a
> time, refreshing subscription each time.
>
> This might take a long time, so my main questions relate to potential
> network issues or various situations where the instance receiving the
> logical replication, suddenly stop being able to receive.
>
> Resyncing, and the effects of WAL buildup, are my main concern.
>
> Accidentally sent a mail to only your email, sorry for that.
>
> Regards,
> Koen De Groote
>
>>
>>
This approach does not prevent WAL build up.
The WAL build up occurs during the initial sync worker once that table is
synced the WAL is replayed and released. The parent worker then become
responsible for replaying the WAL for that table
The WAL build up is during the initial sync of the data by table NOT during
the entire synce of all the tables that have been published.
For 1 gb table the initial sync will be very fast so I doubt any individual
table will cause any significant WAL build up to put the publisher at risk
of of crashing
Once a table becomes synced the main subscriber worker keeps the WAL
replayed. If there are any errors during the replay of WAL such as missing
indexes for Replica Identities during an Update or Delete this will cause
the main subscriber worker slot on the publisher to start backing up WAL
files. If there are missing replica identities the affected tables will
have to be dropped from the publication and subscription refreshed. The
WAL file is already written with incorrect information so the table on the
subscriber table is most likely not in recoverable state.
I suggest confirming all tables have replica identities or primary keys
before going any further. With PG 11 avoid REPLICA IDENTITY FULL as this
causes full table scan on the subscriber for PG 15 and eariler. PG 16 on
the subsciber can use a different unique index that has NOT NULL for all
participating columns if the publisher is using Replicate Identity FULL on
the published table
One must understand the above before deploying logical replication.
Hope this helps
view thread (16+ messages) latest in thread
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], [email protected]
Subject: Re: Questions on logical replication
In-Reply-To: <CALL-XeOZ36D2z5qcxhF0qf-m9+ArioRVXhN9KEUCgMM69UjMLA@mail.gmail.com>
* 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