Thread: database backup trouble
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.
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
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.
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
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
-----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-----
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.
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
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.