Home 2015 May How to create job in PostgreSQL

How to create job in PostgreSQL

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

You can configure recurring tasks by creating database jobs in PostgreSQL.
PostgreSQL provides one external tool to configure SQL job; the name is PGAgent.

PGAgent is an external tool by the Postgres community which is used to create and manage database jobs.

Below is a demonstration for configuring PGAgent in a windows environment using PGAdmin.

How to configure PGAgent in PostgreSQL (windows) ?

Step 1 : download PGAgent using Stack Builder. Go to your Postgres option menu where you can find one more link for Stack Builder. Now, as per below image select pgAgent and press Next.

 

Stack Builder

Step 2: Now it will automatically download pgAgent so wait for few minutes and please make sure that your internet connection is working.

DonwloadingPGAgent

Step 3: After completion of download you can screen of PGAgent for further installation.

InstallPgAgent

Step 4 : Configure authentication detail to access Postgres database server. Please note that PGAgent is running under super user access. In below image, Postgres is a super user of PostgreSQL Database Server.

PgAgentConfiguration

Step 5 : Configure local postgres client password. You can visit this link for more details on client password location.

PostgersPassowrd

Step 6 : Now complete PGAgent installation and verify using PGAdmin. You can find one new pgAgent catalogs in Postgres database. As marked below you can also find Job icon in the main category.

Note : Only super user or administrator can create and manage this job. This icon is only visible to administrator login (postgres super user login.).

VerifyPGAgent

Step 7 : To create you first SQL Job. Right click on the job and select to create new SQL Job. Give the name of your job and prepare for next step. Make sure that job is Enabled.

CreateFirstSQLJob

Step 8: Create definition of job. Give the name of step and define definition or action script into Definition tab. You can also select the remote option when the job is required to execute on the remote machine.

StepToCreate

InsertDefinition

Step 9: Now create a schedule to execute SQL Job. In this window, you can select Days and times. Select all means at every minute, hour, day.

TimeToExecute

Now your PGAgent tool is configured and also we have created one Job to insert test data into table at every minute.

Please note: This PGAgent / Database Job is only created and manage by administrator and another database user can not even see this jobs.

Anvesh Patel
How to reset postgres user password in windows ?MySQL Error Handling
Comments: 23
  1. sdorttuii plmnr
    December 7, 2015 at 12:13 pm

    I really enjoy studying on this website , it holds superb content. “You should pray for a sound mind in a sound body.” by Juvenal.

  2. sdorttuiiplmnr
    December 17, 2015 at 5:55 am

    Glad to be one of the visitors on this awesome web site : D.

  3. Octavi
    January 5, 2016 at 8:40 pm

    It’s really a nice and useful piece of info. I’m glad that you shared this helpful info with us. Please keep us informed like this. Thanks for sharing.

  4. Sara Andrulis
    January 27, 2016 at 11:43 pm

    Great post. Thanks for the read.

  5. maryann
    February 12, 2016 at 5:32 pm

    Hello very nice blog!! Man .. Excellent .. Wonderful .. I will bookmark your site and take the feeds also…I am happy to seek out so many useful information here within the put up, we’d like work out extra strategies on this regard, thank you for sharing.

  6. Glinda
    February 21, 2016 at 2:39 pm

    Hi there, just desired to say, I liked this blog post. It was
    practical. Carry on posting!

  7. Cubias
    March 23, 2016 at 7:04 am

    Very great information can be found on website . “Even if happiness forgets you a little bit, never completely forget about it.” by Donald Robert Perry Marquis.

  8. Rod
    April 3, 2016 at 3:44 am

    really enjoyed studying you are a very smart person thanks for finally talking about this valuable information

  9. Thomasor
    April 18, 2016 at 9:39 am

    Thanks for giving these types of wonderful content material.

  10. Raleigh
    April 21, 2016 at 7:49 am

    Heya i’m for the first time here. I found this board and I find It truly useful & it helped me out much. I hope to give something back and help others like you aided me.

  11. Olek
    September 2, 2016 at 11:01 am

    You can simply create PostgreSQL backup job with the help of GUI tool PostgreSQL-Backup http://postgresql-backup.com/

  12. S RAVIKUMAR
    October 18, 2017 at 5:45 am

    I created scheduler job in Postgres for insert data into tabele
    insert into test(id,name) values(1,’a’);
    But the job is not running.

    • Anvesh Patel
      October 19, 2017 at 7:34 pm

      did you check the status of pgagent service? The status should be running.

  13. Ajinkya Jagtap
    November 29, 2017 at 11:58 am

    I have logged in to Postgres with Superuser credentials. But I am not able to see Jobs menu on left side.

    • Anvesh Patel
      November 30, 2017 at 12:07 pm

      Did you install or configure pg_agent?

  14. Priyanka
    January 30, 2018 at 9:14 am

    I have logged in to Postgres with Superuser credentials. But I am not able to see Jobs menu on left side.

    • Anvesh Patel
      January 30, 2018 at 6:34 pm

      did you install pgagent service? If you installed already, check service is running or not

  15. Byed
    June 27, 2018 at 2:00 pm

    Great sharing anvesh

  16. Murtaza
    July 16, 2018 at 10:06 am

    Hi avnesh,

    Do we have any scripts that can directly create a job as we do in oracle and Mssql through .sql file.
    And if we have then please suggest the steps to do the same.

    • Anvesh Patel
      July 17, 2018 at 4:32 am

      Better to use Linux crontab where you can schedule all your PostgreSQL processing.

  17. Ashwin
    December 10, 2018 at 9:43 am

    Hi Anvesh,

    I have scheduled the job as you said. But, I am unable insert new data to table. service is running properly. but I am unable to run the job Can you please help to find where is the problem.

    One more, can we schedule backup job using this?

    • Anvesh Patel
      December 24, 2018 at 7:35 pm

      Are you using windows?

  18. LikelyYou.com
    September 5, 2019 at 12:19 am

    To create or manage a job, use the pgAdmin tree control to browse to the server on which the pgAgent database objects were created. The tree control will display a

Anvesh Patel
Anvesh Patel

Database Engineer

May 20, 2015 PostgreSQL, , , , , , , , , , ,