Backup, Restore, and Database Cloning

Backup, Restore, and Database Cloning

Managing databases involves two critical skills: Safety (Backups) and Cloning (Copying). In PostgreSQL, these tasks are primarily handled through the command line using utilities like pg_dump and psql.

1. Database Backup (pg_dump)

A backup creates a text file containing the SQL commands necessary to reconstruct the database to the exact state it was in at the time of the backup.

• Command: pg_dump -U username -d dbname > backup_file.sql
• Key Flags:
  • -U: Specifies the database user (e.g., postgres).
  • -d: The name of the database you want to back up.
  • > : Redirects the output into a file.

2. Database Restore (psql)

Restoring involves running the SQL script generated by the backup against a fresh database.

• Step 1: Create an empty database: CREATE DATABASE new_db;
• Step 2: Run the restore command:
  • psql -U username -d new_db < backup_file.sql

3. How to Copy a Database

Sometimes you need to duplicate a database on the same server (e.g., creating a “test” version of a “production” database). PostgreSQL makes this easy with the TEMPLATE clause.

SQL Syntax:

CREATE DATABASE destination_db 
WITH TEMPLATE source_db;

Note: You must disconnect all users from the source_db before running this command, or the copy will fail.

Tasks

Complete these tasks to master the workflow. Assume your username is postgres.

Task 1: The Safety Net

1. Create a database named school_db.
2. Create a table named students with columns for id and name.
3. Insert two rows of data.
4. Action: Run a command in your terminal to back up school_db to a file named school_backup.sql.

Task 2: The Time Machine

1. Drop your school_db database (DROP DATABASE school_db;).
2. Create a brand new, empty database called school_recovered.
3. Action: Restore the data from school_backup.sql into school_recovered. Verify the students are still there.

Task 3: The Sandbox

1. You want to experiment with new features without breaking school_recovered.
2. Action: Use the TEMPLATE command to create a copy of school_recovered named school_test.

Troubleshooting Tips

• Role issues: If you get a “Permission Denied” error, ensure your user has SUPERUSER or CREATEDB privileges.
• Pathing: On Windows, you may need to navigate to the PostgreSQL bin folder (e.g., C:\Program Files\PostgreSQL\16\bin) in your command prompt to run pg_dump.
• Active Connections: If you can’t copy a database because it’s “being accessed by other users,” run this query to kick everyone off:

• SELECT pg_terminate_backend(pg_stat_activity.pid)
  FROM pg_stat_activity
  WHERE pg_stat_activity.datname = 'source_db_name'
    AND pid <> pg_backend_pid();