Обсуждение: Database update problem from crontab on ubuntu server

Hi,

I have daily cron jobs for database update, which where working fine on
Fedore Core 6. No I move it on Ubuntu Server 7.10 and postgres gives error:

"unexpected EOF on client connection"

while update running from cron. If I run this update manually from root
user - it works fine. Here is update command:

su -l postgres -c "gunzip -c /backup/rms.gz | psql rms"

Can somebody give any suggestion?

Thanks,
David

>>> On Thu, Apr 17, 2008 at  3:21 PM, in message <4807B143.5080006@mysoft.ge>,
David Jorjoliani <djorj@mysoft.ge> wrote:

> I have daily cron jobs for database update, which where working fine on
> Fedore Core 6. No I move it on Ubuntu Server 7.10 and postgres gives error:
>
> "unexpected EOF on client connection"
>
> while update running from cron. If I run this update manually from root
> user - it works fine. Here is update command:
>
> su -l postgres -c "gunzip -c /backup/rms.gz | psql rms"
>
> Can somebody give any suggestion?

Not that this should matter, but do you have the same result with?:

su -l postgres -c "gunzip < /backup/rms.gz | psql rms"

I would recommend specifying the full path for the executables.  You
don't always have the normal $PATH for the user under cron.

-Kevin

On Fri, 18 Apr 2008 00:21:23 +0400
David Jorjoliani <djorj@mysoft.ge> wrote:

> Hi,
>
> I have daily cron jobs for database update, which where working fine on
> Fedore Core 6. No I move it on Ubuntu Server 7.10 and postgres gives error:
>
> "unexpected EOF on client connection"
>
> while update running from cron. If I run this update manually from root
> user - it works fine. Here is update command:
>
> su -l postgres -c "gunzip -c /backup/rms.gz | psql rms"
>
> Can somebody give any suggestion?
>
> Thanks,
> David
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

In crontab you have almost no $PATH set up by default. My expectation is that it can't find gunzip. Try either setting
thePATH in the script, or using the full pathname of gunzip instead... 

Cheers,

Steve

> su -l postgres -c "gunzip -c /backup/rms.gz | psql rms"

You could also try:
/bin/zcat /backup/rms.gz | /usr/local/postgres/bin/psql rms


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

I tried with full paths:

su -l postgres -c "/bin/gunzip -c /backup/rms.gz |
/usr/lib/postgresql/8.2/bin/psql rms"
su -l postgres -c "/bin/gunzip -c /backup/rms.gz | /usr/bin/psql rms"

su -l postgres -c "/bin/zcat /backup/rms.gz |
/usr/lib/postgresql/8.2/bin/psql rms"
su -l postgres -c "/bin/zcat /backup/rms.gz | /usr/bin/psql rms"

same result when it running trough cronjob. Manually everything is fine.
Even I put this commands (without su -l ...) in postgres user crontab,
but same result.

Server is ubuntu 64bit. Does it makes any difference from 32bit in terms
of crontab functionality?

Thanks,
David


Phillip Smith wrote:
>> su -l postgres -c "gunzip -c /backup/rms.gz | psql rms"
>>
>
> You could also try:
> /bin/zcat /backup/rms.gz | /usr/local/postgres/bin/psql rms
>
>
> THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
>
> *******************Confidentiality and Privilege Notice*******************
>
> The material contained in this message is privileged and confidential to
> the addressee.  If you are not the addressee indicated in this message or
> responsible for delivery of the message to such person, you may not copy
> or deliver this message to anyone, and you should destroy it and kindly
> notify the sender by reply email.
>
> Information in this message that does not relate to the official business
> of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
> Weatherbeeta, its employees, contractors or associates shall not be liable
> for direct, indirect or consequential loss arising from transmission of this
> message or any attachments
> e-mail.
>
>

> same result when it running trough cronjob. Manually everything is
> fine. Even I put this commands (without su -l ...) in postgres user
> crontab, but same result.
>
> Server is ubuntu 64bit. Does it makes any difference from 32bit in
> terms of crontab functionality?

System architecture shouldn't affect crontab. Can you give us the
full output from cron? Also, just for debugging's sake, try putting
it in a script and call the script from cron.

If it still fails, then it might help identify exactly where the
error is. If it doesn't fail, then you can start shrinking it all
back down in to one line again and see where the error comes in.

