• How to Restore a PostgreSQL Database
  • Create a backup
  • Restore the backup
  • Handling large database
  • The end

#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
OR
$ pg_restore -d database backup.tar
OR
$ 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.