One of the common tasks for any online business is to keep its data consistent. To achieve this task a database backing up technique can be used. PostgreSQL is a wide spread DBMS that is very popular within web application world and in this guide we’ll see how to automate backups creation process for PostgreSQL on Linux taking as an example Alfresco ECM web application.

Let’s assume that PostgreSQL is already installed on your target Linux system. At the time of the installation the postgres user will be automatically created – it’s a default PostgreSQL user which will be used to manage backing up process, this user already exists within DBMS and will be granted to perform dumping of the database. Let’s switch to home of postgres user:

su - postgres

Now we should create (if not exists) a .pgpass file in the root dir of the postgres user and grant this user a permission to connect to a database. To allow this the entity of the hostname:port:database:username:password format should be appended to current file:

printf "localhost:5432:alfresco:postgres:some_password" > .pgpass

On Unix systems, the permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored.

So let’s run a command assuming we are in the root dir:

chmod 0600 .pgpass

The second step after the permissions to postgres user are granted is to write a system shell script that will actually do a backing up of the database and printing some log (containing info about scheduler timings):

#!/bin/sh
DBBACK=`date +%Y-%m-%d-dbback.sql` 
START_TIME=`date +%Y-%m-%d:%H:%M:%S`
echo "start $START_TIME" >> /opt/alfresco1/postgresql/backups/backup.log
pg_dump -h localhost alfresco -f /opt/alfresco1/postgresql/backups/$DBBACK
END_TIME=`date +%Y-%m-%d:%H:%M:%S`
echo "finish $END_TIME" >> /opt/alfresco1/postgresql/backups/backup.log
exit 0

This script uses PostgreSQL pg_dump command and store in backup.sh file.

The third step will be to setup some scheduler that will periodically run the upper script using the permissions granted in the first step. Crontab is a very handy Linux tool that can manage a periodic script execution, it uses well known crontab notation. Using this notation we can define backup process starting from Monday to Friday at 3am (night time is a right time to perform such activities): 0 3 * * 1,2,3,4,5. Having cron-coded script execution string we should save it to crontab tool. Assuming we are under postgres user, run (-e edit mode, –l list mode):

crontab -e

and add the following string, where latter part is the path to the script to be executed:

0 3 * * 1,2,3,4,5 /opt/alfresco1/postgresql/scripts/backup.sh

To sum up: we get the scheduled (automated) backing up process that works on PostgreSQL DBMS and Linux. This approach can be used to dump db data of any web application, e.g. Alfresco. Backups will be stored daily (from Mo to Fri) as shown in the image: