# How to Upgrade PostgreSQL Without Downtime Using PgBouncer > Source: > Published: 2026-10-26 22:56:11+00:00 This guide shows how to upgrade PostgreSQL while your application keeps serving requests. We'll create a second database running the target version, copy the data with logical replication, and use PgBouncer to move application connections across once the new database has caught up. We built [db-cutover](https://github.com/DocSpring/db-cutover?ref=docspring.com) to run this process at DocSpring. The repository contains a Python CLI, configuration examples, verification tools, and a runbook that a coding agent can follow with a human operator. Each command handles one step, and commands that make changes require `--execute`. This tool was originally a TUI that we would operate ourselves, but we rebuilt it for AI agents to use since we now do almost all of our work using coding agents. The goal is to keep the application online throughout the copy and avoid failed requests during the final switch. PgBouncer briefly queues queries while transactions finish and connections move to the new database. That pause must fit within your application's request, proxy, job, and database timeouts. You'll measure it in a rehearsal before switching. The existing database is **BLUE**. The new database is **GREEN**: ``` php Application -> PgBouncer -> BLUE: current PostgreSQL version | logical replication | GREEN: target PostgreSQL version ``` Most of the migration happens while BLUE continues serving the application. Only the final connection switch needs a pause. ## 1. Check That Your Deployment Fits The CLI currently targets PostgreSQL 16 or newer on Amazon RDS, with PgBouncer running in Kubernetes in transaction-pooling mode. The same general approach can work elsewhere, but the AWS and Kubernetes commands in this guide are specific to that setup. Before starting, confirm that: - All foreground database traffic goes through the PgBouncer pods you will switch. These should be dedicated to this workflow: the tool pauses every database on the selected pods. - You can stop every client that bypasses PgBouncer, including background workers, schedulers, consoles, and migration processes, and prevent them from reconnecting during the switch. - PgBouncer reads its configuration from a directory-mounted ConfigMap, without automatic reloads. You can control pod rollouts and restarts during the switch. - Your application and extensions work on the target PostgreSQL version, and you have a tested backup and recovery process. Freeze schema migrations from the schema copy until replication cleanup. PostgreSQL logical replication does not carry DDL or sequence counters to the destination. The tool handles sequences separately and blocks objects it does not migrate, including large objects, unlogged tables, foreign tables, and materialized views. Review the [PostgreSQL replication restrictions](https://www.postgresql.org/docs/17/logical-replication-restrictions.html?ref=docspring.com) for your schema. ## 2. Install the Tool and Create a Private Config You'll need Python 3.11+, [uv](https://docs.astral.sh/uv/?ref=docspring.com), the AWS and Kubernetes CLIs, and current `pg_dump` and `psql` clients at least as new as both databases. Use matching client versions. The operator's machine must reach both databases and the Kubernetes API; GREEN must also reach BLUE directly for replication. ``` git clone https://github.com/DocSpring/db-cutover.git cd db-cutover git checkout c32812f4d657040f4330563f45ffcf8e75df351c uv sync --frozen uv run db-cutover --help mkdir -p "$HOME/db-cutover-run" cp examples/config.toml "$HOME/db-cutover-run/cutover.toml" export C="$HOME/db-cutover-run/cutover.toml" ``` Keep that checkout fixed throughout the migration. Edit the copied config with your database name, AWS region/profile, instance identifiers, Kubernetes context, and direct-writer deployments. The [complete example](https://github.com/DocSpring/db-cutover/blob/c32812f4d657040f4330563f45ffcf8e75df351c/examples/config.toml?ref=docspring.com) documents the settings. For example: ``` name = "staging-upgrade" database = "app" admin_user = "postgres" app_user = "app" [aws] region = "us-east-1" [blue] identifier = "example-blue" [green] identifier = "example-green" [secrets] admin_password = "env:CUTOVER_ADMIN_PASSWORD" app_password = "env:CUTOVER_APP_PASSWORD" pgbouncer_admin_password = "env:CUTOVER_PGBOUNCER_PASSWORD" [pgbouncer] kube_context = "example-staging-cluster" host = "pgbouncer.example.com" ``` These are placeholders, not a ready-to-run deployment. Supply credentials through your secret manager or environment; don't paste them into the config or commit the operational files. The CLI also supports 1Password references. Its schema dumps and reports belong in the private working directory. Check the environment: ``` uv run db-cutover "$C" env uv run db-cutover "$C" preflight ``` Some checks will fail until you complete the preparation below. Read each failure and resolve it before the step that depends on it. A dry run can still connect to databases and run expensive reads; it means the command won't make the remote change. ## 3. Prepare BLUE for Logical Replication You'll need to enable logical replication on BLUE, so unfortunately this may require some downtime during your first database migration. It's important to keep `wal_level = logical` enabled on GREEN so that you won't need to reboot again in a future migration. If BLUE already has `wal_level = logical`, skip the reboot. Otherwise, enable `rds.logical_replication` in its RDS parameter group and apply the change. This is a static parameter, so RDS requires a reboot. The [AWS instructions](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Concepts.General.FeatureSupport.LogicalReplication.html?ref=docspring.com) describe the parameter and role requirements. ``` uv run db-cutover "$C" reboot-blue uv run db-cutover "$C" reboot-blue --execute ``` The command pauses PgBouncer while BLUE restarts. Stop direct clients first. A reboot can last much longer than the eventual connection switch; if that wait exceeds your timeout budget, arrange this prerequisite separately. It cannot be made invisible merely by putting a pool in front of it. Next, prepare the source tables: ``` uv run db-cutover "$C" prepare-blue uv run db-cutover "$C" prepare-blue --execute ``` This adds `REPLICA IDENTITY FULL` where a primary key is missing and creates a table for synthetic probe and marker rows. FULL identity has performance and data-type limitations, so test those tables rather than assuming replication will be cheap. Start the monitors in separate terminals: ``` uv run db-cutover "$C" watch uv run db-cutover "$C" probe --execute ``` `watch` reports changes in application health, replication and PgBouncer state. If your trusted HTTPS health endpoints require a token, configure `[secrets] health_check_token`; the tool sends it as `X-Health-Check-Token` and refuses redirects. `probe` writes through PgBouncer as the application user and reports errors, slow writes and changes of database. Check that both processes stay alive. ## 4. Provision GREEN and Copy the Schema Choose an RDS engine version available in your region and test your application against it. Set `TARGET_VERSION` to that exact version. If your Terraform layout matches the repository's example, set `[terraform] dir` and the resource names to match your files, then generate a draft configuration: ``` uv run db-cutover "$C" green-tf --engine-version "$TARGET_VERSION" uv run db-cutover "$C" green-tf --engine-version "$TARGET_VERSION" --execute ``` Review the generated file and a Terraform plan before applying it. Check instance parameters that changed between PostgreSQL versions, availability, storage, encryption, backups, and network access. The plan should create GREEN without replacing or destroying BLUE. Provisioning with your own infrastructure tools is also fine; the Terraform helpers are optional. Update `[green] identifier` to the provisioned instance, or use the example's Terraform-derived identifier setting. GREEN starts empty. Install any required extension packages and create roles referenced by the schema, then copy it: ``` uv run db-cutover "$C" copy-schema uv run db-cutover "$C" copy-schema --execute ``` The tool creates the destination database when needed and restores a schema-only dump. It excludes ownership, grants, publications and subscriptions. Recreate application roles and permissions through your normal provisioning process. Other databases and instance-level settings need their own migration plan. Keep application writers off GREEN while replication is running. ## 5. Copy the Data and Keep It Current ``` uv run db-cutover "$C" start-replication uv run db-cutover "$C" start-replication --execute uv run db-cutover "$C" wait-sync --execute ``` The publication on BLUE and subscription on GREEN perform the initial copy, then continue streaming changes. BLUE stays in service throughout this phase. The tool defaults to text replication because [binary replication is less portable across PostgreSQL versions and types](https://www.postgresql.org/docs/17/sql-createsubscription.html?ref=docspring.com). `wait-sync --execute` waits for every table to finish copying, writes a unique marker on BLUE, and waits for that marker to appear on GREEN. This establishes progress through the configured replication stream. It doesn't prove that an omitted table or a separate subscription is current, so the cutover also checks publication coverage and subscription configuration. Watch WAL retention and free disk space on BLUE. A slow or stopped subscriber can cause the replication slot to retain enough WAL to fill the source disk. If errors keep increasing, inspect GREEN's PostgreSQL logs privately and fix the cause before continuing. ## 6. Verify the Copy and Advance Sequences ``` uv run db-cutover "$C" verify uv run db-cutover "$C" verify --execute uv run db-cutover "$C" sequences uv run db-cutover "$C" sequences --execute ``` Verification compares schemas, privileges, row counts, and sampled row-content hashes. Without `--execute`, it is read-only. With `--execute`, it also writes a replication marker. These are useful checks, not an exhaustive comparison or a shared snapshot of both databases. Concurrent writes can cause temporary mismatches; persistent differences need investigation. Sequences need their own step because their counters don't replicate with the rows. GREEN may contain every existing ID while its next sequence value still points at an ID already in use. The tool advances GREEN's sequences above BLUE's reserved values, using a configurable gap, then checks them again during the switch. Your application must tolerate gaps in IDs. The gap does not make simultaneous writes to BLUE and GREEN safe: it can't resolve conflicting updates, deletes or other unique constraints. ## 7. Rehearse the Pause Stop and fence the direct writers, leaving PgBouncer's configuration pointed at BLUE. Then run: ``` uv run db-cutover "$C" cutover --execute --rehearse ``` This pauses the real pools, checks remaining sessions, waits for a replication marker, and compares IDs and sequence state. It stops before RELOAD and resumes traffic on BLUE. Use the reported pause and probe output to check your timeout budget. This is a live operation, not a dry run. It also doesn't exercise every part of the real switch: staging tests must cover reloading onto GREEN, partial failures, and application behavior on the new database. ## 8. Prepare PgBouncer and Switch Traffic Keep direct writers stopped. Prevent PgBouncer rollouts, scaling and automatic reloads. Change the database host in its ConfigMap to GREEN, but **don't reload PgBouncer yet**. Wait for the mounted files to update: ``` uv run db-cutover "$C" pgbouncer config ``` Every pod should report **running BLUE, file GREEN**. This intermediate state needs care. A pod that restarts now reads GREEN immediately. The tool checks for an unexpected target before switching, but that check cannot prevent a restart afterwards. Keep the window short; if your deployment cannot control it, design an external traffic gate before using this workflow. Run the preconditions, then execute the switch: ``` uv run db-cutover "$C" cutover uv run db-cutover "$C" cutover --execute ``` The switch performs these steps: 1. Pause every selected PgBouncer pod and let active transactions finish. 2. Check that other client sessions have left BLUE. 3. Write a marker on BLUE and wait until GREEN applies it. 4. Compare maximum integer primary-key values and advance any sequences still too close to BLUE's range. 5. Reload each pod and confirm that every pod points at GREEN. 6. Resume queued work and check the resulting connections. PgBouncer's [administration commands](https://www.pgbouncer.org/usage.html?ref=docspring.com) provide the pause, reload and resume operations. The CLI coordinates them across pods and saves a report in the private state directory. Watch the probe change to GREEN, check real application operations, and confirm that BLUE has no remaining application connections. If a failure happens before RELOAD, the tool attempts to resume BLUE and reports any failure to do so. Once RELOAD starts, mixed or unknown targets remain paused and the command fails. Keep direct writers stopped, inspect every pod, align them all to GREEN, and only then resume. The repository's [recovery procedure](https://github.com/DocSpring/db-cutover/blob/c32812f4d657040f4330563f45ffcf8e75df351c/docs/procedure.md?ref=docspring.com#recovery) covers this state. Don't return traffic to BLUE after GREEN has accepted writes: BLUE doesn't have those new transactions. ## 9. Move Direct Writers and Remove Replication Change direct connection strings to GREEN, restart those clients, and confirm their sessions reach the new database. Prevent application roles, scheduled tasks and consoles from reconnecting to BLUE. A quiet period in database statistics is useful evidence, but it doesn't stop a future writer. Stop the probe before removing its table, then run: ``` uv run db-cutover "$C" finish uv run db-cutover "$C" finish --execute ``` Cleanup checks the PgBouncer targets, BLUE sessions and write activity, and performs a final marker check before dropping the subscription, slot, publication and probe table. If you used the Terraform helpers, reconcile the resource names: ``` uv run db-cutover "$C" promote-tf uv run db-cutover "$C" promote-tf --execute ``` Review and run the printed state-move commands using your normal state backups and locking. A subsequent plan should not create, replace or destroy either database. Update references to the primary and retain BLUE according to your recovery policy before deleting it in a separate change. Keeping BLUE is useful for investigation; it isn't an immediate rollback once GREEN contains new writes. The [db-cutover repository](https://github.com/DocSpring/db-cutover?ref=docspring.com) contains the complete runbook, configuration reference, failure-path tests, and disposable PostgreSQL replication tests. Start with a staging migration, give your agent the repository's `AGENTS.md`, and use that run to establish the commands, traffic controls and timeout budget for your own application.