A nice feature from PostgreSQL are “write ahead log” (WAL) files. Quoting from the PostgreSQL website they describe every change made to the database’s data files. […] if the system crashes, the database can be restored to consistency by “replaying” the log entries made since the last checkpoint. This basically enables online backup of your database cluster. And as you end up having the complete log of all actions within your database(s) you are also able to go back in time to any point you like. I won’t write a complete howto about it, as the PostgreSQL docs are pretty good, just some notes how I did my setup, as a kind of reminder for me when I have to do it again.

---

There must be a place to store WAL files which ends up not being on the same machine, otherwise it doesn’t make much sense. For me that means scp to a different host, but an NFS mount or some storage array or drbd device might also be an option. Much of the following is for the scp method, but easily adaptable to other storage methods. I need a target user on the host I copy files to, and as I did the setup for the pentabarf database running on host skinner, I helpfully named it pentabarf-backup. The copy process can’t be interactive so the postgres user on the database host needs to have a passwordless SSH key to login to the pentabarf-backup user on the backup host. To limit my bad feeling about “passwordless key to login” I restrict the key by adding

from="skinner.debconf.org,72.32.250.224,72.32.79.241",no-port-forwarding,no-X11-forwarding,no-agent-forwarding,no-pty 

in front of it while copying it to the pentabarf-backup users .ssh/authorized_keys file. I need a few directories, namely pentabarf-WAL-skinner, pentabarf-tarballs and pg_xlog-skinner for the stuff to backup, created in the home of the pentabarf-backup user. Next step is to tell PostgreSQL our wish to have online backups. Add the following statements to your postgresql.conf, adapting the values to your needs:

archive_command = 'ssh backup test ! -f pentabarf-WAL-skinner/%f && rsync -az %p backup:pentabarf-WAL-skinner/%f'
checkpoint_timeout = 1h
archive_timeout = 12h

The above tells PostGreSQL that i want it to archive the files using ssh to my backup host and transfer files with rsync (via ssh), but only if they do not exist yet. It also tells it to archive the files at least once every 12 hours. The effect is that WAL files get copied over when they are “full” or after 12 hours passed, whatever happens first. Don’t set it too low, as the file will always have the full, fixed size and you waste space transferring mostly-empty files. In case you are concerned with the possibly slow speed of a network link, the PostgreSQL site has the following to say about this: The speed of the archiving command is not important, so long as it can keep up with the average rate at which your server generates WAL data. Normal operation continues even if the archiving process falls a little behind. I haven’t had a problem with the speed yet. A 16MB file every (at least) 12 hours isn’t that bad. Now that we have done all the setup and are able to transfer WAL files, we need a base backup to start with. Issue a SELECT pg_start_backup(‘BASE_BACKUP’); as a database superuser and then backup the database cluster by backing up the whole clusters data/ directory (and every other location if you use tablespaces). When you are done issue SELECT pg_stop_backup(); and you are done with your base backup. You might want to exclude pg_xlog/ to save space, you don’t need it.
As I’m lazy I use a script (written by Peter ‘weasel’ Palfrader), you can get it here, but whatever you take is fine. If you use the script just make sure to adapt the variables in the script to values that fit your system. From now on you should do such base backups in regular intervals, I run them once a week, as recovery does need the last base backup plus all WAL files from then on. Doing regular base backups limits the amount of WAL files you need to keep around / you need to replay.

---

Reading the above you might find that there is a window of up to 12 hours from which I do not have backups. True, and to make this window way smaller, I am using another script (also written by weasel), get it here, together with an init script. This synchronizes the pg_xlog/ directory to my backup host every 5 minutes, so in case of a server crash I can replay everything up to the last 5 minutes. Recovery using WAL files This is pretty simple, and also good documented at the PostgreSQL site again. For my scenario it basically boils down to

1. Stop postgres
2. Move away the cluster.

   mv /var/lib/postgresql/8.2 /var/lib/postgresql/8.2_old

3. Restore the backup dump, be careful to have the file rights correct. For the above described setup that means copy and untar the latest tarballs created weekly.
4. Recreate pg_xlog:

     mkdir /var/lib/postgresql/8.2/main/pg_xlog
     chown postgres:postgres /var/lib/postgresql/8.2/main/pg_xlog
     chmod 40700 /var/lib/postgresql/8.2/main/pg_xlog
     mkdir /var/lib/postgresql/8.2/main/pg_xlog/archive_status
     chown postgres:postgres /var/lib/postgresql/8.2/main/pg_xlog/archive_status
     chmod 40700 /var/lib/postgresql/8.2/main/pg_xlog/archive_status
    

