How would you migrate a 5 TB on-premises PostgreSQL database to a managed cloud service with less than 15 minutes of downtime?

What the interviewer wants to hear

• Assess compatibility: version, extensions, superuser features and network
• Initial bulk copy followed by continuous change replication (logical replication or DMS)
• Validate data, sequences and performance on the target before cutover
• Short cutover: freeze writes, wait for zero lag, sync sequences, switch connections
• Keep a rollback path, such as reverse replication

Model answer

First I would check compatibility: the target version, the extensions we use, features that need superuser, and network bandwidth and latency to the cloud, because 5 TB takes time to copy. A dump and restore would take far too long, so I would use continuous replication: logical replication or a service like AWS DMS, with an initial copy followed by streaming changes. Tables need primary keys or replica identity, and DDL must be frozen or applied to both sides. While it runs I validate row counts and checksums on key tables, test application performance on the target, and rehearse the cutover. On the day I stop writes, wait for lag to reach zero, sync sequences, switch the connection strings or DNS and run smoke tests. If possible I set up reverse replication so we can roll back.

More hard Database Administrator questions

• Design a high-availability setup for a critical PostgreSQL database with RPO near zero and RTO under one minute.
• Your single database is reaching its limits. How do you decide whether to shard, and how would you do it?
• How would you upgrade a large production PostgreSQL database to a new major version with minimal downtime?
• The application is timing out and you suspect lock contention in production. Walk through how you diagnose and fix it.
• Someone ran DELETE without a WHERE clause on an important production table 20 minutes ago. What do you do?
• Design a disaster recovery plan for a critical database that must survive the loss of a whole cloud region.
• A critical 1 TB PostgreSQL table is 60 percent bloat and queries are slowing. How do you fix it without downtime and stop it coming back?
• The business expects traffic to grow ten times over the next 18 months. How do you plan database capacity?
• Your company must meet compliance rules (such as PCI DSS or GDPR) for a database holding personal and card data. What controls do you put in place?