0

I have quite an exciting and bit frustrating situation.

Postgresql -server (on a desktop Ubuntu):

# I have successfully installed it
# I am trying to start the server but I am not seeing the terminal 
# change from Kia@office ubuntu >> to the postgresql #
# Here is response for various codes: 
kia@officeUbuntu:~$ sudo systemctl start postgresql

kia@officeUbuntu:~$ psql

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

kia@officeUbuntu:~$ 

kia@officeUbuntu:~$ pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file

14  main    5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log

15  main    5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log

Postgresql-Client (on a raspberry Pi):

pi@raspberrypi:~ $ psql --host 10.173.65.37 --username postgres --password
Password: 
psql (13.9 (Raspbian 13.9-0+deb11u1), server 14.7 (Ubuntu 14.7-1.pgdg22.04+1))
WARNING: psql major version 13, server major version 14.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \dt
Did not find any relations. # This means there are no tables in the database yet

# Above response implies that we are able to establish remote connection with the postgresql server. Also, terminal `pi@raspberry:` changed to the `postgres=#`

My question: As you can see above, I am not able to active and enter inside the Postgresql server on the very computer it is installed but I am able to make a successful remote connection from another Raspberry pi device. This is atleast a good news for me but not able to activate Postgresql on the server itself is troubling me. I don't know why? I have seen many Q&A with same situation. I tried few but did not work.

Update: Based on the @Zac Anger comment below:

kia@MMofficeUbuntu:~$ sudo -u postgres -i
[sudo] password for kia: 
postgres@MMofficeUbuntu:~$ \dt
dt: command not found
postgres@MMofficeUbuntu:~$ psql
psql (15.2 (Ubuntu 15.2-1.pgdg22.04+1), server 14.7 (Ubuntu 14.7-1.pgdg22.04+1))
Type "help" for help.

postgres=# \dt
Did not find any relations.
postgres=#
Mainland
  • 4,110
  • 3
  • 25
  • 56
  • 1
    It looks like you're trying to connect with your own user. Did you try the solutions listed [here](https://stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist)? – Zac Anger Apr 30 '23 at 01:18
  • @ZacAnger It worked. I just updated my question. I am so happy to see myself inside the server. I have no words. This is after trying so many things for alomost a day. But I have question: what this code `sudo -u postgres -i` did here? I saw the `kia@MM..ubuntu:$` change to `postgres@MMofficeUbuntu:~$`. What does this mean? Am already inside the Posgresql server? Because this name `postgres` is Postgresql server default username. – Mainland Apr 30 '23 at 01:33
  • 1
    Usually on *nix postgres is installed under a postgres user account. When you switch to that user you have access to the postgres command prompt as the "owner" of the account. Easy peasy. – topsail Apr 30 '23 at 02:13
  • It is bit confusing to me. My usual understanding from MySql is, enter the server and then provide username details. But here I typed username first. I hope you understood my dilemma. Thanks – Mainland Apr 30 '23 at 02:29
  • You do the same in Postgres: `psql -d -U -p `. There is no need to do `sudo -u postgres -i`. In your case `psql -d postgres -U postgres` . It explained here [psql](https://www.postgresql.org/docs/current/app-psql.html) and here [Connection keywords](https://www.postgresql.org/docs/15/libpq-connect.html#LIBPQ-PARAMKEYWORDS). – Adrian Klaver Apr 30 '23 at 15:51
  • 1
    FYI, you do not want to use the `postgres` database to store data. It exists to be a connection database that you connect to and do other operations such as `CREATE DATABASE`. – Adrian Klaver Apr 30 '23 at 15:59
  • @AdrianKlaver `YI, you do not want to use the postgres database to store data. It exists to be a connection database that you connect to and do other operations such as CREATE DATABASE` This is quite helpful to me. So, my understanding is, it like template. Now it is clear to me. – Mainland May 01 '23 at 15:18
  • @AdrianKlaver For `psql -d mynewdb -U mynewuser -p 5432` I got following: `psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "mynewuser"` – Mainland May 01 '23 at 15:23
  • For more detail see [Ubuntu Postgresql](https://ubuntu.com/server/docs/databases-postgresql). In meantime the issue is that [pg_hba.conf](https://www.postgresql.org/docs/current/auth-pg-hba-conf.html) has the `local(socket)` connection authorization set to `peer`. You can change that to one of the other `auth` methods, say either `trust` or `scram-sha-256`(for password auth). If you go the password route make sure you create the password for the user first. – Adrian Klaver May 01 '23 at 15:34
  • The `postgres` database is not really there as a template that is served by the `template1` database. See [Template databases](https://www.postgresql.org/docs/current/manage-ag-templatedbs.html) for more information. `postgres` is there to be the 'default' connection database. – Adrian Klaver May 01 '23 at 15:38

0 Answers0