Thread: Re: Archival API
Simon,
>So in summary, the API is:
>
>Archiver initialises and waits on notify
>Postgresql initialises
>...then
>Postgresql fills log, switches and close it, then calls
>XLogArchiveNotify()
>Archiver moves log somewhere safe, then sets state such that...
>...sometime later
>Postgresql checks XLogArchiveBusy() to see if its safe to recycle file
>and discovers the state set by
>
>API is completely unintrusive on current tried and tested operation, and
>leaves the archiver(s) free to act as they choose, outside of the
>address space of PostgreSQL. That way we don't have to update regession
>tests with some destructive non-manual crash tests to show that works.
>
>Clearly, we wouldn't want WAL logs to hang around too long, so we need
>an initiation method for the archival process. Otherwise, we'll be
>writing "nnn.full" notifications yet without anybody ever deleting them.
>Either this could be set at startup with an archive_log_mode parameter
>(OK, the names been used before, but if the cap fits, wear it) or
>setting a maximum limit to number of archive logs and a few other ideas,
>none of which I like.
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.
A fixed classification of events and a defined framework in user supplied program is the way to success.
First event can be 'WAL log archived' starting backup of an archived WAL log.
After archiver process has created an archived WAL log an event like 'WAL log archived' calls the user supplied program interpreting the event.
The archiver process must have a naming convention for archived WAL logs - especially for recovery.
Archiving WAL logs may require an archive destination - also needed for recovery.
Essential input to user supplied program is the full-pathed archived WAL log.
A user supplied backup program backups full-pathed archived WAL log to an whatever Backup Destination (even /dev/null for suicide characters).
After successfull backup the archived WAL log needs to be removed.
All is handled internally in the user supplied program.
Advantage of this idea is that backup of archived WAL logs is automatically enabled without the manual work of an DB Admin person.
I have to admit I'm not a programer. How much effort to whole source code my idea will cost I can't calculate.
Any ideas ?
Raymond
-----Original Message----- From: pgsql-hackers-pitr-owner@postgresql.org [mailto:pgsql-hackers-pitr-owner@postgresql.org] On Behalf Of raymond.siebert@mobilcom.de > 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. > A fixed classification of events and a defined framework in user supplied > program is the way to success. > > [ ... ] > This is the method used in DB2. It is called "user exit". You can specify a program or a compiled C code to be run before, after or instead of a specific event.
>>Raymond Siebert wrote >>Simon Riggs wrote: >>So in summary, the API is: >>Archiver initialises and waits on notify >>Postgresql initialises >>...then >>Postgresql fills log, switches and close it, then calls >>XLogArchiveNotify() >>Archiver moves log somewhere safe, then sets state such that... >>...sometime later >>Postgresql checks XLogArchiveBusy() to see if its safe to recycle file >>and discovers the state set by >> >>API is completely unintrusive on current tried and tested operation, and >>leaves the archiver(s) free to act as they choose, outside of the >>address space of PostgreSQL. That way we don't have to update regession >>tests with some destructive non-manual crash tests to show that works. >> >>Clearly, we wouldn't want WAL logs to hang around too long, so we need >>an initiation method for the archival process. Otherwise, we'll be >>writing "nnn.full" notifications yet without anybody ever deleting them. >>Either this could be set at startup with an archive_log_mode parameter >>(OK, the names been used before, but if the cap fits, wear it) or >>setting a maximum limit to number of archive logs and a few other ideas, >>none of which I like. > >Another idea... Thank you very much for your input, it is gratefully received. >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... >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. 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. >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. >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. >Essential input to user supplied program is the full-pathed archived WAL >log. >A user supplied backup program backups full-pathed archived WAL log to an >whatever Backup Destination (even /dev/null for suicide characters). >After successfull backup the archived WAL log needs to be removed. >All is handled internally in the user supplied program. That's what the basic design proposed, yes. >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. I hope I can call on you soon to review the design docs and later to assist with testing? More input welcome! Best Regards, Simon Riggs
<br /><br /> Simon Riggs wrote:<br /><br /><blockquote cite="mid002601c3fcae$b08c0020$0200000a@LaptopDellXP" type="cite"><blockquotetype="cite"><pre wrap="">A fixed classification of events and a defined framework in user </pre></blockquote><prewrap="">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... </pre></blockquote> Let's talk in terms of severity from unimportant to critical. That will be basis for defining 'eventhandler' in user supplied script.<br /><blockquote cite="mid002601c3fcae$b08c0020$0200000a@LaptopDellXP" type="cite"><prewrap=""></pre><blockquote type="cite"><pre wrap="">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 </pre></blockquote><pre wrap="">to >backup ofWAL 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. </pre></blockquote> I don't think a general facility would mix up or break something. It's a question of design.<br /> SeeEvent "WAL log archived" as a very little subset of a large number of defined events. <br /><br /> Former Informix Developers(now IBM) have successfully created an powerful database event handling system which does Backup of transactionlog areas - as mimimum service.<br /> Fully used it informs about non-critical to critical events via generatedemails - especially for on call people.<br /><blockquote type="cite"><pre wrap=""> </pre></blockquote><blockquotecite="mid002601c3fcae$b08c0020$0200000a@LaptopDellXP" type="cite"><pre wrap="">External notificationis 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. </pre></blockquote> As long as the 'user supplied program' is configured in postgresql.conf the use of system() call canbe used to pass defined Arguments.<br /> The design of internal message queues etc. is somehow independent from that.<br /> It has two aspects: detecting,interpreting and logging internal events and on the other hand contacting the 'outerworld' initiating a defined action.<br /><blockquote cite="mid002601c3fcae$b08c0020$0200000a@LaptopDellXP" type="cite"><blockquotetype="cite"><pre wrap="">The archiver process must have a naming convention for archived WAL </pre></blockquote><prewrap="">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. </pre></blockquote> Is the database oid unique even if multiple clustersare active on one host ?<br /> For identification purposes the most unique naming convention is needed.<br /><br/><blockquote cite="mid002601c3fcae$b08c0020$0200000a@LaptopDellXP" type="cite"><blockquote type="cite"><pre wrap="">ArchivingWAL logs may require an archive destination - also needed for recovery. </pre></blockquote><pre wrap=""> 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. </pre></blockquote> What format is intended for the the user supplied program ? <br /> Can it be anything from interpetorslanguage like SHELL, PERL up to dynamically linked 32/64bit ELF format ?<br /> It's vital to have a templatewhich can be adapted to your own needs.<br /><blockquote cite="mid002601c3fcae$b08c0020$0200000a@LaptopDellXP" type="cite"><prewrap=""></pre><blockquote type="cite"><pre wrap="">Advantage of this idea is that backup of archived WALlogs is </pre></blockquote><pre wrap="">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. </pre></blockquote><br /> In terms of pratically providing 7x24h 365 days a year business that will bring ugly problems- an DB admin can't always be around.<br /> For my person dealing in this business a Software with sane intelligencefor standard events performing standard actions is a real benefit.<br /><br /> DB admin has the choice.<br />Either an dummy user supplied program is 'contacted' - simply doing nothing and not telling anyone. This leaves archivalinitiation to DB admin.<br /> Or a fully functional user supplied program with archive function to whatever destination.This leaves archival to database system.<br /><br /> For security reasons it is recommended to avoid public usedareas like /tmp at any cost.<br /><blockquote cite="mid002601c3fcae$b08c0020$0200000a@LaptopDellXP" type="cite"><prewrap="">I hope I can call on you soon to review the design docs and later to assist with testing? </pre></blockquote> Just contact me.<br /><br /> Best Regards Raymond <br /><br />