Sophos

Online support

Product maintenance

Contact support

Support services

PureMessage for UNIX: Dumping and Restoring the PostgreSQL database

Version(s): 5.4.0, 5.4.1

This process outlines a dump and restore of a PostgreSQL database. It should be used in situations where disk space maintenance is required. As we no longer recommend full vacuums, this process will clean the database in a similar but more effective manner.  All commands should be done as the pmx user and a BASH shell is assumed.

Step 1:

It is necessary to first setup our shell environment to execute commands without referencing explicit paths. The following commands (all should be executed as the pmx user), will do that.

$ set -a

$ PATH=$PATH:/opt/pmx/postgres/bin

$ PGDATABASE=pmx_quarantine

$ PGDATA=/opt/pmx/postgres/var/data

$ PGUSER=pmx

Step 2:

The database now needs to be shutdown and dumped to a file. There is a potential for jobs that may still be connected to the database. These jobs must be either allowed to finish, or in the case of a sick database, it may be necessary to terminate them explicitly. The port that Postgres runs on will be changed temporarily to port 5433. This will disallow any connections from incoming PureMessage jobs either on the local server, or from edge servers. Please ensure that there is space free in the dump location. In this example, the pmx home directory is used.

This should be used normally (again as the pmx user).

$ pg_ctl stop -msmart 

This aborts transactions in progress.  This is only for very sick databases.

$ pg_ctl stop -mfast

The database should be stopped at this point.

$ PGPORT=5433               

$ pg_ctl start -o "-p$PGPORT" -l /opt/pmx/home/5433.log

$ time pg_dump -Fc >/opt/pmx/home/pmdb.zdump

Step 3:

Once the database dump is complete, we need to drop the existing database and re-create a shell to re-import the database into. A vacuum will be run to ensure that the re-imported database is clean. As a rule of thumb, the LOAD time for a database is about 10 times the dump time. The time can vary and typically it will be much quicker then this.

$ dropdb $PGDATABASE

$ createdb $PGDATABASE

$ time pg_restore /opt/pmx/home/pmdb.zdump | psql -qet >/opt/pmx/home/load.log 2>&1

$ time vacuumdb -vz 2>/opt/pmx/home/initvac.log

$ pg_ctl stop

Step 4:

Finally, we will restore the Postgres configurations to the default options for normal operation.

$ unset PGPORT

$ pmx-database start

If you need more information or guidance, then please contact technical support.