Thread: chron scripts and pgsql
I looked in postgres-general, and VERY little came up about 'chron'. Can anyone outline what it takes not NON INTERACTIVELY do a query with pgsql? So it can be done in a chron script? I want to check a table to see if there's any rows with 'unapproved' in one column and then just call mail to send someone an email to approve some listings.
On Sun, Apr 06, 2003 at 10:55:01PM -0700, Dennis Gearon wrote: > I looked in postgres-general, and VERY little came up about 'chron'. > > Can anyone outline what it takes not NON INTERACTIVELY do a query with > pgsql? So it can be done in a chron script? > > I want to check a table to see if there's any rows with 'unapproved' in > one column and then just call mail to send someone an email to approve > some listings. Just use psql -c <query> -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "the West won the world not by the superiority of its ideas or values or > religion but rather by its superiority in applying organized violence. > Westerners often forget this fact, non-Westerners never do." > - Samuel P. Huntington
Attachment
OOPS,
'chron' != 'cron'
There's maybe 'one or two more' than I found before, using 'cron'
instead :-)
Dennis Gearon wrote:
>
> I looked in postgres-general, and VERY little came up about 'chron'.
>
> Can anyone outline what it takes not NON INTERACTIVELY do a query with
> pgsql? So it can be done in a chron script?
>
> I want to check a table to see if there's any rows with 'unapproved' in
> one column and then just call mail to send someone an email to approve
> some listings.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
My instance of postgres runs on a separate box than the one that does the work. So connections are always via TCP/IP. Therefore, the simple "psql -c <command>" doesn't work because my databases require a username/password. My solution for the cron scripts (the only one I could figure out that works). My database name and password are stored in a file readable only by the script execution id. Those filenames and passwords are parsed and set to the PGUSER and PGPASSWORD variables (which exist only for the life of the script). I can then run my "pgsql -h <hostname> -c <command>" for any and all databases. If anyone has a better solution for the username/password, I would love to see it, but this works. Hope this helps, -- Ken Harris Senior Consultant http://www.lhinfo.com (410) 597-8916 Quoting Dennis Gearon <gearond@cvc.net>: > OOPS, > > 'chron' != 'cron' > > There's maybe 'one or two more' than I found before, using 'cron' > instead :-) > > Dennis Gearon wrote: > > > > I looked in postgres-general, and VERY little came up about 'chron'. > > > > Can anyone outline what it takes not NON INTERACTIVELY do a query with > > pgsql? So it can be done in a chron script? > > > > I want to check a table to see if there's any rows with 'unapproved' in > > one column and then just call mail to send someone an email to approve > > some listings. > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > ------------------------------------------------- This mail sent through IMP: http://horde.org/imp/
Ken Harris <kharris@lhinfo.com> writes:
> If anyone has a better solution for the username/password, I would love to see
> it, but this works.
In 7.3 you can keep the passwords in a ~/.pgpass file. You generally do
NOT want to use PGPASSWORD, as on many platforms it is possible for
other people to look at your environment variables.
regards, tom lane
Isn't the script execution id dynamic, i.e. changes everytime the script is run? How do you also limit permissions to an execution id? Ken Harris wrote: > My instance of postgres runs on a separate box than the one that does the work. > So connections are always via TCP/IP. Therefore, the simple "psql -c > <command>" doesn't work because my databases require a username/password. My > solution for the cron scripts (the only one I could figure out that works). My > database name and password are stored in a file readable only by the script > execution id. Those filenames and passwords are parsed and set to the PGUSER > and PGPASSWORD variables (which exist only for the life of the script). I can > then run my "pgsql -h <hostname> -c <command>" for any and all databases. > > If anyone has a better solution for the username/password, I would love to see > it, but this works. > > Hope this helps, >
su - ken -c "psql -d pi -f update.sql >> update.log" Since your cron file is run as root it can su to any user and as long as user ken has rights for the database your connecting to you don't need to enter any passwords. This is how I do it and then from the same script I call a python script to read in results from update.log and email them to me. > I looked in postgres-general, and VERY little came up about 'chron'. > > Can anyone outline what it takes not NON INTERACTIVELY do a query with > pgsql? So it can be done in a chron script? > > I want to check a table to see if there's any rows with 'unapproved' in > one column and then just call mail to send someone an email to approve > some listings. > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >