How to backup and restore postgresql database 9.6

Having regular backups of your database is important as you might already know.   You might even have a development environment that you want to load with production data.   Here is quick overview of how you can backup your postgresql database and restore it.   This will also work if you are backing it up from Ubuntu linux and restoring it to Mac OSX.

Step 1. Run the pd_dump command, you will be prompted for a password in most cases, enter it when prompted:

Step 2. Copy the file yourdatabase.20170806.sql to the computer you will restore it on

Step 3.  Run the psql command to restore using the file your created, you may be prompted for a password, enter if prompted

That is it, you should have a fully restored copy.

I also like to have a database creation script that sets up the database, a user, and two roles for that user that is used to grant privileges to.  One role will have less access to the database, and the other will have more.  This way you can revoke the role that has more access later one after you have finished your development.

If you have one of these scripts then you can get a really clean restore with no existing data.

Assume you have a file createdatabase.sql that creates database name, user, and roles you could first drop the database on your dev machine with the command:

dropdb ‘yourdatabase’

Then you could re-create it with

psql < createdatabase.sql

Then finally restore the database in full with:

Here is an example createdatabase.sql script, you should replace password with your password and yourdatabase with

your real database name: