Skip site navigation (1) Skip section navigation (2)

Re: Archival API

From: Raymond Siebert <raymond(dot)siebert(at)mobilcom(dot)de>
To: simon(at)2ndquadrant(dot)com
Cc: pgsql-hackers-pitr(at)postgresql(dot)org
Subject: Re: Archival API
Date: 2004-03-02 14:20:08
Message-ID: 40449818.9090800@mobilcom.de (view raw or flat)
Thread:
Lists: pgsql-hackers-pitr
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<br>
Simon Riggs wrote:<br>
<br>
<blockquote cite="mid002601c3fcae$b08c0020$0200000a(at)LaptopDellXP"
 type="cite">
  <blockquote type="cite">
    <pre wrap="">A fixed classification of events and a defined framework in user
    </pre>
  </blockquote>
  <pre wrap=""><!---->supplied &gt;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 'event handler' in user supplied script.<br>
<blockquote cite="mid002601c3fcae$b08c0020$0200000a(at)LaptopDellXP"
 type="cite">
  <pre wrap=""></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 &gt;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.

  </pre>
</blockquote>
I don't think a general facility would mix up or break something. It's
a question of design.<br>
See Event "WAL log archived" as a very little subset of a large number
of defined events. <br>
<br>
Former Informix Developers (now IBM)&nbsp; have successfully created an
powerful database event handling system which does Backup of&nbsp;
transaction log areas
- as mimimum service.<br>
Fully used it informs about non-critical to critical events via
generated emails - especially for on call people.<br>
<blockquote type="cite">
  <pre wrap="">
  </pre>
</blockquote>
<blockquote cite="mid002601c3fcae$b08c0020$0200000a(at)LaptopDellXP"
 type="cite">
  <pre wrap="">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.

  </pre>
</blockquote>
As long as the 'user supplied program' is configured in postgresql.conf
the use of system() call can be 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 'outer world' initiating a defined
action.<br>
<blockquote cite="mid002601c3fcae$b08c0020$0200000a(at)LaptopDellXP"
 type="cite">
  <blockquote type="cite">
    <pre wrap="">The archiver process must have a naming convention for archived WAL
    </pre>
  </blockquote>
  <pre wrap=""><!---->logs - &gt;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 clusters are active on one
host ?<br>
For identification purposes the most unique naming convention is needed.<br>
<br>
<blockquote cite="mid002601c3fcae$b08c0020$0200000a(at)LaptopDellXP"
 type="cite">
  <blockquote type="cite">
    <pre wrap="">Archiving WAL 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&nbsp; format is intended for the the user supplied program ? <br>
Can it be anything from interpetors language like SHELL, PERL&nbsp; up to
dynamically linked 32/64bit ELF format ?<br>
It's vital to have a template which can be adapted to your own needs.<br>
<blockquote cite="mid002601c3fcae$b08c0020$0200000a(at)LaptopDellXP"
 type="cite">
  <pre wrap=""></pre>
  <blockquote type="cite">
    <pre wrap="">Advantage of this idea is that backup of archived WAL logs is
    </pre>
  </blockquote>
  <pre wrap=""><!---->automatically &gt;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
intelligence for 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.&nbsp; This leaves archival initiation to DB
admin.<br>
Or&nbsp; 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 used areas like
/tmp at any cost.<br>
<blockquote cite="mid002601c3fcae$b08c0020$0200000a(at)LaptopDellXP"
 type="cite">
  <pre wrap="">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>
</body>
</html>


Attachment: unknown_filename
Description: text/html (6.0 KB)

In response to

pgsql-hackers-pitr by date

Next:From: Simon RiggsDate: 2004-03-02 22:16:12
Subject: Re: WAL Optimisation - configuration and usage
Previous:From: Josh BerkusDate: 2004-03-02 01:10:07
Subject: Re: WAL Optimisation - configuration and usage

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group