PostgreSQL 14 server not running locally on Ubuntu 22.04

Question

I updated my Ubuntu OS from 20 LTS to 22.04 LTS and installed PostgreSQL from this source. Running, psql -V, I get this:

(PostgreSQL) 14.5 (Ubuntu 14.5-1.pgdg22.04+1)

Initialized the Postgresql with the following:

$ service postgresql initdb
$ systemctl enable postgresql
$ systemctl start postgresql

When running the psql command, it returned this error:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
 Is the server running locally and accepting connections on that socket?

and when running pg_lsclusters, it returned this:

Ver Cluster Port Status Owner Data directory Log file

No files or folders exist in the PostgreSQL directory ls etc/postgresql/.

I also installed the postgresql-contrib, as mentioned in this source. I looked up this source because I wanted to initialize the Postgres server, so I followed the commands mentioned in it.

$ sudo systemctl start postgresql.service
$ sudo -i -u postgres
postgres@ror:~$ createuser --interactive
Enter name of role to add: juzershakir
Shall the new role be a superuser? (y/n) y

Returned the following error messages:

createuser: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
 Is the server running locally and accepting connections on that socket?

Now checking the status of the PostgreSQL service:

$ sudo systemctl status postgresql.service
postgresql.service - PostgreSQL RDBMS
 Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
 Active: active (exited) since Fri 2022年09月09日 12:58:06 IST; 49min ago
 Process: 10493 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 10493 (code=exited, status=0/SUCCESS)
 CPU: 1ms
Sep 09 12:58:06 ror systemd[1]: Starting PostgreSQL RDBMS...
Sep 09 12:58:06 ror systemd[1]: Finished PostgreSQL RDBMS.

I am no expert at this, but it looks like the service starts and closes immediately. Why would this be the case? What steps have I missed?

---

Online sources I have already referred to:

• PostgreSQL: Why psql can't connect to server?

• Psql could not connect to server: No such file or directory, 5432 error?

• could not connect to server: "/var/run/postgresql/.s.PGSQL.5432"?

• psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory

Commands I have tried:

$ sudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

$ sudo service postgresql start --force

$ cat /var/run/postgresql/.s.PGSQL.5432 
cat: /var/run/postgresql/.s.PGSQL.5432: No such file or directory

$cd /tmp
l
 rwxrwxrwt 25 root root 24 KiB Fri Sep 9 13:58:14 2022  ./
 rwxr-xr-x 20 root root 4 KiB Wed Dec 22 11:15:40 2021  ../
 rwx------ 2 jsubuntu jsubuntu 4 KiB Fri Sep 9 12:32:07 2022  .com.google.Chrome.o8bjs4/
 rwxrwxrwt 2 root root 4 KiB Fri Sep 9 12:29:41 2022  .font-unix/
 rwxrwxrwt 2 root root 4 KiB Fri Sep 9 12:30:15 2022  .ICE-unix/
 rwx------ 3 root root 4 KiB Fri Sep 9 12:30:14 2022  snap.canonical-livepatch/
 rwx------ 3 root root 4 KiB Fri Sep 9 12:30:15 2022  snap.snapd-desktop-integration/
 rwx------ 3 root root 4 KiB Fri Sep 9 12:30:16 2022  snap.snap-store/
 rwx------ 3 root root 4 KiB Fri Sep 9 12:30:13 2022  systemd-private-64f08d0504e04acfb3259292fa13580c-apache2.service-ZrAICM/
 rwx------ 3 root root 4 KiB Fri Sep 9 12:30:14 2022  systemd-private-64f08d0504e04acfb3259292fa13580c-bluetooth.service-tGJABQ/
 rwx------ 3 root root 4 KiB Fri Sep 9 12:30:17 2022  systemd-private-64f08d0504e04acfb3259292fa13580c-colord.service-En9sFA/
 rwx------ 3 root root 4 KiB Fri Sep 9 12:30:18 2022  systemd-private-64f08d0504e04acfb3259292fa13580c-fwupd.service-okItPb/
 rwx------ 3 root root 4 KiB Fri Sep 9 12:30:13 2022  systemd-private-64f08d0504e04acfb3259292fa13580c-ModemManager.service-KbJJ4J/
 rwx------ 3 root root 4 KiB Fri Sep 9 12:30:13 2022  systemd-private-64f08d0504e04acfb3259292fa13580c-power-profiles-daemon.service-nguChr/
 rwx------ 3 root root 4 KiB Fri Sep 9 12:30:13 2022  systemd-private-64f08d0504e04acfb3259292fa13580c-redis-server.service-EaKdzC/
 rwx------ 3 root root 4 KiB Fri Sep 9 12:30:13 2022  systemd-private-64f08d0504e04acfb3259292fa13580c-switcheroo-control.service-hLF6lp/
 rwx------ 3 root root 4 KiB Fri Sep 9 12:30:13 2022  systemd-private-64f08d0504e04acfb3259292fa13580c-systemd-logind.service-jMNsHm/
 rwx------ 3 root root 4 KiB Fri Sep 9 12:29:41 2022  systemd-private-64f08d0504e04acfb3259292fa13580c-systemd-oomd.service-ZtaPW4/
 rwx------ 3 root root 4 KiB Fri Sep 9 12:29:41 2022  systemd-private-64f08d0504e04acfb3259292fa13580c-systemd-resolved.service-gHZzR9/
 rwx------ 3 root root 4 KiB Fri Sep 9 12:29:41 2022  systemd-private-64f08d0504e04acfb3259292fa13580c-systemd-timesyncd.service-Ycbvmk/
 rwx------ 3 root root 4 KiB Fri Sep 9 12:30:16 2022  systemd-private-64f08d0504e04acfb3259292fa13580c-upower.service-PBPl2C/
 rwxrwxrwt 2 root root 4 KiB Fri Sep 9 12:29:41 2022  .Test-unix/
 rwx------ 2 jsubuntu jsubuntu 4 KiB Fri Sep 9 12:30:18 2022  tracker-extract-3-files.1000/
 rwxrwxrwt 2 root root 4 KiB Fri Sep 9 12:30:15 2022  .X11-unix/
 rwxrwxrwt 2 root root 4 KiB Fri Sep 9 12:29:41 2022  .XIM-unix/
 r--r--r-- 1 jsubuntu jsubuntu 11 B Fri Sep 9 12:30:15 2022  .X0-lock 
 r--r--r-- 1 jsubuntu jsubuntu 11 B Fri Sep 9 12:30:15 2022  .X1-lock 

