How to backup database files

  • 7024748
  • 22-Jul-2020
  • 22-Jul-2020


GWAVA (Secure Messaging Gateway)


How can the SMG database files be backed up on a regular basis?


If a reoccurring backup is needed of the SMG database files, here's some information on how that can be done.

1) The first thing that needs to be done is the server that will be keeping the backups needs to be granted access to the SMG db's. This can be done by logging into the vaadmin UI (https://serverIP:9443)| Configure Postgresql and entering the IP Address of the server that will be hosting the backups, in Allowed Connections.

2) On the server that will be hosting the backups, a postgres client will need to be installed. There are a number of commercial and free utilities to backup Postges databases that offer a wide range of scheduling features. However, a simple backup of the SMG database can be done, using pg_dump.

3)  A cron job(s) can also be created (on the server that will be hosting the dbs) to perform backups. Below is a sample command line for getting a backup of the SMG primary/admin/configuration database:


PGPASSWORD="password" pg_dump --host= --port=5432 --username=postgres --format=c --dbname=SecureGateway | gzip > $(date +%Y-%m-%d).gz

3) The pg_dump utility will not let allow a password to be provided as a command line argument. There are a few ways to provide the password. There are security trade offs to each method. The example above is using the method of creating (a temporary) environment variable call PGPASSWORD with the password for the postgres user. The PGPASSWORD environment variable will only exist during the execution of this command.

Explanation of arguments used in example:

--host=<ip address or DNS name, if not specified defaults to localhost>
--port=<postgresql server listening port, defaults to 5432>
--username=<name of user with access to the database>
--format=<c (custom), t (tar), d (directory), p (plain text); defaults to plain text, but custom is the preferred output format >
--dbname=<name of database to backup>

 " | gzip > $(data + %Y-%m-%d).gz" was added above to demonstrate a method that can be used to compress and write the backup to a generated filename. In the above example this will result in a file like year-month-day.gz. The example command line could be used in a cron job that runs every x days and each execution would create a new dated and compressed backup of the database.