PureMessage for UNIX: Dumping and Restoring the PostgreSQL database

  • Article ID: 15422
  • Rating:
  • 3 customers rated this article 5.0 out of 6
  • Updated: 12 Aug 2014

This article describes a dump and restore of a PostgreSQL database. It should be used when 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 PureMessage user, and a BASH shell is assumed.

Step 1:

First, set up a shell environment to execute commands without referencing explicit paths. The following commands will do that. (All should be executed as the PureMessage user.)

$ set -a

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

$ PGDATABASE=pmx_quarantine

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

$ PGUSER=pmx

Step 2:

The database must be shut down 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 PostgreSQL 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. Ensure that there is free space in the dump location. In this example, it is the home directory for the PureMessage user.

As the PureMessage user, run:

$ pg_ctl stop -msmart

This aborts transactions in progress and is only required for 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, you must drop the existing database and re-create a shell to re-import the database. 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 than this.

$ dropdb $PGDATABASE

$ createdb $PGDATABASE

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

$ echo "COMMENT ON SCHEMA public IS 'PMX 5.600';" | psql -qet

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

$ pg_ctl stop

Step 4:

Finally, you must restore the PostgreSQL configuration to the default options for normal operation.

$ unset PGPORT

$ pmx-database start

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

Rate this article

Very poor Excellent