PostgreSQL
Updated 31 May 2019
Contents
PostgreSQL is a free object-relational database system.
Installation
Install PostgreSQL:
emerge -a dev-db/postgresql
Setup PostgresSQL:
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:
postgresql-11 | * /run/postgresql: creating directory postgresql-11 | * /run/postgresql: correcting owner postgresql-11 | * Starting PostgreSQL 11 ... [ ok ]
Add the SQL server to autostart:
* service postgresql-11 added to runlevel defaultConfiguration with password access
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:
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:
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:
Пароль пользователя test: psql (11.2) Введите "help", чтобы получить справку. test=> \q
Configuration with peer authorization
Configuring authorization
Configure peer authorization for local connections only:
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: #host all all 127.0.0.1/32 trust # 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
Restart PostgreSQL:
Creating users
Create a system test user:
useradd test
Create a Postgresql test user with the permission to create new databases:
psql (11.2) Type "help" for help. postgres=# CREATE USER test CREATEDB; CREATE ROLE postgres=# \q
Creating database
Create a testdb+ database as the test user:
psql (11.2) Type "help" for help. postgres=# CREATE DATABASE testdb; CREATE DATABASE postgres=# \q