Upgrading the seed node and installing PGD v6.2.0

In this phase, you will perform a major version upgrade of your Postgres distribution on the seed node and prepare it for Postgres Distributed (PGD).

Preparing the seed node for the upgrade

You must take the seed node offline to perform the major version upgrade. We highly recommend to disable the logical subscription before stopping the service, as subscription metadata must be manually refreshed after the upgrade.

1. On the seed node, disable logical replication:

   → WrapCopy

   ALTER SUBSCRIPTION migration_seed_sub DISABLE;

2. Stop the existing Postgres instance on seed node:

   → WrapCopy

   su -u enterprisedb --command "pg_ctl stop"

3. Install the packages for the target version on the seed node. Ensure the old version remains installed, as pg_upgrade requires binaries from both versions to perform the transition. See Installing EPAS, Installing PGE, or Installing Postgres for details.

4. Adjust your ${PATH} environment variable to point to the new binaries (e.g., /usr/edb/as17/bin/) and verify the version:

   → WrapCopy

   which pg_ctl

Performing the in-place upgrade

Use the --link flag with pg_upgrade for an in-place upgrade that uses hard links instead of copying data files. This significantly reduces the required disk space and time, though it requires both the old (${OLD_PGDATA}) and new (${NEW_PGDATA}) directories to reside on the same filesystem.

1. On the seed node, prepare the new data directory:

   → WrapCopy

   OLD_PGDATA = ${PGDATA}
   unset PGDATA
   mkdir ${NEW_PGDATA}
   chown enterprisedb: ${NEW_PGDATA}
   chmod 700 ${NEW_PGDATA}
   su enterprisedb -c "initdb -D ${NEW_PGDATA} -E utf-8"

2. On seed node, run pg_upgrade in check mode first:

   → WrapCopy

   mkdir /tmp/pg_upgrade
   cd /tmp/pg_upgrade
   su postgres -c "pg_upgrade \
   --old-datadir ${OLD_PGDATA} \
   --new-datadir ${NEW_PGDATA} \
   --old-bindir /usr/edb/as14/bin/ \
   --new-bindir /usr/edb/as17/bin/ \
   --link \
   --check"

3. If the check returns a positive result, perform the actual upgrade by executing the same command without the --check flag:

   → WrapCopy

   cd /tmp/pg_upgrade
   su postgres -c "pg_upgrade \
   --old-datadir ${OLD_PGDATA} \
   --new-datadir ${NEW_PGDATA} \
   --old-bindir /usr/edb/as14/bin/ \
   --new-bindir /usr/edb/as17/bin/ \
   --link"

4. Once completed, start the new version of your Postgres distribution:

   → WrapCopy

   su -u enterprisedb --command "pg_ctl start"

Re-enabling the subscription

Major version upgrades do not carry over the internal table-mapping information for subscriptions; specifically, the catalog table pg_subscription_rel will be empty. Enabling the subscription immediately would cause the replication slot to be moved forward without synchronizing table data, resulting in data loss.

To avoid this, you must "bogus-configure" the subscription to prevent it from advancing the real slot while you refresh the table metadata. You can safely ignore errors being reported in the seed node logs about the slot not existing.

1. On the seed node, point to a bogus slot to safely refresh metadata:

   → WrapCopy

   ALTER SUBSCRIPTION migration_seed_sub SET (slot_name = 'bogus');

2. Enable and refresh the publication:

   → WrapCopy

   ALTER SUBSCRIPTION migration_seed_sub ENABLE;
   ALTER SUBSCRIPTION migration_seed_sub REFRESH PUBLICATION WITH (copy_data = false);
   ALTER SUBSCRIPTION migration_seed_sub DISABLE;

3. Verify that the table list in the subscription matches the configuration you obtained on Enabling logical replication to the seed node:

   → WrapCopy

   SELECT n.nspname AS schemaname,
       c.relname AS tablename,
       sr.srsubstate AS state,
       s.subname AS subscription_name
   FROM pg_subscription_rel sr
   JOIN pg_class c ON sr.srrelid = c.oid
   JOIN pg_namespace n ON c.relnamespace = n.oid
   JOIN pg_subscription s ON sr.srsubid = s.oid
   WHERE s.subname = 'migration_seed_sub'
   ORDER BY n.nspname, c.relname;

4. Revert the subscription to the correct slot and resume replication at the right LSN:

   → WrapCopy

   ALTER SUBSCRIPTION migration_seed_sub
       SET (slot_name = 'migration_node_${SEED_NODE_NAME}');
   ALTER SUBSCRIPTION migration_seed_sub ENABLE;

At this stage, the seed node is running the new Postgres version and receiving logical updates from the source primary. Allow the system to settle and ensure the seed node has fully caught up before moving to initialize PGD.

Next step: Initialize PGD.