How to take Automatic SQL Database Backup using pgAgent | PostgreSQL

SQL Database Backup Automation in PostgreSQL

Taking regular SQL database backup is one of the most important responsibilities of a database administrator. By setting up automatic database backup we can get rid of the additional overhead of doing it manually, every day, week or month. When it comes to PostgreSQL running in any Unix distributions or in Windows, there are at least two ways to do it.

1. Using the built in CronJobs/ CronTabs in case of  Linux, and Windows scheduled tasks in Windows .
2. Using PostgreSQL’s own scheduling agent, pgAgent.

Often DBAs tends to rely on CronTabs or Scheduled tasked because of the ease of use it offers. But there are several advantages if you use pgAgent instead. Though it takes a little effort to install pgAgent, it is much better than Cron jobs or Scheduled tasks.

Why use pgAgent over CronJob/ CronTab or Scheduled Tasks ?

We can have multiple steps for a job even without doing it in a script.

Can have multiple schedules for a single job.

Cross platform and comes integrated with pgAdmin

pgAgent makes it easy to run PostgreSQL specific jobs which includes procedure calls or other SQL queries.

So, in this tutorial, we are gonna have a look at how we can set an automatic SQL database backup of your PostgreSQL DB. As we said earlier both Linux and Windows support pgAgent. Here we will see how to do it in Linux, to be more precise in Ubuntu.

Setting up Automatic Scheduled SQL Database backup in PostgreSQL involves 4 Steps:

1. Installing pgAgent in. This is a one time process.
2. Setting up the backup script.
3. Running the pgAgent daemon.
4. Setting up the SQL database backup jobs using pgAdmin.
5. Adding init/ start up script to run pgAgent on Ubuntu start up. (optional)

1. Installing pgAgent

In most of the cases pgAgent comes pre-installed with PostgreSQL. In case, if not, you can download and install it using any package manager like Synaptic, or simply use apt-get command as below.

sudo apt-get update

sudo apt-get install pgagent

After installation there is an additional step involved,  in order to make it functional. This should be done in both cases, whether it comes pre-installed or you installed it manually doesn’t matter.

• Find out the ‘pgagent.sql’ installation directory. Usually, it is found under ‘/usr/local/pgadmin3/share/pgadmin3’ or ‘/usr/share/pgadmin3’
• Once you found the file, open pgAdmin and connect to your postgreSQL maintenance DB(by default the DB named “postgres”), and Open Query Browser.
• Open “pgagent.sql” using File -> Open and run the whole query.
• Open “pgagent_upgrade.sql” from same folder and run.

Now you will able to see a new Database Object, ‘Jobs’ in the left tree of postgres. We will see it in detail, once we complete the next step.

2. Setting up the backup script

SQL backup script is any batch script file which actually do the backup operation. You can write your own backup in bash scripting or any other scripting language. But here we are using a script provided by PostgreSQL, which is more than enough for an average Database.

We will be using two files: ‘pg_backup.config‘ and ‘pg_backup.sh‘. You can download the script and configuration file from the bottom of this post or copy from their site.

Now, create a directory, say, ‘backups’ in your home directory and place both files there. We have to make a couple of changes to the ‘pg_backup.config’ file. Open ‘pg_backup.config’ file in a text editor and make the following changes:

• BACKUP_USER=unix_user_name (this is the Unix system user to run backup as, put your Ubuntu username there)
• HOSTNAME=localhost (The database host name)
• BACKUP_DIR=/home/<user>/backups/database/postgresql/ (Directory where backup should be created. Note that the user specified in BACKUP_USER must have read and write permission to this directory)
• In SCHEMA_ONLY_LIST, specify the databases for which you don’t want the data to be backup-ed. It will take a backup of schema/ ddl only.

Configure rest of the options as your preference. And “SETTINGS FOR ROTATED BACKUPS” need not to be touched since we are not using “pg_backup_rotated.sh” script.

So we are done with ‘pg_backup.config’ file. Now we have to set ‘pg_backup.sh’ as executable. Use CHMOD to do that:

