Thread: running pg_dump via pgAgent

<table bgcolor="white" border="0" cellpadding="3" cellspacing="0" width="100%"><tr valign="top"><td width="100%"><font
color="black"size="2">Hi,<br />I am trying to use pgAgent to automatically backup my database once a day. Wouldn't this
bethe most common use of pgAgent?<br /><br />I have successfully installed pgAgent, and it runs jobs ok. However, I'm
runninginto problems when I set up a job to run a "batch" step with the following definition: <br /><br />pg_dump -f
c:\temp\dump.dat-U postgres test<br /><br />It starts the job, and the statistics for the step shows it is running (I
canalso see in my task manager that pg_dump is running) ... but it never stops! If i kill the process, the statistics
forthe step shows "success", but no backup file has been created. When I run the same command at a dos prompt it works
fine.I'm at the point now where I figure I'll just set up a windows task that calls the script once a day, but i'd
ratheruse pgAgent to do my backups. Can anyone help?<br /><br />system details:<br />windows xp<br />pervasive postgres
8.0.3<br/>pgadmin 1.4.1<br /><br />installed pgAgent via:<br />C:\Program Files\pgAdmin III\1.4>pgAgent INSTALL
pgAgent-1 2 -u postgres -p MyPass123<br /> hostaddr=127.0.0.1 dbname=postgres user=postgres password=postgresABC<br
/><br/>Thanks!<br />Che<br /><br /><br /><br /><br /></font></td></tr></table><p><hr /><font face="verdana"
size="2"><spanstyle="font-weight:bold">No banners. No pop-ups. No kidding.</span><br />Make My Way your home on the Web
-<a href="http://www.myway.com" target="_blank">http://www.myway.com</a></font> 

From: pgadmin-support-owner@postgresql.org
[mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Che
Sent: 28 January 2006 17:52
To: pgadmin-support@postgresql.org#
Subject: [pgadmin-support] running pg_dump via pgAgent
> Hi,

Hi,

> I am trying to use pgAgent to automatically backup my database
> once a day. Wouldn't this be the most common use of pgAgent?

It's not one I considered when I finished writing it, but it does seem
perfectly reasonable.
> I have successfully installed pgAgent, and it runs jobs ok.
> However, I'm running into problems when I set up a job to run
> a "batch" step with the following definition:
>
>    pg_dump -f c:\temp\dump.dat -U postgres test
>
> It starts the job, and the statistics for the step shows it is running
(I can also see in my
> task manager that pg_dump is running) ... but it never stops!

Hm, I've just tried it here and it worked perfectly. The only real
difference that I can see was that I used the full path to pg_dump:

"C:\Program Files\PostgreSQL\8.1\bin\pg_dump" -f c:\temp\dump.dat -U
postgres test

Please check that libpq.dll and whatever dependencies the Pervasive
build of it may have are in the same directory as pg_dump, and please
try specifying the full path to pg_dump. Also, please make sure that the
Windows postgres user has write permissions on the c:\temp directory.

Another thought that just crossed my mind - perhaps pg_dump is prompting
for a password (which pgAgent will never enter of course). Please check
that your postgres account has a pgpass.conf file configured to allow
access to the test database, or that pg_hba.conf has an appropriate
trust entry.

Regards, Dave.