automating backups with windows scheduled tasks and pg_dumpall

Tom Hart <tomhart@coopfed.org> writes:

Do scheduled tasks
have a problem writing to files with > ? I tried an "echo this is a test

test.txt" and that didn't run either.

I know zip about Windows, but try using pg_dump's -f switch instead of ">"
and see if it gets happier.

regards, tom lane

Tom Lane wrote:

Tom Hart <tomhart@coopfed.org> writes:

Do scheduled tasks
have a problem writing to files with > ? I tried an "echo this is a test

test.txt" and that didn't run either.

I know zip about Windows, but try using pg_dump's -f switch instead of ">"
and see if it gets happier.

Good tip, and something I hadn't tried yet, but still not working. I'm
sure the problem lies within strange behavior of scheduled tasks, but
they were not super helpful on the microsoft newsgroups, and I was
hoping to find somebody that had a working windows-based automated
backup "in the wild" if you will. Thanks anyway though, I'll keep trying.

pgsql-general-owner@postgresql.org wrote on 11/15/2007 02:07:07 PM:

Hello everybody. I'm having a bit of trouble automating pg_dumpall to do

nightly backups. I have a batch file whose contents are below

SET PGPASSFILE=C:\foo\bar\PG_BACKUP\PGPASSFILE\pgpass.conf
"C:\Program Files\PostgreSQL\8.2\bin\pg_dumpall.exe" -U foo_postgres >
C:\foo\bar\PG_BACKUP\db.out

Now this file, when I double click on the .bat, works wonderfully. It
sets the variable correctly, reads the pw file with no problem, and
performs the dump. However when this same bat is built into a scheduled
task (same username, administrator in both cases), even though the task
claims to have completed correctly, I have no evidence of it running,
and it certainly isn't doing the backup.

Is the administrator password set correctly in the task scheduler entry?
If it is wrong, the scheduled job just silently does not run.

I'm sure many of you have experience in scheduling backups in this
manner (though admittedly a large number of you are probably using cron
in *nix). Is there anything that jumps out at you? Do scheduled tasks
have a problem writing to files with > ? I tried an "echo this is a test

test.txt" and that didn't run either. What's confusing is that

windows is claiming everything ran fine, in under 5 seconds no less (my
db isn't that big, but it takes longer than that to dump the entire
thing). Any ideas?

TIA

Thomas R. Hart II
tomhart@coopfed.org

I don't run this type of job, per se, from Windows, but I have run a bunch
of others.

Does it say anything useful if you run it from the task scheduler
(right-click on the task, select Run)?

Depending on how the system is set up, I recently saw one that refused to
run correctly until the "Run As" box had the system (host) name before
"Administrator", as "MYHOSTNAME\Administrator".

One other oddity I ran into recently on one system only, is that a perl
program (.pl extension) worked fine from the command-line, double-clicked
on, etc. but in the scheduled job, I had to put "perl xxx.pl" for it to
work. Maybe if all else fails, try "cmd.exe xxx.bat" or "command.com
xxx.bat" or similar in the scheduled task?

It is possible that if you use the schtasks command-line command to
create/run, etc. the scheduled job, that you might get more or better
information (check Windows help for details, or do "schtasks /?" from the
Windows command-line)

Susan Cassidy

----------------------------------------------------
Tiered Data Protection Made Simple
http://www.overlandstorage.com/
----------------------------------------------------

SCassidy@overlandstorage.com wrote:

One other oddity I ran into recently on one system only, is that a
perl program (.pl extension) worked fine from the command-line,
double-clicked on, etc. but in the scheduled job, I had to put "perl
xxx.pl" for it to work. Maybe if all else fails, try "cmd.exe
xxx.bat" or "command.com xxx.bat" or similar in the scheduled task?

Thanks for your reply.

I just got this running right before I read this email. A shame, because
this tip precisely would have helped me. Turns out that on the command
line you can just type foobar.bat, but in scheduled tasks, you must
place a cmd.exe /c before it, instructing windows to spawn a shell first
(why windows isn't intelligent enough to figure this out on it's own, I
have no idea). Anyway if anybody else is curious I didn't change
anything in the batch file I posted previously, but I changed the
scheduled task command from "c:\foo\bar\pg_backup.bag" to "cmd.exe /c
c:\foo\bar\pg_backup.bat".

Hope this can help somebody else avoid the hours of confusion.

On a side note, you seem to have a pretty strange email client setup,
because your text came through extremely small (almost unreadable) to
me, yet your signature came out just fine. Odd...

Tom Hart wrote:

SCassidy@overlandstorage.com wrote:

One other oddity I ran into recently on one system only, is that a
perl program (.pl extension) worked fine from the command-line,
double-clicked on, etc. but in the scheduled job, I had to put "perl
xxx.pl" for it to work. Maybe if all else fails, try "cmd.exe
xxx.bat" or "command.com xxx.bat" or similar in the scheduled task?

Thanks for your reply.

I just got this running right before I read this email. A shame,
because this tip precisely would have helped me. Turns out that on the
command line you can just type foobar.bat, but in scheduled tasks, you
must place a cmd.exe /c before it, instructing windows to spawn a
shell first (why windows isn't intelligent enough to figure this out
on it's own, I have no idea). Anyway if anybody else is curious I
didn't change anything in the batch file I posted previously, but I
changed the scheduled task command from "c:\foo\bar\pg_backup.bag" to
"cmd.exe /c c:\foo\bar\pg_backup.bat".

Hope this can help somebody else avoid the hours of confusion.

On a side note, you seem to have a pretty strange email client setup,
because your text came through extremely small (almost unreadable) to
me, yet your signature came out just fine. Odd...

Side note 2: This could very easily have been an issue with my email
client as well. I did not intend to sound as if it were an issue with
your setup :-)