Virtualbox running PostgreSQL

If like to keep the PC I use for development as clean as possible, to reduce the odds of “other installed stuff” influencing whatever I’m writing. Whenever I need to install some kind of server-type software on it, I prefer to use small virtual machines to install them in. Like a sandbox running the server, which I can just start and access from the host machine whenever I need it. Another big advantage of this approach is that it allows me to just copy the entire virtual machine to another machine and run the server there, for example on a colleague’s machine. Below are some tips on getting the open source database server PostgreSQL running on a virtual machine, accessible from the host (or any other machine on your network).

A good choice for quickly setting up a virtual machine is VirtualBox. It used to be Sun’s and since the recent acquisition of Sun by Oracle, it’s now Oracle VirtualBox. VirtualBox runs on Windows, Linux and OS X and you can install a variety of OSes on it. I run Windows 7 on my machine, but it runs several virtual boxes, including ones with Debian, FreeBSD, Ubuntu and an old copy of Windows XP. Remember that, even though the virtual machine is .. well, virtual, you still have to have a valid license for any OS you run on it. So, I’d recommend going with something free and simple, like an Ubuntu Minimal CD Image installation. You get some decent options to install during installation and beyond that, it’s just a nice, light Linux installation with a desktop. You just mount the iso as a disc in the virtual CDROM drive and boot the virtual machine.

Getting PostgreSQL up and running on the virtual machine works the same as doing it on a real machine, so I’ll assume that’s not a problem. The tricky part is getting to the PostgreSQL server on the guest from the host (and the rest of the network). To get that working, you need to follow these steps:

  • Attach the network adapter of your virtual machine to NAT.  You can change this option in various ways from the VirtualBox Manager, using the VirtualBox window running the guest itself (Devices – Network Adapters …) or by rightclicking on the network icon of the virtual machine window. Attaching it to NAT will give it a network address in the range of 10.0.2.x by default. Using Bridged, Internal or Host-only won’t work with this solution.
  • Forward the port from the virtual machine to a port on the host machine. The default port for the PostgreSQL server is 5432. A good description on how to forward a port on a VirtualBox is this blogpost.
  • Find the “data” directory for your PostgreSQL server and edit the pg_hba.conf. You’ll need to add a rule to allow access to the server from other machines in your network. There’s some explanation in the file itself, but a rule like “host all all md5” will work. It gives access to all databases for all users from any machine whose IP-address starts with “10.0.2” (so, any other machine in the same virtual network as the guest) using a password (md5 hash check).
  • Now, you can just start up the virtual machine whenever you need a PostgreSQL server and just access it from the host as if it were running locally. No services installed on the host, no files cluttering your file system and if you need a PostgreSQL server elsewhere, or you just want to bring the whole thing over to another location (data and all), it’s a simple as moving the .vdi file.

    One more tip: before installing anything else on a virtual machine, but after installing the OS, clone the VirtualBox. That way, if you decide to do things differently, or if you need more virtual machines, you don’t have to go through the OS setup process again.

    Published by

    Jaap van der Velde

    I live and breathe software, love games and spent many a vacation touring Europe on my motorcycle. Currently diving, riding, hopefully flying and gaining perspective around Oz.

    3 thoughts on “Virtualbox running PostgreSQL”

    1. Hello, great guide, I have noob question though 🙂 Can you connect through NAT the other way around ? PostreSQL running on Host and connect from Guest ?

    2. Thanks, you’re welcome. You should be able to connect to a PostgreSQL instance running on the host from the guest fairly easily. Figure out the address of the host from inside the Virtualbox, either by running ‘ipconfig’ from a Windows guest, or ‘netstat -rn’ from a Linux guest.

      The address you’re looking for is the gateway address for that machine. You should be able to connect to most services running on the host from within the guest on that ip. Too allow connections from the guest on the PostgreSQL server, the process is pretty much the same as with the inverse: check what IP your Virtualbox is on and make sure connections from that IP are allowed on the PostgreSQL server.

    Leave a Reply

    Your email address will not be published. Required fields are marked *