$ sudo nano /etc/postgresql/14/main/pg_ident.conf
(Directory doesnt exist)

• Frank Heikens: After installation, did you run initdb ? postgresql.org/docs/14/runtime.html
• Juzer Shakir: Yes indeed! I will update the post for these commands.
• Frank Heikens: And did initdb create the database cluster?
• Juzer Shakir: I ran pg_lsclusters command and it returned the same output as mentioned in the post. So, no it didn't create a cluster.

Answer 1

if service postgresql initdb doesn't create any clusters (which was true in my case) then create a database cluster, like:

sudo pg_createcluster --start (pg_version) (cluster_name)

For ex:

sudo pg_createcluster --start 14 mycluster

To list out all available clusters:

Ver Cluster Port Status Owner Data directory Log file
14 mycluster 5432 online postgres /var/lib/postgresql/14/mycluster /var/log/postgresql/postgresql-14-mycluster.log

As we can see the cluster is now successfully created and when I navigate to ls /etc/postgresql/, a folder named 14 is created which corresponds to the version of PostgreSQL's cluster we initiated. Navigating to directory 14 will return the directory whose name corresponds to the cluster name we gave when we initiated the cluster. Hence my directory looks something like this:

$ ls /etc/postgresql/14/mycluster
  conf.d/  environment  pg_ctl.conf  pg_hba.conf  pg_ident.conf  postgresql.conf  start.conf

---

Creating new role/user: Since I have just installed pg in my system, I don't have any roles available. So I will need to create one via postgres account which is automatically created when we install PostgreSQL.

$ sudo -i -u postgres 
postgres@ror:~$ createuser --interactive
Enter name of role to add: jsubuntu
Shall the new role be a superuser? (y/n) y

The user has now been created as a superuser. Now we need to set the password for it. We can do that by logging in as a postgres super user

$ sudo su postgres

Open PostgreSQL Interactive terminal by: psql:

postgres@ror:/etc/postgresql/14/mycluster$ psql

Lists all the pg users:

postgres=# \du

Now to change the password of an existing user in my case its jsubuntu, I will run the following:

ALTER USER jsubuntu WITH PASSWORD 'password';

If you get an output of ALTER ROLE then the password has been successfully set.

Now let's quit the session with exit command.

So till now, we have successfully created a cluster and a PostgreSQL user which I will use to create databases.

---

Now when I run psql command it shows me a different error:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: database "jsubuntu" does not exist

Let's see the pg log file:

sudo nano /var/log/postgresql/postgresql-14-mycluster.log 

In this file, I see the following error showing up:

2022年09月09日 18:49:41.497 IST [15498] jsubuntu@jsubuntu FATAL: database "jsubuntu" does not exist

Understanding this error message, the part jsubuntu@jsubuntu tells that the user jsubuntu should have a database named jsubuntu. Initially, I didn't think this was true but when other tricks didn't work I created a database with the same name and was successfully able to establish a connection.

$ psql
psql (14.5 (Ubuntu 14.5-1.pgdg22.04+1))
Type "help" for help.
jsubuntu=# 

The name of the psql also changed from postgres to jsubuntu.

---

References:

• Cannot connect to PostgreSQL server