Databases contain important information such as student records, employee details, examination results, fee records, and university information.
If a database is accidentally deleted, damaged, or affected by a system failure, important information may be lost.
To protect database information, PostgreSQL provides backup and restore facilities.
For example, a university may create regular backups of its student database so that the information can be recovered if a problem occurs.
In this lesson, we will learn how to:
A database backup is a copy of database information saved in a separate file.
This file can be used to recover the database if the original database is lost or damaged.
For example:
Suppose a university has a database named university_db.
We can create a backup file:
university_db.backup
If the original database is accidentally deleted, we can use this backup file to restore the database.
Database restore means recovering database information from a previously created backup file.
For example:
university_dbuniversity_db.backupuniversity_dbThe restore operation uses the backup file to recreate the database objects and data.
Database backups are important for several reasons:
PostgreSQL provides several tools for creating and restoring backups.
| Tool | Purpose |
|---|---|
pg_dump |
Creates a backup of one database |
pg_restore |
Restores backups created in custom, directory, or tar formats |
pg_dumpall |
Creates a backup of all databases and global objects |
psql |
Executes SQL commands and restores plain SQL backups |
Important: pg_dump and pg_restore are command-line utilities included with PostgreSQL.
PostgreSQL supports different backup formats.
| Format | Description |
|---|---|
| Plain SQL | Stores SQL statements in a text file |
| Custom | Stores backup in PostgreSQL’s custom archive format |
| Directory | Stores backup in a directory containing multiple files |
| Tar | Stores backup in a tar archive |
The most commonly used formats are:
.sql).backup)Recommendation: Use the custom format when you want flexible restoration options through pg_restore.
The pg_dump utility creates a backup of a PostgreSQL database.
Syntax:
pg_dump -U username -d database_name -f backup_file.sql
Explanation:
pg_dump: PostgreSQL backup utility.-U: Specifies the database username.-d: Specifies the database name.-f: Specifies the output backup file.Example:
pg_dump -U postgres -d university_db -f university_backup.sql
This command creates a plain SQL backup file named:
university_backup.sql
We can create a custom-format backup using the -F c option.
Example:
pg_dump -U postgres -d university_db -F c -f university_backup.backup
Explanation:
-F c: Specifies custom format.-f: Specifies the backup file.The custom-format backup can be restored using pg_restore.
pgAdmin provides a graphical interface for creating database backups.
How to Dump Your Database – Data Science for Social Good
Backup Dialog — pgAdmin 4 9.12 documentation
Follow these steps:
After successful completion, pgAdmin creates the backup file at the specified location.
A plain SQL backup contains SQL statements that recreate database objects and insert data.
We can restore it using the psql utility.
Syntax:
psql -U username -d database_name -f backup_file.sql
Example:
psql -U postgres -d university_db -f university_backup.sql
This executes the SQL statements stored in the backup file against the specified database.
Important: The target database normally needs to exist before restoring a plain SQL backup. If the backup contains commands to create the database, use an appropriate connection and restore procedure.
If you want to restore the backup into a new database, first create the database.
Connect to PostgreSQL and execute:
CREATE DATABASE university_restore;
Then restore the plain SQL backup:
psql -U postgres -d university_restore -f university_backup.sql
This restores the backup contents into university_restore.
The pg_restore utility restores custom, directory, and tar-format backups.
Syntax:
pg_restore -U username -d database_name backup_file
Example:
pg_restore -U postgres -d university_restore university_backup.backup
This restores the custom-format backup into the university_restore database.
Important: The target database must exist before using this command unless you use the appropriate create-database option.
We can use the -C option to create the database during restoration.
Example:
pg_restore -U postgres -C -d postgres university_backup.backup
Here:
-C: Creates the database using the database name stored in the backup.-d postgres: Connects to the existing postgres database before restoration.This is useful when the backup contains the database creation information.
We can also restore a database through pgAdmin.
How to Restore a Database in pgAdmin 4
Restore Dialog — pgAdmin 4 9.17 documentation
4
Follow these steps:
After successful restoration, refresh the database and check its tables.
Note: The Restore option in pgAdmin is generally used for custom, tar, and directory archives. For plain SQL files, use the Query Tool or the psql utility.
Sometimes, we do not need to back up the complete database.
We can back up an individual table using pg_dump with the -t option.
Syntax:
pg_dump -U username -d database_name -t table_name -f backup.sql
Example:
pg_dump -U postgres -d university_db -t students -f students_backup.sql
This creates a backup of the students table.
A custom-format backup can be used to restore selected tables.
Example:
pg_restore -U postgres -d university_restore -t students university_backup.backup
This restores the students table from the custom backup.
The destination database should exist, and any required dependent objects should be considered.
The pg_dumpall utility creates a backup of all databases in a PostgreSQL server, along with global objects such as roles and tablespaces.
Syntax:
pg_dumpall -U postgres -f all_databases.sql
This creates a plain SQL file containing the server-wide backup.
To restore it:
psql -U postgres -d postgres -f all_databases.sql
Important: Restoring a full-server backup may require appropriate privileges. It can also recreate or conflict with existing roles and databases, so review the backup before restoring it on a server that already contains data.
Before restoring a custom-format backup, we can view the objects stored inside it.
Use the -l option with pg_restore.
Example:
pg_restore -l university_backup.backup
This displays information about the database objects included in the backup, such as:
This is useful when you want to inspect a backup before restoring it.
Sometimes, we need to back up the database structure without its records.
This is called a schema-only backup.
Example:
pg_dump -U postgres -d university_db --schema-only -f schema_backup.sql
This backs up database objects such as:
It does not include the table records.
We can also create a backup containing only the table data without the database structure.
Example:
pg_dump -U postgres -d university_db --data-only -f data_backup.sql
This backup contains data insertion commands but does not recreate the database structure.
Remember:
| Option | Purpose |
|---|---|
--schema-only |
Back up database structure |
--data-only |
Back up table data |
| Default | Back up structure and data |
PostgreSQL custom-format backups support compression.
Example:
pg_dump -U postgres -d university_db -F c -Z 6 -f university_backup.backup
Here:
-F c: Creates a custom-format backup.-Z 6: Sets the compression level.-f: Specifies the backup file.The compression level can range from 0 to 9, depending on the PostgreSQL version and supported options.
Compression helps reduce backup storage requirements.
Sometimes, the PostgreSQL command-line tools are not recognized by the operating system.
We can check whether they are installed and accessible.
Example:
pg_dump --version
Check the restore utility:
pg_restore --version
Check the psql utility:
psql --version
If these commands are not recognized, check whether the PostgreSQL bin directory is included in the system’s PATH environment variable.
On Windows, the tools are commonly located in a directory similar to:
C:\Program Files\PostgreSQL\17\bin
The version number may be different on your computer.
Let’s compare the most commonly used commands.
| Command | Purpose |
|---|---|
pg_dump |
Back up one database |
pg_restore |
Restore archive-format backups |
pg_dumpall |
Back up all databases and global objects |
psql |
Execute SQL scripts and restore plain SQL backups |
pg_restore -l |
Display archive contents |
pg_dump --schema-only |
Back up database structure |
pg_dump --data-only |
Back up database records |
Let’s perform a complete practical exercise using a database named university_db.
CREATE DATABASE university_db;
In pgAdmin, select university_db and open the Query Tool.
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
department VARCHAR(50)
);
INSERT INTO students
VALUES
(1, 'Ali', 'Computer Science'),
(2, 'Sara', 'Information Technology'),
(3, 'Ahmed', 'Mathematics');
SELECT * FROM students;
Run this command in the operating system terminal:
pg_dump -U postgres -d university_db -F c -f university_backup.backup
CREATE DATABASE university_restore;
Run:
pg_restore -U postgres -d university_restore university_backup.backup
Connect to university_restore and execute:
SELECT * FROM students;
Expected output:
| student_id | student_name | department |
|---|---|---|
| 1 | Ali | Computer Science |
| 2 | Sara | Information Technology |
| 3 | Ahmed | Mathematics |
Practical completed
The records from the original database have been restored into a new database.
Example backup file naming:
university_backup_2026-11-09.backup
Important: A backup is useful only if it can be restored successfully. Always test your recovery procedure.
Reason: The database username or password is incorrect.
Solution: Verify the PostgreSQL username and password.
Reason: The destination database has not been created.
Solution: Create the database before restoring, or use an appropriate database-creation option.
Reason: The database user does not have sufficient permissions.
Solution: Use an account with the required privileges.
Reason: The backup file path is incorrect.
Solution: Check the file location and name.
Reason: The wrong utility is being used for the backup format.
Solution:
psql for plain SQL backups.pg_restore for custom, directory, and tar backups.Reason: The destination database already contains objects with the same names.
Solution: Restore into a clean database or carefully use appropriate cleanup options. Be cautious because cleanup options can delete existing objects.
Exercise 1: Create a database named college_db.
Exercise 2: Create a table named students with student ID, name, and department.
Exercise 3: Insert five student records.
Exercise 4: Create a plain SQL backup using pg_dump.
Exercise 5: Check the PostgreSQL backup utility version.
Exercise 6: Create a custom-format backup of college_db.
Exercise 7: Create a new database named college_restore.
Exercise 8: Restore the custom backup into college_restore.
Exercise 9: Verify that all student records have been restored.
Exercise 10: Create a schema-only backup of college_db.
Exercise 11: Create a data-only backup.
Exercise 12: Create a backup of only the students table.
Exercise 13: Display the contents of a custom-format backup using pg_restore -l.
Exercise 14: Create a compressed custom-format backup.
Exercise 15: Back up all PostgreSQL databases using pg_dumpall.
pg_dump?pg_restore?pg_dump and pg_dumpall?--schema-only option do?--data-only option do?| Task | Command |
|---|---|
| Plain SQL backup | pg_dump -U postgres -d university_db -f backup.sql |
| Custom backup | pg_dump -U postgres -d university_db -F c -f backup.backup |
| Restore plain SQL | psql -U postgres -d university_restore -f backup.sql |
| Restore custom backup | pg_restore -U postgres -d university_restore backup.backup |
| Backup all databases | pg_dumpall -U postgres -f all.sql |
| Backup one table | pg_dump -U postgres -d university_db -t students -f students.sql |
| Schema-only backup | pg_dump -U postgres -d university_db --schema-only -f schema.sql |
| Data-only backup | pg_dump -U postgres -d university_db --data-only -f data.sql |
| List backup contents | pg_restore -l backup.backup |
| Check tool version | pg_dump --version |
Next Lesson: PostgreSQL User Management and Roles
In the next lesson, students will learn how to create database users, create roles, assign permissions, change passwords, and manage database access using CREATE USER, CREATE ROLE, GRANT, and REVOKE.