Как установить pgagent на windows

PgAgent is probably not installed

The pgAgent download page says:

pgAgent is a job scheduler for PostgreSQL which may be managed using
pgAdmin. Prior to pgAdmin v1.9, pgAgent shipped as part of pgAdmin.
From pgAdmin v1.9 onwards, pgAgent is shipped as a separate
application.

And then it provides a download link to http://www.postgresql.org/ftp/pgadmin3/release/pgagent/
But the problem is there’s only source code there. There are also SQL files in the tarball but they’re useless without pgagent.exe.

How to install it

Apparently the correct way to install a pre-compiled pgAgent is with the Stack Builder installer that also happens to be the primary installation method on Windows for the PostgreSQL server itself. That’s what is suggested on the official download page:
http://www.postgresql.org/download/windows/

If you have already installed PostgreSQL for Windows with this method, the installer is available under the name Application Stack Builder in the PostgreSQL 9.2 folder in Windows Start Menu.

StackBuilder will normally recognize your installed PostgreSQL version(s), and suggest to install among various programs pgAgent in the Add-ons, tools and utilities category.
Once you check pgAgent and continue with the installation, it will download it and launch its setup. The setup phase includes runnning the necessary SQL statements and installing the PostgreSQL scheduling Agent - pgAgent windows service. If this step is successful, there is nothing else to do, the functionality will be available in pgAdmin for creating jobs.

What’s misleading in the docs

The latest pgAdmin docs at http://www.pgadmin.org/docs/1.16/pgagent-install.html has a Service installation on Windows chapter that says:

The service may be quite simply installed from the command line as
follows (adjusting the path as required):

«C:\Program Files\pgAdmin III\pgAgent» INSTALL pgAgent -u postgres -p
secret hostaddr=127.0.0.1 dbname=postgres user=postgres

However in the majority of cases this is not going to be of help because either:

• pgAgent install is done by pgAgent setup as launched by StackBuilder and this step is not necessary.
• or you have got pgAdmin alone and pgagent.exe will not be installed so this step is not possible.

To install PgAgent, there are basically three steps

1. Make sure you have plpgsql language installed in the postgres database. Which you do with the sql command running  postgres database.

   CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
    HANDLER plpgsql_call_handler
    VALIDATOR plpgsql_validator;
   
   

2. Run the PgAgent.sql using PgAdmin III or psql and run it in the db postgres — found in /path/to/PgAdmin III/1.8/scripts (on windows this is usually in «C:/Program Files/PgAdmin III/1.8/scripts»). This creates a schema catalog in the postgres database called pgAgent with the helper pgagent tables and functions.
3. Install the PgAgent server service/Daemon process: On windows — you run a command something like below — the -u user is not the PostgreSQL user but the computer user that the PgAgent will be running under.

   C:\Program Files\PostgreSQL\8.2\bin\pgAgent" INSTALL pgAgent -u postgres -p somepassword hostaddr=127.0.0.1 dbname=postgres user=postgres 

   After you install on Windows — you should go into Control Panel -> Administrative Tools -> Services — «PostgreSQL Scheduling Agent — pgAgent» -> and start the service. If the service doesn’t start — most likely you typed the postgres computer account password in wrong. Simply switch to the Log On tab and retype the password or change to use a different account.Keep in mind — if you wish PgAgent to run scripts that require File Network access (e.g. copying files to network servers, you need to have the service run under a network account that has network access to those servers.
   
   On Unix/Linux systems — it varies how its installed. It is usually run under the root account and the line is added to startupscripts usually /etc/init.d or I think on MacOSX its /etc/xinetd.d 
   /path/to/pgagent hostaddr=127.0.0.1 dbname=postgres user=postgresNote: as the docs say — its probably best not to specify the password. Instead — you can set the postgres account to be trusted from server you have PgAgent installed on or use the ~pgpass approach.

Once you have PgAgent installed, and open/refresh PgAdmin III, you should see another section called Jobs that looks like below:

If per chance, you do not see the new Jobs icon, make sure that you have PgAgent jobs checked by going to File->Options->Display

2.Creating Backup Jobs

Creating backup jobs is done with a shell script of some sort. In Windows this can be done with a .bat file and specifying the file in the PgAgent job or by writing the command directly in the PgAgent job. 

In Linux/Unix — this is done with a .sh file and specifying that in the PgAgent job or writing the command directly in the PgAgent job.

Generally we go with a .bat or .sh file, because using a shell script allows you more granular control — such as backing up multiple databases or having a separately date named file for each daily backup.

Below is a sample batch script for Windows that backs up selected databases and then does a full Pg_dumpall as well

@echo off
REM - backup directory can be a file server share that the PgAgent windows service account has access to
set BACKUPDIR="/path/to/backup/"
set PGHOST="localhost"
set PGUSER="postgres"
set PGBIN="C:/Program Files/PostgreSQL/8.2/bin/"
for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
 set dow=%%i
 set month=%%j
 set day=%%k
 set year=%%l
)

