Connect to Vagrant Postgres Database via pgAdmin3 on Mac

For the past year or so I’ve been developing locally at work using Vagrant. After making some updates to models I found I needed to be able to easily inspect my postgres database in the Vagrant virtual machine. Ideally I could use pgAdmin3 to connect to that database and luckily it wasn’t too hard. There may be a step or two in these instructions that aren’t needed but I’m including everything I did because I know that it works.

First we need to forward a port in the Vagrant file so our host can connect. If you’re using Vagrant you’ve already done this for your connection to your web server. I simply opened the file Vagrantfile and added the following line.

 config.vm.network "forwarded_port", guest: 5432, host: 65432

Then I told it to listen for all connections by connecting via SSH to vagrant and editing the following file:

sudo nano /etc/postgresql/9.1/main/postgresql.conf

Uncomment the following line and edit it to just be *

listen_addresses = '*'

Edit the pg_hba.conf file to allow the connection.

sudo nano /etc/postgresql/9.1/main/pg_hba.conf

Add the following line at the end.

host    all             all             10.0.0.0/16             md5

Now it’s critical that you restart postgres for your changes to take effect.

sudo /etc/init.d/postgresql restart

Now on your host machine fire up pgAdmin3 and add a new server with the following:

• Description = Whatever you want to name your server
• Hostname = localhost
• Port = 65432 (or whatever port you forwarded earlier on the host in your Vagrantfile)
• Username = YOUR_DATABASE_USERNAME
• Password = YOUR_DATABASE_PASSWORD

That should be it. You should be able to connect to your database via pgAdmin3 now! Yea!