PostgreSQL 9.6 won't connect on Ubuntu 16

Question

I installed PostgreSQL 9.6 on my Ubuntu 16.04 system. It seems like it's up and running, but when I try to make a connection it just fails. When I run the status command I see that it's always saying exited for some reason. I see lots of things in a google search but none seem to be helping.

I changed the first entry in the pg_hba.conf to be local all postgres trust

% ps augxw | grep postg
postgres 769 0.0 0.3 303964 24384 ? S 22:13 0:00 /usr/lib/postgresql/9.6/bin/postgres -D /var/lib/postgresql/9.6/main -c config_file=/etc/postgresql/9.6/main/postgresql.conf
postgres 772 0.0 0.0 303964 3956 ? Ss 22:13 0:00 postgres: 9.6/main: checkpointer process
postgres 773 0.0 0.0 303964 3956 ? Ss 22:13 0:00 postgres: 9.6/main: writer process
postgres 774 0.0 0.0 303964 3956 ? Ss 22:13 0:00 postgres: 9.6/main: wal writer process
postgres 775 0.0 0.0 304408 6572 ? Ss 22:13 0:00 postgres: 9.6/main: autovacuum launcher process
postgres 776 0.0 0.0 158964 3204 ? Ss 22:13 0:00 postgres: 9.6/main: stats collector process
ubuntu 1492 0.0 0.0 12944 936 pts/0 S+ 22:14 0:00 grep postg
%
% sudo su - postgres
$ psql -h localhost
psql: could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?
$ exit
logout
% systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Thu 2017-03-30 22:13:57 PDT; 1min 19s ago
Process: 901 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 901 (code=exited, status=0/SUCCESS)
Tasks: 0
Memory: 0B
CPU: 0
CGroup: /system.slice/postgresql.service

Mar 30 22:13:57 ip-172-31-9-223 systemd[1]: Starting PostgreSQL RDBMS...
Mar 30 22:13:57 ip-172-31-9-223 systemd[1]: Started PostgreSQL RDBMS.

Is that Desktop or Server? How did you get 9.6? Clean install of Ubuntu Desktop 16.04.2 LTS gets only 9.5. Did you add official Postgres repo? — Łukasz Kamiński
I installed the "deb apt.postgresql.org/pub/repos/apt xenial-pgdg main" repository via the instructions at askubuntu.com/questions/831292/… — Gargoyle

Łukasz Kamiński Łukasz Kamiński · Accepted Answer · 2017-04-03T06:42:13

You are connecting using TCP/IP socket, but local refers to unix-domain socket.

The first field is the connection type: "local" is a Unix-domain socket, "host" is either a plain or SSL-encrypted TCP/IP socket, "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a plain TCP/IP socket.

I did install postgresql-9.6 from that repo and:

host all postgres 127.0.0.1/32 trust does what you want to do
postgres@lkaminski-ubuntu-desk:~$ psql works out of the box, no need to change config. It is using unix-domain socket that is already trusted. So you can just drop -h localhost and no need to edit configs.

$ sudo grep -e "^[^#]" /etc/postgresql/9.6/main/pg_hba.conf

local   all             postgres                                peer
host   all             postgres         127.0.0.1/32            trust
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

PostgreSQL 9.6 won't connect on Ubuntu 16

2 Answers