Differences

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

--- how_to:database_maintenance [2011/03/08 18:03] – [Rebuilding Indexes] paul
+++ how_to:database_maintenance [2018/11/14 10:45] (current) – external edit 127.0.0.1
@@ Line 9: / Line 9: @@
 (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 <portnumber>', 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.
-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 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.
+====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]]
-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 vpop3**. After you have run this you will need to reindex the database as well - use **reindexdb -U vpop3**. This "vacuum full" operation will generally take a lot longer than the 'clusterdb' program.
+====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.
-Go to the VPOP3\pgsql\bin folder in a Windows command prompt. Then run **reindexdb -U vpop3 vpop3**. The default password is **vpop3pass**. This operation may take a while, and require some free disk space.
+Go to the VPOP3\pgsql\bin folder in a Windows command prompt. Then run **reindexdb -U vpop3 -p <portnumber> vpop3**. The default password is **vpop3pass**. This operation may take a while, and require some free disk space.

PSCS Wiki

User Tools

Site Tools

Differences

Page Tools