Create Daily Backup PostgreSQL Database using Cron in Linux

A backup process for database is an important part when it is really needed to maintain system’s availability. When a database crashed, the role of a backup file to be restored to a new prepared database is certainly necessary.

In this article, there will be a further description of database backup process focusing on PostgreSQL Database Server. Below is the step which is used to do it :

1. Make sure the service is active and it can be done by reading the following article about ‘Checking PostgreSQL service status‘.

2. Try to connect to PostgreSQL Database Server after ensuring the status of PostgreSQL is active. The article named ‘PostgreSQL Database Access from Command Line in Linux‘ can be used as reference.

3. Cron needs the command executed without having to bother answering for password input. Since cron need to connect and dump database without having to input password, create a non-interactively connection to PostgreSQL Database Server. It can be done by reading the following article about ‘Access PostgreSQL without Password‘. This is very important and it is necessary since cron will not be supplied with PostgreSQL username’s password typed in the cron line and it will not functionally working to proceed on backing up the database if the process is stop only by input request for password.

4. After succeed on connecting PostgreSQL without any password since it has already been configured in the 3rd step, last but not least, execute cron to begin entering line of configuration in order for initiating daily backup of database in PostgreSQL Database Server. Type the following command :

root@hostname:~# crontab -e

After executing the command above, it will be directed into a temporary text file which is used to define the configuration line of cron. Insert the following line configuration to enable automatic scheduled backup for PostgreSQL Database Server

30 23 * * *  pg_dump --username postgres -Fc --file=/home/postgres/backup/database_name_$(date +\%d\%m\%Y).pgdump database_name
Description : 
30 : It is used to represent on minute 30 that the task will be executed
23 : It is used to represent at 11 o'clock midnight, combined with the previous number which is 30, it is translated that the task will be executed every 23.30 midnight : 
* * * : The first * sign is a wildcard represent everyday in the month. The second * sign is a wildcard represent every month. The last * sign, or the third * sign is a wildcard represent everyday in the week.
So, based on the combination of time schedule, the task will be executed everyday every 23.30 midnight  

But before executing the task in a scheduled mode using cron, it is a wise decision to execute the task manually which is executing the task without any help from crontab utility.

To execute PostgreSQL Database Server dump process manually, it can refer to article titled ‘Dumping PostgreSQL Database into File via Command Line‘ or ‘Dumping PostgreSQL Database into File with Date Attribute‘.