for /f "tokens=1-3 delims=: " %%i in ("%time%") do (
 set hh=%%i
 set nn=%%j
)

REM - It would be nice to use gzip in the pg_dumpall call (or if pg_dumpall supported compression as does the pg_dump)
REM here as we do on the linux/unix script
REM - but gzip is not prepackaged with windows so requires a separate install/download. 
REM Our favorite all purpose compression/uncompression util for Windows is 7Zip which does have a command-line
%PGBIN%pg_dumpall -h %PGHOST% -U %PGUSER% -f %BACKUPDIR%fullpgbackup-%year%%month%.sql 
%PGBIN%pg_dump -i -h %PGHOST% -U %PGUSER% -F c -b -v -f "%BACKUPDIR%db1-%year%%month%%day%%hh%.compressed" db1
%PGBIN%pg_dump -i -h %PGHOST% -U %PGUSER% -F c -b -v -f "%BACKUPDIR%db2-%year%%month%%day%%hh%.compressed" db2

Below is an equivalent Linux/Unix backup shell script
#!/bin/bash
#backup directory can be a file server share that the PgAgent daemon account has access to
BACKUPDIR="/path/to/backup"
PGHOST="localhost"
PGUSER="postgres"
PGBIN="/usr/bin"
thedate=`date --date="today" +%Y%m%d%H`
themonth=`date --date="today" +%Y%m`

#create a full backup of the server databases
$PGBIN/pg_dumpall -h $PGHOST -U $PGUSER | gzip > $BACKUP_DIR/fullbackup-$themonth.sql.gz 

#put the names of the databases you want to create an individual backup below
dbs=(db1 db2 db3)
#iterate thru dbs in dbs array and backup each one
for db in ${dbs[@]}
do
 $PGBIN/pg_dump -i -h $PG_HOST -U $PGUSER -F c -b -v -f $BACKUPDIR/$db-$thedate.compressed $db
done

