# Genesys Quality Management Suite 8.1.520 : PostgreSQL DB Maintenance

From time to time, it is necessary to perform maintenance of the database in order to regain lost performance and disk space.

There are several options of how to do it:

1. VACUUM FULL
2. REINDEX
3. Dump, Drop and Restore of the database (DDaR)

In general we prefer to use the latest option (DDaR), since it has the best results in terms of regained disk space and performance.

Icon

Please note that all options require a downtime of xQM. (both VACUUM FULL and REINDEX require exclusive lock on the processed object)

## Before You Start

- Stop all xQM services
  If you're running a clustered deployment, have a Replay server or have some 3rd party tool retrieving data from the database, make sure they are stopped.
  You don't want anything try to access the database until the maintenance is finished
- Review database and filesystem tuning
  - Your filesystem should be mounted with a 'noatime' parameter to speed up reads/writes
  - PostgreSQL database should be reasonably tuned. You can use pgtune to help you find the right values
    (but be aware that it doesn't count with limits of 32bit systems, so some of the values suggested by pgtune need to be manually adjusted)
- If you're planning to perform DDaR, make sure you have plenty of free space to store your database dump
  Note that the dump is usually much smaller than what the 'live' database occupies on the disk. (because of fragmentation)
  In general, the more the DB is fragmented, the bigger the difference between the filesize of the dump and the 'live' DB files.

Icon

The following procedures can take up to several hours therefore we highly recommend to execute all the commands in screen. Usage of the screen command can be found here - Using screen

## VACUUM FULL

To start the full vacuum process connect to your database by running the command:

    psql -U postgres callrec

and run the `vacuum full` command:

    VACUUM FULL

The procedure is complete when the word 'VACUUM' appears and when the console is ready for user input again:

Icon

Alternatively you can use the 'vacuumdb' command in bash - see http://www.postgresql.org/docs/9.1/static/app-vacuumdb.html for more info

## REINDEX

Similarly to when performing vacuum, we need to connect the psql console to the database:

    psql -U postgres callrec

and then run the `REINDEX` command:

    REINDEX DATABASE <dbname>;

Where <dbname> is name of the database you want to reindex. In most cases the name is `callrec`. For example:

    REINDEX DATABASE callrec;

The procedure is done when the word 'REINDEX' appears and the console is ready for user input again.

## Dump, Drop and Restore

1. Create a dump of your database by running the command:

       pg_dump -U postgres <dbname> -f <full_path_to_dump_file>

   Where <dbname> is name of your database. In most cases the name is `callrec`.
   For example:

       pg_dump -U postgres callrec -f /home/admin/callrec.dump.sql

2. Verify that the DB dump has been created without problems running the command:

       head -n 3 /home/admin/callrec.dump.sql && tail -n 3 /home/admin/callrec.dump.sql

   The output should look like the following:

3. Drop your database:

       psql -U postgres -c "drop database callrec;"

   Icon

   If you have enough space to keep 2 copies of your database you might want to temporarily rename your original database, finish the restoring procedure, check that everything is ok and then remove it. For renaming the database use the command:

       psql -U postgres -c "alter database callrec rename to callrec_original;"

4. Create a new empty DB:

       /opt/callrec/dbscripts/postgres/mkdb.sh -X Ce -d <dbname>

   Where <dbname> is name of your database. In most cases the name is `callrec`.
   For example:

       /opt/callrec/dbscripts/postgres/mkdb.sh -X Ce -d callrec

5. And finally restore the database:

       psql -U postgres <dbname> -f <full_path_to_dump_file>

   For example:

       psql -U postgres callrec -f /home/admin/callrec.dump.sql

   The process can take a long time and is finished when the console is ready for user input again:

6. When the DB is restored, the last thing that needs to be done is to restore the search path:

       psql -U postgres <dbname> -c "alter database <dbname> set search_path=callrec,wbsc,public;"

   For example:

       psql -U postgres callrec -c "alter database callrec set search_path=callrec,wbsc,public;"

## Useful links

- http://www.postgresql.org/docs/9.1/static/sql-reindex.html
- http://www.postgresql.org/docs/9.1/static/sql-vacuum.html
- http://www.linuxinsight.com/optimize_postgresql_database_size.html

## Attachments:

image2014-4-17 9:45:37.png (image/png)
image2014-4-17 9:56:3.png (image/png)
image2014-4-17 9:56:24.png (image/png)
image2014-4-17 10:5:32.png (image/png)
image2014-4-17 10:16:25.png (image/png)