36.11. Backing Up and Restoring a Database

If one of your databases contains important information, it should be backed up regularly in case a disk failure or SQL mistake causes data loss. It is also a good idea to create a backup before performing some potentially risky operation, such as running a complex SQL statement that modifies lots of records.

Due to changes in the parameters of the pg_dump and pg_restore commands, the module only allows you to create and restore backups when using PostgreSQL versions 7.2 and above. If you are using an older release, the buttons explained in the following steps will not be visible.

To use the module to make a backup, complete the following steps:

1.
On the main page, click on the icon for the database that you want to backup.

2.
Click on the Backup button below the list of tables. This will take you to a form for entering the backup destination and options.

3.
In the Backup file path field, enter the full file path that the backup should be written to, such as /tmp/backup.tar. The file must not already exist. If it does an error will occur when you hit the Backup button.

4.
From the Backup file format menu, select the type of file that should be created. The available options are:

Plain SQL text The file will contain a series of SQL commands that recreate the tables in the database and repopulate them with data. This format is convenient in that backup files can be manually edited, but you cannot include large objects (like blobs) in an SQL backup or selectively restore from it.

Tar archive The backup file will be a standard UNIX tar file, containing various files that specify table structures and contents. Large objects are supported, and selective restoring is possible.

Custom archive The file will be in PostgreSQL's custom backup format, which is compressed and supports large objects, data exclusion, and reordering at restore time.

5.
To make the backup, hit the Backup button at the bottom of the form. If everything goes well, you will be redirected to the table list—otherwise, a page showing the backup command run and its error output will be displayed.

If you have a database that is being used for an important production purpose, it should be backed up regularly, such as once per day. Instead of following the preceding instructions every day, you can use the Scheduled Cron Jobs module (covered in Chapter 10) to create a job that does the backup for you. To find out what command to run, use the instructions above to make a backup first and then visit the Webmin Actions Log module (covered in Chapter 54) to see the command that it used.

Once a backup file has been created, it can be restored on the same system or on another server running PostgreSQL. The steps to restore a backup are:

1.
On the module's main page, click on the icon for the database into which the backup should be restored.

2.
Hit the Restore button below the list of tables to bring up a form for selecting the backup file.

3.
In the Backup file path field, enter the full path to the file containing PostgreSQL backup data such as /tmp/backup.tar. This file can be in any of the formats available on the backup form.

4.
Normally, the restore process will attempt to recreate tables before restoring data into them. To avoid this, change the Only restore data, not tables? field to Yes. This will only work if all the tables in the backup already exist. All data that the tables currently contain will be combined with restored records.

5.
Normally, the restore process will fail if a table in the backup already exists in the database. To have existing tables dropped before restoration, change the Delete tables before restoring? field to Yes. It makes no sense to set both this and the previous field to Yes.

6.
Click the Restore button to reload data and tables from the backup file. An error message showing output from the pg_restore command will be displayed if something goes wrong—otherwise, you will be returned to the list of tables in the database.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.119.104.95