2

So yesterday 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, I get the following 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?

Also when runningpg_lsclusters, I get the following:

Ver Cluster Port Status Owner Data directory Log file

And when navigating to postgresql directory ls etc/postgresql/, it has no files or folders in it.

I also installed postgresql-contrib, as mentioned in this source. The reason I looked up this source is that 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

Get 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?

And when I check 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 in this, but from this error message, it seems like the service starts and then closes. Why would this be the case? What steps have I missed?


Online sources I have already referred to:

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)
4
  • 1
    After installation, did you run initdb ? postgresql.org/docs/14/runtime.html Sep 9, 2022 at 8:50
  • Yes indeed! I will update the post for these commands.
    – Juzer Shakir
    Sep 9, 2022 at 8:56
  • And did initdb create the database cluster? Sep 9, 2022 at 9:02
  • I ran pg_lsclusters command and it returned the same output as mentioned in the post. So, no it didn't create a cluster. Sep 9, 2022 at 9:05

1 Answer 1

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.