Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

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:

Compared to CronTab, PgAgent has the following advantages: 

  • You can have multiple steps for a job without having to resort to a batch script.
  • You can have multiple schedules for a job without having to repeat the line.
  • Is cross platform
  • For running PostgreSQL specific jobs such as stored function calls or adhoc sql update statements etc. it is a bit easier granted the PostgreSQL account used is a super user or has sufficient rights to the dbs.

Compared to Windows Scheduled Tasks - PgAgent has the following advantages: 

  • You can go down to the minute level
  • Have several steps per job
  • Have multiple schedules per job
  • Is cross platform
  • For running PostgreSQL specific jobs such as stored function calls it is easier than using windows scheduled tasks.

Compared to SQL Server Agent - PgAgent has the following advantages: 

  • SQL Server Agent comes only with Microsoft SQL Server Workgroup and above so not an option say for people running SQL Server Express editions or no SQL Server install.
  • Is cross platform
  • Some missing features in PgAgent which would be nice to see in later versions would be some sort of notification system similar to what SQL Server Agent has that can notify you by email when things fail and a maintenance wizard type complement tool similar to what SQL Server 2005 Maintenace Wizard provides that allows users to walk thru a set of steps to build automated backup/DB Maintenance tasks. This is a bit tricky since it would need to be cross-platform. Granted the job history display in PgAdmin that provides success and time taken to perform task is a nice touch and makes up for some of this lack and you can always roll your own by running some monitor to check the job event logs.
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.
Screenshot from 2013-07-13 03:29:57
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: 
  1. pg_backup.config
  2. 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) Optional hostname to adhere to pg_hba policies.  Will default to "localhost" if none specified.
  • BACKUP_DIR=/home/<user>/backups/database/postgresql/ ,This dir will be created if it doesn't exist  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.
  • USERNAME=postgres,username to connect to database as.  Will default to "postgres" if none specified.
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 do you use  pgagent with securely

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,
   1.Open pgAdmin and start a New Job as seen in the screen shot
                                            postgres  sql database backup - starting new job in pg agent
          2. Give a name to the job.

New Job window in pgAgent - taking automatic sql backup

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

New Step window in pgAgent - taking automatic sql backup

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

Step definition window in pgAgent - taking automatic sql backup

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

New Schedule window in pgAgent - taking automatic sql backup

             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/
Sql backup file
Verdict: In step 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.
# /etc/init.d/pgagent
test -f /lib/lsb/init-functions || exit 1
. /lib/lsb/init-functions
PGAGENTOPTIONS="hostaddr= 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
 status /usr/bin/pgagent
 log_success_msg "Usage: $0 {start|stop|restart|reload|status}"
 exit 1
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


Popular posts from this blog

rman interview and scenario based questions and answer and also rman(backup and recovery) discussions panel

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

PostgreSQL pgBadger