PostgreSQL

Updated 18 April 2019

PostgreSQL is a free object-relational database system.

Installation

Install PostgreSQL:

emerge -a postgresql

Setup PostgresSQL:

emerge --config postgresql
Configuring pkg...

 * You can modify the paths and options passed to initdb by editing:
 *     /etc/conf.d/postgresql-11
 * 
 * Information on options that can be passed to initdb are found at:
 *     http://www.postgresql.org/docs/11/static/creating-cluster.html
 *     http://www.postgresql.org/docs/11/static/app-initdb.html
 * 
 * PG_INITDB_OPTS is currently set to:
 *     --encoding=UTF8
 * 
 * Configuration files will be installed to:
 *     /etc/postgresql-11/
 * 
 * The database cluster will be created in:
 *     /var/lib/postgresql/11/data
 * 
 * Continuing initialization in 5 seconds (Control-C to cancel) ...
 [ ok ]
 * Creating the data directory ...
 * Initializing the database ...
Файлы, относящиеся к этой СУБД, будут принадлежать пользователю "postgres".
От его имени также будет запускаться процесс сервера.

Кластер баз данных будет инициализирован со следующими параметрами локали:
  COLLATE:  ru_RU.utf8
  CTYPE:    ru_RU.utf8
  MESSAGES: ru_RU.utf8
  MONETARY: ru_RU.utf8
  NUMERIC:  C
  TIME:     ru_RU.utf8
Выбрана конфигурация текстового поиска по умолчанию "russian".

Контроль целостности страниц данных отключён.

исправление прав для существующего каталога /var/lib/postgresql/11/data... ок
создание подкаталогов... ок
выбирается значение max_connections... 100
выбирается значение shared_buffers... 128MB
выбор реализации динамической разделяемой памяти... posix
создание конфигурационных файлов... ок
выполняется подготовительный скрипт... ок
выполняется заключительная инициализация... ок
сохранение данных на диске... ок

ПРЕДУПРЕЖДЕНИЕ: используется проверка подлинности "trust" для локальных подключений.
Другой метод можно выбрать, отредактировав pg_hba.conf или используя ключи -A,
--auth-local или --auth-host при следующем выполнении initdb.

Готово. Теперь вы можете запустить сервер баз данных:

    /usr/lib64/postgresql-11/bin/pg_ctl -D /var/lib/postgresql/11/data -l файл_журнала start

 * The autovacuum function, which was in contrib, has been moved to the main
 * PostgreSQL functions starting with 8.1, and starting with 8.4 is now enabled
 * by default. You can disable it in the cluster's:
 *     /etc/postgresql-11/postgresql.conf
 * 
 * The PostgreSQL server, by default, will log events to:
 *     /var/lib/postgresql/11/data/postmaster.log
 * 
 * You should use the '/etc/init.d/postgresql-11' script to run PostgreSQL
 * instead of 'pg_ctl'.

The output above contains the main information.

Start PostgreSQL:

/etc/init.d/postgresql-11 start
postgresql-11 | * /run/postgresql: creating directory
postgresql-11 | * /run/postgresql: correcting owner
postgresql-11 | * Starting PostgreSQL 11 ...                                 [ ok ]

Add the SQL server to autostart:

rc-update add postgresql-11
 * service postgresql-11 added to runlevel default

Postgres user configuration

Set a password for the postgres user:

psql -U postgres

psql (11.2)
Введите "help", чтобы получить справку.
postgres=# \password
Введите новый пароль:
Повторите его:
postgres=# \q

Database access configuration

Configure access to the SQL server by allowing only local queries and those from the local network ~192.168.0.0/24~:

/etc/postgresql-11/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     password
# IPv4 local connections:
host    all             all             127.0.0.1/32            password
host    all             all             192.168.0.0/24          password
# IPv6 local connections:
#host   all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local  replication     all                                     trust
#host   replication     all             127.0.0.1/32            trust
#host   replication     all             ::1/128                 trust

Specify IP addresses to be listened to by the PostgreSQL, comma-separated:

/etc/postgresql-11/postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '192.168.0.1,127.0.0.1' # what IP address(es) to listen on;
                                           # comma-separated list of addresses;
                                           # defaults to 'localhost'; use '*' for all
                                           # (change requires restart)

Note

If the server needs to be accessed from all network interfaces, set "*" instead of IPs.

Restart the database server:

/etc/init.d/postgresql-11 restart
postgresql-11 | * Stopping PostgreSQL 11 (this can take up to 92 seconds) ... [ ok ]
postgresql-11 | * /run/postgresql: correcting mode
postgresql-11 | * Starting PostgreSQL 11 ...                                  [ ok ]

Check for operating ports:

netstat -an
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN     
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN     
tcp        0      0 192.168.0.1:5432        0.0.0.0:*               LISTEN     
tcp6       0      0 :::22                   :::*                    LISTEN     
udp        0      0 127.0.0.1:41006         127.0.0.1:41006         ESTABLISHED
Active UNIX domain sockets (servers and established)
Proto RefCnt Flags       Type       State         I-Node   Path
unix  2      [ ACC ]     STREAM     LISTENING     281718   /run/postgresql/.s.PGSQL.5432

Exampe: create a database and a database user

Let's create a database, called dbtest and a user, called test to work with it:

psql -U postgres

Пароль пользователя postgres: 
psql (11.2)
Введите "help", чтобы получить справку.

postgres=# create database dbtest;
CREATE DATABASE
postgres=# create role test with login;
CREATE ROLE
postgres=# \password test
Введите новый пароль: 
Повторите его: 
postgres=# grant connect, create on database dbtest to test;
GRANT
postgres=# \l dbtest
                                 Список баз данных
    Имя    | Владелец | Кодировка | LC_COLLATE |  LC_CTYPE  |     Права доступа     
-----------+----------+-----------+------------+------------+-----------------------
 dbtest    | postgres | UTF8      | ru_RU.utf8 | ru_RU.utf8 | =Tc/postgres         +
           |          |           |            |            | postgres=CTc/postgres+
           |          |           |            |            | test=Cc/postgres
(1 строка)

postgres=# \q

The database and the database user have been created.

You can now check the user:

psql -U test -d dbtest
Пароль пользователя test:
psql (11.2)
Введите "help", чтобы получить справку.

test=> \q