When would you use a logical backup like pg_dump or mysqldump, and when a physical backup?

What the interviewer wants to hear

• Logical backups export SQL-level data and are portable across versions and platforms
• Logical backups are good for small databases, single tables and migrations
• Physical backups copy data files and are much faster to restore for large databases
• Physical backups plus WAL or binlog archiving allow point-in-time recovery
• Many teams use both, and test restores of each

Model answer

A logical backup, such as pg_dump or mysqldump, exports tables as SQL or a portable archive. It is great for small databases, for copying one table or schema, and for moving data between major versions or platforms. Its weakness is restore time, because every row is reloaded and every index rebuilt, and it only captures one moment. A physical backup, made with pgBackRest, pg_basebackup or Percona XtraBackup, copies the data files themselves. It restores much faster for large databases and, combined with archived WAL or binlogs, lets me recover to any point in time, for example one minute before a bad DELETE. For production I rely on physical backups with archiving as the main recovery path, keep logical dumps of important schemas as an extra, and test restores of both regularly.

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 checks would you do at the start of a normal day as a DBA?
• What is database replication, and what is the difference between synchronous and asynchronous replication?
• 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.