Simon Riggs wrote:

A fixed classification of events and a defined framework in user
    
supplied >program is the way to success. 

Yes, I agree. I'm working on a better description of the overall design,
to allow everybody to think it through with me. Should be next week
now...
Let's talk in terms of severity from unimportant to critical. That will be basis for defining 'event handler' in user supplied script.

  

    
Another idea is an event handling system in database system. 
Sending defined signals to the 'outer world' through an API to a user
supplied program. 
This user supplied program can later on handle other events in addition
    

  

  
to >backup of WAL logs.

That was an approach I considered. One aspect of PITR is that testing is
a very important part of the overall solution. With that in mind, a very
tightly defined, very specific API seems the best way to go. A more
general facility might get mixed up and end up breaking the PITR logic.

  



I don't think a general facility would mix up or break something. It's
a question of design.

See Event "WAL log archived" as a very little subset of a large number
of defined events. 



Former Informix Developers (now IBM)  have successfully created an
powerful database event handling system which does Backup of 
transaction log areas
- as mimimum service.

Fully used it informs about non-critical to critical events via
generated emails - especially for on call people.



  
  





  
External notification is possible (I think, never tried) by using user
defined C language functions. It should be fairly straightforward to use
those to get/put on message queues, send POSIX signals etc.

  



As long as the 'user supplied program' is configured in postgresql.conf
the use of system() call can be used to pass defined Arguments.

The design of internal message queues etc. is somehow independent from
that. 

It has two aspects: detecting,interpreting and logging internal events
and on the other hand contacting the 'outer world' initiating a defined
action.



  

    
The archiver process must have a naming convention for archived WAL
    

  

  
logs - >especially for recovery. 

Yes. I would suggest the database name as a prefix to the WAL log file
name. The log file name is a very long string of digits that "never
repeats" according to the manual. I'll cross that bridge when it
happens. That should guarantee uniqueness **within** an organisation,
(assuming there is a unique database naming policy is place) which is
hopefully the limit of archived log files anyway.
  



Is the database oid unique even if multiple clusters are active on one
host ?

For identification purposes the most unique naming convention is needed.





  

    
Archiving WAL logs may require an archive destination - also needed for
recovery.
    

  

  

That would be the user-supplied program's role to define that. The
implementation will clearly require a reference implementation of just
such a program, to allow testing.

  



What  format is intended for the the user supplied program ? 

Can it be anything from interpetors language like SHELL, PERL  up to
dynamically linked 32/64bit ELF format ?

It's vital to have a template which can be adapted to your own needs.



  

  

    
Advantage of this idea is that backup of archived WAL logs is
    

  

  
automatically >enabled without the manual work of an DB Admin person. 

I think it is important that archival can only be activated by explicit
command from the database administrator. If not, then it could be some
kind of security hole to start sniffing a log without permission. Your
input is good though, because I haven't given security much thought yet
and you have spurred me to consider this further.

  





In terms of pratically providing 7x24h 365 days a year business that
will bring ugly problems - an DB admin can't always be around.

For my person dealing in this business a Software with sane
intelligence for standard events performing standard actions is a real
benefit.



DB admin has the choice.

Either an dummy user supplied program is 'contacted' - simply doing
nothing and not telling anyone.  This leaves archival initiation to DB
admin.

Or  a fully functional user supplied program with archive function to
whatever destination. This leaves archival to database system.



For security reasons it is recommended to avoid public used areas like
/tmp at any cost.



  
I hope I can call on you soon to review the design docs and later to
assist with testing?

  



Just contact me.



Best Regards Raymond