Search code examples
postgresqlsocketspasswordspsql

How do I access PostgreSQL by both socket and port?

I have started a PostgreSQL process as follows:

pg_ctl start -w -D /path/to/data -l /path/to/log -o "-F -k /path/to/unix/socket -h ''"

(alternatively -h '*' instead of -h '')

Aside, references for pg_ctl (the outer function) and postgres (the -o parameter)

and created a user (with password) and database:

createuser -P admin
createdb -O admin db

I can connect to the database via the unix socket (does not trigger a password prompt):

psql -h /path/to/unix/socket -U admin -d dbname

but connecting via the TCP port fails:

psql -h localhost -U admin -d dbname
Password for user snaprevs_admin:
psql: FATAL: password authentication failed for user "admin"

What do I need to change so that both the unix socket and TCP connections work as expected?

Solution

  • If you are performing a custom pg_ctl launch, you should check that there isn't a default service already running. If there is it will consume the default TCP port.

    To stop the default service and prevent it from starting on next boot:

    sudo systemctl disable postgresql --now

    If you only want to stop it temporarily:

    sudo service postgresql stop

    You should now be able to stop and re-start your custom service (with -h '*'), then log in over TCP.