How to Use pg_dump to Create a Backup File of the Database in PostgreSQL

1. Create and Connect to the Database in PostgreSQL
2. Use pg_dump to Create a Backup File of the Database in the Current Directory
3. Use pg_dump to Create a Backup File of the Database in a Custom Directory
4. Conclusion

Data backup is a crucial process to ensure that precaution measures are effective if there is an incident such as data loss. We can backup data in different forms and ways depending on the size and the environment.

This article will discuss how to create a backup of the PostgreSQL database and access the different locations the file is stored on our computer.

Create and Connect to the Database in PostgreSQL

When working with real user data, store the backup in a secure environment such as a USB stick, external hard drive, cloud storage, or other safe location.

We will create a database with one entity and then create a backup of this database. The entity will contain only one record, but you can add more for testing purposes.

We will then use the pg_dump command-line tool to back up a single database to a script file. The script file is a text file with the SQL commands to reconstruct the database to its state when saved.

To reconstruct the database, feed the Psql with the script.

Use the command shown below to log in to the PostgreSQL server.

david@david-HP-ProBook-6470b:~$ psql -U postgres
Password for user postgres:

Enter the password for user postgres and press the Enter button on your keyboard.

psql (14.2 (Ubuntu 14.2-1.pgdg18.04+1))
Type "help" for help.

postgres=#

Create a database named pg_dump_db which will contain our entity. Copy and paste the following command on your terminal and press the Enter button on your keyboard.

postgres=# create database pg_dump_db;
CREATE DATABASE

Connect to the pg_dump_db database using the following command to ensure we are working on the correct database.

postgres=# \c pg_dump_db;
You are now connected to database "pg_dump_db" as user "postgres".

Create an entity named users with the fields id, first_name, last_name and email. Copy and paste the following data definition language to your terminal and press Enter.

pg_dump_db=# create table users(
pg_dump_db=# first_name varchar(30),
pg_dump_db=# last_name varchar(30),
pg_dump_db=# email varchar(50),
pg_dump_db=# id SERIAL UNIQUE NOT NULL,
pg_dump_db=# PRIMARY KEY(id));
CREATE TABLE

Insert a record into the users entity. Copy and paste the following data manipulation commands to your terminal and press Enter.

pg_dump_db=# insert into users(first_name, last_name, email)
pg_dump_db-# values('john','doe','john@gmail.com');
INSERT 0 1

Use pg_dump to Create a Backup File of the Database in the Current Directory

Since the pg_dump is a command-line tool, ensure that you log out from the PostgreSQL server and execute the command when logging in to the server.

postgres=# exit;

Use the following command to create a backup of the pg_dump_db database. The output file is stored in the current directory.

david@david-HP-ProBook-6470b:~$ pg_dump -U postgres pg_dump_db > backup.sql
Password:

The output file is created in the current directory because there was no specified path where you will make the file.

For Linux users, the current directory is the /home/ directory where all the data, such as folders and files regarding the user, is stored.

After going to the /home/ folder, you will find a file named backup.sql containing a backup of the pg_dump_db database.

Use pg_dump to Create a Backup File of the Database in a Custom Directory

If we want to backup at a particular location on our computer, we can use the same command and add the path where we want to create the file.

Use the following command to create a backup of the pg_dump_db database stored on our Desktop.

david@david-HP-ProBook-6470b:~$ pg_dump -U postgres pg_dump_db > /home/david/Desktop/backup.sql
Password:

In the above example, we provided the absolute path to the Desktop and the file name created on the location.

Go to the /Desktop/ folder. You will find a file named backup.sql containing a backup of the pg_dump_db database.

Conclusion

We used the > symbol in the two examples covered. However, you can use the -f flag to tell pg_dump that you want to write the results to a file.

If you do not provide the path where the file is to be created, the file is made in the /home/ directory. Note that this is for Linux users only and depends on the Linux distribution.

If you provide the path where the file is to be created, you can find the file in that location.