PostgreSQL Backup and Restore

Introduction

In this lab, you will learn how to back up and restore PostgreSQL databases. The lab covers essential techniques for safeguarding your data, including exporting a database using pg_dump, restoring a database using pg_restore, and backing up specific tables.

You'll start by creating a sample database named mydatabase. Then, you'll export this database to a SQL file using the pg_dump command. Next, you'll explore how to restore a database from a backup file using pg_restore. Finally, you'll learn how to back up only specific tables within a database and verify the integrity of the restored data.

Skills Graph

Create and Populate a Database

In this step, you will create a PostgreSQL database named mydatabase and populate it with some sample data. This database will be used for the backup and restore exercises in the following steps.

First, create the mydatabase database using the following command:

sudo -u postgres psql -c "CREATE DATABASE mydatabase;"

This command connects to the PostgreSQL server as the postgres user and executes the SQL command CREATE DATABASE mydatabase.

Next, connect to the mydatabase database using the psql command:

sudo -u postgres psql -d mydatabase

You should now be in the psql shell, connected to the mydatabase database. You'll see a prompt like mydatabase=#.

Now, create a table named users with an id and name column:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

This SQL command creates a table named users with two columns: id (an automatically incrementing integer) and name (a string).

Next, insert some sample data into the users table:

INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');

These SQL commands insert two rows into the users table, with the names 'Alice' and 'Bob'.

Finally, create another table named products and insert some data:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

INSERT INTO products (name) VALUES ('Laptop');
INSERT INTO products (name) VALUES ('Tablet');

To verify that the tables and data have been created successfully, you can run the following SQL queries:

SELECT * FROM users;
SELECT * FROM products;

You should see the data you just inserted.

Exit the psql shell by typing:

\q

You are now back in the Linux terminal.

Export the Database

In this step, you will export the mydatabase database to a SQL file using the pg_dump command. This file will contain the SQL commands needed to recreate the database.

Run the following command to export the database:

pg_dump -U postgres -d mydatabase -f ~/project/mydatabase.sql

This command uses the pg_dump utility to connect to the mydatabase database as the postgres user and export the database to a file named mydatabase.sql in your ~/project directory.

To verify that the file was created successfully, you can list the contents of your ~/project directory:

ls ~/project

You should see mydatabase.sql in the output.

You can also view the first few lines of the backup file using the head command:

head ~/project/mydatabase.sql

This will show you the beginning of the SQL script that was created by pg_dump.

Restore the Database

In this step, you will restore the mydatabase database from the mydatabase.sql backup file into a new database named newdatabase.

First, create the newdatabase database:

sudo -u postgres psql -c "CREATE DATABASE newdatabase;"

Now, restore the database from the backup file:

pg_restore -U postgres -d newdatabase ~/project/mydatabase.sql

This command uses the pg_restore utility to connect to the newdatabase database as the postgres user and restore the database from the mydatabase.sql file.

To verify that the database was restored successfully, connect to the newdatabase database using psql:

sudo -u postgres psql -d newdatabase

And then list the tables:

\dt

You should see the users and products tables listed.

You can also query the data in the tables to verify that it was restored correctly:

SELECT * FROM users;
SELECT * FROM products;

You should see the same data that you inserted in Step 1.

Exit the psql shell:

\q

Backup and Restore a Single Table

In this step, you will learn how to back up and restore a single table from a PostgreSQL database. This can be useful if you only need to back up a specific part of your database.

First, let's back up only the users table from the mydatabase database:

pg_dump -U postgres -d mydatabase -t users -f ~/project/users_only.sql

This command uses the -t option to specify that only the users table should be backed up.

Now, let's restore this table into the newdatabase database. First, drop the existing users table in newdatabase:

sudo -u postgres psql -d newdatabase -c "DROP TABLE IF EXISTS users;"

Then, restore the users table from the users_only.sql file:

pg_restore -U postgres -d newdatabase ~/project/users_only.sql

To verify that the table was restored successfully, connect to the newdatabase database using psql:

sudo -u postgres psql -d newdatabase

And then list the tables:

\dt

You should see the users table listed, but not the products table (since we only restored the users table).

You can also query the data in the users table to verify that it was restored correctly:

SELECT * FROM users;

You should see the same data that was in the users table in the mydatabase database.

Exit the psql shell:

\q

Summary

In this lab, you have learned how to back up and restore PostgreSQL databases using the pg_dump and pg_restore utilities. You learned how to:

• Create a database and populate it with data.
• Export an entire database to a SQL file using pg_dump.
• Restore a database from a SQL file using pg_restore.
• Back up a single table from a database using pg_dump.
• Restore a single table into a database using pg_restore.

These are essential skills for any PostgreSQL database administrator.