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

postgresqlpostgresql-9.2

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

Best Answer

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.

Related Question