5. Copy possibly unarchived WAL files from /var/lib/postgresql/8.2_old/main/pg_xlog into the newly created pg_xlog.
6. Copy all the WAL segment files that are needed over from the backup host. That should be all the files on the backup host, directory pentabarf-WAL-skinner/. You should be able to limit it by taking all those files >= the latest *.backup file.
7. Create a recovery command file recovery.conf in the cluster data directory. You may also want to temporarily modify pg_hba.conf to prevent ordinary users from connecting until you are sure the recovery has worked. The content of recovery.conf is

         restore_command = 'cp /tmp/pg-WAL/%f "%p"'
        

   Make sure that the file location you specify is the location you put the WAL segment files into in the prior step.
8. Start the server, it will go into recover mode and proceed to read through all the archived WAL files it needs.
9. Look at the database and decide if you like what you see.

Of course the procedure can be slightly adapted, so you end up with a database state from 2 weeks ago. Or 3 days. Or whatever you may need (and have WAL files for). There are many possibilities, and there is a lot of text on the PostgreSQL page that I don’t want to duplicate here, so please read it. Also, I’m not using this as the sole and only way to backup my database(s), they are all still dumped into SQL files every day. This is just additional.

XQuery 1.0 and XSLT 2.0: news and how to use them in Debian (long post about the second generation of the XSL family and how to use the related languages in Debian)

• XPath 2.0, XQuery, et al.
• the above stuff .. in Debian
• links

---

The 2nd generation of the XSL family At the beginning of 2007, W3C released a family of interrelated specification: XPath 2.0, XSLT 2.0, XQuery (1.0). The 3 specifications are based on the very same underlying data model which (finally!) supports typed values and exploits them to some extent, for example permitting some form of static type checking in XPath and XQuery. Types usually come from XML Schema, with both built-in and user-defined types supported. A brief overview of the 3 specifications and what they change in the state of the art follows. XPath 2.0 XPath 2.0 basically consists in XPath 1.0 + 3 new macro features. The first one is a revamp of those language features which are dealing with types, in order to better integrate them with the new typed data model. XPath now has operators to check whether a value belongs to a given type (so yes, type information are kept at run time), and to cast them from one type to another. The basic new type constructor is now the sequence, which replaces the old node-set solving many annoying issues, such as the impossibility of having a node ordering other than document order. The second feature is the improvement of standard library of functions which was ridiculously small in XPath 1.0; it is much better now. Additionally, 2nd generation languages (XSLT 2.0 and XQuery) now support the ability to define functions which will then be visible to inner (XPath) expressions, pushing yet forward the possibilities of plain old XPath. Functions can now also declare types in their signatures (for their arguments and return value) and untyped arguments will be automatically casted to them upon invocation. Hence, even if you are not using an implementation which assigns types to your XML trees, once you "enter" the typed world calling a typed function (almost all standard library functions are decently typed) you will be able to stay there avoiding annoying casts everywhere. Finally XPath 2.0 has turned into a powerful purely functional language and is now powered by constructs like conditionals, for-each loops, existential/universal quantifiers, and existentially-quantified comparison operators for sequences. Here is a complex expression to hwet your appetite (or scare you away ...), comments come as (: smiley faces :)

for $book in /bookshelf//books
return
  if ((every $author in $book/authors/author
       satisfies $author/nativeLang eq "it_IT")
      and $book/lang eq "it_IT")
  then $book
  else ()
(: think about the trouble of writing this in XSLT/XPath 1.0 ... :)

XSLT 2.0 XSLT 2.0 is what I would call a "bug fix release" of XSLT 1.0 + the routinary reworking of the language to deal with typed values, which is not sensibly different than what has been done for XPath 2.0. The fixed "bugs" are several, starting from the annoying issue of result tree fragments. They are basically tree snippets that in XSLT 1.0 you were able to generate for future use. Unfortunately they were not thaaat reusable, given that you were not even able to navigate them with XPath operators! Now the specification is much more clear and distinguishes final result trees from ordinary variables, which can now contain sequences of (navigable) tree nodes. Another important "bug" fixed is the new ability to output multiple documents with a single XSLT stylesheet: it marks the end of stupid extra post processing to be added in pipeline to a XSLT processor. Other minor "bugs" fixed are a limited amount of backtracking capabilities among imported templates, regular expression support directly in the language, and powerful grouping constructs on the lines of SQL's GROUP BY (but much more powerful). Here is a template snippet exploiting the latter feature:

<xsl:for-each-group select="*" group-starting-with="h1">
  <div>
    <xsl:apply-templates select="current-group()" />
  </div>
<xsl:for-each-group>

