Обсуждение: automating backups with windows scheduled tasks and pg_dumpall

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.

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

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.

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...
> Susan Cassidy
>
> ----------------------------------------------------
> Tiered Data Protection Made Simple
> http://www.overlandstorage.com/
> ----------------------------------------------------

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 :-)
>> Susan Cassidy
>>
>> ----------------------------------------------------
>> Tiered Data Protection Made Simple
>> http://www.overlandstorage.com/
>> ----------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org/

Hi Tom

In windows you would need to include the runas command so your bat file
needs to execute the command via runas as in
runas /user:postgres "bin\pg_dumpall.exe -U foo_postgres"

HTH
Martin
----- Original Message -----
From: "Tom Hart" <tomhart@coopfed.org>
To: "Postgres General List" <pgsql-general@postgresql.org>
Sent: Thursday, November 15, 2007 5:07 PM
Subject: [GENERAL] automating backups with windows scheduled tasks and
pg_dumpall


> 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.
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

Martin Gainty wrote:
> Hi Tom
>
> In windows you would need to include the runas command so your bat file
> needs to execute the command via runas as in
> runas /user:postgres "bin\pg_dumpall.exe -U foo_postgres"
>
> HTH
> Martin
Actually, schtasks.exe and the windows task scheduler both have this
functionality built in. Also, when using runas.exe from a command
prompt, there's no way to give it a password, thus rendering it
incapable of running unattended in a scheduled task setup. I believe
that's why they placed the runas functionality into schtasks.exe
actually. Thanks anyway.

Thomas R. Hart II
tomhart@coopfed.org