Announcing walctl for PostgreSQL Transaction Log Management

March 27th, 2014 | Published in Database, Tech Talk | 2 Comments


I’ve managed to convince my employer to open source one of the tools I recently wrote. That tool goes by the name of walctl, and I believe the importance of this kind of tool can not be overstated.

The PostgreSQL Write Ahead Log (WAL) files are key to crash recovery, point in time recovery, and all standby use not derived from streaming replication. WAL files are extremely critical to proper database operation. Yet their archival is treated as an afterthought. Some people use regular cp while others go as far as to use rsync to send data to a replica server.

This isn’t enough. A much safer architecture is to involve three servers. One server produces WAL files. One server acts as a storage and distribution location. One (or more) final server consumes the WAL files as necessary. In instances where streaming replication gets disconnected and the replica is too far behind for the WAL files the master server has available, the archive is a good mechanism for catching up.

Well, wallctl does all of that. It forces you to do all of that. Just set up a couple SSH keys prior to installation, and it needs nothing else. I added a database clone tool as a convenience which needs superuser access to the master database, but otherwise, walctl is very unobtrusive. This toolkit is simple, and will have a few limited advancements in updated versions. It’s meant to be small, fast, and light, following the UNIX philosophy of doing one thing well. In fact, I wrote this tool after examining several other PostgreSQL WAL and replication management systems. Almost all of them require transmitting WAL files directly from the master server to one or more slave systems. But this presumes you only have one replica, or forces the master server to do much more work by contacting several systems in a row. Why not let the slaves do all the hard work?

I highly recommend all DBAs use a WAL management tool of some kind, no matter which. Barman and repmgr are great alternatives that do much more than walctl. But if all you want to do is stash your WAL files in a safe location that multiple replica servers can utilize, this is the easier path.


Tags: , , ,

2 Comments

Feed
  1. Alexey Klyukin says:

    March 28th, 2014 at 6:44 am [#]


    Hi,

    That looks like quite a nice tool. My question would be, what happens if the remove WAL storage itself goes away, do you have any redundancy there in terms of tooling, i.e. additionally syncing the segments to yet another host, or having the master copy the files to multiple external archive locations, or, perhaps, synchronization of the filesystem/device level?


    1. Shaun says:

      April 11th, 2014 at 2:56 pm [#]


      That could be added. But so far, I’ve been depending on PostgreSQL itself to handle that. When the archive_command fails, PG just saves the file and tries to archive it again later.

      Of course, this doesn’t solve the issue of getting the WAL files somewhere safe, but this tool is still in its infancy. The problem with adding multiple hosts based on availability is, while the files are safe, they could potentially be spread across several systems. You need to consider the effort of merging these later to really have a safe WAL archive. Though a multicast isn’t out of the question. :)


Sorry, this post is closed to comments.