Unable to connect to Postgres on Vagrant Box - Connection refused?

First off, I'm new to Vagrant and Postgres.

I created my Vagrant instance using http://files.vagrantup.com/lucid32.box without any trouble. I am able to run vagrant up and vagrant ssh without issue.

I followed the instructions with one minor alteration, I installed the "postgresql-8.4-postgis" package instead of "postgresql postgresql-contrib".

I started the server using:

postgres@lucid32:/home/vagrant$ /etc/init.d/postgresql-8.4 start

While connected to the vagrant instance I can use psql to connect to the instance without issue.

In my Vagrantfile I had already added:

config.vm.forward_port 5432, 5432

but when I try to run psql from the localhost I get:

psql: could not connect to server: Connection refused
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

I'm sure I am missing something simple. Any ideas?

Update:

I found a reference to an issue like this and the article suggested using:

psql -U postgres -h localhost

with that I get:

psql: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

Solution 1:

The instructions from the blog post you mentioned are not right at all for ubuntu: they use pieces from the installation of a self-compiled server that don't mix well with a packaged version.

One should not create /usr/local/pgsql/data and run initdb to that directory, because the ubuntu package uses /var/lib/postgresql/<pg-version-number>/<cluster-name> and runs initdb on behalf of the the user.

The error mentioning "/tmp/.s.PGSQL.5432" shows that the expected location for this file is incorrect (for ubuntu). It should be in /var/run/postgresql. This is probably due to running initdb manually with parameters that are incompatible with ubuntu.

The postgresql.conf and pg_hba.conf files to edit to enable non-local connections should be inside /etc/postgresql/8.4/main, and not /usr/local/pgsql/data.

The /etc/init.d/postgresql-8.4 should be launched by root (as everything else in /etc/init.d), not by the postgres user.

PGDATA should not be set manually, because again it really gets in the way of how the ubuntu postgres packages works.

My answer would be to purge and reinstall the postgresql-8.4 package without following any of the instructions from the blog post. postgresql-8.4-postgis depends on postgresql-8.4 so it will be removed as well. Also make sure to undo the setting of PGDATA in /etc/bash.bashrc.

Solution 2:

You may find my cookbook useful. I just posted it on github. It configures Ubuntu 12.04 LTS with PostgreSQL 9.1.