SnapShooter Backups Server, Database, Application and Laravel Backups - Get fully protected with SnapShooter

How to Restore a PostgreSQL Database

If you want to restore a PostgreSQL database, there are several ways.

Create a backup

First, you can use the pg_dump utility to generate a pg_dump file. This will contain the SQL commands required to restore the database.

pg_dump dbname > dbbackup.sql

You can specify other output formats when using pg_dump using the -F option.

Available options are:

  • The c means custom format archive file.
  • The d means directory format archive.
  • The t means tar format archive file.

For example:

pg_dump -F c database > database.dump
pg_dump -F t database > database.tar

Restore the backup

There are two utilities avaiable to restore a backup created by pg_dump.

The first one is pgsql. This utility only support text file:

psql database < backup.sql

The other utility is pg_restore. It is used to restore a PostgreSQL database from non text formats (custom, tar, or directory):

$ pg_restore -d database backup.dump
$ pg_restore -d database backup.tar
$ pg_restore -d database backup

Handling large database

Performing a pg_restore operation can be very time-consuming, especially if you are restoring a large database. Therefore, you should perform compress the dump file to ensure the success of your restoration job.

pg_dump tecmintdb | gzip > tecmintdb.gz

The end

That is how you handle backup and restore a PostgreSQL database.