How To Backup And Restore A PostgreSQL Database

There are different ways to backup and restore a PostgreSQL database. However, you use the following methods at your own risk and its up to you to ensure that you find the backup and restore methods that suit you best.

This article explains how to back up a PostgreSQL database using a ‘dump’ and then you can transfer the resulting SQL file to the destination drive or computer and then restore it. This is also a viable way to import a PostgreSQL database into a new installation, or migrate your database to another PostgreSQL installation.

Do A PostgreSQL Database Dump (Backup A PostgreSQL Database To An SQL File)

You can back up a PostgreSQL database by doing a ‘dump’, which will generate an SQL file with the database contents. Type the following to do a database dump (you may be prompted for your password):

pg_dump -U your_username databasename > databasename.sql

The resulting SQL file containing your database is in whichever directory your command prompt/terminal window was under at the time of execution. So just type ‘ls’ on Linux or ‘dir’ on Windows to confirm that the database dump operation was successful.

Restore A PostgreSQL Database From A Backup

To restore a PostgreSQL database from a backup (specifically a dump in the form of an SQL file, in this case), you would type the following:

psql -U your_username databasename < databasename.sql

If you are trying to recover a database from a formatted hard drive, or if it was accidentally deleted, my data recovery guide might help.

PostgreSQL Introduction: How To Update Records In A PostgreSQL Database