DeployU
Interviews / Databases / How do you do a backup and restore of a PostgreSQL database?

Questions

What is the difference between `INNER JOIN`, `LEFT JOIN`, and `RIGHT JOIN`?
Q C
What are window functions and how do you use them in PostgreSQL?
Q C
What are common table expressions (CTEs) and how do you use them in PostgreSQL?
Q C
What is the difference between `UNION` and `UNION ALL` in PostgreSQL?
Q C
Your database query is slow. How do you optimize it?
Q C
What are indexes and how do they work in PostgreSQL?
Q C
What are transactions and why are they useful in PostgreSQL?
Q C
How do you set up replication in PostgreSQL?
Q C
How do you do a backup and restore of a PostgreSQL database?
Q C
What is the `JSONB` data type and how do you use it in PostgreSQL?
Q C
How do you do full-text search in PostgreSQL?
Q C
What is database normalization and why is it important?
Q C

How do you do a backup and restore of a PostgreSQL database?

practical Backup and Recovery Interactive Quiz Code Examples

The Scenario

You are a database administrator at a fintech company. You are responsible for a PostgreSQL database that is critical to the business.

You need to make sure that you have a backup of the database so that you can restore it in the event of a disaster.

The Challenge

Explain how you would do a backup and restore of a PostgreSQL database. What are the different types of backups, and what are the trade-offs between them?

Wrong Approach

A junior engineer might not be aware of the different types of backups. They might just use `pg_dump` to create a logical backup, which would not be a very robust solution for a large database.

Right Approach

A senior engineer would know that there are two main types of backups: logical and physical. They would be able to explain the trade-offs between the two and would have a clear plan for how to do a backup and restore of a production database.

Step 1: Understand the Different Types of Backups

Backup TypeDescription
LogicalA logical backup is a human-readable file that contains the SQL commands to recreate the database.
PhysicalA physical backup is a copy of the files that make up the database.

Step 2: Choose the Right Tool for the Job

Use CaseRecommended Tool
Backing up a small databasepg_dump
Backing up a large databasepg_basebackup
Point-in-time recoveryContinuous archiving

Step 3: Create a Backup

Here’s how we can create a backup using pg_dump:

pg_dump -U myuser -d mydb > mydb.sql

Here’s how we can create a backup using pg_basebackup:

pg_basebackup -h myhost -D /path/to/backup -U myuser -P

Step 4: Restore a Backup

Here’s how we can restore a backup from a pg_dump file:

psql -U myuser -d mydb < mydb.sql

To restore a backup from a pg_basebackup, you just need to start a new PostgreSQL server using the backup directory as the data directory.

Continuous Archiving and Point-in-Time Recovery (PITR)

For a production database, you would want to use continuous archiving and point-in-time recovery (PITR). This involves continuously archiving the WAL (Write-Ahead Logging) files to a separate location. This allows you to restore the database to any point in time.

Practice Question

You need to be able to restore your database to any point in time. Which of the following would you use?