How to Backup and Restore the Database

Summary

1. Local backup (not recommended as sole backup method)
2. Local server backing up to Report file system
3. Remotely initiated backup
4. Restoring database from backup

Local backup

Local backup, by definition, is not very reliable. It will protect you from such errors as bugs in the database software that result in corrupted database or a user deleting 2 years worth of data. On the other hand, local backups are less beneficial against catastrophic software failures (operating system corrupts the file system) or hardware failures.

Nevertheless, a local backup is better than no backup. Furthermore, it is wise to set up a local backup in addition to a remote backup.

To set up a local database backup, all we need is access to the database without a password. To accomplish this, we first need to alter the configuration file. On the computer running the database server, go to Start→All Programs→PostgreSQL 8.0→Configuration files→Edit pg_hba.conf. This should bring up a text editor with the configuration file. Go to the end of such a file and alter the line that reads,

host all all 127.0.0.1/32 md5

to read,

host all all 127.0.0.1/32 trust

The change from md5 to trust results in all connections from localhost, or 127.0.0.1, to be trusted. Now restart the PostgreSQL database service by going to Start→All Programs→PostgreSQL 8.0→Reload configuration. You should now be able to connect to the database using any account from the local computer without using a password.

The second step is to create a backup directory on your hard drive. Go to C:\ and create a folder called backup. Now, download this local backup batch file and save it in this directory. If you have installed PostgreSQL in a non-default directory, you may need to change the paths in this batch file.

Finally, go to the Control Panel and setup a daily Scheduled Task to run the backup batch file on daily basis.

Local Server to Remote System Backup

The local to remote system backup is the recommended method of saving your data. Ideally, you do not want to have the remote backup machine in the same room, building or even city to counteract some of the potential disasters that could occur (fire, water, electrical spikes, etc). Of course, any machine physically separated from the server may be used.

To configure backup to the remote machine, your will need to make sure that users on the server machine can log into the database without a password. The procedure for doing that is listed in the above section about Local backups.

The second step that is needed is to create a directory on the remote machine where the backups are to be stored. This directory must be accessible over the Windows Network to the server. Now, download this back file into that directory. You will probably need to alter the backup path in this batch file if it is not \\Remote Server\Backup. To do this, open the batch file using Notepad and change the BACKUP_PATH accordingly.

Finally, just like with local backups, go to the control panel of the server and set up a Scheduled Task to run the remote_backup.bat batch file on a daily basis. Once a Scheduled Task is configured, verify that the command will actually backup the database correctly.

Remotely Initiated Backup

Another possible type of backup is the remotely initiated backup. A remote machine connects to the database server and backs up all of the databases onto its own hard drive.

For this type of backup to be possible, the remote machine must have at least a copy of the Command Line Tools installed. To install the command line tools, follow the instruction for the Database Server with the exception that,

• in Step 7, do not install the Database Server or the Database Drivers. You only need the User Interfaces (more specifically, the psql user interface) installed.
• do not do any database initialization, etc.. as you are not installing the database, just the client tools

After the command line tools are installed on the remote machine, create a backup directory (for example, C:\Backup) and download the following batch file and save it into that directory. You will then need to edit this file using Window's Notepad to reflect the configuration of your database. More specifically, you at least will need to change the REMOTE_DATABASE_SERVER setting to the IP address of the database server

Thirdly, you will need to alter the configuration file of the database server to allow connection from the remote backup machine without a password. You will need to add a line at the end of the configuration file. If the IP address of the remote backup machine is 123.123.123.123 then the access line should look like

host all all 123.123.123.123/32 trust

After the database configuration is changed, restart the service on the database server and test if the backup works on the remote machine. If the backup succeeds, you can configure a Scheduled Task to execute the backup batch file every day.

Restoring Database From Backup

To restore the database from a given backup file, copy a backup file to a location accessible by the database server. For the sake of this document, it is assumed that the backup file will be called C:\backup.sql.

Install the database server as per the Database Server Installation Instruction. DO NOT initialize the database server as the backup restore will perform this function.

After a new database was installed, open the command line client from Start→All Programs→PostgreSQL 8.0→Connect to template1. Once you logged in to the database, on the prompt enter,

\i 'c:/backup.sql'

or whatever the backup file is called. A large number of lines should scroll by indicating the progression of the restore process. After the restore is completed, enter \q followed by Enter to exit the command line.