On Sat, Jun 8, 2024 at 1:41 PM Koen De Groote <kdg.dev@gmail.com> 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.