#this section deletes the previous month of same day backup except for the full server backup
rm -f $BACKUPDIR/*`date --date="last month" +%Y%m%d`*.compressed
Save the respective above scripts in a (dailybackup.bat for windows pgagent) or (dailybackup.sh for Linux/Unix pgagent) file.
For bash unix scripts make sure it has unix line breaks (not windows) — you may use dos2unix available on most linux/unix boxes to convert windows line breaks to unix linebreaks. When saving as .sh make sure to give the .sh file execute rights using chmod on linux/unix. Also change the db1, db2 and add additional lines for other databases you wish to backup to the respective names of your databases and add additional as needed.
cd /path/toscriptfolder
dos2unix dailybackup.sh
chmod 771 dailybackup.sh
/path/toscriptfolder/dailybackup.sh  #this is to test execution of it
771 permissions gives execute rights to public and all rights (read,write,execute) to owner and group. Alternatively you could do 640 instead which would remove all rights from public, but then you will need to do a Change owner chown to change ownership to account you are running PgAgent under. Note the above script and commands we tested on a CentOS box so commands and script may vary if you are running on MacOSX or another Linux variant.
A couple of notes about the above which are more preferences than anything.

We like to create a dump all backup which would contain all the databases and just overwrite it daily but keep one for each month. This is more for major disaster recovery than anything else.
We prefer the Postgres Native Compressed format for our date stamped backups. The reason for that is with the pg_dump compressed format, it takes up less space, deals with binary objects well, and has the benefit that you can restore individual database objects for it. This is very useful in cases where someone screws up and they come back to you days or months later.
You will note that the date stamp format we have included includes the Hour and would create a file something of the form — dbname-2008010102.compressed — the reason for that is that it sorts nicely by name and date of backup and if disk space was an issue, you could easily include a line that deletes say backups older than a month. Going down to the hour level allows us to quickly create emergency backups by clicking the Run Now on PgAdmin Jobs interface that wouldn’t overwrite the current days backup.
In practice we also like to have at least one of the backups ftped to a remote location and include that as part of the script and/or backed up to a remote server that has good connectivity with the pgagent server. This helps in cases of complete server failure. This step is not included here since its too OS and install specific to get into.

Next to create the PgAgent backup job follow the following steps.

Open up PgAdmin — navigate to jobs section, right mouse click and click New Job — 
Fill in the properties tab as shown in this snapshot — 
Switch to the Steps tab and select Batch and fill in details as shown — 
Switch to the Definition tab and type in the path to the batch or sh file. Keep in mind the path is in context of the PgAgent service. So if you have PgAgent installed on a server that is different from the PostgreSQL server, then make sure the paths in your script and path to the file is set as if you were the PgAgent account on PgAgent server. As show here  and then click the OK button.
Next switch to the Schedules tab and click to add a Schedule. 
Next Switch to Times tab. The reason we are skipping the Days tab is that anything you do not fill in is assumed to be All since we want all days, we leave that tab blank. This diagram shows setting the backup time to be 02:15 AM every day — 

Once the job is saved, the hierarchy in PgAgmin looks like the below snapshots


Clicking on the Daily Schedule Icon Clicking on the respective objects in the Job Hierarchy such as a Step or schedule gives you detailed information about each of those. The statistics tab gives you details such as how long a step took, whether or not it succeeded or failed and when it was run.

Keep in mind that while PgAgent is closely related to PostgreSQL and uses PostgreSQL for scheduling and logging, there isn’t any reason you can not use it as an all-purpose scheduling agent. In fact we use it to backup MySQL as well as PostgreSQL databases, do automated web crawls, download remote backups etc. Using the SQL Job Type option, you can use it to run postgresql functions that rebuild materialized views, do other standard postgresql specific sql maintenance tasks, etc. On top of that PgAdmin provides a nice interface to it that you can use on any computer (not just the one running PgAgent).

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

Как установить pgAgent в Windows (PostgreSQL Job Scheduler)

C:\Program Files\PostgreSQL\8.2\bin\pgAgent INSTALL pgAgent -u postgres
-p secret hostaddr=127.0.0.1 dbname=newdb user=postgres password=secret

(решил написать очень подробно)

Итак, pgAgent -тузла к PostgreSQL для запуска скриптов без вашего участия в определенное время и/или через определенные периоды времени.
pgAgent поставляется вместе с pgAdmin III или его можно скачать отдельно.
Ну а официальную документацию можно найти там же рядом или в хелпе pgAdmin.

Все бы хорошо, но с первого раза мне запустить агента не удалось, как и с последующих пары десятков (я даже начал думать, что это секретный агент). Дело было в понедельник, да и настроение не очень… сутра во вторник я-таки вытащил свои руки из одного места и приставил их туда, откуда они и должны расти, после чего агент удачно запустился (на самом деле я просто подробней почитал инструкцию 

Начнем.
1. Нужно запустить скрипт C:\Program Files\PostgreSQL\8.3\pgAdmin III\Scripts\pgagent.sql в базе данных «postgres» (именно в этой базе).
Этот скрипт создает системную схему pgAgent и в ней несколько объектов. Также в pgAdmin появляются новые типы объектов — Задачи (Если их нет, нужно в меню [Файл-Опции-вкладка Показать] отметить pgAgent jobs).

2. Подготовка сервиса.
— Зайдите в папку C:\Program Files\PostgreSQL\8.3\bin
— для удобства, создайте 2 ярлыка на файл pgagent.exe первый назовите- pgagent_install, второй — pgagent_remove
с pgagent_remove все понятно, он удаляет установленный сервис и содержит строку как по инструкции:
«C:\Program Files\PostgreSQL\8.3\bin\pgAgent.exe» REMOVE pgAgent

А в pgagent_install я написал так:
«C:\Program Files\PostgreSQL\8.3\bin\pgagent.exe» INSTALL pgAgent -u pg -p 123 -l 2 hostaddr=127.0.0.1 dbname=postgres user=admin password=123

Параметры:
-u pg — имя пользователя винды (которого я, например, создал при установке постгреса) есть подозрение, что русские имена он не воспринимает.
-p 123 — пароль того самого виндового пользователя
-l 2 — задает уровень логирования (ERROR=0, WARNING=1, DEBUG=2, default 0) — ставим максимальный.

Дальше строка подключения
hostaddr=127.0.0.1 — тут все понятно — IP’шник машины, на которой крутится постгрес
dbname=postgres — это имя базы
user=admin — имя юзера постгреса, который имеет доступ к созданной в п.1 схеме (pgagent) и всем объектам внутри нее
password=123 это пароль того юзера

3. Запускаем ярлык pgagent_install
Дальше открываем виндовые Сервисы (я уж не буду говорить, как), там должен появиться сервис
PostgreSQL Scheduling Agent — pgAgent
Но он еще не запущен. Это и нужно здесь сделать — запустить.
Проверьте таблицу pgagent.pga_jobagent — если в ней нет записей, значит сервис не смог подключиться к базе или таблице.
Именно для этого мы и ставили параметр -l 2
Нужно смотреть логи: Панель управления-Администрирование-Просмотр событий — Приложение . Там, где Источник указан pgAdmin смотрим какие были косяки и симметрично реагируем — удаляем сервис (запускаем ярлык pgagent_remove), правим ярлык pgagent_install, потом его запускаем и не забываем стартовать сервис.

4. Когда все успешно завершилось, чтобы не загаживать системный журнал, нужно убрать параметр логирования -l 2.

Все остальное написано в хелпе.