DEV Community

loading...

7 steps after installing PostgresQL on Ubuntu Server 20.04

try_catch profile image try_catch 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"
Enter fullscreen mode Exit fullscreen mode

Solution:

1) Make sure you've run pg_ctlcluster

sudo pg_ctlcluster 12 main start
Enter fullscreen mode Exit fullscreen mode

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

sudo nano /etc/postgresql/12/main/postgresql.conf
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

4) Sign-in into psql using sudo

sudo -u postgres psql postgres
Enter fullscreen mode Exit fullscreen mode

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

alter user postgres password 'your_new_password';
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

and the final step

psql -U postgres --password
Enter fullscreen mode Exit fullscreen mode

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

Discussion (0)

pic
Editor guide