Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

3 Answers 3

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.

share|improve this answer
    
Apparently he uses password authentication with the 9.1 cluster. But thanks, I did not realize so far that this is the default setup. –  dezso Oct 8 '13 at 20:16

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.

share|improve this answer

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).

share|improve this answer
    
Thanks for the reply. I thought that it would be installed under the same postgres user account. And therefore, I should be able to initially connect to it via su postgres. But, that doesn't seem to be the case. The OS account "postgres" won't connect. –  David S Oct 5 '13 at 18:18
    
Actually, the OS and the DB users are different. The OS user is the same for all DB instances, but the one with which you connect is the DB user. –  dezso Oct 5 '13 at 20:19
    
And I realized that I linked an other question than I wanted, now updated the link. –  dezso Oct 5 '13 at 22:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.