iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Moving a busy PostgreSQL database to a new server without stopping the application is a replication problem, not a file copy. Sabudh Thapa, a backend engineer in Kathmandu, Nepal, describes doing this for one 79 GB database three different ways while the application kept sending reads and writes. The method names, timings and final outcomes are not visible in the copy of the post available for this article, so nothing below attributes a specific technique or result to the author. Instead, this guide sets out what any live move has to prove, based on the PostgreSQL 18 documentation, so you can judge each approach, including the author’s, against the same tests.
What the source does and does not establish
- Author and origin: the post is by Sabudh Thapa and appears on the author’s blog at https://tsabudh.com.np/blog/migrating-live-postgres-without-stopping-writes/. A DEV Community syndication shows a “Posted on Sep 24” date, which search metadata places in 2024.
- Headline claims: one 79 GB database, three server-to-server approaches, and ongoing application reads and writes. These are the author’s own framing. They are not independently audited measurements.
- Versions: a September 2024 post predates PostgreSQL 18, which was released in 2025. The source and target versions the author used should be read from the post itself, not assumed to match the 18 documentation cited here.
Why live writes turn a copy into a replication project
A copy taken at one moment is stale as soon as the next transaction commits. The standard way to close that gap is to ship changes continuously from the source to the destination, let the destination catch up, and then switch the application over. PostgreSQL’s built-in physical replication does this. According to the “Log-Shipping Standby Servers” chapter of the PostgreSQL 18 documentation, streaming replication keeps a standby more up to date than file-based log shipping because it streams write-ahead log (WAL) records from the primary as they are generated, rather than waiting for a complete WAL file.
Streaming replication is asynchronous by default. A transaction can commit on the primary before its changes are visible on the standby, and the gap, or lag, depends on write volume, network throughput, standby disk and CPU capacity, and configuration. A small lag under normal load is expected, but it is not zero, and it can grow during a heavy write burst. Any cutover plan has to show that the destination has received and replayed everything the source has committed.
Synchronous commit: a stronger guarantee with a latency cost
Synchronous replication makes a commit wait until a standby acknowledges it. The PostgreSQL documentation states that this increases transaction response time, because every commit now includes a round trip to the standby. It is the tool to reach for when losing a committed transaction at cutover is unacceptable, but it changes how the application behaves during the migration window. If a method in the original post relied on synchronous commit, its latency effect on the application is the figure to check, and the post’s own description of that effect is the one to rely on.
#1 Best Overall
- Easily store and access 2TB to content on the go with the Seagate Portable Drive, a USB external hard drive
- Designed to work with Windows or Mac computers, this external hard drive makes backup a snap just drag and drop
- To get set up, connect the portable hard drive to a computer for automatic recognition no software required
- This USB drive provides plug and play simplicity with the included 18 inch USB 3.0 cable
- The available storage capacity may vary.
Replication slots and the risk to pg_wal
A replication slot tells the primary to keep WAL until a specific consumer has read it, which protects a standby that falls behind. The PostgreSQL manual warns that this retention can fill the space allocated to pg_wal on the primary. If the primary runs out of disk, it stops accepting writes, which is the outage a live migration is meant to avoid.
Check slots on the primary before and during the move:
Rank #2
- Easily store and access 5TB of content on the go with the Seagate portable drive, a USB external hard Drive
- Designed to work with Windows or Mac computers, this external hard drive makes backup a snap just drag and drop
- To get set up, connect the portable hard drive to a computer for automatic recognition software required
- This USB drive provides plug and play simplicity with the included 18 inch USB 3.0 cable
- The available storage capacity may vary.
SELECT slot_name, active, wal_status FROM pg_replication_slots;shows whether each slot is active and whether the WAL it needs is still retained. Awal_statusoflostmeans the standby can no longer catch up from WAL and must be rebuilt.max_slot_wal_keep_size(available since PostgreSQL 13) caps how much WAL a slot can retain. Setting it protects the disk, but a slot that exceeds the cap is invalidated, so you trade disk safety for the risk of a rebuild.- Monitor free space on the filesystem that holds
pg_walalongside the slot status. Retention problems usually appear as disk growth first.
Comparing migration approaches on the same criteria
Three approaches can only be compared fairly if each is measured against the same questions. The table below lists the criteria that decide whether a method is safe for a live database. Record each value for each approach rather than relying on a summary.
| Criterion | What to record | Why it decides the method |
|---|---|---|
| Copy and catch-up time | Time for the initial copy, then time until lag reaches an acceptable level | Determines how long the window for cutover stays open |
| Write continuity | Whether any write fails or is queued during the move, and for how long | Separates a seamless move from one with a hidden outage |
| Cutover and read routing | How reads and writes are redirected, and whether both can be redirected independently | Shapes the downtime and the risk of split writes |
| Lag visibility | Which view or metric showed lag, and how often it was checked | Without it, the cutover decision is a guess |
| Version and configuration compatibility | Source and target major versions, and any settings the method requires | Some methods fail or need extra setup when versions or settings differ |
| Rollback path | How writes would return to the source if the destination fails after cutover | A move without a return path is a one-way bet |
| Consistency verification | Row counts, checksums or spot queries on critical tables, and when they ran | Confirms the copy matches what the application wrote |
| Operational complexity | Number of components, manual steps, and who must be on call | Complexity is where unplanned errors come from |
| Recovery if the destination falls behind | What happens to lag and slot retention, and how it is reversed | Decides whether a slow move can be rescued or must be restarted |
A cutover checklist for a live PostgreSQL move
- Confirm the destination runs the same PostgreSQL major version as the source and that the settings your replication method needs are in place on both servers.
- Check lag on the primary:
SELECT client_addr, state, sent_lsn, replay_lsn, replay_lag FROM pg_stat_replication;. Do not cut over whilereplay_lagis growing. - Check the slot status with the query shown above, and confirm free space on the
pg_walfilesystem. - Stop new writes to the source, or route them away from it. This step sets the downtime, so keep it as short as the method allows.
- Compare the source’s current WAL position,
SELECT pg_current_wal_lsn();on the primary, with the standby’s replay position,SELECT pg_last_wal_replay_lsn();on the standby. Proceed only when the standby has replayed to that point. - Promote the standby with
SELECT pg_promote();orpg_ctl promoteon the destination. - Verify the data on the new primary using row counts, checksums or known-value queries on critical tables, then run application smoke tests.
- Point the application at the destination and keep the old primary intact. Writes that land on the new primary after promotion do not flow back automatically, so a rollback either needs a reverse replication path or accepts losing those writes. Decide which before cutover.
When the destination falls behind
- Lag keeps growing: check network throughput between the servers, disk I/O on the standby, and whether long-running queries on the standby are delaying replay.
- Slot retention is climbing: the standby is consuming WAL more slowly than the primary generates it. Reduce write load if the method allows, add standby capacity, or rebuild the standby if the slot has been invalidated.
- Lag does not recover: stop the cutover plan and restart from a fresh base copy rather than cutting over to a destination you cannot prove is current.
Logical replication is a different tool
PostgreSQL’s logical replication is documented in its own chapter of the PostgreSQL 18 documentation and works at the level of table changes rather than physical WAL. It can suit some migrations where the source and destination versions differ, but it has its own restrictions, including requirements on tables and schema changes. Check those restrictions in that chapter before choosing it, and do not assume a method built on physical streaming carries over.
Rank #3
- Easily store and access 1TB to content on the go with the Seagate Portable Drive, a USB external hard drive.Specific uses: Personal
- Designed to work with Windows or Mac computers, this external hard drive makes backup a snap just drag and drop. Reformatting may be required for Mac
- To get set up, connect the portable hard drive to a computer for automatic recognition no software required
- This USB drive provides plug and play simplicity with the included 18 inch USB 3.0 cable
- The available storage capacity may vary.
Questions to answer from the original post
Once you read the full post, check each of the three approaches against these questions:
- Which PostgreSQL versions were on the source and destination, and what replication or copy mechanism did each approach use?
- How long did the initial copy take, and how long did it take to reach low lag while writes continued?
- How were reads and writes redirected, and was there any write downtime or failed write?
- What lag measurement was used, and what lag was accepted before cutover?
- What consistency checks ran after cutover, and what was the rollback plan?
- What did the author observe about slot retention, disk use and latency during the move?
Any answer the post gives should be read as the author’s report of one environment, not a general result for PostgreSQL migrations.
Rank #4
- Easily store and access 4TB of content on the go with the Seagate Portable Drive, a USB external hard drive.Specific uses: Personal
- Designed to work with Windows or Mac computers, this external hard drive makes backup a snap just drag and drop
- To get set up, connect the portable hard drive to a computer for automatic recognition no software required
- This USB drive provides plug and play simplicity with the included 18 inch USB 3.0 cable
- The available storage capacity may vary.
The Bottom Line
Choose the method whose lag you can measure, whose write freeze you can keep short, and whose rollback you can execute before you start. Judge the three approaches in the original post by those tests rather than by the headline.
Recommended Free Tools
Quick Recap
Best Value
- [Upgraded Version] - This external hard drive features a mirrored logo stripe combined with a striped anti-slip design, and the rounded corners of the casing make it easier to grip. The stripes also have a heat dissipation function, ensuring stable and fast data transfer.
- 【Ultra-thin and quiet】 - The motherboard adopts JMicron 578 noise-free solution, giving you a quiet working environment. Lightweight and portable size designed to fit in your pocket for easy portability.
- 【Ultra-Fast Data Transfers】 - Pairing this external hard drive with JMicron 578 solution USB 3.0 and USB 2.0 interfaces enables blazing-fast data transfer. It boasts theoretical read speeds of up to 125MB/s and write speeds of up to 103MB/s.
- 【Plug and Play】 - With no software to install, just plug it in and the drive is ready to use.The hard disk chip is wrapped with an aluminum anti-interference layer to increase heat dissipation and protect data.
- 【What You Get】 - 1 x Portable Hard Drive, 1 x USB 3.0 Cable, 1 x User Manual, Gift-type shell packaging ,Three-year manufacturer's warranty and free technical support services.
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.
