Thread: database backup trouble

database backup trouble

From
"Tomi N/A"
Date:
I'm trying to set up an automatic backup mechanism and have a number
of questions about issues I've encountered.
First of all, I tried to install pgAgent (the server is running win2k3
and pgsql 8.1.5). Trying to start the pgagent service results in an
"error 193", one google knows very little about. Upon inspection, I
found out that Windows has more to say about the problem: "pgAgent is
not a valid Win32 application". Hmm.
At this point, I decided to try a much more primitive approach: using
a windows scheduled task running a .bat or .cmd script which in turn
calls pg_dump. Don't really know why, but the scheduled task can't be
executed successfully (not even when all the script contains is a
simple print statement). This is obviously not a postgresql issue, but
it is one of the solutions I've tried.
Finally, I tried it on my linux laptop (pgsql 8.1.5): I created a test
linux user, created a valid .pgpass file in his home dir with
-rw------- privileges and attempted to back up the database. Doesn't
work. The username and password are valid, but no dump file is
created. I found out that I explicitly have to tell pg_dump the user
name in the .pgpass file using the -U switch: then it works, but only
when called directly from the shell like so:
pg_dump -h localhost -U myuser mydatabase -f outputfile.backup
The same command executed from a beanshell script (I wanted to be
platform independent) does nothing.

Where I stand at the moment:
1.) obviously, the beanshell solution will do nothing for me until I
find out why I can't execute a windows scheduled task, but it might be
a fairly flexible workarround if corrected
2.) again, obviously, I'd much, much rather do the backup from
pgAgent, if anyone can tell me how I can get arround the error Windows
throws at me.

Any suggestions are much appreciated.
Cheers,
t.n.a.

Re: database backup trouble

From
George Weaver
Date:
From: "Tomi N/A"

> At this point, I decided to try a much more primitive approach: using
> a windows scheduled task running a .bat or .cmd script which in turn
> calls pg_dump.

It might be helpful if you provided the exact pg_dump command you are trying
to use, as well as the text of the .bat file you set up.

Regards,
George



Re: database backup trouble

From
"Tomi N/A"
Date:
2007/2/2, George Weaver <gweaver@shaw.ca>:
>
> From: "Tomi N/A"
>
> > At this point, I decided to try a much more primitive approach: using
> > a windows scheduled task running a .bat or .cmd script which in turn
> > calls pg_dump.
>
> It might be helpful if you provided the exact pg_dump command you are trying
> to use, as well as the text of the .bat file you set up.

I did exactly that a bit into my message (should have probably pointed
it out at the beginning).
Anyway, here it is again:
pg_dump -h localhost -U myuser mydatabase -f outputfile.backup

Oh, and I forgot to ask another question in the original message: why
can a linux user with an invalid or non-existant .pgpass successfully
use pg_dumpall (pg_dumpall -U existing_pg_role > dump.sql) and read
any data he/she cares to read?

Thanks,
t.n.a.

Re: database backup trouble

From
Richard Huxton
Date:
Tomi N/A wrote:
> 2007/2/2, George Weaver <gweaver@shaw.ca>:
>>
>> From: "Tomi N/A"
>>
>> > At this point, I decided to try a much more primitive approach: using
>> > a windows scheduled task running a .bat or .cmd script which in turn
>> > calls pg_dump.
>>
>> It might be helpful if you provided the exact pg_dump command you are
>> trying
>> to use, as well as the text of the .bat file you set up.
>
> I did exactly that a bit into my message (should have probably pointed
> it out at the beginning).
> Anyway, here it is again:
> pg_dump -h localhost -U myuser mydatabase -f outputfile.backup

Should work if the executing user has permission to write to the output
file. You've not specified a directory for the file though, so there's
no way to check.

> Oh, and I forgot to ask another question in the original message: why
> can a linux user with an invalid or non-existant .pgpass successfully
> use pg_dumpall (pg_dumpall -U existing_pg_role > dump.sql) and read
> any data he/she cares to read?

Can't. Not unless you've set pg_hba.conf to allow it. Not unless you've
found a gaping hole that no-one else has noticed.

--
   Richard Huxton
   Archonet Ltd

Re: database backup trouble

From
George Weaver
Date:
From: "Tomi N/A"

>>
>> > At this point, I decided to try a much more primitive approach: using
>> > a windows scheduled task running a .bat or .cmd script which in turn
>> > calls pg_dump.
>>
>> It might be helpful if you provided the exact pg_dump command you are
>> trying
>> to use, as well as the text of the .bat file you set up.
>
> I did exactly that a bit into my message (should have probably pointed
> it out at the beginning).
> Anyway, here it is again:
> pg_dump -h localhost -U myuser mydatabase -f outputfile.backup
>

And the .bat file script?

George



Re: database backup trouble

