chron scripts and pgsql

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/

OOPS,

'chron' != 'cron'

There's maybe 'one or two more' than I found before, using 'cron'
instead :-)

Dennis Gearon 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,

--
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:

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.