Question: How do you upgrade PostgreSQL with logical replication?

Answer

Logical replication in PostgreSQL allows data to be replicated to other PostgreSQL databases in a flexible and customizable manner, using a publish/subscribe model. This feature is particularly useful for upgrading PostgreSQL databases with minimal downtime. Here's a comprehensive guide on how to perform an upgrade using logical replication.

Preconditions

1. Version Compatibility: Ensure both the old (source) and new (target) PostgreSQL instances support logical replication. Logical replication was introduced in PostgreSQL 10.
2. Configuration: Both databases should allow logical replication. In postgresql.conf, set wal_level to logical.

Steps to Upgrade Using Logical Replication

Step 1: Set Up Publisher

On your source database:

-- Create a publication for all tables
CREATE PUBLICATION my_publication FOR ALL TABLES;

Step 2: Set Up Subscriber

On your target (new version) database:

-- Create a subscription to the publication
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=source_host port=5432 dbname=source_db user=replicator password=secret'
PUBLICATION my_publication;

Step 3: Synchronize Data

Data will start synchronizing from the source to the target. Monitor the progress and ensure that all data is replicated successfully. You can monitor the status of subscriptions in the target database by querying pg_stat_subscription.

Step 4: Switch Over Clients

Once you've confirmed that the data is fully synced, you can begin redirecting clients from the old database to the new one. This process will depend on your specific application setup.

Step 5: Finalize

After ensuring that everything works as expected on the new database and all clients have been redirected, you can drop the subscription on the target database and decommission the old database.

DROP SUBSCRIPTION my_subscription;

Considerations

• Downtime: While logical replication allows for a low-downtime upgrade, a brief switch-over period is required when redirecting clients to the new database.
• Large Objects: If you use large objects (lo), note that logical replication does not replicate them by default. Separate handling might be needed.
• Sequence Values: Sequence values are not replicated. You may need to set them manually after the replication is complete to avoid conflicts.

By following these steps, you can upgrade your PostgreSQL database with minimal downtime, leveraging logical replication for a smooth transition.