Обсуждение: Running pg_dump under vcron

Hi there,

I'm having a bit of trouble running a particular script with vcron. The script does a pg_dump of a postresql database,
compressesthe sql file and copies it to a remote server. I can run the script manually, as in 

./scriptname.sh

But if I set the script to be run by vcron, as in

crontab -u root -e

0 16 * * * /path/to/scriptname.sh

Nothing happens.

I have inserted code into the script that rings the hardware bell. When I run the script with vcron, the hardware bell
beeps.There's still no pg_dump of my database, however. The pg_dump directive is written as follows: 

#!/bin/sh
/usr/bin/pg_dump -a -f /home/httpd/htdocs/db_name.sql db_name

Someone told me that the problem has to do with vcron not reading any rc. files to initialize its environment. So I
triedsourcing my environment initiation files in the script as follows: 

. /etc/profile
. ${HOME}/.profile

This caused the script to stop functioning altogether.

I should note that pg_dump runs for the particular database I want to backup under my user name. If I try to manually
runthe script as root, I get 

pg_dump: [archiver (db)] connection to database "db_name" failed: FATAL: user "root" does not exist

Since cron is run as root, I modified the pg_dump as follows:

/usr/bin/pg_dump -S user -a -f /home/httpd/htdocs/db_name.sql db_name

This didn't help either.

If anybody has any suggestions, please tell me.

Mark

Hello,

> /usr/bin/pg_dump -a -f /home/httpd/htdocs/db_name.sql db_name
Try to use a pipe

PG_PATH$/pg_dump [PARAMETERS] > BACKUP_PATH$/dumpfile.sql

HTH,
Daniel
--
postgreSQL on Netware - the red elephant
http://postgresql.dseichter.org
Last update: 26th May 2003

On Sun, 22 Jun 2003 19:57:18 +0200
"Daniel Seichter" <daniel@dseichter.de> wrote:

> Hello,
>
> > /usr/bin/pg_dump -a -f /home/httpd/htdocs/db_name.sql db_name
> Try to use a pipe
>
> PG_PATH$/pg_dump [PARAMETERS] > BACKUP_PATH$/dumpfile.sql

As in

/usr/bin/pg_dump -a herboris > /home/httpd/htdocs/herboris_data.sql

This works manually but not from cron.

>
> HTH,
> Daniel
> --
> postgreSQL on Netware - the red elephant
> http://postgresql.dseichter.org
> Last update: 26th May 2003
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

On Sun, 22 Jun 2003 19:57:18 +0200
"Daniel Seichter" <daniel@dseichter.de> wrote:

> Hello,
>
> > /usr/bin/pg_dump -a -f /home/httpd/htdocs/db_name.sql db_name
> Try to use a pipe
>
> PG_PATH$/pg_dump [PARAMETERS] > BACKUP_PATH$/dumpfile.sql

As in

/usr/bin/pg_dump -a herboris > /home/httpd/htdocs/herboris_data.sql

This works manually but not from cron.

>
> HTH,
> Daniel
> --
> postgreSQL on Netware - the red elephant
> http://postgresql.dseichter.org
> Last update: 26th May 2003
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

On Sun, 22 Jun 2003, MT wrote:

> On Sun, 22 Jun 2003 19:57:18 +0200
> "Daniel Seichter" <daniel@dseichter.de> wrote:
>
> > Hello,
> >
> > > /usr/bin/pg_dump -a -f /home/httpd/htdocs/db_name.sql db_name
> > Try to use a pipe
> >
> > PG_PATH$/pg_dump [PARAMETERS] > BACKUP_PATH$/dumpfile.sql
>
> As in
>
> /usr/bin/pg_dump -a herboris > /home/httpd/htdocs/herboris_data.sql
>
> This works manually but not from cron.
>

Having not read the start of this I don't know if you've already posted it but
what is the error message(s) you get?


--
Nigel Andrews

On Mon, Jun 23, 2003 at 07:09:43PM -0600, Keary Suska wrote:
> Check your root email. STDERR should be emailed to the cron user‹this will
> tell you more specifically what is wrong. Also set PGUSER and PGPASSWORD
> either in your crontab or script. Make sure pg_dump is in your path.

The 7.3 documentation says:

"PGPASSWORD sets the password used if the backend demands
password authentication. This functionality is deprecated
for security reasons; consider migrating to use the
$HOME/.pgpass file."

But the release 7.3 notes say:

"Libpq
Add $HOME/.pgpass to store host/user password combinations (Alvaro Herrera)"

So I'm guessing $HOME/.pgpass does nothing for releases prior to
7.3. I think that means that before 7.2 cron activated stuff is
stuck using the less secure PGPASSWORD method.

I do something like this:

------------------------------------------------------------------------------------------------------------
#!/usr/bin/perl -w
                   

use strict;
my $dfltSqlScriptDir=qq(/root/.secretscripts);          # make sure /root/.secretscripts permission is 700
my $dataOutputDirectory=qq(/home/less_secret_output);   # make sure /home/less_secret_output permission is 755
my $script2run = qq($dfltSqlScriptDir/sql_script.sh);
my $shell_script = <<END_OF_SCRIPT;
#!/bin/bash
                   
set -x
export PGPASSWORD=so_secret_that_even_the_owner_cant_know_it
export PGUSER=manifest
/usr/bin/psql -f ${dfltSqlScriptDir}/sql_script.sql >> ${dataOutputDirectory}/output.log 2>&1
END_OF_SCRIPT

open  SHELL_FILE, qq(>$script2run) || die qq(cant open output data file $script2run\n);
print SHELL_FILE $shell_script;
close SHELL_FILE || die qq(cant close output data file $script2run\n);
chmod 0700, $script2run;

system($script2run);
------------------------------------------------------------------------------------------------------------

This perl file is what cron executes.


Can anyone suggest a better method? This works, but I'm using a pile driver to set a ten penny nail.


Cheers,


--JPK