What is Transaction Log Shipping and How is it Used?
Learn what transaction log shipping is, how standbys replay shipped logs, and how it powers database failover.
Expected Interview Answer
Transaction log shipping is a high-availability technique where the write-ahead or transaction log records generated on a primary database are continuously copied to one or more standby servers, which replay them to keep an up-to-date, near-identical copy of the data ready to take over if the primary fails.
Rather than copying whole data files, log shipping moves the same compact log records the database already writes for durability and crash recovery, streaming or periodically transferring them to a standby instance. The standby applies each batch of log records in order, effectively replaying the same history the primary went through, so its data pages end up matching the primary's. Because log shipping reuses the log rather than a separate replication protocol, it is often the underlying mechanism behind physical replication and warm-standby failover setups, with the trade-off that standbys typically lag the primary by however long shipping and replay take, known as replication lag.
- Provides a warm standby ready for failover with minimal data loss
- Reuses the existing transaction log instead of a separate replication format
- Supports offloading read-only queries to standbys in many setups
- Enables point-in-time recovery when archived log segments are retained
AI Mentor Explanation
Think of a stadium that streams its ball-by-ball scorer's log to a backup commentary booth in real time, rather than sending a fresh full scoreboard photo after every ball. The backup booth replays each logged ball in order to keep its own scoreboard matching the stadium's, staying just moments behind. Transaction log shipping works the same way: the primary's log stream is what gets sent, and the standby replays it to stay synchronized.
Step-by-Step Explanation
Step 1
Primary generates log records
Every committed change is written to the primary’s write-ahead or transaction log as part of normal operation.
Step 2
Ship log segments to the standby
Completed log segments (or a continuous stream) are transferred to one or more standby servers over the network.
Step 3
Standby replays the log
The standby applies each received log record in order, reproducing the same changes the primary made.
Step 4
Promote on failover
If the primary fails, a standby that has replayed the most recent log can be promoted to become the new primary.
What Interviewer Expects
- Understanding that log shipping transfers log records, not full data files
- Awareness of replication lag between the primary and the standby
- Knowledge that this underlies many physical replication and failover setups
- Ability to distinguish log shipping from logical or statement-based replication
Common Mistakes
- Confusing log shipping with full periodic database backups
- Assuming standbys are always perfectly in sync with zero lag
- Not mentioning that failover requires promoting a standby
- Conflating log shipping with sharding, which is unrelated
Best Answer (HR Friendly)
“Transaction log shipping means the primary database continuously sends the same log it uses for crash recovery to a backup server, and that backup server replays the log to stay almost up to date. If the primary goes down, the backup can be promoted to take over quickly with very little data loss.”
Code Example
-- On the primary: archive completed WAL segments for shipping
-- (postgresql.conf)
-- archive_mode = on
-- archive_command = 'cp %p /archive/%f'
-- On the standby: continuously fetch and replay shipped WAL
-- segments instead of accepting direct writes
-- restore_command = 'cp /archive/%f %p'
-- hot_standby = on
-- The standby stays read-only and behind by however long
-- shipping and replay take, until it is promoted:
SELECT pg_promote();
-- promotes this standby to become the new read-write primaryFollow-up Questions
- What is the difference between log shipping and streaming replication?
- How does replication lag affect failover data loss guarantees?
- What is the difference between physical and logical log-based replication?
- How would you monitor how far behind a standby is from the primary?
MCQ Practice
1. What does transaction log shipping transfer from the primary to the standby?
Log shipping streams or periodically transfers the compact log records the primary already generates, not full data files.
2. What is a typical trade-off of transaction log shipping compared to a perfectly synchronous copy?
Because log shipping and replay take some time, the standby is usually slightly behind the primary, known as replication lag.
3. What must happen for a standby kept in sync via log shipping to become the new active database?
A standby remains read-only, replaying logs, until it is explicitly promoted to take over as the new primary during failover.
Flash Cards
What is transaction log shipping? — Continuously copying a primary’s transaction log to standby servers, which replay it to stay synchronized.
What does the standby do with shipped log records? — Replays them in order to reproduce the same changes the primary made.
What is replication lag in log shipping? — The delay between a change committing on the primary and it being replayed on the standby.
What must happen before a standby serves writes? — It must be explicitly promoted to become the new primary.