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.

A Slashdot question today about putting to use all the unused disk space on corporate desktops got me to thinking. Now, before I start, comments there raised valid points about performance, reliability, etc.

But let's say that we have a "cloud filesystem". This filesystem would, at its core, have one configurable parameter: how many copies of each block of data must exist in the cloud. Now, we add servers with disk space to the cloud. As we add servers, the amount of available space on the cloud increases, subject to having enough space for replication according to our parameters.

Then, say we say we want a minimum of 3 copies of each block replicated. Each write to the filesystem will then cause a write to at least 3 different servers. Now, what if one server goes down? If the cloud filesystem is short on space, we may be down to only 2 copies of some blocks until that server comes back up. Otherwise, space permitting, it can rebuild that third copy on other servers.

Now, has this been done before? As far as I can tell, no. Wouldn't it be sweet?

But there are some projects that are close. Most notably, GlusterFS. GlusterFS does all of the above, except the automated bits. You can have this 3 copy redundancy, but you have to manually tell it where each copy goes, manually reconfigure if a server goes offline, etc. Other options such as NBD, OpenAFS, GFS, DRBD, Lustre, GFS, etc. aren't really well-suited for this scenario for various reasons.

So, what does everyone think? Can this work? Has it been done outside of Google?

disk = [ 'phy:/dev/vg/xen1,hda,w', 'phy:/dev/vg/xen1-swap,hdb,w', 'phy:/dev/vg/xen1-drbd,hdc,w', 'phy:/dev/vg/san,hdd,w!' ]

For some work that I am doing I am trying to simulate a cluster that uses fiber channel SAN storage (among other things). The above is the disk line I'm using for one of my cluster nodes, hda and hdb are the root and swap disks for a cluster node, hdc is a DRBD store (DRBD allows a RAID-1 to be run across the cluster nodes via TCP), and hdd is a SAN volume. The important thing to note is the "w!" mode for the device, this means write access is granted even in situations whre Xen thinks it's unwise (IE it's being used by another Xen node or is mounted on the dom0). I've briefly tested this by making a filesystem on /dev/hdd on one node, copying data to it, then umounting it and mounting it on another node to read the data.

There are some filesystems that support having multiple nodes mounting the same device at the same time, these include CXFS, GFS, and probably some others. It would be possible to run one of those filesystems across nodes of a Xen cluster. However that isn't my aim at this time. I merely want to have one active node mount the filesystem while the others are on standby.

One thing that needs to be solved for Xen clusters is fencing. When a node of a cluster is misbehaving it needs to be denied access to the hardware in case it recovers some hours later and starts writing to a device that is now being used by another node. AFAIK the only way of doing this is via the xm destroy command. Probably the only way of doing this is to have a cluster node ssh to the dom0 and then run a setuid program that calls xm destroy.

So, NEW down to 104 again. It was 111 yesterday at dinstall time, but people keep uploading NEW packages (was up at 121). Damn people. :) In other news I finished the project for work I had the last months, which was a nice server migration, getting some 50 machines from woody to sarge, with heartbeat, drbd, bonding and a lot of other things for some redundancy. But as thats finished for now, lets see what Im doing next. Oh, the next 5 days I will be on a trip to Sarajevo and Edinburgh, see this mail for the reason. We should have net there, so in theory I should be able to get more out of that NEW queue in the evenings, but if not - well, go bite in a table. :)