Re: psql scripting tutorials

On Wed, Sep 10, 2008 at 10:14 PM, Artacus <artacus(at)comcast(dot)net> wrote:
>> This sounds a lot like what I did in my last job using bash for most
>> things, and php for the more complicated stuff. Wrote a simple oracle
>> to pgsql table replicator in php that worked pretty well.
>
> Well we do this stuff all the time with Oracle and sql*plus. And I've heard
> people hear say about how much better psql is than sql*plus. So I figured
> surely someone would have figured that its a really slick way of automating
> imports and scheduling with cron... and then blogged about it. But it looks
> like I'm on my own.

Well, sadly I didn't keep a copy of the scripts when I left. I did
something in bash that was basically to run a query, and process each
line as it came out of psql to detect system problems. I had a stats
db with all the events the app generated aggregated by total time /
req / minute and stuffed into the db. 5 to 10 app servers making 60
to 200 inserts a minute each. Not a lot of data each minute, but it
added up. then I had a table with each request type and a max average
and max absolute threshold that we ran a single query to find which
rows were over their maxes and generated alerts. I used the line by
line reading techniques you can find from googling, then used read (I
think it was read) to split the line up into parts to stuff into vars
and do math. I need to make something like that again anyway, I'll
post it when it works.