chmod +x pg_backup.sh

3. Running the pgAgent daemon

We should run the pgAgent daemon first in order to take scheduled sql database backup. Use the following command to run pgAgent daemon:

pgagent hostaddr=localhost dbname=postgres user=postgres

Change this according to your DB setup.

 pgAgent is a powerful tool and has some security concerns which need to be taken care of. To understand how to set up pgAgent, minimizing risks, please read following:
How to use pgAgent Securely with PostgreSQL [guide]

4. Setting up the SQL database backup jobs using pgAdmin

Now, we have the pgAgent running in the background. Let’s see how to create a new backup job in PgAdmin,

4.1. Open pgAdmin and start a New Job as seen in the screen shot

4.2. Give a name to the job.

4.3. From Steps tab add a new step. Choose Kind as Batch and give a name.

4.4. In Definition tab, enter the location to the script file, in my case it will be like below CS,

4.5. Now press OK, you would be returned into New Job window. From there go to Schedules tab and click Add. In New Schedule window, you  will have all possible options to select date and time of your  choice.

4.6. Press OK twice. That’s it! We completed all steps of setting automatic sql database backup of your PostgreSQL DB using pgAgent. You can see the backup file in the path you set in config file. In my case it is, /home/vipin/backups/database/postgresql/

Verdict: In step 4.3 we can select SQL as Kind, if you want to run an SQL statement instead of bash script. You can enter any kind of SQL procedures there. When we use bash script, it is not necessary that it should perform an SQL database backup. It can be anything! What pgAgent does is that, it run the script on time. Nothing more! That means, pgAgent can be used as a general purpose scheduling tool in addition of being used as an SQL database backup auto-mater.

 

5.  Adding startup script for pgAgent to be run at Ubuntu bootup

Adding pgAgent to the list of start up program will make it run every system boot up without manual intervention. Normally programs like PostgreSQL comes with inbuilt startup scripts, but pgAgent doesn’t. So here is the simple steps to add a startup script for pgagent:

1. Create a text file named pgagent in /etc/init.d/ folder and add the following text to it.(all credits goes to Derek Arnold for providing the script)

   #!/bin/bash
   #
   # /etc/init.d/pgagent
   #
   
   test -f /lib/lsb/init-functions || exit 1
   . /lib/lsb/init-functions
   
   PIDFILE=/var/run/pgagent.pid
   prog=PGAgent
   PGAGENTDIR=/usr/bin
   PGAGENTOPTIONS="hostaddr=127.0.0.1 dbname=postgres user=postgres"
   
   start() {
    log_begin_msg "Starting PGAgent"
    start-stop-daemon -b --start --quiet --exec "$PGAGENTDIR/pgagent" --name pgagent --startas "$PGAGENTDIR/pgagent" -- $PGAGENTOPTIONS || log_end_msg 1
    log_end_msg 0
   }
   
   stop() {
    log_begin_msg "Stopping PGAgent"
    start-stop-daemon --stop --quiet -n pgagent || log_end_msg 1
    log_end_msg 0
   }
   
   #
   # See how we were called.
   #
   case "$1" in
    start)
    start
    ;;
    stop)
    stop
    ;;
    reload|restart)
    stop
    start
    RETVAL=$?
    ;;
    status)
    status /usr/bin/pgagent
    RETVAL=$?
    ;;
    *)
    log_success_msg "Usage: $0 {start|stop|restart|reload|status}"
    exit 1
   esac
   
   exit 0
   

2. Make it executable using the command:

   sudo chmod +x /etc/init.d/pgagent

3. Add it to startup using:

   sudo update-rc.d /etc/init.d/pgagent defaults

4. That’s it. Now pgAgent daemon will start automatically on system boot up and stop at shutdown.
5. If you want to start or stop server manually you can simply use these commands:

   /etc/init.d/pgagent start
   /etc/init.d/pgagent stop

 

If you have any questions or problems in setting automatic sql database update, drop a comment here!