PostgreSQL - установка, конфигурирование (Redmine)

1.1 PostgreSQL — установка (Debian)

Установите базу данных

   # aptitude install postgresql

это установит сервер базы данных PostgreSQL, клиент и другие необходимые инструменты.

Чтобы проверить, установлен ли сервер/клиент PosgreSQL, вы можете использовать следующую команду:

   # psql --versio

она покажет вам текущую версию PostgreSQL, установленную на вашем сервере.

Чтобы проверить состояние службы PostgreSQL, выполните следующую команду:

 # systemctl status postgresql.service 

Если PostgreSQL запущен и работает в данный момент, вывод будет похож на следующий:

# postgresql.service - PostgreSQL RDBMS 
 Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) 
    Active: active (exited) since Sat 2018-07-21 10:57:16 CDT; 40min ago 
 Main PID: 1018 (code=exited, status=0/SUCCESS) 
    CGroup: /system.slice/postgresql.service

1.2 Подключение к СУБД

SSH: От пользователя postgres открываем командную оболочку psql:

    $ su -c - postgres psql

или последовательностью команд:

    $ su - postgres 
    # psql

ИЛИ ЖЕ:

    psql -U postgres -W (с вводом пароля)

или
    # psql -U postgres (без пароля если под root, и если в pg_hba.conf для пользователя postgres - trust)

(пароль по умолчанию обычно postgres или pgsql, в зависимости от дистриб-а).

postgres=# \l - выведет доступные базы
           \du - список ролей и их атрибутов
           \dn - список схем и их владельцев

1.3 Создание пустой базы данных и пользователя для подключения к БД

(имена: redmine).

При установке Debian Way эти действия не требуются.

В командной оболочке psql выполняем следующие запросы:

 > CREATE ROLE redmine LOGIN ENCRYPTED PASSWORD 'my_password' NOINHERIT VALID UNTIL 'infinity'; 
 > CREATE DATABASE redmine WITH ENCODING='UTF8' OWNER=redmine; 

 VALID UNTIL 'дата_время' - предложение устанавливает дату и время, после которого пароль роли перестаёт действовать. Если это предложение отсутствует, срок действия пароля будет неограниченным.

БД redmine создаётся под пользователем postgres. Пользователь redmine назначается владельцем БД, но владельцем всех таблиц БД (при создании) становится пользователь postgres... и в итоге под пользователем redmine не отрабатывает команда bundle exec rake db:migrate... соответственно Redmaine не запустится. Приходится либо использовать пользователя postgres, либо давать пользователю redmine права суперпользователя (ALTER ROLE redmine WITH SUPERUSER;).

        Правильной последовательностью представляется:

1) > CREATE ROLE redmine LOGIN ENCRYPTED PASSWORD 'my_password' NOINHERIT; - создание пользователя redmine

2) > ALTER ROLE redmine WITH SUPERUSER; - предоставление ему прав суперпользователя

3)  \q - выход

4)  psql -U redmine - вход под пользователем redmine

5)  > CREATE DATABASE redmine WITH ENCODING='UTF8' OWNER=redmine; - создание БД redmine

6)  \q - выход

7)  psql -U postgres - вход под пользователем postgres

8) > ALTER ROLE redmine WITH NOSUPERUSER; - лишение его прав суперпользователя

...но это не работает - владелец таблиц в БД все-равно оказывается postgres (не помогает и предоставление права создания БД)... пока не знаю в чем засада.

Управление PostgreSQL

Краткая памятка по управлению службами systemd для PostgreSQL 

Посмотреть статус службы:

    # systemctl status postgresql.service

Чтобы запустить службу PostgreSQL:

    # systemctl start postgresql.service

Чтобы остановить службу PostgreSQL:

    # systemctl stop postgresql.service

Чтобы перезапустить службу:

    # systemctl restart postgresql.service 

Чтобы переГрузить службу:

    # systemctl reload postgresql.service

Чтобы включить (активировать) службу PostgreSQL при загрузке системы:

    # systemctl enable postgresql.service

Если вы хотите деактивировать службу PostgreSQL при загрузке системы:

    # systemctl disable postgresql.service

Если вы хотите управлять местоположениями файлов PostgreSQL, подключениями и аутентификацией, использованием ресурсов, протоколированием и т. д., Вам необходимо отредактировать основной файл конфигурации. Основной файл конфигурации для PostgreSQL находится в каталоге /etc/postgresql/VERSION-NUMBER/main/. Поэтому, если ваша версия PostgreSQL равна 11, местоположение будет /etc/postgresql/11/main/postgresql.conf

Вы можете использовать любой текстовый редактор по вашему выбору, чтобы открыть и отредактировать файл конфигурации для PostgreSQL. Файл состоит из строк формы NAME = VALUE и убедитесь, что сделанные вами изменения действительны. В противном случае PostgreSQL не будет работать. Файл конфигурации считывается при запуске сервера, поэтому при внесении изменений вам необходимо перезапустить службу PostgreSQL, чтобы изменения вступили в силу.

    # systemctl restart postgresql.service

Для подключения к СУБД из вне (Порт - 5432, по умолчанию) необходимо:

раскомментировать и скорректировать строку (postgresql.conf):

   listen_addresses = '*'

добавить в /etc/postgresql/VERSION-NUMBER/main/pg_hba.conf подсеть внешнего доступа, например:

   host all all 192.168.1.1/24 md5

(пример файла pg_hba.conf )

PS Создание пользователя PostgreSQL

Чтобы создать нового пользователя под которым будем  подключаться к серверу PostgreSQL:

   $ su - postgres    #домашний каталог пользователя postgres - /var/lib/postgresql/
   $ createuser -s -r -d -P admin

createuser создаёт роль пользователя PostgreSQL. Использование: createuser [ПАРАМЕТР]... [ИМЯ_РОЛИ]
     -s, --superuser       роль с полномочиями суперпользователя
     -r, --createrole       роль с правом создания других ролей

     -d, --createdb        роль с правом создания баз данных
     -P, --pwprompt     назначить пароль новой роли

 

 

Обновление версии (кластеров) PgSQL с 11 на 13

Установите последние пакеты (postgresql-13 и postgresql-client-13): установка postgresql-13 автоматически создаст кластер по умолчанию /var/lib/postgres/13/main.  Обновите ваши существующие кластеры (../11/main):

su - postgres
pg_dropcluster --stop 13 main    
pg_upgradecluster 11 main

После обновления существующих кластеров, пакеты postgresql-11 и postgresql-client-11 должны быть удалены.

Документация PostgreSQL