From
Ron Johnson
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/02/07 06:01, Tomi N/A wrote:
> I'm trying to set up an automatic backup mechanism and have a number
> of questions about issues I've encountered.
> First of all, I tried to install pgAgent (the server is running win2k3
> and pgsql 8.1.5). Trying to start the pgagent service results in an
[snip]
> pg_dump -h localhost -U myuser mydatabase -f outputfile.backup
> The same command executed from a beanshell script (I wanted to be
> platform independent) does nothing.
>
> Where I stand at the moment:
> 1.) obviously, the beanshell solution will do nothing for me until I
> find out why I can't execute a windows scheduled task, but it might be
> a fairly flexible workarround if corrected
> 2.) again, obviously, I'd much, much rather do the backup from
> pgAgent, if anyone can tell me how I can get arround the error Windows
> throws at me.

Did I miss it, or are you saying that the database is hosted on a
win2k3 box?

Are you also saying that you are running the pg_dump command from
the "console" of the machine that is hosting the database?

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFw0ZES9HxQb37XmcRAmY1AJ9fn795qH2sQdmZLSl/hPAwdjXqrwCfUZVb
RSR+Jqp+CknlaBnKayCRWXQ=
=64wU
-----END PGP SIGNATURE-----

Re: database backup trouble

From
"Tomi N/A"
Date:
2007/2/2, George Weaver <gweaver@shaw.ca>:

> And the .bat file script?

The .bat script is of no importance: it's a windows-related problem
(or, to be more precise, the problem of my ignorance when it comes to
windows scripting). The most trivial .bat scripts containing only a
simple echo statement or nothing at all aren't executed by the
scheduler.

The _idea_ was to invoke the pg_dump statement from a script using the
syntax I already described: it shouldn't be the problem. For
completeness, this is the beanshell script:

db_name = "my_db";
out_dir = "/tmp/";

//////////////////// DALJE SE NE MIJENJA NISTA ////////////////////
archive_name = db_name;
archive_name = archive_name + "_" + Calendar.getInstance().get(Calendar.YEAR);
archive_name =  archive_name + "-" + Calendar.getInstance().get(Calendar.MONTH);
archive_name =  archive_name + "-" + Calendar.getInstance().get(Calendar.DATE);
archive_name =  archive_name + "_" + Calendar.getInstance().get(Calendar.HOUR);
archive_name =  archive_name + "-" +
Calendar.getInstance().get(Calendar.MINUTE);
archive_name =  archive_name + "-" +
Calendar.getInstance().get(Calendar.SECOND);
archive_name = archive_name + ".backup";
print(archive_name);
command = "pg_dump -h localhost -U my_user_name my_db -f \"" + out_dir
+ archive_name + "\"";
print(command);
exec(command);

t.n.a.

Re: database backup trouble

From
George Weaver
Date:
From: "Tomi N/A"

>> And the .bat file script?
>
> The .bat script is of no importance:

So be it...

>it's a windows-related problem

Is the Task Scheduler service running? (Start > Settings >  Control Panel >
Administrative Tools > Services - Task Scheduler?).

If the Task Scheduler service is running , what does the Task Scheduler log
indicate about the tasks you tried to run (menu item: Advanced > View Log)?

> (or, to be more precise, the problem of my ignorance when it comes to
> windows scripting). The most trivial .bat scripts containing only a
> simple echo statement or nothing at all aren't executed by the
> scheduler.

Did any of your test .bat files contain a PAUSE command to keep the console
window open in case the bat file did run as scheduled?

>this is the beanshell script:

Unfortunately I am not familar with beanshell and cannot offer assistance
here.

George



Re: database backup trouble

From
"Tomi N/A"
Date:
2007/2/3, George Weaver <gweaver@shaw.ca>:

> >it's a windows-related problem
>
> Is the Task Scheduler service running? (Start > Settings >  Control Panel >
> Administrative Tools > Services - Task Scheduler?).
>
> If the Task Scheduler service is running , what does the Task Scheduler log
> indicate about the tasks you tried to run (menu item: Advanced > View Log)?

Thanks for the tip: I'll check the log.
I just did a test on the office W2k3 server and the test was
successful in that the task ran. This leads me to believe it's a
matter of priviledges: our client has much stricter user rights
policies than we do at the office. :)


> Did any of your test .bat files contain a PAUSE command to keep the console
> window open in case the bat file did run as scheduled?

As I said, it doesn't seem to be related to the script contents (see
above) and I'm kind of surprised all the reactions on the mailing list
seem to be of the windows-scheduled-tasks-don't-work?-really?-type
instead of the pgagent-works-like-a-clock-you-just-have-to-wind-it-up
type of anwer, as I had hoped and expected.

> >this is the beanshell script:
>
> Unfortunately I am not familar with beanshell and cannot offer assistance
> here.

These are just my very mediocre attempts to get around the fact that I
can't get around pg_dump, i.e. there seems to be no way to issue a
request from a client on the network and get the database dump from
the server: I listed the beanshell approach to a platform independent
backup solution only for completeness sake.

Thanks,
t.n.a.