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. A screenshot.)
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:
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:
My path looks like this before I add anything:
Append these two directories at the back of what is already there:
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.)
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.