#!/bin/bash

BACKUP_FILE_GZ='/backup/rms.gz'
BACKUP_FILE='/tmp/rms.sql'

echo "-----------------------------------------------"
echo "Unzipping backup..."
/bin/gunzip -c ${BACKUP_FILE_GZ} > ${BACKUP_FILE}

echo "-----------------------------------------------"
echo "Attempting restore..."
/usr/bin/psql rms < ${BACKUP_FILE}

echo "-----------------------------------------------"
echo "Done"


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

The usual trap in cron usage is the fact that crontab commands are executed in a cleanroom environment, ie no environment variable is used/inherited, so PATH, HOME, PGDATA, etc are not set/available when the command is launched.

You can set vars or be very explicit in your script including DB names, DB Users, etc

Cheers
Medi

It fixed! When I make shell script and send to some file like:

/usr/local/backupscripts/bk.sh > /var/log/rmsupdate.log

and inside bk.sh I have the same command as it was in cron:

su -l postgres -c "/bin/gunzip -c /backup/rms.gz |
/usr/lib/postgresql/8.2/bin/psql rms"

Only putting command in shell script didn't work. Only after I made it
to send output to real file, it start working (no error messages in
postgres error log).

So, I think may be from cron it runs very fast (without file io) and
postgres is unable to serve requests :-). Because, main difference
between my old fedora and new ubuntu is hardware, which is much better.
It was: 512M RAM, IDE drives, 1.8GHZ CPU; Now it is: 4G RAM, Quad 2 Duo
3.0GHZ CPU, SATA2 HDD.

Thanks,
David




Medi Montaseri wrote:
> The usual trap in cron usage is the fact that crontab commands are
> executed in a cleanroom environment, ie no environment variable is
> used/inherited, so PATH, HOME, PGDATA, etc are not set/available when
> the command is launched.
>
> You can set vars or be very explicit in your script including DB
> names, DB Users, etc
>
> Cheers
> Medi
>
> On Sun, Apr 20, 2008 at 5:08 PM, Phillip Smith
> <phillip.smith@weatherbeeta.com.au
> <mailto:phillip.smith@weatherbeeta.com.au>> wrote:
>
>     > same result when it running trough cronjob. Manually everything is
>     > fine. Even I put this commands (without su -l ...) in postgres user
>     > crontab, but same result.
>     >
>     > Server is ubuntu 64bit. Does it makes any difference from 32bit in
>     > terms of crontab functionality?
>
>     System architecture shouldn't affect crontab. Can you give us the
>     full output from cron? Also, just for debugging's sake, try putting
>     it in a script and call the script from cron.
>
>     If it still fails, then it might help identify exactly where the
>     error is. If it doesn't fail, then you can start shrinking it all
>     back down in to one line again and see where the error comes in.
>
>     #!/bin/bash
>
>     BACKUP_FILE_GZ='/backup/rms.gz'
>     BACKUP_FILE='/tmp/rms.sql'
>
>     echo "-----------------------------------------------"
>     echo "Unzipping backup..."
>     /bin/gunzip -c ${BACKUP_FILE_GZ} > ${BACKUP_FILE}
>
>     echo "-----------------------------------------------"
>     echo "Attempting restore..."
>     /usr/bin/psql rms < ${BACKUP_FILE}
>
>     echo "-----------------------------------------------"
>     echo "Done"
>
>
>     THINK BEFORE YOU PRINT - Save paper if you don't really need to
>     print this
>
>     *******************Confidentiality and Privilege
>     Notice*******************
>
>     The material contained in this message is privileged and
>     confidential to
>     the addressee.  If you are not the addressee indicated in this
>     message or
>     responsible for delivery of the message to such person, you may
>     not copy
>     or deliver this message to anyone, and you should destroy it and
>     kindly
>     notify the sender by reply email.
>
>     Information in this message that does not relate to the official
>     business
>     of Weatherbeeta must be treated as neither given nor endorsed by
>     Weatherbeeta.
>     Weatherbeeta, its employees, contractors or associates shall not
>     be liable
>     for direct, indirect or consequential loss arising from
>     transmission of this
>     message or any attachments
>     e-mail.
>
>     --
>     Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org
>     <mailto:pgsql-admin@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-admin
>
>