10

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.
Greg
  • 167
  • 4

2 Answers2

5

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.

Daniel Vérité
  • 3,045
  • 16
  • 19
  • I am redoing the install now, but noticed in your answer you suggested the path was wrong do to Ubuntu looking in wrong place for file. Could that be due to the client running on OSX while the server is an ubuntu box? –  Mar 27 '12 at 12:52
  • So i did the reinstall and got the exact same error. I looked around a bit more and found that I didn't have ports open in iptables. After following instructions here: http://www.cyberciti.biz/tips/howto-iptables-postgresql-open-port.html I am able to connect. –  Mar 27 '12 at 13:33
  • I can't say for sure if the reinstall was part of the fix, but this was definitely helpful. So I am giving you the win –  Mar 27 '12 at 13:35
  • When client and servers are on different hosts, psql has to be invoked by `psql -h [optional other arguments]` where is the hostname or IP address (and is not localhost) of the server (ubuntu box in this case). In the question it doesn't look like you were calling it this way so I was assuming all commands were launched on the server. Anyway I'm really positive that reinstalling was a sane measure even if the connection could have been established. –  Mar 27 '12 at 14:11
  • I am calling it like: psql -U postgres -h localhost since i have vagrant port forwarding setup –  Mar 27 '12 at 15:05
2

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

Michael Hampton
  • 244,070
  • 43
  • 506
  • 972
rogelio2k
  • 21
  • 1