XQuery XQuery is the end of the chains imposed by XML-based syntaxes. Why the heck one has to use an XML syntax (as in the above snippet) only because she is manipulating XML tress is one of the mysteries of XML technologies which have always been floating around in my head. XQuery is the (supposedly) SQL equivalent for databases of XML documents, but is actually much more than that. I depict it in my head as the XML manipulation language with a syntax I can finally stand. Technically it is XPath 2.0 (say 80% of the whole language) + some extra ingredients (say 20%); so remember that every XPath 2.0 expression is also a XQuery expression. The main extra ingredient is the so called FLWOR expression (to be read: "flower expression", which in addition to the "smiley faces" used for comments gives a "back to 1968"-flavour to the language .... erm FLWOR/flower/flavour, no pun intended). A FLWOR expression is very similar to SQL's SELECT-FROM-WHERE: it lets you generate a tuple stream by iterating on sequences (F: for clauses), binding expression values to names (L: let clauses), filter out tuples which do not satisfy a required condition (W: where clause), order the survived tuples (O: order by clause), and finally return a sequence built using the residual tuple stream (R: return clause). The other interesting extra ingredient is the ability to build the XML snippets you want to manipulate. Within XQuery you do that using plain XML syntax (the only place where a sane-minded programmer actually wants to see it!) which also supports a classical interpolation mechanism to embed expressions which will be evaluated inside XML snippets, and also the other way around. A canonical XQuery example is:

for $t in doc("books.xml")//title,
    $e in doc("reviews.xml")//entry
where $t = $e/title
return <review>  $t, $e/remarks  </review>
(: braces denote the escaping context where XQuery expressions will
   be evaluated inside snippets; plain XML syntax is used for the
   other way around :)

But remember: XQuery for XML is much more than SQL for RDBMS, thanks to the implicit templating mechanism implemented by interpolation, and thanks to several language features fostering modularity (user-defined functions, library modules, XPath 2.0 standard library, ...) you can basically do with it any kind of XML manipulation you can imagine. I don't think I will ever write myself any other single line of XML output in DOM or XSLT ...

---

