Backup challenge

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.

Exercise Backup challenge

Add COPY users TO '/tmp/users.csv' to the previous solution to back up the table.

Show example 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';

Test yourself

What does COPY TO do?
- Creates an index
- Creates a trigger
- Takes a backup
- Creates a view
Show answer

Takes a backup — COPY … TO writes the table's data to a file; it is a simple way to take a backup.
Backup challenge: match the terms

COPY TO

backup
COPY FROM

restore
PITR

point-in-time
test

verify

Backup file

COPY users TO '____';

Show answer

COPY users TO '/tmp/users.csv';

Next lesson: Expert section exam

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies