How to backup and restore in PostgreSQL

I was fairly impressed with pgAdmin III when I first used it. It seems to be simple to use for anyone who can’t write their own SQL statements. I wasn’t too good and it and each time I execute a change in the database I have a feeling the world’s going to end. But one thing the pgAdmin III doesn’t do well is backup and restore.

Using pgAdmin III for PostgreSQL

(Using pgAdmin III for PostgreSQL. A screenshot.)

0. Objective

In the end it’s best to use the command prompt (or terminal) and in this really beginner tutorial, we’ll do a backup and restore using the command line.

1. Set PATH for PostgreSQL in Windows Vista

This guide assumes you install in ‘C:Program FilesPostgreSQL8.3’, the default installation directory.

If you are using Microsoft Windows XP or Vista, you may need to add in the PATH of PostgreSQL’s bin to your environment variables. We can do so by going to your Control Panel and type ‘environ…’ at the search area. We’ll get the following:

Editing system environment variables in Windows Vista.

Click on ‘Edit the system environment variables’, a dialog box will pop up. Click on ‘Environment Variables…’, you should be greeted with the following dialog box:

Editing the path for environment variables

My path looks like this before I add anything:


Append these two directories at the back of what is already there:

;C:Program FilesPostgreSQL8.3bin

The folder ‘bin’ contains all the programs that of PostgreSQL like pg_dump and psql. It allows you to run psql in the command prompt without having to be in the “C:Program FilesPostgreSQL8.3bin” directory.

2. Backing up in the command prompt

To backup in PostgreSQL, you can run the following command:

[code lang=”bash”]pg_dump -U myusername mydb > mydb.sql[/code]

As you can probably guess “myusername” is the username I am using and the database I would like to backup is named “mydb”. You will be prompted by the password.

Running pg_dump in the command prompt in Windows Vista

(Running pg_dump in the command prompt in Windows Vista.)

The above command would create “mydb.sql” in the current folder that your command prompt is in. As you can see my current folder is “C:UsersKahWee”. You can check your folder after running that command.


3. Restoring in the command prompt

Restoring is pretty similar to backing up. To restore in PostgreSQL, you can make use of psql:

[code lang=”bash”]psql -U myusername mydb < mydb.sql[/code]

This runs every single command in the file “mydb.sql”. That’s all, hope it helps.

Oh, and by the way, to run SQL statements in the command prompt:

[code lang=”bash”]psql -U myusername mydb[/code]

Then just type your SQL statements followed by the semicolon and press enter. The SQL command only executes after your semicolon.

If you’re interested how to do the same in MySQL, I wrote a guide on how to do backup and restore for MySQL.

2 thoughts on “How to backup and restore in PostgreSQL”

  1. Cheers, been messing about with pgadmin n it was messy, this seems to have worked great. I still need to test if the users in the restored db that weren’t in the backed up db are still there (I will find out tomorrow morning) :)

Leave a Reply

Your email address will not be published. Required fields are marked *