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

Search Discussions

  • Nicolai Tufar at Feb 26, 2004 at 9:08 pm
    -----Original Message-----
    From: 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.
  • Simon Riggs at Feb 26, 2004 at 9:23 pm

    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
  • Raymond Siebert at Mar 2, 2004 at 2:18 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers-pitr @
categoriespostgresql
postedFeb 26, '04 at 10:54a
activeMar 2, '04 at 2:18p
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase