loading...

7 steps after installing PostgresQL on Ubuntu Server 20.04

pa_catch profile image trycatch Updated on ・1 min read

Problem:

After installation (sudo apt install postgresql-12) you try to connect to your fresh PostgresQL 12 using psql, but it's responding

psql: error: could not connect to server: FATAL:  Peer authentication failed for user "postgres"

Solution:

1) Make sure you've run pg_ctlcluster

sudo pg_ctlcluster 12 main start

2) Check postgres port and change it to 5432 if needed

sudo nano /etc/postgresql/12/main/postgresql.conf

in newer versions it might be 5433 by default, so it causes problems

find a string with port = 5433 and change it to port = 5432

3) Restart postgres service (just in case)

sudo service postgresql restart

4) Sign-in into psql using sudo

sudo -u postgres psql postgres

5) Set up postgres user's password. Don't forget the semicolon!

alter user postgres password 'your_new_password';

6) Open sudo nano /etc/postgresql/12/main/pg_hba.conf

Change 12 to your porstgresql version

And find a row where you see

local   all             postgres           peer

and change peer to md5

Make sure that you change the row, some of them look similar

Press CTRL + O, save the file, and press CTRL+X to exit

7) Start using psql with your new password

Restart the service again

sudo service postgresql restart

and the final step

psql -U postgres --password

then enter the password you've created at the third step. You can use these credentials for your apps, too.

Discussion

pic
Editor guide