Beginner’s Guide to PostgreSQL Database Dump and Restore

Backing up a PostgreSQL database and restoring it to a new database is a critical part of maintaining your data. This tutorial will guide you through the process of creating a backup using the pg_dump command and restoring it using either psql or pg_restore.

 Prerequisites

• Ensure that pg_dump and psql/pg_restore tools are installed on your system
  
• You should have the appropriate permissions to run PostgreSQL commands and access the database.

Backing Up a PostgreSQL Database Using pg_dump

pg_dump -U [username] -h [host] -p [port] -F c -b -v -f [backup_file] [database_name]

• -U [username]: The PostgreSQL username you are connecting with. For example, postgres.
• -h [host]: The hostname or IP address of the PostgreSQL server (use localhost if you're working locally).
• -p [port]: The port number on which PostgreSQL is running. The default is 5432.
• -F c: Specifies the backup format (c stands for custom format). You can also use p for plain text SQL or t for tar format.
• -b: Includes large objects (binary data) in the backup.
• -v: Enables verbose mode to show detailed progress.
• -f [backup_file]: The location and name of the backup file. This can be a full path (e.g., /home/user/backup.sql).
• [database_name]: The name of the database you are backing up.

Restoring the PostgreSQL Database

Once you've backed up your database, you may want to restore it to a new or existing PostgreSQL database. You can use either the psql command for plain text SQL dumps or pg_restore for custom backups.

Restoring with pg_restore (for custom format backups)

The pg_restore tool is used to restore backups created in the custom format (-F c).

 pg_restore -U [username] -h [host] -p [port] -d [new_database_name] -v [backup_file]

Restoring with psql (for plain SQL dump backups)

If you used pg_dump with the -F p option (plain SQL text format), you can restore the database using psql

psql -U [username] -h [host] -p [port] -d [new_database_name] -f [backup_file]

Verifying the Restore

Once the restore process is complete, you should verify that your database was restored correctly. To do this:

Log into the PostgreSQL database:

psql -U postgres -h localhost -p 5432 -d newdatabase

Check the tables and data:

\dt

SELECT * FROM some_table LIMIT 10;

Examples

To create and restore the backup created in custom format (-F c)

pg_dump -U postgres -h localhost -p 5432 -F c -b -v -f /path/to/backup/mydatabase.backup mydatabase

pg_restore -U postgres -h localhost -p 5432 -d newdatabase -v /path/to/backup/mydatabase.backup

To create and restore the backup created in plain sql format

pg_dump -U postgres -h localhost -p 5432 -F p -b -v -f /path/to/backup/mydatabase.sql mydatabase
psql -U postgres -h localost -p 5432 -d new_database -f /path/to/backup/mydatabase.sql