Delete OutQueue messages directly from the database

If there is an extremely large number of messages (eg hundreds of thousands) in the VPOP3 OutQueue, then it may take a very long time for VPOP3 v5 and later to start as it check the message database tables for consistency during initialisation.

In this case, you can access the database directly to clean up the OutQueue.

1. Go to a command prompt in the VPOP3 installation folder.
2. Type 'psql'.
3. At the password prompt, enter the database password - default: vpop3pass

At this point you may wish to see how many messages there are, and see if you can work out how they got there.

SELECT COUNT(*) FROM outqueue.outmessages;

SELECT COUNT(*) FROM outqueue.outmessages WHERE finishedtime IS NULL;

SELECT senderip, authsender, returnpath, subject, created FROM outqueue.outmessages ORDER BY msgid DESC LIMIT 10;

The three most common causes of unwanted messages in the OutQueue are:

• open relay - you can tell this because the 'senderip' is an external IP address, and the 'authsender' is blank
• 'hacked' account - you can tell this because the 'senderip' is an external IP address, and the 'authsender' is not blank - in this case someone has found or guessed the 'authsender's password
• virus/spambot on local PC - you can tell this because the 'senderip' is an internal IP address - check the computer with that IP address

To delete all the messages from the VPOP3 OutQueue, at the psql prompt, type:
DELETE FROM outqueue.outmessages;

If you know the IP address the unwanted messages came from, you could use:
DELETE FROM outqueue.outmessages WHERE senderip='<ip address>';

If you know the authenticated sender the unwanted messages came from, you could use:
DELETE FROM outqueue.outmessages WHERE authsender='<username>';

etc.

After deleting the messages, you may want to shrink the disk file, otherwise there may be a lot of wasted disk space if you have deleted an extremely large number of messages. To do this, at the psql prompt, do:

CLUSTER VERBOSE outqueue.outmessages;
CLUSTER VERBOSE outqueue.outrecipients;

An alternate way to delete ALL the outqueue messages, and related information (but not settings or messages in mailboxes) is to stop VPOP3 first, and then use:

DROP SCHEMA outqueue CASCADE;

This will delete all the relevant tables from the database, including their contents, so will be a lot quicker. When you restart VPOP3 it will recreate the deleted tables. You cannot selectively delete messages in this case. If you are using some sort of third party database replication, such as Bucardo, then that may need reinitialising.