JIYIK CN >

Data backup is a critical process to ensure that preventive measures are effective in the event of an incident such as data loss. We can back up data in different forms and ways depending on the size and environment.

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

Create and connect to a database in PostgreSQL

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

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

We will then use pg_dumpthe command line tool to back up a single database to a script file. A script file is a text file with SQL commands to rebuild the database to the state it was in when it was saved.

To rebuild the database, provide the script to Psql.

Log in to the PostgreSQL server using the command shown below.

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

Type the user postgres's password 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 pg_dump_dbdatabase called , which will contain our entities. Copy and paste the following command into your terminal and press the Enter button on your keyboard.

postgres=# create database pg_dump_db;
CREATE DATABASE

Connect to pg_dump_dbthe database using the following command to make sure we are using the correct database.

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

Create an entity named with fields id, first_name, last_nameand . Copy and paste the following data definition language into your terminal and press Enter.emailusers

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 usersthe entity. Copy and paste the following data operation command into 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

pg_dumpCreate a backup file of the database in the current directory using

Since pg_dumpis a command line tool, make sure you are logged out from the PostgreSQL server and execute the command while logged into the server.

postgres=# exit;

Use the following command to create pg_dump_dba backup of the 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 files are created in the current directory because no path to the generated files is specified.

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

Once you go to /home/the folder, you will find a backup.sqlfile called that contains pg_dump_dbthe backup of the database.

Use pg_dumpto create a backup file of the database in a custom directory

If we want to back up in a specific location on our computer, we can use the same command and add the path where we want the file to be created.

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

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 at that location.

Go to /Desktop/the folder. You will find a backup.sqlfile named that contains pg_dump_dbthe backup of the database.

in conclusion

We used the _notation in both of the examples we covered >. However, you can use -fthe _ flag to tell _ that pg_dumpyou want to write the results to a file.

If you do not provide a path to create the file, the file will be /home/created in the directory. Please note that this only works for Linux users and depends on the Linux distribution.

If you provide a path to create a file, the file can be found at that location.