Joerg Jaspert: PostgreSQL Continuous Archiving and Point-In-Time Recovery (PITR)
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-ptyin 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 = 12hThe 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
- Stop postgres
- Move away the cluster.
mv /var/lib/postgresql/8.2 /var/lib/postgresql/8.2_old
- 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.
- 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
- Copy possibly unarchived WAL files from /var/lib/postgresql/8.2_old/main/pg_xlog into the newly created pg_xlog.
- 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.
- 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. - Start the server, it will go into recover mode and proceed to read through all the archived WAL files it needs.
- Look at the database and decide if you like what you see.
I wonder if things could go any worse if we actively tried?
So despite going back to see the cardiologist on Monday afternoon, concerned
about there possibly being still some sort of infection, and being told
there wasn't, on Thursday morning, the top part of the incision was looking
swollen.
It looked even more so by the evening when I got home from work, and Sarah
emailed a photo to the cardiologist, and he agreed it didn't look too crash
hot, so he phoned a prescription for another, longer course of antibiotics
through to the pharmacy, but too late to get filled last night.
By bedtime it was looking even more ready to blow, and sure enough, around
1am (it's always 1am) it erupted everywhere. Fortunately (for me), I missed
all the gory details, but I think there was a fair bit of fluid built up in
there.
So we went back to the clinic this morning, and they decided that they
needed to open up part of the incision to let it drain and heal properly.
They took another swab to run another culture, and then got a physician's
assistant to take to her with a scalpel. I've seen my poor wife go through
too many painful things in the last six months. Today's was no better. Most
of the problem was the local anaesthetic injections, but watching them open
up the incision again wasn't much fun either. No one should have to go
through as much shit in a short time as what Sarah has had to. It's just not
fair.
She now has a gaping hole in her chest with some gauze packed in it, which
has to be changed twice a day (by lucky old me). The idea is to let it heal
from the bottom up, rather than the top down, as it seems an abscess had
formed in the wound, and that was were all the trouble was. So by leaving it
all open, it can drain properly, and heal from the bottom up. $DEITY only
knows what sort of mess it's going to make of the incision area, but we can
deal with that later. They reckon it'll take another 4-6 weeks to heal.
I'm really hoping now that with it draining, and another course of
antibiotics, Sarah will be in much less pain.
I just did the evening gauze change. Fun. The opening in the incision is
longer than I remembered them making this morning. It's about an inch long,
and a centimetre deep, and there's a small hole in the bottom that
presumably leads to the "interior". It's not as bad looking as I thought it
might be. Packing the gauze in didn't seem to hurt Sarah too much, which is
just as well.
This really is the pits. I really hope this is the end of this, and the
infection goes away for good, and the incision heals quickly. In terms of
recovery, this is a pretty major setback in my opinion. From now on, we
won't be relying on the cardiologist for surgical opinions, we'll be
insisting a member of the surgical team checks Sarah out.
Recently,
I've gone over the design for "dammit" again, and I think I can even get away with non-specialised nodes for the various compilation tasks in the work tree, which will make support for additional input languages pluggable, an idea I tossed in the beginning for fear that it might not be doable. So I finally get to solve one thing I found annoying with automake. :-)
The other big feature I'm excited about is having two different modes of importing stuff from libraries, with and without reexporting the ABI. Depending on that, any newly built library will incorporate the SONAME of the dependent library into its own, and its development package will depend on the other -dev package (obviously, for that to work, I'll have to add package building to it as well :-P )


Saturday 2 February 2008 was the annual meeting of
/me++

As 
Early next morning we had breakfast on the wet grass and then started our
way to Sant Joan through a deliciously well equipped and maintained cycling
track, which unfortunately only lasts for 12 kilometres through a splendind
landscape. After having breakfast in the old railway station in Sant Joan, we
abandoned the Ruta del Ferro to take a road to Olot via Sant Pau de
Seguries, where we had to climb a small mountain and then descend through the
Vall de Bianya until we got to Olot.






Yay! I got my new toy two days ago but I wasn't able to install debian on it booting from USB with several versions, when I almost got it there was some issue with the ethernet driver (with sarge) or that didn't loaded the correct driver for detecting my hard drive or worst, with the
So today I'm a little bit lazy and I've got the day off work. As my previous plan suggested I wanted to spend at least some of the day tackling semi-random bugs. Earlier I picked a victim:
Say you're working on a Debian box for which you don't have root access, you
need to get some source packages, but there is no