Using pglogical Replication for Rails Upgrades

By USEO Engineering Team December 15, 2025

BLUF (Bottom Line Up Front): Upgrading massive PostgreSQL databases using pg_dump or pg_upgrade requires extended maintenance windows and production downtime. Using pglogical replication allows you to sync data in real-time from a legacy database (Publisher) to a new, upgraded database (Subscriber). Once synced, you simply repoint your Rails app to the new database, achieving a near-zero downtime upgrade.

Phase 1: The Outage Window

When upgrading a 500GB legacy Postgres 9.6 instance to Postgres 15, a standard pg_dump and pg_restore can take over 10 hours.

Synthetic Engineering Context: The Unacceptable Delay

# Typical legacy upgrade attempt
$ pg_dump -Fc -h legacy-db.internal -U postgres myapp > dump.custom
$ pg_restore -h new-db.internal -d myapp -1 -j 4 dump.custom
# Output: [Archiver] restored 150GB of 500GB... Time elapsed: 4h 12m

During this entire process, your Rails application must be placed in maintenance mode to prevent data drift, resulting in unacceptable business disruption.

Phase 2: Logical Replication Setup

pglogical operates at the row level via logical replication slots, meaning it can replicate data between different major versions of PostgreSQL.

Execution: Configuring Publisher (Legacy DB)

Install the pglogical extension on the old database and define it as the publisher. You must add the tables you want to replicate to a replication set.

-- On Postgres 9.6 (Publisher)
CREATE EXTENSION pglogical;

SELECT pglogical.create_node(
    node_name := 'legacy_provider',
    dsn := 'host=legacy-db.internal port=5432 dbname=myapp user=repl_user'
);

-- Add all public tables to the default replication set
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

Execution: Configuring Subscriber (New DB)

On the new Postgres 15 database, create the schema (using rake db:schema:load) and subscribe to the legacy node.

-- On Postgres 15 (Subscriber)
CREATE EXTENSION pglogical;

SELECT pglogical.create_node(
    node_name := 'new_subscriber',
    dsn := 'host=new-db.internal port=5432 dbname=myapp user=repl_user'
);

SELECT pglogical.create_subscription(
    subscription_name := 'upgrade_subscription',
    provider_dsn := 'host=legacy-db.internal port=5432 dbname=myapp user=repl_user'
);

The subscriber will perform an initial copy of the data and then stream changes in real-time. To cut over, update the DATABASE_URL in your Rails application and restart the web servers.

Phase 3: Next Steps & Risk Mitigation

Logical replication does not replicate DDL (schema changes). You must freeze schema migrations during the sync window. Additionally, sequences (Primary Keys) are not automatically synced and must be manually updated on the subscriber right before the cutover.

Need Help Stabilizing Your Legacy App? Database upgrades carry immense risk. Our DevOps and Rails experts at USEO execute seamless PostgreSQL upgrades using advanced logical replication techniques.