Setting up Postgresql for the first time

If you have just installed Postgresql you may want to do the following things if you want to use psql(a command line utility for managing Postgresql)

First let’s connect to our freshly installed Postgresql server by executing the following command

sudo -u postgres psql template1

Next let’s give a password to the postgres user, we do that by executing the following command

ALTER USER postgres with encrypted password 'xxxxxxx';

you may press Ctrl+d to exit psql

next we want to edit pg_hba.conf, on Debian based systems this can be found in the /etc/postgresql/POSTGRESQLVERSION/main directory

nano /etc/postgresql/9.6/main/pg_hba.conf

scroll down and find

local   all             postgres                                peer

change it to

local   all             postgres                                md5

Noe let’s restart Postgresql for changes to take effect

sudo /etc/init.d/postgresql restart

Now you can login to psql as postgres by executing the following

psql -U postgres -W

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.