PSCS Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
======Recovering messages from corrupted database into fresh database======

If the VPOP3 message database is corrupted, then one option is to build a fresh database and restore the settings into it, then recover the messages from the old database using the 'Message Restore' function in VPOP3. This has minimal downtime, but old messages will not all be available until the restore has completed. New messages will be available quickly, and you will be able to send messages.

This procedure requires about as much free space on the VPOP3 disk as is already used in the VPOP3\pgsql\data folder and subfolders

We recommend using the latest version of VPOP3 (v8.8 or later) as older versions may not copy all messages if they encounter database errors while attempting to copy messages.

====Stop VPOP3====
Go to Services in Windows, and stop the 'VPOP3' service. Do NOT stop the 'VPOP3DB' service.

====Backup the settings from the existing database====
  - At a command prompt, go to the VPOP3\pgsql\bin folder
  - Run: <code>pg_dump -v -p 5433 -U postgres -N messages -F c -f settings.dmp</code> \\  (default password is 'pgsqlpass') \\   (this backs up the VPOP3 settings (not messages) into 'settings.dmp')
  
====Rename old database and create new database====
  - At a command prompt, go to the VPOP3\pgsql\bin folder
  - Run: <code>psql -U postgres -p 5433</code> \\ (default password is 'pgsqlpass')
    - type: <code>ALTER DATABASE vpop3 RENAME TO vpop3_old;</code> \\ (this renames the old database)
    - type: <code>CREATE DATABASE vpop3 OWNER vpop3 encoding 'SQL_ASCII' template template0;</code> \\ (this creates a new database)
    - type: <code>\q</code>
  - Run: <code>pg_restore -U postgres -p 5433 -v -d postgres settings.dmp</code> \\ (this restores the VPOP3 settings (not messages) into the new database)

====Restart VPOP3====
Go to Services in Windows, and start the 'VPOP3' service.

It should start OK, and have settings, users, etc, but not the messages

====Restore messages====
In the VPOP3 settings, go to Settings -> Database -> Restore
  * In Server, put 'localhost'
  * In Username, put 'postgres'
  * In Password, put 'pgsqlpass'
  * In Port, put 5433
  * In Database, choose 'vpop3_old' (the name you renamed the original database to in the steps above)

A table should populate containing all the users. Select them all, and press the 'Start Restore' button. That should bring up a list of messages and users and show the message counts as they are copied from the old database to the new one

Note that VPOP3 will not be able to copy corrupted messages, and older versions may stop copying messages in a folder if it encounters a problem with messages there. We recommend using the latest version of VPOP3

VPOP3 writes a text log file called 'RESTORE.LOG' containing details of the restore process

====Clean up====
Once you are sure that you have all the message data you want, you can remove the old data. Note that this cannot be undone, so **be absolutely certain** before doing it

  - delete the 'settings.dmp' file you created in the VPOP3\pgsql\bin folder
  - at a command prompt, go to the VPOP3\pgsql\bin folder and run: <code> dropdb -U postgres -p 5433 vpop3_old</code> \\ (default password is 'pgsqlpass')



=====Older version of VPOP3=====
If you have a version of VPOP3 prior to v8.8, it may stop restoring a user's mailbox folder if it encounters a corrupted message, so one option is to use these steps on the 'vpop3_old' database

[[repairing_corrupted_database#finding_the_problem_message]]

to find and delete the problem messages. If you can't delete the problem messages, you could try marking them as deleted instead, which will make the message restore option skip those messages.

In PSQL run this instead of deleting the message:

  UPDATE messages.foldermessages SET deletedtime=now() WHERE msgdataid= <problem message id>;
PSCS Wiki

User Tools

Site Tools

Page Tools
