Differences

This shows you the differences between two versions of the page.

--- how_to:database_maintenance [2012/02/15 10:53] – paul
+++ how_to:database_maintenance [2018/11/14 10:45] (current) – external edit 127.0.0.1
@@ Line 20: / Line 20: @@
 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.
-The main program you should use for decreasing the size of the database is the 'clusterdb' program, so, from a command prompt, go to the VPOP3\pgsql\bin folder, and run **clusterdb -U vpop3 -p <portnumber> vpop3**. The default password is **vpop3pass**. This operation may take some time, and VPOP3 can't run while it is doing it. Also, the 'clusterdb' program may require a lot of free disk space while it is running, although it will use less when it has finished. (It creates a new copy of the table while it is running).
+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.
-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, run **vacuumdb -f -U vpop3 -p <portnumber> vpop3**. After you have run this you will need to reindex the database as well - use **reindexdb -U vpop3 -p <portnumber> vpop3**. This "vacuum full" operation will generally take a lot longer than the 'clusterdb' program.
+====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 <portnumber> 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 <portnumber> 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.

PSCS Wiki

User Tools

Site Tools

Differences

Page Tools