Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2002
    Posts
    7

    Unanswered: cron jobs for postgresql

    Hi tried to schedule a cron job(on solaris) to run a scripts(as postgres user) which in turn runs a query to the postgresql database and stores the results into a text file.But I get errors from the cron demon that there is no directory.When I tried to run the cron job as root.The error I get is psql not found.

    the script I used is

    psql -p 5432 -A -F ',' -c 'select * from test' -d test -t -o /cronjob/test.txt

    Any suggestions?


    Thanks
    Montfort

  2. #2
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    cron problem

    I had the same problem

    the cron has only minor rights
    also the path variable is one

    start the psql with the absolute path and not relative

    I think thats the problem
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  3. #3
    Join Date
    Mar 2002
    Posts
    7

    cron jobs for postgresql

    Hello eperich,
    Thanks for the response.
    Could You be more specific What you meant, about starting the psql with absolute path.
    An example coould be very useful to me.

    Thanks
    Montfort.

  4. #4
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    Absolute path

    for EXAMPLE
    /usr/local/pgsql/psql -p 5432 -A -F ',' -c 'select * from test' -d test -t -o /cronjob/test.txt


    absolute path
    cron does not have cause of security reason not the environment variable path
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  5. #5
    Join Date
    Mar 2002
    Posts
    7
    hello eperich,
    I tried to the absolute path.The error I got was
    /usr/local/pgsql/psql not found

    any suggestions?

    Thanks
    Montfort

  6. #6
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    EXAMPLE

    this was an example

    I don't know where the file is on your system

    you must enter the path where your psql file
    is located
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  7. #7
    Join Date
    Mar 2002
    Posts
    7
    hello eperich,
    Thanks.
    Running a cron job as a postgres user is possible?


    Thanks
    montfort

  8. #8
    Join Date
    May 2001
    Posts
    51
    hi all, just read this, i arent and big postgre/php user, that my brother, but something i picked up...

    inorder to access the postgre db during the cron, dont you need to be logged in as the postgre db user.

    Try generating the cron whilst logged in as the postgre db user, may solve your problem

    Regards

  9. #9
    Join Date
    Mar 2002
    Location
    Pilipinas
    Posts
    36

    variables for cron

    i'd setup my linux box to backup and vacuum my postgreSQL dbase, the result is fine except for the name of the output file.

    i put this lines to .bash_profile:
    DATE1=`date +%b_%d_%y`
    export DATE1

    in crontab:
    03 04 * * 0 ~/bin/pg_dump CDKEY /bin/gzip "$DATE1"_dbname1.dump.gz 2> "$DATE1"_CDKEY.error.log
    06 04 * * 0 ~/bin/vacuumdb -a -f 2> "$DATE1"_vacuumdb.log

    the output file is like this:
    _dbname.dump.gz
    _vacuumdb.log

    but if i do this command:
    #echo $DATE1
    # Jun_07_02 --> output

    pls help me guys...
    --- Hey! its me!!! the MaleMan ---

  10. #10
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    User problem

    When starting the cron

    be aware that almost no environment variables are active due to security reasons

    always take the absolute PATH starting the postgresql commands

    maybe this is the same thing that happened with the .bash_profile

    make a shell script that executes the pg_dump and vacuumdb and generate the date there
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  11. #11
    Join Date
    Mar 2002
    Location
    Pilipinas
    Posts
    36

    Thumbs up thanks



    now i konw... ill just make some adjustments and create some scripts for it.

    THNX!
    --- Hey! its me!!! the MaleMan ---

  12. #12
    Join Date
    Sep 2002
    Posts
    1

    Re: User problem

    add DATE variable to your crontab file

    eg.

    #!/bin/sh
    DATE = `date +%b_%d_%y`

    03 04 * * 0 ~/bin/pg_dump CDKEY /bin/gzip "$DATE1"_dbname1.dump.gz 2> "$DATE1"_CDKEY.error.log 06 04 * * 0 ~/bin/vacuumdb -a -f 2> "$DATE1"_vacuumdb.log

  13. #13
    Join Date
    Mar 2002
    Location
    Pilipinas
    Posts
    36

    Thumbs up thanx for the answers guys...

    thanx! that problem was already solved using bash/perl scripts, all your commands and sql queries are all in the script so once you do your cron job, you just create some line there to run your script and whala!!! your problem is solved.

    --- Hey! its me!!! the MaleMan ---

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •