I can currently not connect to my Postgresql server (9.3) from pgadmin (Running on Ubuntu 14.04). The server according to pg_lsclusters is on port 5433.
Ver Cluster Port Status Owner Data directory Log file
9.1 main 5432 online postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log
9.3 main 5433 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log
I can connect to the 9.1 server with pgadmin, but when I try to access the 9.3 server I get the error that the password can't be authenticated:
An error has occurred:
Error connecting to the server: FATAL: Passwort-Authentifizierung für Benutzer „postgres“ fehlgeschlagen
FATAL: Passwort-Authentifizierung für Benutzer „postgres“ fehlgeschlagen
The 9.1 server has the following lines in pg_hba.conf:
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 md5
#host replication postgres ::1/128 md5
and the 9.3 server has the following in the pg_hba.conf:
# Database administrative login by Unix domain socket
local all postgres md5
# 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
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 md5
#host replication postgres ::1/128 md5
The difference is the "peer" or "md5" in the first two active lines. I am going to try switching those to peer next. Am I on the right track to a solution? (Edit: No, this did not help).
Password
I tried changing the default password of the server using:
$ sudo -u postgres psql
psql (9.3.4, Server 9.1.13)
postgres=# \password
I have no idea why this didn't work. Maybe the command only affects the 9.1 server and not the 9.3 server (Hopefully I can kill the old one now and properly set up the 9.3 server).
trust
mode, then login using thepostgres
super user (no need for asudo
, just usepsql -U postgres
) change the password, then changetrust
back tomd5