Testing PostgreSQL patches on Windows using Amazon EC2

Many people who develop patches for PostgreSQL don't have access to Windows machines to test their patches on. Particularly not with complete build environments for the MSVC build on them. The net result of this is that a fair amount of patches are never tested on Windows until after they are committed. For most patches this doesn't actually matter, since it's changes that don't deal with anything platform specific other than that which is already taken care of by our build system. But Windows is not Posix, so the platform differences are generally larger than between the different Unix platforms PostgreSQL builds on, and in MSVC the build system is completely different. In a non-trivial number of cases it ends up with breaking the buildfarm until somebody with access to a Windows build environment can fix it. Lucky, we have a number of machines running on the buildfarm with Windows on them, so we do catch these things long before release.

There are a couple of reasons why it's not easy for developers to have a Windows machine ready for testing, even a virtual one. For one, it requires a Windows license. In this case the same problem with availability for testing exists for other proprietary platforms such as for example Mac OSX, but it's different from all the free Linux/Unix platforms available. Second, setting up the build environment is quite complex - not at all as easy as on the most common Linux platforms for example. This second point is particularly difficult for those not used to Windows.

A third reason I noticed myself was that running the builds, and regression tests, is very very slow at least on my laptop using VirtualBox. It works, but it takes ages. For this reason, a while back I started investigating using Amazon EC2 to do my Windows builds on, for my own usage. Turns out this was a very good solution to my problem - the time for a complete rebuild on a typical EC2 instance is around 7 minutes, whereas it can easily take over 45 minutes on my laptop.

Now, EC2 provides a pretty nice way to create what's called an AMI (Amazon Machine Image) that can be shared. Using these facilities, I have created an AMI that contains Windows plus a complete PostgreSQL build environment. Since this AMI has been made public, anybody who wants to can boot up an instance of it to run tests. Each of these instances are completely independent of each other - the AMI only provides a common starting point.

I usually run these on a medium size Amazon instance. The cost for such an instance is, currently, $0.30 per hour that the instance is running. The big advantage here is that this includes the Windows license. That makes it a very cost-effective way to do quick builds and tests on Windows.

Read on for a full step-by-step instruction on how to get started with this AMI (screenshot overload warning).

The place to start is the Amazon AWS console. If you haven't already signed up for access to the EC2 service, you will need to do that before you get started. The start page of the console contains a convenient Launch Instances button, which will let you get right to the point of starting a new instance.


Once you have clicked this button, you will get to a page where you can choose what machine image to launch. Click on the tab for "Community AMIs", and typepostgres in the box for filtering. This will filter the list to those containing postgres, which should include an image named postgres_images/postgres_win32_msvc_buildenv_20090902.manifest.xml. Currently, the AMI id for this instance is ami-7b20c012. These may change in case I update the images. Click Select for the proper image.


The next step in the wizard will let you configure the instance. As mentioned earlier, I prefer the medium size instance but it works equally well, but significantly slower, with the small one. For this type of usage, you'll normally put 1 in the box for number of instances. The instance comes with a preconfigured administrator password that is postgres, so configure it to use No Key Pair. Finally, you may want to enable some extra firewall rules so you can connect to the machine - I have a security group called Everything from me that simply opens up the firewall completely for access from my machine.


With this done, press the Launch button. If everything went well, that should take you to the confirmation page, with a link back to the list of instances.


Back on the list of instances, you will see your instance in mode starting. Unfortunately it seems that instances can be stuck at this state for quite a while, especially with Windows instances. Up to several minutes.


This will eventually change to running. However, for a Windows instance, this doesn't mean you can start using it. To know when you can actually start using it, you can look at the output of the System Log. You find this option by right-clicking the instance in the list, and picking it from the menu.


The output here would be the kernel boot information if it was a Linux machine for example. For Windows, there is very little information, but there is a note when the machine is ready for use.


When the machine is ready, look at the details pane (the information is visible below the list of instances, once you have clicked on the instance you are looking for). In this pane, look for the Public DNS name of your instance.


You can now connect your RDP client to this hostname. In my case, I use rdesktop on Linux, so I run the following command:

rdesktop -uAdministrator -ppostgres -g1280x960 -rsound:off -rclipboard:PRIMARYCLIPBOARD -b -k sv -5 ec2-75-101-204-145.compute-1.amazonaws.com

Adapt for your screen resolution and hostname. The -k sv parameter is to set the keyboard layout to Swedish - adapt this to your local keyboard, or remove completely for US keymapping. You can obviously use a graphical RDP client as well.

Once the RDP client is connected, you should see the desktop of the machine.


I've found that the easiest and most flexible way to get the PostgreSQL sourcetree on and off the Windows VMs is to use git. For this reason, the machine is preset to do this. To get an up-to-date git environment to work in, click the Create new git environment icon on the desktop. This will create a working directory and pull the latest version from git.postgresql.org. It will also copy in a default build configuration file adapted for the machine.


When the source is downloaded, you can start building. Click the Visual Studio 2005 Command Prompt icon on the desktop. This will set up an environment to do building for you, and drop you in the correct directory. All you need to do to start a build, is to type build and hit enter.


