воскресенье, 12 апреля 2020 г.

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






Комментариев нет:

Отправить комментарий