17.6. Database Maintenance

Developer Studio comes with several features to aid database maintenance. You can target maintenance at both database and table level. This can be done by selecting the desired database object from Developer Studio and then right clicking on it. A drop down list will appear containing all the different options to choose from.

Here we will only consider the options related to database maintenance only, the options for table level maintenance are almost the same. You can perform the following main maintenance activities via Developer Studio:

17.6.1. VACUUM

Running VACUUM will scan the database or table for rows that are not in use any more. In normal EnterpriseDB operation, tuples that have been deleted or obsoleted by an update are not physically removed from their table. Such tuples remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables.

While this tool is very handy for ad-hoc maintenance purposes, you are encouraged to install an automatic job, which performs a VACUUM job regularly to keep your database in a neat state.

For more information see Section 35.1 and VACUUM

17.6.2. ANALYZE

ANALYZE collects statistics about the contents of tables in the database. Accurate statistics will help the query planner in choosing the most appropriate query plan, and thereby improving the speed of query processing. Every time your data is changing radically, you should perform this task. It can also be included in a VACUUM run.

For more information see Section 35.1.2 and ANALYZE

17.6.3. REINDEX

REINDEX rebuilds indexes using the data stored in the index's table. We will normally need to rebuild indexes an index has become corrupted, and no longer contains valid data or the index in question contains a lot of dead index pages that are not being reclaimed.

The output and progress is of the REINDEX operation is displayed in a window which appears to the user once the button is pressed.

For more information see Section 35.2 and REINDEX

17.6.4. Backup

Developer Studio provides users with the option to taking backups of their data. You can backup a single table, a schema or a complete database. This can be done by selecting the appropriate object node from Developer Studio and selecting Tools->Backup from the main menu bar. The backup dialogue box will appear before the user. The PLAIN format will create an SQL script that can be executed using EnterpriseDB PSQL. For standard backup and restore purposes, the COMPRESS and TAR options are recommended.

In order to use backup, the pg_dump tool must be accessible by Developer Studio. This can be accomplished by having it locatable using the path, or by copying it into the same directory where the Developer Studio executable resides.

For more information see Chapter 36.

17.6.5. Restore

Developer Studio also facilities users with the option of restoring their data. This can be done by selecting an appropriate data source from Developer Studio and selecting Tools->Restore from the main menu bar. Once you have selected a valid backup file, Developer Studio will read the contents of the file when you press the button. This may take some time, depending on the size of the file. You can watch the progress on the EnterpriseDB PSQL window which will during the Restore process. After the Restore has completed, Developer Studio will display the contents on the EnterpriseDB PSQL window.

The tool will intentionally not create the database to be restored. In order to restore a database, first create a destination database via Developer Studio, and carry out the above mentioned steps.

You can also select a table as restore destination. Developer Studio will restore its data if it is present in the backup file.

For more information see Chapter 36.