What checks would you do at the start of a normal day as a DBA?

What the interviewer wants to hear

• Confirm last night's backups finished and WAL or binlog archiving is healthy
• Check replication status and lag on every replica
• Look at disk space, CPU, memory and connection counts
• Review the error log and slow query reports
• Look for long-running or idle-in-transaction sessions and blocked queries

Model answer

I start with the things that can turn into an outage. First I confirm last night's backups completed and that WAL or binlog archiving has no failures, because without those we cannot recover. Then I check replication: every replica connected and lag within limits. Next I look at the dashboards for disk space and growth, CPU, memory, I/O and connection counts compared with max_connections. I scan the error log for new errors, such as authentication failures or out-of-memory messages, and the slow query report or pg_stat_statements for queries that got worse. Finally I look at pg_stat_activity or SHOW PROCESSLIST for long-running queries, sessions idle in transaction and lock waits. Anything repeated becomes an automated alert so the checks get shorter over time.

More easy Database Administrator questions

• What is a database index, and what are the costs of adding one?
• Explain the ACID properties of a database transaction with a simple example.
• How would you design a basic backup strategy for a production database?
• What is normalization, and when would you choose to denormalize?
• How do you find the slowest queries on a database server?
• What is database replication, and what is the difference between synchronous and asynchronous replication?
• When would you use a logical backup like pg_dump or mysqldump, and when a physical backup?
• What basic steps would you take to secure a new production database?
• Explain the main SQL join types and give an example of when you would use each.