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

 


WEB DEVELOPMENT

Latest Articles

Latest from djangocentral

Capturing Query Parameters of request.get in Django

In Django, the request object contains a variety of information about the current HTTP request, including the query parameters. Query parameters are a way to pass additional information in the URL and are used to filter or sort data. The request object p…
Read more →

2 min read

Understanding related_name in Django Models

In Django, related_name is an attribute that can be used to specify the name of the reverse relation from the related model back to the model that defines the relation. It is used to specify the name of the attribute that will be used to access the relat…
Read more →

2 min read