Use a VM when in any doubt

Long experience has taught me that some software is best run in a Virtual Machine where it can do relatively little harm to other components of your system. Right at the top of this list is almost all VPN software. The only exception I usually make is for OpenVPN, which is almost always very well behaved. I have a couple of VM's set up for just this purpose. They normally only get fired up when I need to connect to a client over a VPN.

The other day I needed to connect fairly urgently to a client's network, and they require use of a VPN. The VPN software in question, which is proprietary, is one I know from experience can cause problems. However, I could not get it working on my sacrificial VMs, which run Linux, and they told me that they couldn't get it reliably working on Linux either, and that they all connect from Windows. I didn't have a sacrificial VM running Windows, and so, despite my best judgement, I installed the software on a non-virtual Windows machine, that, among other things, runs a couple of buildfarm animals as well as a number of Linux VMs I am using currently on another project.

Big Mistake.

The VPN worked OK, but, when I was done, the Virtualbox network interfaces were hosed. It took me a long time - literally hours, to work out what had happened, since the problem only became apparent when the VMs were recycled, which was a couple of days after using the VPN software. I tried, in turn, disabling the VPN software, uninstalling the VPN software, rebooting, and updating Virtualbox. Nothing worked. Finally I did a system restore back to the point before I had first installed the VPN software. Suddenly everything worked again.

So today I have spent more hours setting up a Windows VM I can use for this purpose. First I had to go out and buy a legitimate copy of Windows, and then get it set up and installed in a VM on my laptop. I put it there so I still have it available when I'm on the road.

But this software still had more surprises for me. It's set up not to allow VPN connections to be made from remote desktop connections. And this is rather important to me since I don't carry my laptop around my house, and when at home I mostly don't work from the laptop. But it turns out that Virtualbox will run a remote desktop server for you and connect the guest console to it, so that the guest thinks the connection is local even when it's not. Take that, nasty VPN vendor!

All in all this has cost me the best part of two days to fix and configure. I'm more than mildly annoyed by it. But I have learned my lesson. I will never run this sort of software from a non-virtual host again.

unnest_ordinality extension

The other day Josh Berkus was working on one of our clients and needed a fast way to unnest some arrays with ordinality, that is to have a row number attached to each element. The usual way that is recommended for doing this is to use subscripting with generate_series(), or calling unnest() in a plpgsql function and then returning the elements one by one. Both of these turned out to be not fast enough, and he was resorting to some fairly ugly code to get around the speed issue. I offered him a simple C function that would do this very quickly, and we're releasing it publicly today. In use on our client's site it has been extremely fast and simple to use.

example:

andrew# create extension unnest_ordinality;

    andrew=# select * from unnest_ordinality('{a,b,c,d,e,f,g}'::text[]);
     element_number | element 
    ----------------+---------
                  1 | a
                  2 | b
                  3 | c
                  4 | d
                  5 | e
                  6 | f
                  7 | g

The package is available on The PostgreSQL Experts Inc Github Repository and also on PGXN

Vagrant buildfarm client

I've recently been exploring use of the nifty vagrant package for a project I'm about to start working on. So today in an idle moment waiting for some other stuff to complete I created a simple setup to allow you to create a complete and working, unregistered buildfarm client, all stashed away nicely in a ScientificLinux 6.3 x86_64 VM for you. If you have vagrant installed and working with VirtualBox, all you have to do to run this is:

git clone https://bitbucket.org/adunstan/sl6-x64-buildfarm-vagrant.git
cd sl6-x64-buildfarm-vagrant && vagrant up

Once the machine is up and running you can login and run the client straight away. It will create a git mirror, and configure, build, and test Postgres. All from one command:

cd ~vagrant/bf && perl run_build.pl --test --verbose

Enjoy.

Backport of 9.3 json enhancements

I've done a backport of the 9.3 JSON enhancements, as a 9.2 extension, or rather as much of them as it's reasonably possible to backport. There are some caveats to using this extension, so please read the warnings carefully before you use it.

If you use PGXN you can get it from there, or it can be got from the public git repository at bitbucket.

Thanks to David Wheeler for prodding me to finish this up.

Enjoy.

Linux/ZFS is great

A few days ago the ZFS on Linux people finally produced a production release of their latest code. I had been playing around a bit with it on an Ubuntu VM, but yesterday I installed it on a customer's  RedHat 6.2 production system. This system will be used to store PostgreSQL backups made with the barman package, and the feature we most wanted to use was the builtin file system compression - mainly to save on space requirements. Installing it and turning a SAN volume into a ZFS volume turned out to be incredibly easy and painfree. And it's a win-win situation. The IO time saved by using a compressed file system has reduced the time for a backup by about 60%, and the space saved in our case is about 80%. All in all I'm impressed, and I'm certainly going to be trying out some more of its features. It will be nice to ascertain what modes will be best suited to production database use.