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 Files\PostgreSQL\8.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:

%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem

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

;C:\Program Files\PostgreSQL\8.3\bin

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 Files\PostgreSQL\8.3\bin” directory.

2. Backing up in the command prompt

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

pg_dump -U myusername mydb > mydb.sql

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:\Users\KahWee”. 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:

psql -U myusername mydb < mydb.sql

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:

psql -U myusername mydb

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.

4 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) :)

  2. i am issuing this pg_dump -U myusername mydb > mydb.sql in command prompt with my dbname and user name under windows xp but it is displaying an error “connection failed received invalid response to ssl negotiation”

    please help as early as possible.

    sumit

  3. Excellent post.Keep posting such kind of info on your blog.
    Im really impressed byy it.
    Hey there, You have done an incredible job. I will definitely
    digg it and personally suggest to my friends. I aam confident they’ll be benefited from thiks web site.

    Here iss my site; purchase instagram likes (Doreen)

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>