Discover Datashelter
Guides

PostgreSQL Backup and Restore: A Complete Guide Using pg_dump and pg_restore

Malo Paletou
· 2 min read
Send by email

PostgreSQL is an open-source relational database management system (RDBMS) known for its high performance, flexibility, and strict compliance with SQL standards. Its ability to handle large amounts of data makes it a preferred choice for businesses and developers looking for a robust and scalable solution.

Whether you are a system administrator or a developer, it is essential to know the best practices to ensure the security and integrity of your PostgreSQL databases. This guide will help you to:

  • Efficiently back up your databases,
  • Secure your data against potential losses,
  • Restore your databases when needed.

Backing Up Your PostgreSQL Databases

Backing up a single database

To back up a PostgreSQL database to an SQL dump file, use the following command:

pg_dump -h localhost -p 5432 -U <username> -d <database_name> -f dump.sql

Explanation:

  • -h localhost: Specifies the PostgreSQL host.
  • -p 5432: Specifies the PostgreSQL connection port.
  • -U <username>: Specifies the PostgreSQL user.
  • -d <database_name>: Name of the database to back up.
  • -f dump.sql: Name of the backup file.

In some cases, you may want to generate a dump in a custom format. This can be achieved by adding the -F c option to the command:

pg_dump -U <username> -d <database_name> -F c -f backup.dump
💡
Tip: To avoid entering the password every time, configure the .pgpass file.

Configuring the .pgpass File (Optional)

The .pgpass file allows you to securely store login credentials and automate PostgreSQL access without having to enter a password for each command execution.

Steps to configure the .pgpass file:

Create a .pgpass file in the PostgreSQL user's home directory:

touch ~/.pgpass
chmod 600 ~/.pgpass

Add the connection details in the following format:

hostname:port:database:username:password

Once this file is configured, all PostgreSQL commands such as pg_dump, pg_restore, and psql can be executed without manually entering a password.

Backing up all databases

To back up all databases, use the pg_dumpall command:

pg_dumpall -U <username> -f full_dump.sql

This generates an SQL file containing all the databases in the cluster.

Backing up schemas only

If you want to back up only the table structures (without data):

pg_dump -U <username> -d <database_name> -s -f structure.sql
💡
Tip: It is common to separately store database schema backups and associated data. This allows you, for example, to restore only the schema to create a staging environment.

Restoring Your PostgreSQL Databases

In case of issues, here is how to restore your PostgreSQL databases from a dump file.

Reinstalling PostgreSQL (optional)

If PostgreSQL has been removed or corrupted, reinstall it using:

sudo apt install postgresql

Recreating databases and users

Before restoring, ensure that the target database exists:

CREATE DATABASE my_database;

Create a PostgreSQL user:

CREATE USER new_user WITH PASSWORD 'password';

Grant necessary privileges to this user:

GRANT ALL PRIVILEGES ON DATABASE my_database TO new_user;

Restoring a PostgreSQL database

To restore a database from an SQL dump file:

psql -U <username> -d <database_name> -f full_dump.sql

If your backup is in a custom format:

pg_restore -U <username> -d <database_name> -c backup.dump

The -c option allows existing objects to be dropped and recreated before restoring, avoiding conflicts.

💡
Tip: To prevent errors, ensure necessary extensions are enabled before restoring.

PostgreSQL Cheatsheet: Useful Commands

Display available databases:

\l

Connect to a database:

\c database_name

List tables in a database:

\dt

Display PostgreSQL users:

SELECT usename FROM pg_user;

By following these best practices, you ensure the security and integrity of your PostgreSQL databases while optimizing your backup and restoration processes.

Written by Malo Paletou

Keep reading