How would you upgrade a large production PostgreSQL database to a new major version with minimal downtime?

What the interviewer wants to hear

• Read release notes for incompatible changes and test the app
• Options: pg_upgrade with --link, or logical replication to a new cluster
• Logical replication gives the shortest downtime but has limits (sequences, DDL, large objects)
• Rehearse on a copy and measure timing
• Run ANALYZE after upgrade and have a rollback plan

Model answer

First I read the release notes and test the application against the new version in staging, including extension versions. There are two main methods. pg_upgrade with --link mode is fast even for large databases because it reuses data files, but the database is down during the upgrade and rollback is harder once the new server starts. For minimal downtime I prefer logical replication: build a new cluster on the new version, copy the schema, subscribe to the old primary, and let it catch up. Logical replication does not copy DDL or sequence values, so I freeze schema changes and set sequences before cutover. At cutover I stop writes, wait for zero lag, sync sequences, and switch the connection endpoint. Managed services offer a similar blue-green deployment. I rehearse on a production copy, run ANALYZE afterwards, and keep the old cluster ready for rollback.

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?
• 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.
• How would you migrate a 5 TB on-premises PostgreSQL database to a managed cloud service with less than 15 minutes of downtime?
• 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?