Postgresql Centos 7

To install Postgresql :

yum install -y postgresql-server

Initialize  cluster:

postgresql-setup initdb

Modify config /var/lib/pgsql/data/pg_hba.conf, comment all rows and left one:

local     all             postgres                    peer

Such we enable access to all databases from localhost via Unix socket only user postgres.

Also, you can reset password system's user postgres:

passwd postgres

Now start postgresql server:

systemctl start postgresql

Became postgres user:

su - posgres

Enter postgres shell:

psql

Now you can create a new database, user and grant all privileges to the datababase:

CREATE DATABASE test_database;

CREATE USER test_user WITH password 'qwerty';

GRANT ALL ON DATABASE test_database TO test_user;

Now you can't connect as user  test_user because in config  /var/lib/pgsql/data/pg_hba.conf all connection disabled except postgres. Lets add next row to config file:

local     test_database   test_user                                      md5

and then restart postgresql server :

systemctl restart postgresql

and try to connect :

psql  -d template1 test_user

server'll ask password for user test_user

If you need to connect to the server from another host, need to add next 

host      test_database   test_user     192.168.1.21/32             md5

But you should place it before the previous, set listening interface in config /var/lib/pgsql/data/postgresql.conf:

listen_addresses = '*'

and restart postgresql server

For backup all databases need to run from user postgres:

su - postgres 

pg_dumpall  > pgdumpall.sql

To restore use:

su - postgres

psql -f  pgdumpall.sq