14.4.15

Installation pgAgent on CentOs

I faced the task to install pgAgent on PostgreSql 9.3 CentOs 6 server.
I serached a lot but I didn't find anything on web very complete.
So why don't write it by myself?
The most explanatory guide I found was this on OrbitPhreak blog

Due to the fact that updating CentOs Epel Repository I met this error

 Loaded plugins: fastestmirror, security  
 Determining fastest mirrors  
 Error: Cannot retrieve metalink for repository: epel. Please verify its path and try again  

I found this useful link to solve also this problem

So let's install this fucking pgAgent.
To install pgAgent we also have to install many packages (wxWidgets) related to graphic libraries used by pgAgent itself.
This is maybe due to the fact that pgAgent was originally a part of the pgAdmin client.
Tha last package of pgAgent in the time I'm writing is 3.4.0, but you find the last package here.
Type in sequence this commands on your CentOs server

 yum install wxGTK.x86_64 wxGTK-devel.x86_64 cmake  
 yum install gcc  
 yum install gcc-c++  
 yum install postgresql-devel  
 yum install wget  
 wget http://ftp.postgresql.org/pub/pgadmin3/release/pgagent/pgAgent-3.4.0-Source.tar.gz  
 tar -zxvf pgAgent-3.4.0-Source.tar.gz  
 wget http://sourceforge.net/projects/wxwindows/files/2.8.12/wxWidgets-2.8.12.tar.gz  
 tar -zxvf wxWidgets-2.8.12.tar.gz  
 cd wxWidgets*  
 ./configure --with-gtk --enable-gtk2 --enable-unicode  
 make  
 su -c "make install; /sbin/ldconfig"
cd contrib
make
su -c "make install"  

Now we can install pgAgent.

 cd pgAgent*  
 cmake -D PG_CONFIG_PATH:FILEPATH=/usr/pgsql-9.3/bin/pg_config -D STATIC_BUILD:BOOL=OFF .  
 make  
 su -c "make install"  
 cp sql/pgagent.sql /var/lib/pgsql/9.3  
 su postgres  
 psql -U postgres -d postgres -f pgagent.sql  
 exit  
 echo '/usr/pgsql-9.3/lib' >> /etc/ld.so.conf.d/postgres.conf

Ok, now we have installed all, we can create a service called pgagent useful for starting, stopping pgagent whenever we want.

 vim /etc/rc.d/init.d/pgagent  

and we can copy all this text inside the text editor

 #!/bin/bash  
 #  
 # /etc/rc.d/init.d/pgagent  
 #  
 # Manages the pgagent daemon  
 #  
 # chkconfig: - 65 35  
 # description: PgAgent PostgreSQL Job Service  
 # processname: pgagent  
 . /etc/init.d/functions  
 RETVAL=0  
 prog="PgAgent"  
 start() {  
   echo -n $"Starting $prog: "  
   daemon "/usr/local/bin/pgagent hostaddr=127.0.0.1 dbname=postgres user=postgres password=??????"  
   RETVAL=$?  
   echo  
 }  
 stop() {  
   echo -n $"Stopping $prog: "  
   killproc /usr/local/bin/pgagent  
   RETVAL=$?  
   echo  
 }  
 case "$1" in  
  start)  
   start  
   ;;  
  stop)  
   stop  
   ;;  
  reload|restart)  
   stop  
   start  
   RETVAL=$?  
   ;;  
  status)  
   status /usr/local/bin/pgagent  
   RETVAL=$?  
   ;;  
  *)  
   echo $"Usage: $0 {start|stop|restart|reload|status}"  
   exit 1  
 esac  
 exit $RETVAL  

Then we set grants on file service and put it starting at boot time.

 chmod 0755 /etc/rc.d/init.d/pgagent  
  /sbin/chkconfig --level 345 pgagent on  
 service pgagent start  

To make pgAgent usable from Postgres we should modify pg_hba.conf in this way

 vim /var/lib/pgsql/9.3/data/pg_hba.conf  

and make the "host" row like this

 host  all       all       127.0.0.1/32      trust  

In this manner the postgres user can trust himself in any way: both with password or woithout it.
For example running this command it should work

 SELECT dblink_connect('user=postgres password=ocpNHosting14 host=127.0.0.1 dbname=postgres');  

Just insert the host definition equal to 127.0.0.1.

That's all!

Now you can create new jobs following this useful guide.

Enjoy!




7 comments:

  1. Anonymous8/3/16 11:04

    works like a charm.

    Thank you,
    zmeu

    ReplyDelete
  2. Anonymous18/5/16 15:35

    very good job Thanks

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Anonymous26/7/16 13:16

    Hi, i have installed all the things mention above in the article but still get the error, please see below error:

    localhost.localdomain systemd[1]: Starting SYSV: PgAgent PostgreSQL Job Service...
    localhost.localdomain pgagent[29792]: Starting PgAgent: /bin/bash: /usr/bin/pgagent: Is a directory
    localhost.localdomain pgagent[29792]: [FAILED]
    localhost.localdomain systemd[1]: pgagent.service: control process exited, code=exited status=1
    localhost.localdomain systemd[1]: Failed to start SYSV: PgAgent PostgreSQL Job Service.
    localhost.localdomain systemd[1]: Unit pgagent.service entered failed state.
    localhost.localdomain systemd[1]: pgagent.service failed.

    ReplyDelete
    Replies
    1. Sorry, I can't help you, because I don't have any linux server available to have a try with you.
      Have you gave the right grants on service?

      Delete
  5. great, but with the postgres 9.5 repo I could just do this:
    # sudo yum install pgagent_95

    Hope it helps!

    ReplyDelete
  6. FOR THE LAST COMMENT ... it's not working
    sudo yum install pgagent_95
    [sudo] password for sandronikos:
    Loaded plugins: fastestmirror, langpacks
    Loading mirror speeds from cached hostfile
    * base: ftp.ntua.gr
    * epel: mirrors.neterra.net
    * extras: ftp.ntua.gr
    * nux-dextop: mirror.li.nux.ro
    * updates: ftp.ntua.gr
    No package pgagent_95 available.
    Error: Nothing to do

    ReplyDelete