Problems while connecting to a newly installed PostgreSQL 9.2 on same box as 9.1

Question

I have a new project at work that is using a PostgreSQL 9.2. But, I'm still having to support a project that uses 9.1. So, I'm trying to configure my local dev box to have both installed.

I have gotten 9.2 installed and confirmed it runs fine.

However, I can't connect to it.

xxxxxxx@xxxxxxx-desktop-ubuntu:~$ sudo /etc/init.d/postgresql restart
[sudo] password for xxxxxxx: 
 * Restarting PostgreSQL 9.1 database server                                                                     [ OK ] 
 * Restarting PostgreSQL 9.2 database server                                                                     [ OK ] 
xxxxxxx@xxxxxxx-desktop-ubuntu:~$ su postgres
Password: 
postgres@xxxxxxx-desktop-ubuntu:/home/xxxxxxx$ psql -h localhost -p 5432
Password: 
psql (9.2.4, server 9.1.9)
WARNING: psql version 9.2, server version 9.1.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=# \q
postgres@xxxxxxx-desktop-ubuntu:/home/xxxxxxx$ psql -h localhost -p 5433
Password: 
psql: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"
postgres@xxxxxxx-desktop-ubuntu:/home/xxxxxxx$ 

So, how do I connect to this new instance? I thought that the Ubuntu/OS postgres user would allow me to connect, but it doesn't.

Other info:

• 9.1 is on the default port: 5432
• 9.2 is on port: 5433
• Using Ubuntu 12.04

Answer 1

You need to use

psql -p 5433

instead of

psql -h localhost -p 5433

because the default Ubuntu access control setup has password authentication over TCP/IP but peer authentication over Unix-domain sockets.

Answer 2

The two installations are two completely different database clusters, each with its own users, databases, etc. So unless you've configured the same password for the postgres user, you can't use the same here and there. From the error message I would say that everything else is fine with the new cluster. Try to find out what's the new password, or if you are really lost, you can still reset it (it is about PostgreSQL on Windows but the steps are very similar on linux).

Answer 3

You might be interested in looking at pg_wrapper options since psql is handled by pg_wrapper Debian/Ubuntu. It's transparent in a single cluster environment, but in a multi-cluster environment it adds the option to specify the cluster.

NAME
       pg_wrapper - wrapper for PostgreSQL client commands

SYNOPSIS
       client-program [--cluster version/cluster] [...]

       (client-program: psql, createdb, dropuser, and all other client
       programs installed in /usr/lib/postgresql/version/bin).

So the "debian-way" would be, when you're logged in a the postgres OS user:

$ psql --cluster 9.1/main  # to use 9.1

and assuming the other cluster is also named main (the names of the clusters can be seen with pg_lsclusters if unsure)

$ psql --cluster 9.2/main  # to use 9.2

You don't have to specify -h localhost and the port number, so it connects through Unix domain socket instead of a TCP/IP connection, which differs in these two ways:

• it uses the peer method to authenticate the postgres user locally per the default first rule in pg_hba.conf, so it doesn't need a password.

• it doesn't use the SSL encryption, which saves CPU cycles.