Free tools Windows power users keep installed
One-click scans. No signup required.
Moving a live PostgreSQL database means copying data while the application keeps changing it, then switching traffic to the new server without losing committed writes or applying them twice. Sabudh Thapa’s post describes moving the same 79 GB database between servers three different ways while an application kept sending reads and writes. The publicly visible text of that post is only its headline and byline, so this article cannot report which methods he used, how long each took, or what he measured. What it can do is explain what a live move must solve, which PostgreSQL behaviors govern it, and what to record when you compare approaches yourself.
What the source does and does not establish
- Author: Sabudh Thapa, who describes himself as a backend engineer based in Kathmandu, Nepal.
- Claimed scope: one PostgreSQL database of 79 GB, moved server to server by three different approaches, with an application reading and writing throughout. These are the post’s own framing. They have not been independently audited.
- Dates: the DEV Community syndication shows a post date of September 24. Search metadata places it in 2024, so treat the publication year as 2024 and confirm it on the original page.
- Not visible in the available text: the three methods, source and target PostgreSQL versions, timings, downtime, validation steps, and which approach the author preferred.
Read the full post at tsabudh.com.np/blog/migrating-live-postgres-without-stopping-writes for those details. The sections below describe the general problem and PostgreSQL’s documented behavior, not the author’s experiments.
The problem a live move has to solve
Every live migration, whatever tool performs it, has to get through the same sequence. The order matters because each step depends on the one before it.
- Establish a starting point on the destination that reflects the source at a known moment.
- Capture every change committed on the source after that moment, while the application keeps writing.
- Confirm the destination has applied everything the source committed before you stop trusting the source.
- Redirect writes to the destination, and redirect reads if the application separates them.
- Keep a way back to the original server until you are satisfied with the new one.
The hard part is step three. A copy that looks complete a few minutes ago can still be missing the most recent commits, and a cutover that skips this check can silently lose writes or leave two servers accepting different data.
#1 Best Overall
PostgreSQL behavior that governs the move
The following points come from the PostgreSQL 18 documentation, mainly the chapter titled ‘Log-Shipping Standby Servers’. They apply to any approach built on streaming replication, whichever method the author used.
Streaming replication sends WAL as it is generated
Streaming replication transfers write-ahead log (WAL) records from the primary to the standby as they are produced. It keeps the standby closer to current than file-based log shipping, which only moves completed WAL segment files. That is why a streaming standby can be used as a migration target for a database that is still taking writes.
Asynchronous is the default, so lag is normal
By default, a primary does not wait for the standby before reporting a commit. The standby therefore trails the primary by a delay that depends on workload, network throughput, and how quickly the standby can replay WAL. The documentation describes this delay as small under suitable conditions. It is not zero by design, so a cutover must measure it rather than assume it.
Rank #2
Synchronous replication trades latency for confirmation
With synchronous replication, the primary waits for the standby to confirm a commit before returning success. This gives stronger guarantees about what the standby holds, at the cost of higher transaction response time. For a migration that must not lose any committed write, it is a legitimate option, but it adds latency to every write for as long as it stays enabled.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Replication slots protect WAL, but can fill pg_wal
A replication slot makes the primary keep WAL until the standby has consumed it, so a standby that falls behind does not lose the records it needs. The cost is retention. If the standby stops or stalls, WAL accumulates in the primary’s pg_wal directory and can exhaust disk space. Slots need monitoring and a plan for removing abandoned ones.
Logical replication is a different mechanism
PostgreSQL also offers logical replication, which replicates row changes rather than physical WAL. Its suitability depends on version pairs, schema details, and which objects are replicated. Check the logical replication chapter of the PostgreSQL 18 documentation before assuming it applies to your schema.
Checks to run during any live move
These checks are general practice built on PostgreSQL’s statistics views. They are not steps the author reported.
- Replication progress on the primary: run
SELECT application_name, client_addr, state, sent_lsn, replay_lsn, replay_lag FROM pg_stat_replication;and watchreplay_lsnapproachsent_lsn. - Slot health: run
SELECT slot_name, slot_type, active, restart_lsn FROM pg_replication_slots;. An inactive slot with an oldrestart_lsnis retaining WAL. - Disk headroom: check the size of the WAL directory under the data directory, for example
du -sh $PGDATA/pg_wal, and set an alert before it approaches the free space on that volume. - Data agreement: row counts alone do not prove two tables match, because counts can agree while contents differ. Compare per-table checksums or key-range hashes on both servers after the destination has caught up.
How to compare the three approaches
Use the table below to record each approach you test. It is a template for your own measurements, not a list of results.
Recommended Free Tools
| Criterion | Question to answer for each approach | Why it matters |
|---|---|---|
| Total copy and catch-up time | How long from start until the destination has replayed all source commits? | Sets the length of the window where lag and rollback risk exist. |
| Write continuity | Did any write fail, queue, or get rejected during the move? | Shows whether the application saw an outage. |
| Cutover and read routing | How were connections redirected, and how fast did the switch take effect? | Connection pools and cached endpoints often delay the switch. |
| Lag visibility | Which view or metric showed lag, and how often was it sampled? | A lag you cannot see is a lag you cannot trust at cutover. |
| Version and configuration compatibility | Do source and target versions, extensions, and settings support the approach? | Mismatches can block the method or create subtle differences. |
| Rollback path | Can writes return to the original server, and what happens to data written only on the new one? | Determines whether a failed cutover is recoverable. |
| Consistency verification | Which checksums or comparisons were run, and on what schedule? | Counts and spot checks can miss divergent rows. |
| Operational complexity | How many components, credentials, and manual steps does the approach need? | More moving parts raise the chance of a mistake during cutover. |
| Recovery if the destination falls behind | What is the procedure if lag grows faster than replay? | Shows whether the move can be paused safely. |
A cutover decision framework
Use these conditions as gates. Do not switch writes until each one is true.
- The destination’s replay position matches the source’s sent position in
pg_stat_replication, and it stays matched across several samples. - Per-table verification on the agreed key ranges has passed on the destination.
- A rollback path has been rehearsed, including how writes made on the destination would be handled if you return to the source.
- Disk space on the source has headroom for WAL retention while the cutover is underway.
- Connection strings, pools, and any cached endpoints point to the intended server after the switch.
If any gate fails, pause the move and keep writing to the source. A delayed cutover costs time, while a bad one can cost data.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Choosing a destination server
If you are building a new destination rather than reusing an existing server, the host is part of the decision. Evaluate any provider, managed or self-hosted, against the same list:
- PostgreSQL major version and extension support matching the source.
- Network path between servers, including bandwidth and latency during the copy.
- Support for streaming replication, and whether replication slots and synchronous settings are available.
- Backup schedule, plus a restore test on the destination before you depend on it.
- Region placement relative to the application and to users.
- Migration assistance, and the cost of extra storage and transfer during the overlap period.
Practical sequencing for a 79 GB database
At this size, the copy phase is usually the longest step, and the time needed depends on disk throughput and network bandwidth more than on PostgreSQL settings. Start the destination with enough disk for the full copy plus WAL retention. Monitor replay continuously during the copy, not only at the end, because a destination that falls behind during the copy will fall further behind during the cutover window.
Keep the source at its existing configuration until cutover. Changing settings on a live primary during a move adds a second variable and makes it harder to explain any lag you observe.
The Bottom Line
The headline makes a concrete claim, but the available text does not show how the author moved the database or what happened. For your own move, choose the approach whose lag you can observe, whose cutover you can rehearse, and whose rollback you can explain before any writes are switched.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