Cool, how can I use it in Debian? ... if only this stuff were decently supported in the open source world. Last time I checked, the author of most parts of the GNOME toolchain for dealing with XML (libxml2, libxslt, ...) was not intentioned to implement XSLT 2.0, not even mentioning XQuery. This comes as no surprise, the whole GNOME XML toolkit is written in C, and XSLT 2.0 / XQuery have reached a level of complexity and formal specification which usually entails a higher level approach. So on the GNOME side we are stuck. The other open source implementations of XSLT 2.0 / XQuery I'm aware of are Saxon and Galax. Saxon is an XSLT 2.0 and XQuery implementation written in Java, which is unfortunate per se. Additionally, it is also unfortunate that it is only partially open source. Indeed, Saxon is split into SaxonB (for "basic") which is open source under the Mozilla Public License and SaxonSA which is commercial. While SaxonSA is a fully conformant, XML Schema-aware processor, with support for static typing, SaxonB is a basic-conformant processor with no type-aware features and actually much less optimized than SaxonSA. This is annoying. (Much more annoying is the fact that SaxonSA's author is the only editor of the XSLT 2.0 specification and that instead of his mail address the specification includes an URL pointing to the website selling SaxonSA ...) Galax is an open source (IBM CPL / Lucent license) OCaml implementation of XQuery which is not fully conformant to the specification (though it gets quite close) which is type-aware and implements static typing. The only XQuery / XSLT 2.0 implementation available in Debian at the time of writing is SaxonB. The binary package is libsaxonb-java, kudos to Michael Koch and the Debian Java Maintainers for having packaged it (and to have stood some annoying pings of mine bug #408842). To execute XQuery code you just have to aptitude install libsaxonb-java, prepare a query.xq file containing your query, and then execute something like:

CLASSPATH=/usr/share/java/saxonb.jar \
java net.sf.saxon.Query query.xq

note that the information in README.Debian are still referring to old Saxon versions, see bug #465894 which proposes a more up to date README.Debian. Similarly, to perform a XSLT 2.0 transformation you have to do something like:

CLASSPATH=/usr/share/java/saxonb.jar \
java net.sf.saxon.Transform -ext:off -s:input.xml -xsl:style.xsl -o:output.xml

Do not remove the -ext:off flag when processing untrusted stylesheets!, see bug #465885 for the reason. I've written some handier (1-liner) shell script helpers which remove the need of invoking java manually. They are attached to bug #465894 and I've proposed their addition to the saxonb package. Using them the above invocations become:

saxonb-xquery query.xq
saxonb-xslt -ext:off -s:input.xml -xsl:style.xsl -o:output.xml

Galax in Debian ... well, since long time I've been planning to package it (the ITP has been filed some months ago: bug #447984) and the authors sent me a newer version than what is available online to gather feedback before the long overdue final release. Unfortunately I've been lagging behind in finishing the packaging (which is tricky due to the need of binding libraries to several different languages: OCaml is native, but there is also Java for example). Hopefully this post will give me some renewed motivation for finishing the work ...

---

References

• Debian package w3-recs, ships the whole list of W3C Recommendations for offline consultation; it includes all the specifications we have discussed in this post
• Saxon: home page of Saxon, a XSLT 2.0 / XQuery processor written in Java
• Galax: home page of Galax, a XQuery processor written in OCaml
• Debian package libsaxonb-java, ships SaxonB as a Debian package

---

Acknowledgements Thanks to godog for his helpful comments.

---

Update: the helpers I've proposed have been accepted into the official package, I've also made available their manpages. Also the pending changes to README.Debian has been accepted. Kudos again to Michael Koch for his quick feedback on my patches!

Requiring Internet Service Providers to bar users for illegal downloading is stupid because:- 1. ISPs can't tell what we're sending over the network and aren't in a position to verify whether we have permission to send it. However, cutting off small businesses and home offices could kill some businesses. I own a share of my ISP (one of 6000 or so equal shares) and would vote against opening it up to that liability. I'm not surprised that ISPA has already said ISPs want to pass any liability back to the accusers. 2. government sometimes doesn't understand what can be downloaded legally. There's a famous case where a Trading Standards Officer complained to a Mozilla Foundation worker about their free Firefox browser because

"it makes it virtually impossible for us, from a practical point of view, to enforce UK anti-piracy legislation"

Source: Times Online 3. there is permission for "fair dealing" in copyrighted material for various purposes, including current affairs reporting and education, unless someone changed the law since I last looked! However, some organisations, including the BBC's 100-million-pound iPlayer, try to deny viewers those permissions, so it is easier to start from illegal copies than it is to deal with iPlayer (only works on some computers, installs bandwidth-using code into the system startup and so on). Finally, where did the 6 million illegal downloaders figure come from, ultimately? The film and music dominant producers? The Recording Industry Association of America has already spoken out against legally-required filtering, in http://www.news.com/8301-10784_3-9861460-7.html?part=rss&subj=news&tag=2547-1_3-0-5 Our software is available for free download and free sharing. The music and movie industries need to adapt, not be propped up by making ISPs pay through this sort of bad law. This isn't so much ISP disconnect as BPI logical disconnect!

Saturday 2 February 2008 was the annual meeting of the Phone Coop (agent link). I travelled to Chipping Norton for it and took a few abbreviated notes, which I've expanded slightly here. Ask questions if you want, but it's already fading in my memory:- Local Loop Unbundling workshop: 20 years of calls for it. 1999 ofcom announced 01 eu announced. 05 threat to breakup BT. 06 openreach split. By 2007-04 there were 2million unbundled. Distribution points and cabinets. Exchange may make even cheaper faster video. TPC LLU should launch this summer. Should take minutes to transfer. Equivalence problems - want evidence of when BT is preferred. Alarm lines: coop unable to take some. Broadband workshop: at year 2000 launch was a .5M service for GBP 40pcm. Now charge represents use. The coop has 4 ADSL specialists and it accounts for 15pc of income. Bought in three parts: tails are the links to the customers, bandwidth is then 155 or 622M fused 60m base plus extra. Home contention is 50:1, business is 20:1. Available in bundles. My.phone.coop offers a usage view with only a day's delay - might be a first for retail broadband. Ambassadors workshop: Harness cooperative difference. Customers can help to get more customers. Possibly give half our local adverts to our phone agency. Interaction with agent scheme unknown yet. Someone asked about running cinema ads. AGM: intros apologies evaluations mugs window stickers 1b 565m mins. Directors report record sales and growth. Profit again. Service progress: 2nd of 19 in Which surveys. Sven is new manager. Midcounties. Sustainability fund. Finances. Highest dividend. Sales mix changed kind rental growth. Costs down 200k. 9k postage saving. Record balance sheet. Recruiting membership manager and secretary. Currently 6735 members. Negotiating .5k sales acquisition - no info to members yet as commercially sensitive. Shortlisted for green business awards on 12 march. Questions: Mobiles? high volume only economic. What is cws? Cooperative Wholesale Society, part of the Cooperative Group. Member profile. Coop purchases. Diversity. Southern acquisitions? Regulator Dependent. Name. Biodiesel. Satisfaction, corp members, large corp custs. Miles travelled. Walking payments. Group promotion of AOL is annoying. Third party card discouraged? Member-account links. Larger affinity. Works bus. 1 against scrutineer. Directors spend on average 4 hours a week on the coop.