Версия: 16.2
PostgreSQL - свободная объектно-реляционная система управления базами данных.
При установке PostgreSQL в систему добавляется пользователь
postgres. К командам, выполняемым от пользователя postgres, добавлен префикс:[postgres]$.
Контактная информация:
Официальный сайт
Лицензия:
custom:PostgreSQL
Синтаксис:
postgres [ПАРАМЕТР]...
Параметры:
| Параметр | Описание |
|---|---|
| -B ЧИСЛО_БУФ | Число разделяемых буферов |
| -c ИМЯ=ЗНАЧЕНИЕ | Установить параметр выполнения |
| -C ИМЯ | Вывести значение параметра выполнения и выйти |
| -d 1-5 | Уровень отладочных сообщений |
| -D КАТАЛОГ | Каталог с данными |
| -e | Использовать европейский формат дат (ДМГ) |
| -F | Выключить синхронизацию с ФС |
| -h ИМЯ | Имя или IP-адрес для приёма сетевых соединений |
| -i | Включить соединения TCP/IP |
| -k КАТАЛОГ | Расположение Unix-сокетов |
| -l | разрешить SSL-подключения |
| -N МАКС_ПОДКЛ | Предельное число подключений |
| -o ПАРАМЕТРЫ | Параметры для серверных процессов (уже неактуально) |
| -p ПОРТ | Номер порта для приёма подключений |
| -s | Показывать статистику после каждого запроса |
| -S РАБ_ПАМЯТЬ | Задать объём памяти для сортировки (в КБ) |
| -V, --version | Показать версию и выйти |
| --ИМЯ=ЗНАЧЕНИЕ | Установить параметр выполнения |
| --describe-config | Вывести параметры конфигурации и выйти |
| -?, --help | Показать эту справку и выйти |
Параметры для разработчиков:
| Параметр | Описание |
|---|---|
| -f s|i|n|m|h | Запретить некоторые типы планов |
| -n | Не переинициализировать разделяемую память после аварийного выхода |
| -O | Разрешить изменять структуру системных таблиц |
| -P | Отключить системные индексы |
| -t pa|pl|ex | Показать время каждого запроса |
| -T | Посылать сигнал SIGSTOP всем серверным процессам при отключении одного |
| -W СЕК | Ждать заданное число секунд для подключения отладчика |
Параметры для монопольного режима:
| Параметр | Описание |
|---|---|
| --single | Включить монопольный режим (этот аргумент должен быть первым) |
| ИМЯ_БД | База данных (по умолчанию - имя пользователя) |
| -d 0-5 | Переопределить уровень отладочных сообщений |
| -E | Выводить SQL-операторы перед выполнением |
| -j | Не считать конец строки разделителем интерактивных запросов |
| -r ИМЯ_ФАЙЛА | Перенаправить STDOUT и STDERR в указанный файл |
Параметры для режима инициализации:
| Параметр | Описание |
|---|---|
| --boot | Включить режим инициализации (этот аргумент должен быть первым) |
| --check | Включить режим проверки (этот аргумент должен быть первым) |
| ИМЯ_БД | Имя базы данных (необходимо в режиме инициализации) |
| -r ИМЯ_ФАЙЛА | Перенаправить STDOUT и STDERR в указанный файл |
Для переключения на пользователя postgres ввести команду:
sudo -iu postgres
Прежде чем PostgreSQL сможет работать правильно, необходимо инициализировать кластер базы данных:
[postgres]$ initdb -D /var/lib/postgres/data
initdb принимает дополнительные аргументы:
--locale=локаль (где локаль должна быть одной из доступных системных локалей) и --encoding=кодировка для выбора кодировки (должна соответствовать выбранной локали). (После настройки базы данных можно посмотреть используемые значения командой [postgres]$ psql -l.)[postgres]$ psql -c "SHOW data_checksums".)initdb --help или официальной документации.Пример для русской локали:
[postgres]$ initdb --locale=ru_RU.UTF-8 --encoding=UTF8 -D /var/lib/postgres/data --data-checksums
Запустить сервис:
systemctl start postgresql.service
Если создать роль/пользователя PostgreSQL с тем же именем, что и имя пользователя, то это позволяет получить доступ к оболочке базы данных PostgreSQL без необходимости указывать пользователя для входа в систему.
Добавить нового пользователя базы данных:
[postgres]$ createuser --interactive
Создать новую базу данных от имени пользователя, имеющего доступ на чтение-запись (в ином случае добавить опцию: -O username)
createdb DatabaseName
Если у созданного пользователя нет прав на создание БД, то следует добавить опцию:
-U postgres
Доступ к оболочке базы данных.
Запустить основную оболочку базы данных psql:
[postgres]$ psql -d DatabaseName
Некоторые полезные команды:
Получение справки:
=> \help
Подключение к определённой базе данных:
=> \c <database>
Список всех пользователей и их уровни доступа:
=> \du
Краткая информация о всех таблицах в текущей базе данных:
=> \dt
Выход из оболочки psql:
=> \q или CTRL+d
Просмотр всех мета-команд
=> \?
Файл настроек сервера баз данных PostgreSQL — postgresql.conf. Этот файл находится в папке данных сервера, обычно /var/lib/postgres/data. В этой же папке находятся основные файлы настроек включая и pg_hba.conf, который определяет параметры аутентификации, как для локальных пользователей, так и для пользователей с других хостов.
По умолчанию эта папка не будет доступна для просмотра или поиска обычным пользователем. Вот почему find и locate не находят файлы конфигурации.
Ограничить права доступа суперпользователя базы данных по умолчанию:
По умолчанию pg_hba.conf разрешает подключение любого локального пользователя к любому пользователю базы данных, в том числе суперпользователю. Чтобы разрешить подключение только пользователю postgres, изменить строку:
/var/lib/postgres/data/pg_hba.conf
-----------------------------------------------------------------------
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
На эту:
/var/lib/postgres/data/pg_hba.conf
-----------------------------------------------------------------------
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all postgres peer
При необходимости можно добавить дополнительные строки.
В /var/lib/postgres/data/pg_hba.conf, прописать метод аутентификации для каждого пользователя (или «all» для всех пользователей) на scram-sha-256 (предпочтительно) или md5 (менее безопасно; по возможности стоит его избегать):
/var/lib/postgres/data/pg_hba.conf
-----------------------------------------------------------------------
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all user scram-sha-256
Выбрав scram-sha-256, также нужно изменить /var/lib/postgres/data/postgresql.conf:
/var/lib/postgres/data/postgresql.conf
-----------------------------------------------------------------------
password_encryption = scram-sha-256
В разделе «connections and authentication» прописать:
/var/lib/postgres/data/postgresql.conf
-----------------------------------------------------------------------
listen_addresses = ''
Это полностью отключит доступ через сеть. Необходимо перезапустить postgresql.service для применения изменений:
systemctl restart postgresql.service
В разделе «connections and authentication» раскомментировать или исправьте строку listen_addresses, например:
/var/lib/postgres/data/postgresql.conf
-----------------------------------------------------------------------
listen_addresses = 'localhost,локальный_ip'
Можно использовать '*' для прослушивания всех доступных сетевых интерфейсов.
PostgreSQL по умолчанию использует TCP-порт 5432 для удалённого доступа. Необходимо убедиться, что этот порт открыт в межсетевом экране и может принимать входящие соединения. Изменить порт можно в этом же файле настроек, под строкой listen_addresses.
Далее измените настройки аутентификации:
/var/lib/postgres/data/pg_hba.conf
-----------------------------------------------------------------------
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
host all all ip_адрес/32 md5
Документация по pg_hba.conf
Для применения изменений необходимо перезагрузить службу:
systemctl restart postgresql.service
В случае проблем посмотреть журнал сервера:
journalctl -u postgresql.service
PostgreSQL предлагает несколько методов аутентификации. Если необходимо разрешить пользователям аутентифицироваться с их системным паролем, первоначально нужно включить PAM для соединения.
Например, та же конфигурация, что и выше, но с включенным PAM:
/var/lib/postgres/data/pg_hba.conf
-----------------------------------------------------------------------
# IPv4 local connections:
host all all ip_адрес/32 pam
Однако сервер PostgreSQL работает без прав root и не сможет получить доступ к файлу /etc/shadow. Можно обойти это, разрешив группе postgres доступ к этому файлу:
setfacl -m g:postgres:r /etc/shadow
По умолчанию PostgreSQL настроен на использование каталога /var/lib/postgres/data для хранения всех баз данных. Для его изменения необходимо:
Создать новый каталог и сделать пользователя postgres его владельцем:
mkdir -p /путь/к/pgroot/data
chown -R postgres:postgres /путь/к/pgroot
Войти в пользователя postgres и выполнить инициализацию кластера:
[postgres]$ initdb -D /путь/к/pgroot/data
Отредактировать службу postgresql.service, создав drop-in файл и переопределив настройки Environment и PIDFile. Например:
/etc/systemd/system/postgresql.service.d/PGROOT.conf
-----------------------------------------------------------------------
[Service]
Environment=PGROOT=/путь/к/pgroot
PIDFile=/путь/к/pgroot/data/postmaster.pid
Если необходимо использовать каталог в /home, добавить ещё одну строку:
ProtectHome=false
Если initdb выполнялась с опцией
--encoding=UTF8или с использованием локали UTF-8, выполнять эти шаги не нужно.
Когда создаётся новая база данных (например, командой createdb blog), PostgreSQL просто копирует шаблон базы данных. Есть два стандартных шаблона: template0 — ванильный, и template1, который используется по умолчанию и предназначен для редактирования администратором. Один из вариантов изменения кодировки новой базы данных — изменить шаблон template1. Для этого зайти в оболочку PostgreSQL (psql) и выполнить:
Сперва нужно удалить template1. Шаблоны нельзя удалять, так что сперва нужно преобразовать его в обычную базу данных:
UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
Теперь можно удалить:
DROP DATABASE template1;
Затем создать новую базу данных с новой кодировкой по умолчанию, в качестве шаблона используя template0:
CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';
Теперь снова сделать template1 шаблоном:
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
По желанию, если не нужно, чтобы кто-либо подключался к этому шаблону, необходимо присвоить параметру datallowconn значение FALSE:
UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template1';
Этот шаг может привести к проблемам при обновлении через pg_upgrade.
Теперь можно создать базу данных, используя стандартные команды в терминале:
[postgres]$ createdb blog
Если снова войти в psql и проверить базу данных, то можно увидеть правильную кодировку новой базы данных:
\l
-----------------------------------------------------------------------
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+-----------+-----------+-------+----------------------
blog | postgres | UTF8 | C | C |
postgres | postgres | SQL_ASCII | C | C |
template0 | postgres | SQL_ASCII | C | C | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8 | C | C |
Если файлы базы данных находятся на файловой системе, которая не использует контрольные суммы, то данные в ней могут незаметно повреждаться из-за битфлипов и аппаратных проблем. Хотя такие случаи редки, желательно включить встроенный в PostgreSQL подсчёт контрольных сумм. Эта функция должна быть включена на уровне кластера, а не для отдельных баз данных или таблиц.
Примечание: Ряд предостережений, которые стоит иметь в виду:
- Есть небольшое влияние на производительность, особенно при чтении больших массивов данных с диска. На операции в памяти это не влияет.
- PostgreSQL не может исправить повреждённые данные — он только прервёт транзакции, читающие с повреждённых страниц, чтобы предотвратить дальнейшее повреждение или получение некорректных результатов выполнения.
- Контрольные суммы охватывают только страницы данных (строк) на диске, но не метаданные или управляющие структуры. Страницы в памяти не проверяются. Хранилища с коррекцией ошибок и память с ECC по-прежнему полезны.
Чтобы включить подсчёт контрольных сумм при создании кластера, необходимо добавить аргумент --data-checksums к команде initdb.
Проверить, включен ли подсчёт контрольных сумм:
[postgres]$ psql -c "SHOW data_checksums"
Включить подсчёт контрольных сумм на существующем кластере:
Остановить службу:
systemctl stop postgresql.service
Выполните команду:
[postgres]$ pg_checksums --pgdata /var/lib/postgres/data --enable #--disable, если необходимо наоборот выключить эту функцию
Включение подсчёта контрольных сумм приведёт к перезаписи всех страниц базы данных, что займет некоторое время в больших базах данных.
Запустить службу:
systemctl start postgresql.service
Установить можно отдельным модулем или пакетом:
Для установки следует воспользоваться утилитой "Установка и удаление программ".
Модуль, необходимый для установки:
Для установки следует воспользоваться утилитой "Установка и удаление программ".
Пакет, необходимый для установки:
postgresql
Внимание! Если система загружена в режиме полного сохранения, то внесенные изменения в систему будут сохранены после перезагрузки.
Если режим загрузки другой, то рекомендуется воспользоваться утилитой "Сохранение изменений" до перезагрузки системы .