======Database Maintenance====== VPOP3 uses the [[http://www.postgresql.org|PostgreSQL]] database server for most of its data storage. Normally this will not require any special maintenance, because it will manage itself. To do any special database maintenance you will need to close down VPOP3 first, because PostgreSQL needs exclusive access to the database to perform any operations such as decreasing the size of database files. Note that these operations may take some time! There are some PostgreSQL utility programs in the VPOP3\pgsql\bin directory. These are command-line programs so need to be accessed from a command prompt. If you are keen to get the best performance, you may feel like doing a 'clusterdb' and 'reindexdb' operation regularly, and that is generally OK (remember to close VPOP3 first, and restart it afterwards). However, it is usually not necessary. On our own busy VPOP3 servers we have run clusterdb and reindexdb ONCE in the past 3 years, and they still perform well. (Doing a 'vacuumdb -f' should just be a last resort, we don't recommend you do that regularly at all - note that many people want this functionality to be removed from PostgreSQL totally, that is how bad it is considered to be). =====Differences between versions of VPOP3===== VPOP3 versions 3 and 4 installed PostgreSQL on the default port of 5432. In version 5 this was changed so that it will now find the first free port AFTER 5432, this was to avoid conflicts with other installations of PostgreSQL on the same computer. Usually this means that for version 5 PostgreSQL will be on port 5433 (but it may be a higher number, if other software is using port 5433). You can tell which port VPOP3 is using by going to the **Settings -> Database -> Connection** page in the VPOP3 settings, and look at the **PostgreSQL Port** setting. If VPOP3 isn't running, then you can view the 'psql.bat' file in the VPOP3 installation directory. This will contain something like '-p 5433' which tells you the port number. You need to specify the port number in all PostgreSQL commands, as '-p ', eg ''-p 5433'' =====Freeing disk space===== In exceptional circumstances you may want to reduce the size of the database files created by the database system. Normally this is not recommended, as it reduces performance and can increase disk fragmentation, but if you are running short of disk space and have made a setting change which will lead to a reduction in the amount of data being stored (such as decreasing the time for which historical logging records are kept) then it may be something you wish to do. Usually the best way of freeing disk space is to do a [[backup_vpop3#manual_database_backup|database backup]] & [[restore_a_backup_of_vpop3|restore]]. This requires VPOP3 to be stopped while it is performed. ====Steps for backup/restore==== (These instructions are for VPOP3 v5 or later) * Stop the VPOP3 service, //not// the VPOP3DB service * Go to a command prompt in the VPOP3 directory * Backup the database to a file, eg 'database.dmp', using, eg: **pgsql\bin\pg_dump -p 5433 -v -F c -U postgres -f database.dmp vpop3** * Make sure there were no errors reported during the backup! * Remove the old database using **pgsql\bin\dropdb -U postgres -p 5433 vpop3** * Restore the database backup using, eg: **pgsql\bin\pg_restore -v -j 4 -U postgres -p 5433 -C -d postgres database.dmp** * Restart the VPOP3 service If asked for a password, the default password to use is **pgsqlpass** For further information, see the PostgreSQL documentation: * [[http://www.postgresql.org/docs/9.1/static/app-pgdump.html]] * [[http://www.postgresql.org/docs/9.1/static/app-dropdb.html]] * [[http://www.postgresql.org/docs/9.1/static/app-pgrestore.html]] ====Alternative to backup/restore==== If you are very short of free disk space, then you can use the 'vacuumdb' program, but this is not recommended except as a last resort. To do this, stop VPOP3, then run **[[http://www.postgresql.org/docs/9.1/static/app-vacuumdb.html|vacuumdb]] -f -U vpop3 -p vpop3**. After you have run this you will need to reindex the database as well - use **[[http://www.postgresql.org/docs/9.1/static/app-reindexdb.html|reindexdb]] -U vpop3 -p vpop3**. This "vacuum full" operation will generally take a lot longer than a backup/restore program. =====Rebuilding Indexes===== If you suspect that indexes have become damaged, then you can reindex the database. Again, this requires VPOP3 to be stopped first. Go to the VPOP3\pgsql\bin folder in a Windows command prompt. Then run **reindexdb -U vpop3 -p vpop3**. The default password is **vpop3pass**. This operation may take a while, and require some free disk space.