Backup challenge
- SQL course
- Expert
- Lesson 45/45
- 6 min
Write a backup and recovery scenario.
Backup challenge
Last challenge: back up the users table to a CSV file with COPY TO. Regular backups save you in a disaster.
COPY users TO '/tmp/users.csv';COPY TO takes a backup.
Practice Backup challenge
Add COPY users TO '/tmp/users.csv' to the previous solution to back up the table.
Starter code
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT UNIQUE,
password_hash TEXT
);
-- optimization
EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';
-- security
PREPARE find_user(text) AS SELECT * FROM users WHERE email = $1;
-- backup
COPY usersSample solution
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT UNIQUE,
password_hash TEXT
);
-- optimization
EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';
-- security
PREPARE find_user(text) AS SELECT * FROM users WHERE email = $1;
-- backup
COPY users TO '/tmp/users.csv';Expected output
COPY 3
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 What does COPY TO do?
- Creates an index
- Creates a trigger
- Takes a backup Correct answer
- Creates a view
Explanation: COPY … TO writes the table's data to a file; it is a simple way to take a backup.
2 Backup challenge: match the terms
| Term | Meaning |
|---|---|
COPY TO |
backup |
COPY FROM |
restore |
PITR |
point-in-time |
test |
verify |
3 Backup file
Question
COPY users TO '____';
Answer
COPY users TO '/tmp/users.csv';Explanation: /tmp/users.csv is the right answer.