Re: how to start a procedure after postgresql started. - Mailing list pgsql-general

2011/5/22 Scott Marlowe <scott.marlowe@gmail.com>:
> On Sun, May 22, 2011 at 6:49 AM, jun yang <slickqt@gmail.com> wrote:
>> 2011/5/22 Scott Marlowe <scott.marlowe@gmail.com>:
>>> On Sat, May 21, 2011 at 10:57 PM, jun yang <slickqt@gmail.com> wrote:
>>>> now all the question:
>>>> 1.how start a procedure or a script after postgresql start.
>>>
>>> Do you need this stored procedure or script to always run at pg
>>> database start?  Or every time a client connects to the database?  Or
>>> every  minute, or every hour or every day?
>> i am just want pg execute the given stored procedure every time after
>> pg sucess start.
>
> Then just add the logic to run it from your init script.    If you're
> running something like ubuntu there's a script in /etc/init.d to start
> postgres you can hack up to add a new step at the end.  Not sure
> that's the best place but it'll work.
>
>>>> 2.how to get notify when a table created.
>>>> 3.how to get notify when a database created.
>>>
>>> Set db to log all dll, scrape logs, email timestamp from table or db
>>> creation.  Do you need more info other than just that it was created?
>>>
>> well,it may be not what i want,
>> if i can start a procedure after pg start,then i would like to get
>> notify when new object create,of course,i should be subcribe first.
>> now what i interest is the database created, when a new database
>> created,i want to init some schme and table,index in the it.
>
> If you just need each new db to have certain tables, then put them in
> template1 and when you create a new db they'll be there.  As is often
> the case, instead of asking how to do very specific things, you're
> better off telling us what you're trying to accomplish in the bigger
> sense so we can recommend better ways to accomplish what you want.
>
> Note that PostgreSQL does NOT support triggers on system tables and
> objects so you'll have to find some way of monitoring pg logs if you
> want to know when things like new tables are created.  Again, give us
> the bigger picture and we can likely be more helpful.
>
what we want to do is explore the ability to move the system to the
architecture like below:
some pg---message broker(qpid)---(web front and some collect data
terminal,some business logic server,some system status monitor)
when pg start it subscribe to qpid then process the message send to it.
when pg is down,the important message is saved in message broker.
when web front has activity,the data send to message broker and  will
route to pg then saved,it may be fire some trigger and some messge
send to message broker then to web front or collect data terminal
when collect data terminal has data,it send to broker.
through the route ability of message broker,the message send to it
will be send to right pg instance,log data to log pg instance,finance
data to finance pg instance,repair data to repair pg instance etc.
message broker has cluster and federation,pg has hot standby and sync
replication, deployment will be easier for the life after the first
time.
the prupose of get notify of object create result,first is to monitor
the system status,second is want to do some special init action with
such a object.