Windows PostgreSQL pgAgent Jobs Fail

Installation for proof-of-concept of PostgreSQL

Problem

======

I cannot get any jobs to run from the pgAdminIII GUI. My goal is to create automated backup as well as db purging jobs to run unattended on their own schedules.

All my jobs come back with the following status. Note that an empty backup file of 0 bytes IS created in the correct folder but that the backup immediately aborts.

Pg_dump: [archiver (db)] connection to database “dbname” failed: fe_sendauth: no password supplied

Configuration…

=============

PostgreSQL Noob :-0

Virtualized Windows 2008 Server R2 DC running on VmWare

PostgreSQL 9.3

I have a directory set up to contain scripts (BAT files to run various backups) and the postgresdbaccount is full access to it.

I have a directory to create backup files in and the postgresdbaccount has full access to it.

The pgpass.conf file is located in               : c:\users\postgresdbaccount\appdata\roaming\postgresql

The contents of the file..one line              : localhost:5432:postgres:postgresdbaccount:thepassword

The pgAgent service (PostgreSQL Scheduling Agent – pgAgent) and it is running. I KNOW it reads the pgpass.conf file above as the service will not start if certain changes are made (see below)

-          Logon: This account        :               .\postgresdbaccount

-          Password                            :               thepassword

The pg_hba.conf file as a number of entries in it as there are multiple developers testing apps on it. These are set up as follows…

Developers…

Type: host, Database: all, User: all, IP-Address: Network address/32, Method: trust, Option: blank

Other…

Type: host, Database:all, User:all, IPAddress: 127.0.0.1/32, Method: MD5, Option: blank

Type: host: Database: all, User:all, IPAddress: ::1/128, Method:MD5, Option Blank

The backup jobs I’ve tried to run were created in the Jobs are of pgAdminIII and were set up as follows…

PROPERTIES TAB

Name: any name

Enabled: Check

Job Class: Routine Maintenance

Host Agent: tried 3 different settings…see below

STEPS TAB (only a single step)

Properties..Name: anyname

Properties..Enabled: checked

Properties..Kind: Batch

Properties..On error: Fail

Definition:           path to the BAT file containing the pg_dump command…              DRIVE:\ScriptDirectory\Backup.bat

SCHEDULES TAB

Created a dummy schedule

The Contents of one of the Backup.bat files is as follows…

-----------------------------------------------------------------------

@echo off

set BACKUPDIR="drive:/backupdirectory/"

set PGHOST="localhost"

set PGUSER="postgresdbaccount"

set PGBIN="C:/Program Files/PostgreSQL/9.3/bin/"

set cdmDatabase="DatabaseBeingBackedUp"

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

)

%PGBIN%pg_dump -h %PGHOST% -U %PGUSER% -w -F c -b -v -f "%BACKUPDIR%%cdmDatabase%-%year%%month%%day%%hh%.compressed" %cdmDatabase%

NOTE: I’ve removed the “-w” parameter from the above line and can run the script directly from the command line, enter a password and generate a backup file in the directory needed.  But this only tests the script of course and not all the authentication going on when run in automated mode…I understand that.

Whats been tried…

===============

I tried changing the “localhost” portion of the pgpass.conf file with: 127.0.0.1 (service would no longer start)

I’ve tried changing the “localhost” portion of the pgpass.conf file with: <server IP address> (service would no longer start)

I tried putting a copy of the pgpass.conf file here:            C:\Users\postgresdbaccount\AppData\postgresql (no effect)

I tried making a copy of the pgpass.conf file here:            C:\Users\adminaccount\AppData\postgresql (no effect)

I remoted in as the postgresdbaccount, running pgAdminIII and right-clicking and doing a RunNow on a job (no effect)

I remoted in using an adminaccount, launching pgAdminIII and trying RunNow on the jobs (no effect)

On the Properties tab of the job in the Host Agent field I have tried:

1: Leaving it blank (empty backup file was created)

2: putting the full hostname in there without quotes around the name (empty created)

NOTE: I got this name by running this query against the postgres database:         select jagstation from pgagent.pga_jobagent;

3: putting the full hostname WITH quotes around the name. (empty backup file was NOT created)

I tried copying the BAT file contents into the Definition area of the Job Step (empty file created)

I tried letting the job hit the scheduled time (empty file created)

I tried forcing a RunNow on the job (empty file created)

I WANT TO TRY changing the pg_hba.conf file as follows…but since I’ll need to restart the database, I wanted to hold off a bit..after hours.

Type: host, Database:all, User:all, IPAddress: 127.0.0.1/32, Method: trust, Option: blank

Type: host: Database: all, User:all, IPAddress: ::1/128, Method:trust, Option Blank

I’m out of ideas…can anyone help me out?