PostgreSQL Backup and Restore

Introduction

In this lab, you will learn the essential techniques for backing up and restoring PostgreSQL databases. Properly managing backups is a critical skill for any database administrator to prevent data loss.

You will start by creating a sample database and populating it with data. Then, you will use the pg_dump utility to create a complete backup of the database. Following that, you will learn how to restore the database from the backup file using the psql command-line tool. Finally, you will practice backing up and restoring a single, specific table from your database.

Create and Populate a Sample Database

In this step, you will create a PostgreSQL database named salesdb and populate it with sample data. This database will serve as the basis for the backup and restore operations in the subsequent steps.

Step 1: Create the Database

First, from your terminal, create the salesdb database using the createdb command-line utility. This is a convenient wrapper for the CREATE DATABASE SQL command.

createdb salesdb

Step 2: Connect to the Database

Next, connect to your newly created salesdb database using the psql interactive terminal.

psql -d salesdb

You will now see the psql prompt, which looks like salesdb=#, indicating you are connected to the salesdb database.

Step 3: Create Tables and Insert Data

Inside the psql shell, execute the following SQL commands to create two tables: customers and products.

Create the customers table:

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Create the products table:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2)
);

Now, insert some sample data into both tables.

Insert data into customers:

INSERT INTO customers (name) VALUES ('Alice'), ('Bob');

Insert data into products:

INSERT INTO products (name, price) VALUES ('Laptop', 1200.00), ('Mouse', 25.00);

Step 4: Verify the Data

To confirm that the data was inserted correctly, run SELECT queries on both tables.

SELECT * FROM customers;

The output should be:

 id | name
----+-------
  1 | Alice
  2 | Bob
(2 rows)

Now, check the products table:

SELECT * FROM products;

The output should be:

 id |  name  |  price
----+--------+---------
  1 | Laptop | 1200.00
  2 | Mouse  |   25.00
(2 rows)

Finally, exit the psql shell to return to your regular terminal.

\q

You have successfully set up the database for the next steps.

Back Up the Entire Database

In this step, you will use the pg_dump utility to create a full backup of the salesdb database. pg_dump generates a file containing SQL commands that can be used to reconstruct the database.

Understanding pg_dump

pg_dump is a standard PostgreSQL utility for backing up a single database. It creates a consistent snapshot of the database, even if it is being actively used. By default, it generates a plain-text SQL script file.

Step 1: Perform the Backup

From your terminal, run the following command to dump the salesdb database into a file named salesdb.sql inside your project directory.

pg_dump salesdb > ~/project/salesdb.sql

Let's break down the command:

• pg_dump salesdb: This specifies that you want to back up the salesdb database.
• > ~/project/salesdb.sql: This redirects the standard output of the command to a file named salesdb.sql in the ~/project directory.

Step 2: Verify the Backup File

First, check if the file was created using the ls command.

ls -l ~/project

You should see salesdb.sql in the file listing.

Next, inspect the contents of the backup file to understand what pg_dump created. Use the head command to view the first few lines.

head ~/project/salesdb.sql

The output will show the start of the SQL script, including comments about the pg_dump version and commands to set up the database environment. This confirms your backup file is a valid SQL script.

Restore the Database from a Backup

In this step, you will learn how to restore a database from a plain-text SQL backup file. You will create a new database and then use the psql utility to execute the SQL commands from your backup file.

Understanding Database Restoration

For plain-text SQL files created by pg_dump, the psql utility is the standard tool for restoration. It reads the SQL commands from the file and executes them against the specified database, effectively recreating the tables and data.

Step 1: Create a New Database for Restoration

To avoid overwriting your original database, you will restore the backup into a new, empty database. Create a database named salesdb_restored.

createdb salesdb_restored

Step 2: Restore the Backup

Now, use psql to restore the salesdb.sql backup into the salesdb_restored database.

psql -d salesdb_restored -f ~/project/salesdb.sql

Let's break down the command:

• psql: The PostgreSQL interactive terminal, which can also execute script files.
• -d salesdb_restored: Specifies the target database for the restoration.
• -f ~/project/salesdb.sql: Specifies the input file containing the SQL commands to execute.

You will see the output of the SQL commands being executed, such as CREATE TABLE and INSERT.

Step 3: Verify the Restored Database

Connect to the salesdb_restored database to verify that the tables and data have been successfully restored.

psql -d salesdb_restored

Inside the psql shell, first list the tables:

\dt

You should see the customers and products tables.

              List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | customers | table | labex
 public | products  | table | labex
(2 rows)

Next, query the customers table to ensure the data is present.

SELECT * FROM customers;

The output should match the original data.

 id | name
----+-------
  1 | Alice
  2 | Bob
(2 rows)

Exit the psql shell.

\q

You have successfully restored a database from a backup.

Back Up and Restore a Single Table

Sometimes, you may only need to back up or restore a specific table rather than the entire database. The pg_dump utility supports this with the -t option.

Step 1: Back Up a Single Table

Let's back up only the customers table from the original salesdb database.

Run the following command in your terminal:

pg_dump -d salesdb -t customers > ~/project/customers_table.sql

• -d salesdb: Specifies the source database.
• -t customers: Specifies the target table to be dumped.
• > ~/project/customers_table.sql: Redirects the output to a new backup file.

Step 2: Simulate Data Loss

To demonstrate the restore process, let's first drop the customers table from the salesdb_restored database to simulate accidental deletion.

psql -d salesdb_restored -c "DROP TABLE customers;"

The command will output DROP TABLE, confirming the deletion.

Step 3: Restore the Single Table

Now, restore the customers table from your table-specific backup file into the salesdb_restored database.

psql -d salesdb_restored -f ~/project/customers_table.sql

Step 4: Verify the Table Restoration

Finally, connect to the salesdb_restored database and verify that the customers table is back, while the products table remains untouched.

psql -d salesdb_restored

Inside psql, list the tables:

\dt

You should see both customers and products tables listed again.

              List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | products  | table | labex
 public | customers | table | labex
(2 rows)

Query the restored customers table to confirm its data is intact.

SELECT * FROM customers;

The output should show the original customer data.

 id | name
----+-------
  1 | Alice
  2 | Bob
(2 rows)

Exit the psql shell.

\q

You have successfully backed up and restored an individual table.

Summary

In this lab, you have learned the fundamental procedures for backing up and restoring PostgreSQL databases. These skills are essential for data protection and disaster recovery.

You have learned how to:

• Create a database and populate it with tables and data.
• Use pg_dump to export an entire database to a plain-text SQL file.
• Use psql to restore a database from a SQL backup file into a new database.
• Back up a single table from a database using the -t option with pg_dump.
• Restore a single table into a database.

Mastering these commands provides you with the confidence to manage and protect your PostgreSQL data effectively.