As the title says, I've upgraded the PostgreSQL database from version 8.4 to 9.1.
My pg_hba.conf file contains following:
local all all trust
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
#local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
I also ran these commands:
sudo gem uninstall pg
sudo apt-get remove libpq-dev
sudo apt-get install libpq-dev
sudo gem install pg
sudo service postgresql restart
When I run psql from the command line all works properly, but here's what happens when I try to connect from the Ruby console:
$: /va$ sudo pry
[1] pry(main)> require 'pg'
=> true
[2] pry(main)> conn = PG.connect( dbname: 'mydb' )
PG::ConnectionBad: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
from /usr/local/lib/ruby/gems/1.9.1/gems/pg-0.17.1/lib/pg.rb:40:in `initialize'
Your Pg gem is using a libpq version that defaults to a different unix_socket_directory to the one your new server uses. Most likely your new server uses /tmp and your old one used /var/run/postgresql.
You can force a TCP/IP connection by specifying localhost as the target host to connect to, e.g a connstring like dbname=fred host=localhot user=bob, or however you're giving the parameters to the Pg gem. That's not really the best answer, though.
Instead, you should either:
pg gem against the updated libpq from your new PostgreSQL install. To do that, set PATH so that the pg_config from the new PostgreSQL install is first on the path before rebuilding the Pg gem. You can check the version with pg_config --version.or
unix_socket_directory to connect to explicitly, with host=/tmp dbname=fred user=bob. The host parameter can take a hostname or a path to a socket directory. Doing this means you're still using nice efficient unix sockets for your sessions and not paying unnecessary TCP/IP overheads.