0

I'm starting to use the PostgreSQL now, and I wonder if I can schedule tasks / work (in SQL) to be done by the db without having to use pgAgent.

I'm working on a system where administrators need to schedule promotions. For example, from day X to day Y there is a Z promotion. This must be done in the system interface (UI), on a page that will send the command to the database. All I need is to perform a SQL command when a proper time comes.

I have searched on the internet, and all I find is about pgAgent or how to configure it. I do not want it. From what I saw, the pgAgent only works by pgAdmin interface, and system administrators can not lay a finger on pgAdmin... Or not (I'm new to PostgreSQL)...? :/

In pgAdmin, when creating a new job I also clicked on the help button but there does not talk much except set everything through pgAdmin interface.

Is there any way to achieve this? Are there alternatives?

Thank you for your attention.

Kenny Linsky
  • 1,726
  • 3
  • 17
  • 41
Loa
  • 2,117
  • 3
  • 21
  • 45
  • Most people simply schedule a SQL script using the scheduler built into the operating system, e.g. `cron` –  May 09 '16 at 21:15
  • @a_horse_with_no_name Hmm... Can you elaborate, please? – Loa May 10 '16 at 00:42

1 Answers1

0

PgAgent doesn't work just through PgAdmin, but rather, PgAdmin is the only (current) GUI that interfaces with the PgAgent tables. PgAgent is a service that interacts exclusively with its own set of tables, which can be modified and reported on by any software, not just PgAdmin. PgAdmin can be very useful since it implements multi-stepped jobs, and the results are stored in the database and can be queried or custom reports can be made.

There are many alternatives, from developing your own PgAgent-like tool, to using cron in Linux/Unix/Cygwin or Scheduled Tasks in Windows.

For example, in Linux, a daily table export can be implemented in cron by adding a batch file in /etc/cron.daily/ like

sudo -i -u postgres psql -c "copy foo.bar to '/var/lib/dbexports/foo-bar.csv' with csv header" foo_db

or in a file in /etc/cron.d/ to export that file specifically on Mondays at 5:30 like

30 5 * * 1 postgres psql -c "copy foo.bar to '/var/lib/dbexports/foo-bar.csv' with csv header" foo_db

or similar on any user's crontab.

Ezequiel Tolnay
  • 4,302
  • 1
  • 19
  • 28
  • Well, this I know. You can do the same thing with pgAdmin to schedule a backup, for example, is not it? But this part of writing scripts on the server machine would not be a responsibility of those who work and prepares the server machine to use? I do not understand this part to be honest. I do model, prepare and manage databases (even I not being DBA), and I create and manage systems (I am an analyst and developer). But I believe that is not quite the point. You see, I need to allow non-technical people to use this system (via web site) to schedule events. So... (keep reading below) – Loa May 10 '16 at 19:03
  • I can not whenever there is an event or a change to force people from different areas to work together just to add a record in a table. This may well be done in a system in which the application schedule is sent to the database, no? I do not know. Or it does not make much sense to me, or I do not have enough information about scripts in operating systems (which is, in fact). What do you think? The staff of the marketing sector could access the system and schedule simply promoting events, or not? Could this be wrong? help... :( – Loa May 10 '16 at 19:07
  • @Loa what you talk about is application-based maintenance jobs. PgAgent is generally used by system administrators rather than end-users. You'll have to build your own, and execute it every minute to check your own schedule, perhaps triggered via a single PgAgent job, or a cron job. – Ezequiel Tolnay May 10 '16 at 23:31