Postgresql on OSX and Windows (using VMware fusion)

NB! I decided not to use this setup after all. I had different problems with postgres, authentication and lion. I am not sure why this happened. I decided to still publish this in case anyone finds it (or parts of it) useful. Now I setup a port forward to a Linode VPS with Bitvise Tunnelier which is free for non-commercial use.

Original post:
--------------------------------------------------------------------------------------------------------------------------------
I wanted to setup a postgresql server on my mac os x, but also share it with my virtual Windows machine. Whenever I want to code in C# I use Visual Studio on Windows, and since I run Postgres on Linux i wanted a "similar setup" on my development machine.

To do this you need to have OS X installed with VMware Fusion and a virtual machine with Windows.

The following 3 steps will be performed.
1. Verifying setup
2. We install postgresql on Mac os x
3. We configure VMware to use a static Ip-Address, to ease with port forwarding.
4. We set up port forwarding from the host to the virtual machine.

Verify your setup

Make sure you have VMware Fusion installed and have a virtual network adapter on the host machine called vmnet8 with the IP address 172.16.84.1.

You can do this by entering: $ ifconfig

Please note that OS X Lion already comes with a version of postgres, so make sure you don't confuse this installation with the one from brew. For more info see this article: http://goo.gl/MW8Fc

Installing postgresql on OSX


# Install using Homebrew
$ brew install postgresql

Homebrew installs in /usr/local/Cellar/postgresql/9.0.4/bin
(Optional) For more info you can type: $ brew info postgresql
(Optional) Or this to get the full path: $ brew info postgres | awk '{print $1"/bin"}' | grep "/postgresql/"

# Initialize database, has to be run one time after install
$ initdb /usr/local/var/postgres

If this is your first install, automatically load on login with:
$ mkdir -p ~/Library/LaunchAgents
$ cp /usr/local/Cellar/postgresql/9.0.4/org.postgresql.postgres.plist ~/Library/LaunchAgents/
$ launchctl load -w ~/Library/LaunchAgents/org.postgresql.postgres.plist

Or start manually with:
$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

And stop with:
$ pg_ctl -D /usr/local/var/postgres stop -s -m fast

$ sudo nano /usr/local/var/postgres/postgresql.conf

change the listen_addresses line into this (note the IP, this is the host on the virtual network):
listen_addresses = 'localhost,172.16.84.1'

$ pg_ctl -D /usr/local/var/postgres reload

pg_ctl can be used to reload config! Be aware that you cannot shut it down without
$ launchctl unload -w ~/Library/LaunchAgents/org.postgresql.postgres.plist
$ rm ~/Library/LaunchAgents/org.postgresql.postgres.plist


Now make sure your virtual machine can access postgresql on the host

$ sudo nano /usr/local/var/postgres/pg_hba.conf

Now add the following lines at the bottom:

# virtual machine
host    all             all             172.168.84.0/24         md5

$ pg_ctl -D /usr/local/var/postgres reload

Verify that you can still connect from localhost:
$ psql -h localhost -d postgres

Configuring VMware Fusion to use static IP

$ open "/Library/Preferences/VMware Fusion/vmnet8/
Now locate the file called nat.conf and open in your favorite editor


Now perform the following change to the virtual machine. The host name doesn't have to match, only mac-address.

host Windows7 {
hardware ethernet 00:0c:29:ff:ff:ff;
fixed-address 172.16.84.20;
}

Setting up port forwarding

In order for your virtual machine to talk with the postgres on the host we need to set up some port forwarding.

On the OS X host:
$ brew install autossh

And then every time you want to forward ports (this could also be configured to be run automatically on boot or net-up):

$ autossh -M 20000 -q -N -o "ServerAliveInterval 60" -o "ServerAliveCountMax 3" -L 172.16.84.1:5432:127.0.0.1:5432 somename@somehost -p 22

somename@somehost can be any server you have access to.. I am sure can do the same thing with nc (net cat), but I haven't figured out how to do this yet or how to resume connection if it breaks.

Now you should have a fully functional postgresql on your Mac, that you can access from your virtual machine (for instance Windows). Just install pgadmin and try to connect to 172.16.84.1.

Some useful commands to get you started:
$psql -h localhost -d postgres
CREATE USER testuser WITH PASSWORD '123abc';
CREATE DATABASE mydb;
GRANT ALL PRIVILEGES ON DATABASE mydb TO testuser;

Now verify that you can login, and then create a table
$ psql -h localhost -d mydb -U testuser -W
CREATE TABLE films (title varchar(20));
ALTER USER testuser WITH ENCRYPTED PASSWORD '123abc';

References:

http://www.rubyinside.com/how-to-install-and-use-postgres-on-os-x-for-ruby-and-rails-development-4999.html

http://communities.vmware.com/thread/329883


Helpful comments and tricks are more than welcome!