RE: Multi-Master Logical Replication

From: "kuroda(dot)hayato(at)fujitsu(dot)com" <kuroda(dot)hayato(at)fujitsu(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: Bruce Momjian <bruce(at)momjian(dot)us>, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru>, vignesh C <vignesh21(at)gmail(dot)com>, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, 'Peter Smith' <smithpb2250(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Subject: RE: Multi-Master Logical Replication
Date: 2022-06-06 10:54:21
Message-ID: TYAPR01MB58660FCFEC7633E15106C94BF5A29@TYAPR01MB5866.jpnprd01.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Dear hackers,

I found another use-case for LRG. It might be helpful for migration.

LRG for migration
------------------------------------------
LRG may be helpful for machine migration, OS upgrade,
or PostgreSQL itself upgrade.

Assumes that users want to migrate database to other environment,
e.g., PG16 on RHEL7 to PG18 on RHEL8.
Users must copy all data into new server and catchup all changes.
In this case streaming replication cannot be used
because it requires same OS and same PostgreSQL major version.
Moreover, it is desirable to be able to return to the original environment at any time
in case of application or other environmental deficiencies.

Operation steps with LRG
------------------------------------------

LRG is appropriate for the situation. Following lines are the workflow that users must do:

1. Copy the table definition to the newer node(PG18), via pg_dump/pg_restore
2. Execute lrg_create() in the older node(PG16)
3. Execute lrg_node_attach() in PG18

=== data will be shared here===

4. Change the connection of the user application to PG18
5. Check whether ERROR is raised or not. If some ERRORs are raised,
users can change back the connection to PG16.
6. Remove the created node group if application works well.

These operations may reduce system downtime
due to incompatibilities associated with version upgrades.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message James Coleman 2022-06-06 12:10:19 Re: pg_rewind: warn when checkpoint hasn't happened after promotion
Previous Message Jean Landercy - BEEODIVERSITY 2022-06-06 09:34:24 Sudden database error with COUNT(*) making Query Planner crashes: variable not found in subplan target list