Live Database Migration Safely to AWS RDS Using Bucardo
How EasyLauncher engineered an asynchronous, trigger-based replication pipeline to migrate a 3.5 TB mission-critical PostgreSQL database from an on-premise datacenter to AWS RDS with zero seconds of downtime and zero lost transactions.
Executive Summary
For high-transaction digital platforms, the database is the central heart of operational continuity. When an enterprise financial technology provider outgrew their aging on-premise hardware infrastructure, migrating their primary PostgreSQL database to fully managed Amazon RDS was designated as an urgent strategic milestone.
The database supported continuous global operations: over 4,200 write transactions per second, hundreds of relational tables, and 3.5 TB of structured customer ledgers, account balances, and audit history. Traditional maintenance windows (taking the application offline for 12 to 24 hours to execute a database backup and restore) were commercially impossible, carrying estimated revenue losses exceeding $400,000 per hour.
“When a financial ledger transacts around the clock, there is no such thing as a scheduled maintenance outage. The migration must happen live while money moves.”
EasyLauncher Database Reliability PracticeEasyLauncher was contracted to architect and execute a zero-downtime database migration strategy. By deploying a custom Bucardo asynchronous replication architecture over dedicated cloud networking, our database reliability engineering team synchronized 3.5 TB of active relational records and executed the production cutover with zero downtime and 100% row checksum parity.
The Terabyte Migration Dilemma
Migrating a multi-terabyte production database across physical boundaries introduces intense technical risks that simple tooling cannot navigate:
4,200+ Write TPS Around the Clock
High-volume INSERT and UPDATE operations generated hundreds of megabytes of WAL (Write-Ahead Logs) every minute, threatening replication lag over wide-area networks.
3.5 TB Across 280+ Tables
Massive historical ledger tables, indexed JSONB audit trails, and bytea binary document storage created significant I/O pressure during initial bulk transfers.
AWS RDS Superuser Restrictions
AWS RDS does not provide raw OS-level superuser root access, preventing raw physical file streaming or low-level replication hooks that require root filesystem permissions.
Strict Zero-Data-Loss Mandate
Every transaction represented financial settlement records. Dropping or duplicating even a single row during cutover would trigger compliance and regulatory penalties.
Why Traditional Migration Methods Failed
Before selecting Bucardo, our architects benchmarked and eliminated standard database migration utilities:
- pg_dump and pg_restore: Dumping 3.5 TB required over 9 hours, and restoring to RDS took approximately 14 hours. Freezing the business for a 23-hour downtime window was strictly rejected by company leadership.
- AWS Database Migration Service (DMS): During dry runs, DMS struggled with specific custom PostgreSQL data types, exhibited unpredictable replication delays on heavily updated tables with JSONB fields, and did not replicate primary key sequence offsets.
- Physical Streaming Replication: Native PostgreSQL physical replication requires exact version matching and OS superuser controls that were blocked by Amazon RDS managed security policies.
The Engineered Bucardo Replication Topology
Bucardo is an asynchronous PostgreSQL replication engine capable of multi-master and master-slave replication. It operates logically using database triggers, making it independent of underlying operating system architectures and ideal for migrating to managed cloud databases like AWS RDS.
Replication Architecture Pipeline
Row Parity & Integrity Verification
Before cutover, EasyLauncher executed an exhaustive data validation suite to certify that the target RDS database mirrored on-premise records with zero divergence:
- Row Count Verification: Executed atomic transaction count audits across all tables simultaneously, confirming zero missing rows.
- Chunked MD5 Checksums: Partitioned large historical tables into deterministic primary key blocks of 100,000 rows, computing MD5 checksums across on-premise and RDS nodes to verify column-level parity.
- Sequence Synchronization: Scripted automated checks querying
pg_get_serial_sequence()across every table, advancing RDS sequence values by a safe buffer to prevent auto-increment key collisions post-cutover.
The Zero-Downtime Cutover Sequence
The final production transition was orchestrated using PgBouncer connection pooling and fast DNS rerouting during a scheduled window:
Cutover Execution Timeline (Total Elapsed Time: 78 seconds)
Migration Method Comparison
How the EasyLauncher Bucardo replication architecture outperformed alternative migration patterns:
| Evaluation Metric | pg_dump / Restore | AWS DMS | EasyLauncher Bucardo Pipeline |
|---|---|---|---|
| Application Downtime | 18 to 24 Hours | 15 to 45 Minutes | 0.00 Seconds (Zero Downtime) |
| Sequence Migration | Manual capture required | Not supported natively | Automated pre-cutover sync |
| JSONB & Blob Handling | Reliable but slow | Known LOB truncation limits | Native PostgreSQL trigger fidelity |
| Rollback Capability | None (data loss after cutover) | Complex reverse replication | Bidirectional fallback standby |
Bucardo Replication Blueprint
Representative shell commands configuring the Bucardo database herd and synchronization pipeline:
# 1. Install Bucardo schema on replication controller bucardo install --batch # 2. Register on-premise source and AWS RDS target databases bucardo add database source_onprem dbname=core_prod host=10.0.1.50 user=bucardo_user bucardo add database target_rds dbname=core_prod host=rds-cluster.prod.internal user=bucardo_admin # 3. Add all operational tables and sequences to Bucardo herd bucardo add all tables db=source_onprem --herd=prod_herd --pkonly bucardo add all sequences db=source_onprem --herd=prod_herd # 4. Create asynchronous sync pipeline with source_wins conflict policy bucardo add sync cloud_migration_sync \ herd=prod_herd \ dbs=source_onprem:source,target_rds:target \ conflict=source_wins \ onetimecopy=2 \ autokick=1 # 5. Start Bucardo replication daemon and monitor lag bucardo start bucardo status cloud_migration_sync
Verified Outcomes & Reliability Gains
The completed migration delivered immediate operational, architectural, and financial returns:
Continuous operations during the entire 3.5 TB data transfer with zero user-facing error spikes or dropped checkout transactions.
Every table, constraint, primary key sequence, and JSONB audit trail verified with zero bit divergence.
Transitioned to Multi-AZ Amazon RDS PostgreSQL with automated snapshot backups, read replicas, and zero manual server maintenance.
Upgraded storage IOPS and modern PostgreSQL query engine optimizations reduced 99th percentile query latency from 180ms down to 98ms.
Frequently Asked Questions
Common architectural questions regarding live database migrations and Bucardo replication:
Why was Bucardo selected over native PostgreSQL logical replication or AWS DMS?
The source PostgreSQL version ran on an older self-hosted cluster where native pgoutput logical replication had major schema constraints, and AWS Database Migration Service (DMS) struggled with heavy JSONB payload sizes and sequence synchronization. Bucardo uses Perl-based trigger hooks that allow non-blocking asynchronous change capture with granular table filtering and custom conflict handling.
How do you copy 3.5 TB of data without saturating the production database or network bandwidth?
We segregated the process into two streams: initial baseline copy and continuous delta harvesting. We exported baseline data using multi-threaded parallel chunks via COPY during low-traffic windows while Bucardo triggers were already capturing updates in delta tables. Network saturation was prevented via rate-limiting rsync pipes over an encrypted Direct Connect link.
How do you ensure zero data loss when switching application database connections?
During the cutover, the application connection pools (PgBouncer) are paused briefly for less than 1.5 seconds. Once Bucardo delta queues reach zero pending transactions, sequence numbers (autoincrement IDs) are synchronized, and PgBouncer directs active traffic to the AWS RDS endpoint without dropping client HTTP requests.
How are sequence primary keys prevented from colliding during replication?
Bucardo replicates table rows, but PostgreSQL sequence counters require manual catch-up. Prior to the cutover window, our verification suite ran an automated script querying pg_get_serial_sequence() across every table, executing setval() on the target RDS database with a safe margin to prevent duplicate key constraint violations.
Can this migration architecture roll back if AWS RDS encounters an unexpected issue?
Yes. By configuring Bucardo in a bidirectional or reverse-delta standby configuration, transactions executed on RDS can be replicated back to the self-hosted database during the initial 48-hour burn-in phase, providing an instant zero-data-loss rollback safety net.
Planning a Terabyte-Scale Cloud Database Migration?
EasyLauncher engineers reliable zero-downtime database migrations, active replication pipelines, and high-availability database architectures across AWS, GCP, and Azure.
Explore More Case Stories
Discover how we help enterprises optimize operations, automate workflows, and scale architectures.
