Support Our Site

To ensure we can continue delivering content and maintaining a free platform for all users, we kindly request that you disable your adblocker. Your contribution greatly supports our site's growth and development.

How To Backup and Restore Data in PostgreSQL Database

2 min read

PostgreSQL is undoubtedly one of the most popular and efficient open-source relational database management system powering millions of applications.

These days a database backup mechanism is essential for any project onboard. Luckily PostgreSQL comes with built-in utilities for creating and restoring backups to make our life simpler.

Backup and Restore Databases in PostgreSQL

PostgreSQL allows a great degree of flexibility for backup and restoration of data, In this article one by one we will go through the most used utilities.

Backing up a database in a tar file

The pg_dump utility is used for backing up data of a single database or table in PostgreSQL.

pg_dump -U db_user -W -F t db_name > /path/to/your/file/dump_name.tar

Here we used the following options:

  • -W will force pg_dump to prompt for a password before connecting to the server.
  • -F is used to specify the format of the output file, which can be one of the following:
    1. p – plain-text SQL script
    2. c – custom-format archive
    3. d – directory-format archive
    4. t – tar-format archive

In case you encounter the following error.

 pg_dump: [archiver (db)] connection to database "mydb" failed: FATAL: Peer authentication failed for user "postgres"

Please follow this guide - How To Fix – FATAL: Peer authentication failed for user “postgres” Error

Restoring a database from a tar file

Similarly, pg_restore utility is used for restoring a database from a dump created by pg_dump.

pg_restore -d db_name /path/to/your/file/dump_name.tar -c -U db_user

The -C flag is for creating a database before restoring data into it.

Backing up a database in SQL format

A tar backup may cause problems while restoring the dump to a different version of PostgreSQL. Therefore I always recommend taking backups in-plane SQL format.

pg_dump -U db_user database_name > path/to/backup.sql

Restoring a database from SQL file

SQL files can be restored using psql as follows.

 psql -U db_user < path/to/backup.sql

Backing up a specific table

Postgres also allow us to backup specific tables instead of the entire database.

pg_dump -U db_user -d db_name -t table_name > path/to/backup.sql

Restoring a table from SQL file

psql -U db_user -d db_name < path/to/backup.sql

Backing up all the databases at once

We can also backup all the database belonging to a user using the pg_dumpall utility.

pg_dumpall -U db_user > path/to/backup.sql

Restoring all the databases from the backup file

psql -U db_user < path/to/backup.sql

Backing up a Remote database

pg_dump -h host_name -p port_number db_name > path/to/backup.sql

Restoring a Remote database

psql -h host_name -p port_number db_name < path/to/backup.sql

Take a compressed backup of PostgreSQL database

pg_dump -U db_user -d db_name | gzip > path/to/backup.sql.gz

Restoring from a compressed backup

gunzip -c path/to/backup.sql.gz | psql -U db_user -d db_name



Latest Articles

Latest from djangocentral

How to Use Subquery() in Django With Practical Examples

In the realm of web development, Django stands as a powerful and versatile framework for building robust applications. One of the key aspects of developing efficient and optimized web applications is handling database queries effectively. In this article…
Read more →

4 min read

DRF Serializer: Handling OrderedDict and Converting It to a Dictionary or JSON

In Django Rest Framework (DRF) tests, when you access, you might encounter an OrderedDict instead of a regular dictionary. This behavior is intentional and reflects the design of DRF's serialization process.Understanding the Problem The u…
Read more →

3 min read

Django Rest Framework CheetSheet: Mastering API Development

Django Rest Framework (DRF) is a powerful toolkit that makes building robust and scalable web APIs with Django a breeze. Whether you're a seasoned Django developer or a newcomer, having a comprehensive cheat sheet at your disposal can be a game-changer. …
Read more →

5 min read

How to Perform NOT Queries in Django ORM

In Django, performing NOT queries allows you to exclude certain records from the query results based on specific conditions. The NOT operator, represented by the tilde (~) when used in conjunction with the Django ORM's Q object, helps you construct compl…
Read more →

3 min read