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:
- VACUUM FULL
- REINDEX
- 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.
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.
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:

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
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
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:

Drop your database:
psql -U postgres -c "drop database callrec;"
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
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:

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