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
author's image
Abhijeet Pal Author and Editor in Chief @djangocentral

Abhijeet is a full-stack software developer from India with a strong focus on backend and system design. He is driven by the need to create impactful solutions that add value to the internet in any way possible.

LinkedIn Twitter Github

Latest Articles

Latest from djangocentral

Django 4.1 adds async-compatible interface to QuerySet

The much-awaited pull request for an async-compatible interface to Queryset just got merged into the main branch of Django.Pull Request - https://github.com/django/django/pull/14843 The Django core team has been progressively adding async suppor…
Read more →

3 min read

Making Django Admin Jazzy With django-jazzmin

Django admin is undoubtedly one of the most useful apps of Django. Over the years there has been very little change in the admin app as far as the UX is concerned and it's not a bad thing at all. Django admin was designed to provide a simple and minimali…
Read more →

4 min read