The build is not warning-free, this is normal. But there should not be any errors. The total build time is usually around 7-8 minutes. Once this is done, you can run the regression tests on this new build. To do so, run vcregress check.


The regression tests run pretty quickly, and should always finish without any errors.


At this point you are ready to start testing the specific patch(es) you wanted to test. Again, I have found the easiest way to get the patches to test onto the machine is to simply use a git branch. For example, if you want to test the indexfilter patch, Heikki has made this available on git.postgresql.org. Git here works just as it does on other platforms, so to get a copy of Heikki's patch to test it, run the following:

git remote add heikki git://git.postgresql.org/git/users/heikki/postgres.git
git remote update heikki
git checkout -b indexfilter --track heikki/indexfilter

The MSVC build system sometimes gets a bit confused when you change a whole tree under it. Most of the time it works fine, but just to be safe I recommend you always run clean after doing something like this. Then just run build again to build the patched tree.


When the build is completed, you can run the regression tests again. Luckily for Heikki, it seems his patch worked.


You can leave the machine around for a while, but since it costs money as long as it's booted, you don't want to leave it around indefinitely. To terminate the instance, just go back to the AWS console, right-click the instance, and select Terminate.


A final note about keeping machines around. Once you power down the machine, all your modifications are lost. That's just how the Amazon cloud works. So make sure you have pushed out any local changes you made.

There is another option around this, and this is to use Elastic Block Storage. This is another service in the Amazon cloud that lets you have persistent disks. They are provided at an extra charge, where you pay for the size of the disk and for how many requests you make to it - see the pricing page for details.

To use EBS with this image, create and attach the image. Then make sure you unmount the existing D: drive, and mount the EBS volume there before running the Create new git environment script. Once this is done, the git environment will be created on the EBS volume. The second time you boot an instance and want to re-use this environment, mount the drive on D: again, but do not run the script at all. The environment is there and ready to be used. Performance-wise, building on EBS appears to be roughly the same speed as on the local drive. Obviously, any changes you make on the C: drive will be lost on reboot, but the build system itself never makes such changes.

I haven't quite made up my mind if I should maintain this past my own immediate needs. For this reason, please let me know if you find this useful, and I probably will. Also, if you know if there is a way for me to get statistics from AWS on how many people are using the image, that would be appreciated...


Umm, difficult to set up build environment? What's difficult in installing Visual Studio? Or VS Express? Or the command line tools?

This reminds me of the PostGIS posse crying about how difficult it is to compile stuff on Windows. Funny how I've never even seen the source code for that (or geom or proj) and in less than 30 minutes I had a Visual Studio solution that compiled the whole thing, produced a smaller output etc. Very difficult, yes. But only if you insist on using certain tools (why oh why MinGW?)

Why don't people who don't have Windows machines leave the testing/development to those that do have? I really can't understand this...

Posted on Sep 3, 2009 at 17:09 by Symbiatch.

It's not VS Express. That's pretty easy. Though it is quite a challenge to find the downloads for VS 2005 Express these days. One reason we need to look at porting to VS 2008.

It's all the dependencies. Have you ever tried getting gettext to work properly? Or the KRB5 libraries?

And yes, getting a mingw environment running can be a lot worse. Which is one reason we switched to Visual Studio. But some of the dependencies are actually worse when you use Visual Studio.

Posted on Sep 3, 2009 at 17:20 by Magnus.

It would've helped if you'd written that it's hard to get the build environment up for this particular project :) Otherwise there is this misconception that it just is hard to set up any build environment :P

And yes, you really should upgrade to 2008 and check 2010. I don't think the project files are that different and it compiles with 2008 just fine. I could check the upgrade to 2008 while I'm at it.

Posted on Sep 3, 2009 at 18:20 by Symbiatch.

The reason it's hard to setup is the long list of dependencies, which you can see at http://www.postgresql.org/docs/8.3/static/install-win32-full.html

Best case is that it takes the better part of a day to get all of those setup and running properly to do a Windows PostgreSQL build.

Posted on Sep 3, 2009 at 20:42 by Greg Smith.

Thanks for an excellent write-up. I have been thinking about using Amazon for Windows development for a while. Glad to see it works so nicely for PostgreSQL.

Posted on Sep 4, 2009 at 17:45 by Robert Hodges.

+1 on "just use Express."

Also of note is that the Express toolchain works very well under WINE, and supports 32-bit x86, 64-bit and Itanium.

Posted on Sep 8, 2009 at 18:51 by Toby.

Excellent write-up, Magnus.

Posted on Sep 14, 2009 at 18:59 by Per Buer.


I speak at and organize conferences around Open Source in general and PostgreSQL in particular.


Postgres Open 2017
Sep 6-8, 2017
San Francisco, USA
PGConf.EU 2017
Oct 24-27, 2017
Warsaw, Poland
Dec 4-6, 2017
Tokyo, Japan


Jul 5-7, 2017
St Petersburg, Russia
Jul 4, 2017
London, UK
Amsterdam PUG
Jun 29, 2017
Amsterdam, Netherlands
PGCon 2017
May 23-26, 2017
Ottawa, Canada
Apr 26, 2017
Gothenburg, Sweden
More past conferences