By default, Netmail Archive uses the PostgreSQL database (located on the master Archive node) to store job information logged by the various Netmail system agents. Over time, the database can grow quite large, especially if extra tracing was used to gather detailed information for troubleshooting. Such information is generally not used again once the issue is resolved and a large SQL database can have a negative impact on Netmail Archive. A bloated database can slow the paging speed of the Netmail WebAdministration console, or issues may arise when exporting job summary or error reports because information cannot be properly logged. This article will explain how to drop the existing database and then recreate it to either resolve issues with the database or to prevent potential issues from occurring. Dropping the old database also frees up space on the server hosting it.
NOTE: If your Archive system has Crawler functionality, DO NOT DROP THE ENTIRE DATABASE. Drop only the table needed to free up space (E.G. mplus_trace). Dropping the entire database will delete the Crawler table containing Crawler Location IDs, making it impossible to use the UI to delete existing locations and their corresponding indexes.
Start by logging into to the PostgreSQL management utility, pgAdmin III.
When the administration console opens, the left pane (object browser) will display the PostgreSQL 9.3 server container.
Double click the container to connect to the database. The postgres user's password is required; default password is 'M3ss4g1ng'.
Dropping the existing database
Once logged in to the administration console, expand the Databases container to display the netmailarchive and postgres databases.
Right-click the netmailarchive container and choose the Delete/Drop option to delete the existing database:
Re-creating the logging database
Right-click the Databases node and select "New Database". This will bring up a dialog box. Give the new database the name netmailarchive. Select the owner to be the user postgres.
Re-creating the tables (automatic method)
Log into the the Netmail WebAdministration console and navigate to Archiving > Address Book Sync tab:
Click the Run Now button to launch the job. Assuming a successful run, six tables will be recreated in the database.
Upon returning back to pgAdmin III, the following table should be visible under the new database (schemas > public > tables)
Re-creating the tables (manual method)
The following file (located on the server hosting PostgreSQL) will contain the SQL statements required for table creation: C:\Program Files (x86)\Messaging Architects\GWOpen\netmail.postgres.xml
Open the file in a text editor. This file contains the SQL required to create the tables, encapsulated in XML tags for the Netmail scripts to understand. Copy all the contents to the clipboard.
Connect to PostgreSQL with pgAdmin III. Navigate to the netmailarchive database and select the SQL button from the menu bar (highlighted in the screenshot below).
Paste the contents of the file into the SQL Editor (top-left pane). Scroll through the pasted text and delete any XML tags (eg. <sqlstmnt>). Once all tags have been purged, next click the Execute query button (green 'forward' arrow, highlighted below); the output pane will indicate the progress of the operation.
When the operation completes, the new table(s) should be visible in the pgadmin III console. Verify this using the pgAdmin III Object browser by navigating to the following container:
Databases > netmailarchive > Schemas > public > tables