Migrating from Postgres Physical Streaming Replication (PSR) to PGD v6.2.0

Moving from Postgres Physical Streaming Replication (PSR) to Postgres Distributed (PGD) represents a shift from a single-primary architecture to a multi-primary mesh network. This transition is typically driven by the need for higher availability and geographically distributed write capabilities.

See Choosing between PSR and PGD for more information.

The core of this migration strategy is the seed node, a new node that initially joins your existing PSR cluster as a standard physical standby. Once synchronized, it is decoupled from the physical stream and converted into a logical subscriber.

Logical replication allows for data to flow between different major versions of Postgres. Once the seed node is upgraded and running PGD, it serves as the "anchor" for the new cluster, allowing additional PGD nodes to join using a "physical join" from the seed node while still receiving fresh data from the original source primary.

By using this method, the original primary node remains the "source of truth" throughout the process, ensuring that the application can continue to run without interruption until the final cutover phase.

Prerequisites

• Nodes running PGE, EPAS, or Community Postgres version 14 or later currently utilizing physical replication (optionally managed by EFM).
• Two additional nodes with sufficient storage (the seed node and an additional node).
• Network connectivity between all existing and new nodes.

Migration overview

The migration consists of the following phases:

1. Prepare the environment: Identify the source primary node and select a seed node to anchor the PGD cluster.
2. Transfer data to the seed node: Establish a manual physical standby on the new node.
3. Convert to logical replication: Transition the seed node from physical to logical streaming.
4. Upgrade the seed node: Perform an in-place major version upgrade and install the PGD packages.
5. Initialize PGD: Create the BDR extension and initialize the first PGD node group.
6. Join additional nodes: Expand the cluster to ensure high availability.
7. Execute application cutover: Switch traffic to the new PGD cluster.
8. Clean up the environment: Decouple the clusters and remove legacy versions.