Home > database >  postgresql - psql always goes to port 5432, even when 2 versions of postgresql are running
postgresql - psql always goes to port 5432, even when 2 versions of postgresql are running

Time:02-17

I have 2 versions of postgresql running on Suse SLES, 9.6 and 10.7. The 9.6 version is running on port 5432, and 10.7 is running on port 5433. All the executables for each version are in the directory for that version, including psql.

If I execute the default psql, I get psql for version 9.6.3:

psql (9.6.3)

However, when I try to execute psql for version 10.7:

/usr/pgsql-10/bin/psql

it opens the version for 10.7, but reading from postgresql 9.6.3:

psql (10.7, Server 9.6.3).

If I try to give it the port number for 10.7:

/usr/pgsql-10/bin/psql -p 5433

it demands a password, but rejects the password for the user postgres that I know works in other contexts.

I have spent considerable time searching for an answer to this question, and I am sorry I have not found anything helpful. Thanks for any suggestions!

CodePudding user response:

There are two issues in play here.

1)

I su to postgres: su - postgres This requires a password, and the password that works here, does not work when psql wants/demands a password.

The OS postgres user is not the same as the database postgres user.

  1. On a fresh install the database postgres user does not have a password. Generally speaking the auth method for the user is set to either trust, ident or peer. See here pg_hba.conf what they mean. The key to a solution is to modify the pg_hba.conf to allow the user to connect in order to be able to create a password. The easiest way is to set a connection method, best practice local(socket) to trust. Then you can connect without a password and create one.
  • Related