Thread: 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?