Postgres 14 goes end of life on 12 November 2026. If you are still on it, you have had the same conversation I have had several times: the only upgrade path anyone on the team has used is pg_upgrade, pg_upgrade needs the database stopped, and nobody can tell the business how long "stopped" means because it scales with the data.
So the upgrade gets deferred, and then the database is three majors behind on unsupported software.
Logical replication removes the window almost entirely. You stand the new major version up beside the old one, stream into it, and flip. The Postgres half of that is well documented. The half that actually bites — sequences, DDL, and what your queue workers are doing at the moment you flip — is not.
Decide between pg_upgrade and logical replication#
Pick the method from your data size and the window you can actually get signed off, not from what feels modern. There are four options and three of them are fine most of the time.
Dump and restore is the simplest and the slowest. Downtime scales directly with data volume. Under ~20GB on a system that tolerates an overnight window, do this and stop reading.
pg_upgrade --link is fast — minutes, mostly independent of data size, because it hard-links the data files rather than copying them. But the database is down for the duration, you cannot roll back once the old cluster has been linked, and the post-upgrade ANALYZE on a large database can leave you serving awful query plans for a while after you reopen.
Logical replication has the most moving parts and the least downtime. The copy runs for as long as it runs — hours, days on a large database — with the application serving traffic the whole time. The actual outage is the cutover: stop writes, reset sequences, flip the connection string. Seconds to a couple of minutes.
Managed blue/green (RDS Blue/Green Deployments, Cloud SQL, and similar) is logical replication with the fiddly parts automated. If your provider offers it, use it. You still own the sequence and application-sequencing problems described below; the provider only owns the replication.
One thing worth knowing before you go looking for it: pg_createsubscriber does not help here. It converts a physical standby into a logical replica, and its prerequisites require the source and target to be the same major version. It cannot bridge 14 to 18.
Audit replica identity before anything else#
Run this first, because the fix is itself a migration you may need to schedule. A published table must have a replica identity — a way for Postgres to identify which row an UPDATE or DELETE applies to on the subscriber. By default that is the primary key.
-- Every table that will silently fail to replicate UPDATE and DELETE
SELECT n.nspname AS schema,
c.relname AS table,
c.relreplident AS replica_identity
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' -- ordinary tables only
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
AND c.relreplident = 'd' -- 'd' = default, i.e. the primary key
AND NOT EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.oid AND i.indisprimary
)
ORDER BY 1, 2;
schema | table | replica_identity
--------+--------------------+------------------
public | job_batches_legacy | d
public | page_view_events | d
(2 rows)
Those two tables will replicate inserts perfectly and drop every update and delete on the floor. The Postgres restrictions documentation is explicit about it. This is the single nastiest failure mode in the whole process, because nothing errors on the publisher — you find out when row counts diverge at verification time, if you check, or in production three weeks later, if you don't.
Fix each one by adding a primary key, or by pointing the replica identity at an existing unique index on NOT NULL columns:
ALTER TABLE page_view_events REPLICA IDENTITY USING INDEX page_view_events_uuid_idx;
REPLICA IDENTITY FULL works as a last resort — it matches on the whole row — but it makes every update on the subscriber a sequential scan, and it breaks outright on tables holding types like point or box that have no default B-tree or hash operator class.
While you are in here, set wal_level on the source and restart it:
# postgresql.conf on the SOURCE
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
max_slot_wal_keep_size = 200GB # see the gotchas — do not leave this small
That restart is your only mandatory downtime on the old server, and it is a restart, not an upgrade. Schedule it a week ahead of everything else.
Copy the schema and extensions to the new instance#
Logical replication carries no DDL at all. The subscriber needs the schema in place before the subscription starts, and it needs the extensions in place before the schema restores.
# Extensions first — the schema dump will reference their types
psql -h new-db -U postgres -d app_production \
-c "CREATE EXTENSION IF NOT EXISTS pg_trgm;" \
-c "CREATE EXTENSION IF NOT EXISTS citext;" \
-c "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";"
# Then the schema, with no data
pg_dump -h old-db -U postgres -d app_production \
--schema-only --no-owner --no-privileges \
> schema.sql
psql -h new-db -U postgres -d app_production -f schema.sql
Check the target major's release notes for extension behaviour changes before you do this. The usual casualties are full-text search configurations and anything depending on collation — a glibc version difference between the two hosts changes text sort order, which quietly corrupts the ordering of every text index you rebuild.
Leave the foreign keys in place but consider dropping non-essential indexes on the largest tables before the initial copy and rebuilding them after. On a 400GB table that is the difference between a copy that finishes overnight and one that doesn't.
Create the publication and subscription#
The publication goes on the old server, the subscription on the new one. Create a dedicated replication role rather than using postgres.
-- On the SOURCE (Postgres 14)
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'redacted';
GRANT USAGE ON SCHEMA public TO replicator;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;
CREATE PUBLICATION app_pub FOR ALL TABLES;
-- On the TARGET (Postgres 18)
CREATE SUBSCRIPTION app_sub
CONNECTION 'host=old-db port=5432 dbname=app_production user=replicator password=redacted'
PUBLICATION app_pub
WITH (copy_data = true, streaming = on, binary = true);
streaming = on applies large transactions as they arrive instead of buffering them until commit, which matters if your app has a nightly batch job that writes a million rows in one transaction. binary = true is faster but requires compatible type representations on both sides — drop it if the subscription errors on a custom type.
FOR ALL TABLES is the right default. If you want to exclude churn-heavy tables you will never need historic rows from — telescope_entries, sessions, a page-view log — name the tables explicitly instead, and remember you then have to create them empty on the target yourself.
Monitor the initial copy#
The initial copy is the long part. These are the three queries that tell you whether it is progressing, stalled, or dead.
-- On the TARGET: which tables are still copying
SELECT srrelid::regclass AS table, srsubstate, srsublsn
FROM pg_subscription_rel
WHERE srsubstate <> 'r' -- 'r' = ready, everything else is in flight
ORDER BY 1;
States run i (initialising) → d (copying data) → f (finished copy) → s (synchronised) → r (ready). A table sitting in d for hours on a big table is normal. A table sitting in i for hours is not — that usually means max_sync_workers_per_subscription is saturated and it is queued behind others.
-- On the SOURCE: lag, once the copy is done and streaming has started
SELECT application_name,
state,
pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS total_lag
FROM pg_stat_replication;
-- On the SOURCE: is the slot healthy, and how much WAL is it pinning?
SELECT slot_name, active, wal_status,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
pg_size_pretty(safe_wal_size) AS headroom
FROM pg_replication_slots;
wal_status is the one to alert on. reserved and extended are fine. unreserved means you are close to the limit. lost means the slot has been invalidated, the subscription is dead, and you start the initial copy again from zero.
Freeze DDL for the duration of the copy#
Postgres replicates no DDL whatsoever. A migration that adds a column on the source produces rows the target's table has no room for, and replication stops with an error until you fix the target by hand.
The clean answer is a freeze: no migrations merge to production between the subscription starting and the cutover finishing. Put the rule in the change request, and add a CI guard so it is enforced rather than remembered:
# .github/workflows/content-validate.yml — fail the build if migrations change during the freeze
if [ -f .migration-freeze ] && \
git diff --name-only origin/production...HEAD | grep -q '^database/migrations/'; then
echo "::error::Migration freeze active for the Postgres cutover. See the change request."
exit 1
fi
If the freeze is genuinely impossible, additive-only changes can be applied to the target first, then the source — the subscriber tolerates columns the publisher doesn't send, but not the reverse. Anything destructive waits. This is the same ordering discipline behind the expand and contract pattern for zero-downtime migrations, applied across two servers instead of two deploys.
Reset the sequences during the cutover#
This is the step everyone misses, and it fails loudly and immediately on the busiest table you have.
Sequences are not replicated. The restrictions documentation states it plainly: the data in a serial or identity column replicates as part of the row, but the sequence object itself still sits at its start value on the subscriber. Cut over without fixing that and your first insert tries to claim id = 1, which has existed since 2019:
SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates
unique constraint "invoices_pkey"
DETAIL: Key (id)=(1) already exists.
Generate the setval statements on the source and run them on the target, with headroom for anything written between generating and applying them:
# On the SOURCE, after writes have stopped
psql -h old-db -U postgres -d app_production -At -c "
SELECT format(
'SELECT setval(%L, %s, true);',
schemaname || '.' || sequencename,
COALESCE(last_value, 1) + 1000 -- headroom; gaps in ids cost nothing
)
FROM pg_sequences
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');
" > sequences.sql
# Then on the TARGET
psql -h new-db -U postgres -d app_production -f sequences.sql
Run this during the cutover, after writes have stopped — not the night before. A sequence reset taken while the app is still writing is already stale by the time you flip.
On Postgres 19, ALTER SUBSCRIPTION ... REFRESH SEQUENCES does this for you, but only when the publisher is also 19 or later. Upgrading from an older major means you are writing the script.
Run the Laravel cutover runbook#
Order matters more than speed here. The goal is that no Laravel process holds a write connection to the old primary when you flip.
# 1. Stop the scheduler first — it is what starts new work
sudo systemctl stop laravel-scheduler
# 2. Stop queue workers and WAIT for in-flight jobs to drain
php artisan queue:restart # graceful: workers exit after the current job
sudo supervisorctl stop laravel-worker:*
# 3. Confirm nothing is still writing
psql -h old-db -U postgres -d app_production -c "
SELECT pid, state, wait_event_type, query_start, left(query, 60) AS query
FROM pg_stat_activity
WHERE datname = 'app_production' AND state <> 'idle' AND pid <> pg_backend_pid();
"
# 4. Close the front door, keeping a bypass for your own verification
php artisan down --secret="cutover-2026-11-01" \
--render="errors::503" --retry=120
# 5. Wait for lag to reach zero
psql -h old-db -U postgres -d app_production -c "
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind
FROM pg_stat_replication;
" # repeat until this returns 0
# 6. Reset sequences (the script from the previous step)
psql -h new-db -U postgres -d app_production -f sequences.sql
# 7. Stop replication and allow writes on the new primary
psql -h new-db -U postgres -d app_production -c "ALTER SUBSCRIPTION app_sub DISABLE;"
# 8. Flip the connection and clear every cached config
# (deploy the new DB_HOST, then:)
php artisan config:clear && php artisan config:cache
# 9. Smoke test through the bypass URL, then reopen
php artisan up
sudo supervisorctl start laravel-worker:*
sudo systemctl start laravel-scheduler
Three details in there are worth dwelling on.
Step 2 is why workers get stopped before maintenance mode rather than after: php artisan down stops new jobs from being processed, but a worker already inside a job keeps running, and if that job opened a transaction it will hold the lag above zero until it commits. Supervisor's stopwaitsecs must be longer than your slowest job or supervisorctl stop will SIGKILL the worker mid-transaction.
Step 4's --secret gives you a bypass URL — https://yourapp.com/cutover-2026-11-01 sets a cookie and lets you through the 503 to run a real write test before the public gets back in. Be aware of the interaction between --render and the cache driver: the pre-rendered 503 only works with the file-based maintenance driver.
Step 8 does not take effect for long-lived processes. Octane workers and any persistent connections hold the old PDO instance until the worker process itself is replaced — so Octane needs a restart, not a reload, and the same goes for anything else that boots the framework once and keeps it.
Verify before you let traffic back in#
Through the bypass, before php artisan up. Row counts on the tables that matter, a sampled checksum, and one real write that goes through the full application path.
-- Run on BOTH, compare. Fast enough on tables under ~50M rows.
SELECT 'users' AS t, count(*), max(id) FROM users
UNION ALL SELECT 'invoices', count(*), max(id) FROM invoices
UNION ALL SELECT 'invoice_lines', count(*), max(id) FROM invoice_lines;
-- Sampled checksum: catches silent divergence that counts alone miss
SELECT md5(string_agg(t::text, '' ORDER BY id))
FROM (SELECT * FROM invoices ORDER BY id DESC LIMIT 5000) t;
# Sequence sanity: every sequence must now be ahead of its column's max
php artisan tinker --execute '
collect(["users", "invoices"])->each(function (string $table): void {
$max = DB::table($table)->max("id");
$next = DB::selectOne("SELECT last_value FROM {$table}_id_seq")->last_value;
echo "{$table}: max={$max} seq={$next} " . ($next > $max ? "OK" : "BROKEN") . PHP_EOL;
});
'
Then dispatch one queued job synchronously and confirm it writes. A read smoke test passing proves very little — the failure modes of this process are all on the write path.
Keep a rollback path open#
Be honest with yourself about this one. The moment a write lands on the new primary and not the old one, "rollback" means a data merge, not a switch.
If you want a real rollback option, build it before the cutover: a second subscription pointing the other way, created but disabled, so the old server can start consuming from the new one the moment you enable it.
-- On the OLD server, created before cutover, left disabled
CREATE SUBSCRIPTION app_sub_reverse
CONNECTION 'host=new-db port=5432 dbname=app_production user=replicator password=redacted'
PUBLICATION app_pub_reverse
WITH (copy_data = false, enabled = false); -- copy_data = false is essential
copy_data = false is the critical option — the old server already has the rows, and a re-copy would duplicate everything.
Your point of no return is the first successful public write. Set a decision deadline before you start: if verification is not green within N minutes, you flip DB_HOST back, re-enable the forward subscription, and go home. Deciding that in the moment at 3am is how outages get extended.
Clean up slots, run ANALYZE and check plans#
The slot cleanup is not optional housekeeping. An orphaned replication slot on the old server pins WAL forever, and Postgres will fill the disk rather than discard WAL a slot claims to need.
-- On the OLD server, once you are committed
DROP SUBSCRIPTION app_sub_reverse; -- if you created it
SELECT pg_drop_replication_slot('app_sub');
DROP PUBLICATION app_pub;
-- Confirm nothing is left pinning WAL
SELECT slot_name, active, wal_status FROM pg_replication_slots;
# On the NEW primary: planner statistics do not replicate
vacuumdb -h new-db -U postgres -d app_production --analyze-in-stages --jobs=4
--analyze-in-stages does three passes of increasing accuracy, so you get usable statistics in seconds rather than waiting for a full analyse on a large database. Until it has run, the planner is working from nothing and will choose sequential scans over your indexes.
Then re-check plans. Major versions change planner behaviour, and a query that was fine on 14 can regress on 18. Pull your slowest ten endpoints and EXPLAIN (ANALYZE, BUFFERS) the queries behind them. If you have read replicas routing traffic, rebuild and verify those too — they are a separate upgrade with the same sequence and statistics problems.
Rehearse the whole thing against a snapshot#
Do not let the first run be the real one. Restore a production snapshot — your automated backups are already producing one — onto two throwaway instances and run the entire procedure end to end.
The number you need from the rehearsal is not "how long does the initial copy take". It is how long between the last write on old and the first write on new. That is the only figure that goes in the change request, and it is usually far smaller than people expect — on a rehearsal I ran this year, 94 seconds, of which 71 were waiting for a stubborn queue job to finish.
Time each step separately. The one that surprises people is the sequence reset on a schema with 200+ sequences, which is not instant.
Gotchas and Edge Cases#
An orphaned slot will take the old server down days later. This is the most common post-cutover incident, and it happens long after everyone has declared success. The old server keeps every WAL segment the slot claims to need until the disk is full. max_slot_wal_keep_size caps the damage but also invalidates the slot when the cap is hit — which is the right trade-off for a server you are decommissioning and the wrong one mid-copy.
max_slot_wal_keep_size set too low kills the copy. If the slot's wal_status reaches lost during the initial sync, the subscription is finished and you restart from scratch. On a busy database a multi-hour copy can easily accumulate 100GB+ of WAL. Size it for the whole copy window, then lower it afterwards.
A long-running job keeps lag above zero. pg_stat_activity shows it with an old query_start and state = 'idle in transaction'. Once the workers are stopped, pg_terminate_backend(pid) is almost always the right call — the job will be retried against the new primary anyway.
Collation differences corrupt text indexes silently. If the two hosts run different glibc versions, text sort order differs, and any index built on the target is subtly wrong. Compare SELECT version() and the OS libc version on both hosts, and REINDEX text indexes after cutover if they differ. Postgres 18 will warn about collation version mismatches; 14 will not.
Large objects are not replicated. If anything in the app uses lo_* functions rather than bytea, logical replication will not carry it and there is no workaround short of moving the data into normal tables first.
TRUNCATE across foreign keys fails on the subscriber when some of the linked tables are not in the publication. If you used a table list rather than FOR ALL TABLES, check for foreign-key groups that got split.
Wrapping Up#
Start with the replica identity audit this week — it is read-only, takes ten minutes, and tells you whether you have a migration to schedule before the upgrade can even begin. Then rehearse against a restored snapshot and get a real cutover number before you book anything with the business.
If the sequencing in the runbook is new to you, the expand and contract pattern is the smaller version of the same discipline, and blue/green deployment behind an Nginx upstream gives you the application-side switch that makes the connection flip a config change rather than a deploy.
FAQ#
How do I upgrade Postgres without downtime?
You cannot get literally zero downtime, but logical replication gets the window down to seconds. You stand up the new major version alongside the old one, create a publication on the source and a subscription on the target, let the initial copy and ongoing stream catch up while the app serves traffic normally, then stop writes briefly to reset sequences and flip the connection string. The outage is the length of that flip, typically under two minutes, rather than the hours pg_upgrade needs.
What is the difference between pg_upgrade and logical replication for a major version upgrade?
pg_upgrade converts the cluster in place with the database stopped. With --link it is fast and mostly independent of data size, but the downtime is real and rollback after linking is not possible. Logical replication copies the data into a separate new-version instance while the old one keeps serving, so the only downtime is the cutover itself. The trade-off is complexity: you own the schema copy, the DDL freeze, the sequence reset and the application sequencing, none of which pg_upgrade asks of you.
Does logical replication copy sequences?
No. The rows in serial and identity columns replicate as table data, but the sequence objects themselves stay at their start value on the subscriber. You must reset every sequence with setval() during the cutover, generating the statements from pg_sequences on the source after writes have stopped. Postgres 19 adds ALTER SUBSCRIPTION ... REFRESH SEQUENCES, but it requires the publisher to also be running 19 or later, so it does not help when upgrading from an older major.
How do I cut a Laravel app over to a new database with minimal downtime?
Stop the scheduler first, then stop queue workers and wait for in-flight jobs to drain, because a job holding an open transaction keeps replication lag above zero. Put the app into maintenance mode with a --secret bypass so you can still verify, confirm lag has reached zero, reset the sequences, disable the subscription, then deploy the new DB_HOST and clear the config cache. Restart — not reload — Octane workers and any other long-lived processes, because they hold the old connection until the process is replaced.
What is a replica identity and why does my table not replicate?
A replica identity is how Postgres identifies which row on the subscriber an UPDATE or DELETE applies to, and it defaults to the table's primary key. A published table with no primary key and no explicitly set replica identity will replicate inserts normally while silently discarding updates and deletes — nothing errors on the publisher. Audit pg_class.relreplident before you start and fix offenders by adding a primary key or pointing the replica identity at a unique index on NOT NULL columns.
How do I roll back a Postgres major version upgrade after cutover?
Only if you prepared for it beforehand. Once writes land on the new primary, the old server is missing data, so rolling back means replicating in the reverse direction — which you have to set up before the cutover as a disabled subscription on the old server with copy_data = false. Without that, rollback is a manual data merge. Set a decision deadline in the runbook: if verification is not green within an agreed number of minutes, you revert the connection string and re-enable the forward subscription.