PostgreSQL (Русский)

The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

Состояние перевода: На этой странице представлен перевод статьи PostgreSQL. Дата последней синхронизации: 14 декабря 2021. Вы можете помочь синхронизировать перевод, если в английской версии произошли изменения.

PostgreSQL — это поддерживаемая сообществом система управления базами данных с открытым исходным кодом.

Установка

Установите пакет postgresql. Он также создаст системного пользователя postgres.

Важно: Смотрите #Обновление PostgreSQL для выполнения обязательных шагов перед установкой обновлений пакета PostgreSQL.

Примечание: Команды, которые нужно запускать от имени пользователя postgres, в данной статье обозначены префиксом [postgres]$.

Для переключения в пользователя postgres можно использовать sudo:

$ sudo -iu postgres

Или su:

$ su
# su -l postgres

Смотрите также документацию sudo(8) или su(1).

Начальная настройка

В первую очередь необходимо инициализировать кластер баз данных:

[postgres]$ initdb -D /var/lib/postgres/data

Где опция -D указывает на стандартное расположение данных кластера (если вы хотите использовать другой каталог, смотрите #Изменение стандартного каталога данных).

По умолчанию локаль и кодировка наследуются из вашего текущего окружения (используется значение $LANG). [1] Если вас это не устраивает, вы можете прописать нужные параметры вручную с помощью опций:

--locale=локаль, где локаль должна быть одной из доступных системных локалей;
-E кодировка для выбора кодировки (должна соответствовать выбранной локали).

Пример для русской локали:

[postgres]$ initdb --locale=ru_RU.UTF-8 -E UTF8 -D /var/lib/postgres/data

После инициализации на экране появится много строчек, некоторых из которых оканчиваются на ... ок:

Файлы, относящиеся к этой СУБД, будут принадлежать пользователю "postgres".
От его имени также будет запускаться процесс сервера.

Кластер баз данных будет инициализирован с локалью "ru_RU.UTF-8".
Выбрана конфигурация текстового поиска по умолчанию "russian".

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

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

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

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

pg_ctl -D /var/lib/postgres/data -l файл_журнала start

Если вы видите подобное, значит инициализация прошла успешно. Можно вернуться в обычного пользователя, выполнив команду exit в сеансе пользователя postgres.

Примечание: Подробнее об этом предупреждении читайте в разделе #Ограничение доступа к суперпользователю по умолчанию.

Совет: Если вы хотите использовать путь отличный от /var/lib/postgres, нужно обновить файл службы systemd. Если вы помещаете его в /home, не забудьте отключить ProtectHome.

Важно:

Если база данных располагается на файловой системе Btrfs, стоит отключить Copy-on-Write для каталога перед созданием любых баз данных.
Если база данных располагается на файловой системе ZFS, прочтите ZFS#Databases перед созданием любых баз данных.

Наконец, запустите и включите службу postgresql.service.

Создание Вашей первой базы данных

Совет: Если имя созданного пользователя совпадает с именем вашего пользователя в Linux, вы сможете получить доступ к базе данных оболочки PostgreSQL без явного указания имени пользователя (что весьма удобно).

Становимся пользователем postgres. Добавляем нового пользователя базы данных с помощью команды createuser:

[postgres]$ createuser --interactive

Создаём новую базу данных от имени пользователя, имеющего доступ на чтение-запись, с помощью команды createdb (выполните эту команду в вашей обычной оболочке, если имя будущего владельца базы данных совпадает с вашим именем пользователя в Linux, в ином случае добавьте опцию -O имя-пользователя)

$ createdb myDatabaseName

Совет: Если вы не выдали разрешение на создание баз данных вашему свежесозданному пользователю, добавьте опцию -U postgres к этой команде.

Знакомство с PostgreSQL

Доступ к оболочке базы данных

Становимся postgres пользователем. Запускаем основную оболочку базы данных, в которой мы сможем создавать, удалять базы данных/таблицы, задавать права и запускать команды SQL. Используйте опцию -d, чтобы указать название базы данных, которую вы создали (если опцию не указать, то psql попытается подключиться к базе, имя которой совпадает с именем пользователя).

[postgres]$ psql -d имяМоейБазы

Список всех возможных команд (например, CREATE TABLE) для запросов

=> \h

Подробное описание команды

=> \h CREATE_TABLE

Подключаем определённую базу данных

=> \c <database>

Список всех пользователей и их уровни доступа

=> \du

Краткая информация о всех таблицах в текущей базе данных

=> \dt

Меняем пароль

=> \password

Показать все используемые настройки

=> SHOW ALL;

Выйти из psql

=> \q или CTRL+d

Есть, конечно, много других мета-команд, но именно эти должны помочь вам начать работу. Для просмотра всех мета-команд введите:

=> \?

Дополнительные настройки

Файл настроек сервера баз данных PostgreSQL postgresql.conf. Этот файл находится в папке данных сервера, обычно /var/lib/postgres/data. В этой же папке находятся основные файлы настроек включая и pg_hba.conf.

Примечание: По умолчанию эта папка не доступна даже для просмотра (или поиска) от лица обычного пользователя.

Ограничение доступа к суперпользователю по умолчанию

По умолчанию 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             пользователь                            scram-sha-256

Если вы выбрали scram-sha-256, также нужно изменить /var/lib/postgres/data/postgresql.conf:

/var/lib/postgres/data/postgresql.conf

password_encryption = scram-sha-256

Перезапустите службу postgresql.service и заново пропишите пароли для пользователей с помощью SQL-запроса ALTER USER пользователь WITH ENCRYPTED PASSWORD 'пароль';.

Доступ только через Unix-сокет

В разделе сonnections and authentications пропишите:

/var/lib/postgres/data/postgresql.conf

listen_addresses = ''

Это полностью отключит доступ через сеть. Не забудьте перезапустить службу postgresql.service для применения изменений.

Доступ с удалённых хостов

В разделе connections and authentications раскомментируйте или исправьте строку listen_addresses по вашему желанию на

listen_addresses = '*'

либо

listen_addresses = 'localhost,ip_у_сервера_в_сети'

и внимательно просмотрите другие строки.
Далее добавляем следующую строку в основной файл настройки проверки подлинности /var/lib/postgres/data/pg_hba.conf. (если вы планируете подключатся только со своего компьютера, то пропустите данный шаг) Этот файл определяет, каким хостам разрешено подключаться, так что будьте осторожны.

/var/lib/postgres/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             your_desired_ip_address/32   md5

где your_desired_ip_address — IP-адрес клиента.

После этого необходимо перезапустить демон, чтобы изменения вступили в силу

$ systemctl restart postgresql

Смотрите также документацию по pg_hba.conf.

Примечание: PostgreSQL по умолчанию использует порт 5432 для удалённого доступа. Поэтому убедитесь, что этот порт открыт и может принимать входящие соединения.

Примечание: Ни отправка простого пароля, ни отправка md5-хэша (использованный в приведённом выше примере) через интернет не являются безопасными, если это не выполняется через защищенное SSL-соединение. Смотрите Secure TCP/IP Connections with SSL, чтобы узнать, как настроить PostgreSQL с использованием SSL.

Если возникли проблемы взгляните на лог-файл сервера

# journalctl -u postgresql

Настройка аутентификации через PAM

PostgreSQL предлагает несколько методов аутентификации. Если вы хотите разрешить пользователям аутентифицироваться с их системным паролем, необходимы дополнительные шаги. Сначала вам нужно включить PAM для соединения.

Например, та же конфигурация, что и выше, но с включенным PAM:

/var/lib/postgres/data/pg_hba.conf

# IPv4 local connections:
host   all   all   my_remote_client_ip_address/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

Изменение кодировки новых баз данных на UTF-8

Примечание: Если вы выполнили initdb с опцией -E UTF8 или с использованием UTF-8 локали, выполнять эти шаги не нужно.

Когда создаётся новая база данных (например, createdb blog) PostgreSQL просто копирует шаблон базы данных. Есть два стандартных шаблона: template0 - ваниль, и template1 используемый по умолчанию. Один из вариантов изменения кодировки новой базы данных, заключается в изменении шаблона template1. Для этого, заходим в оболочку PostgresSQL (psql) и делаем вот что:

1. Первое, мы должны сбросить template1. Шаблоны не могут быть сброшены, так что мы сначала изменим его, как обычную базу данных:

UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';

2. Сейчас уже можно сбросить её:

DROP DATABASE template1;

3. Создаём новую базу данных, с новой кодировкой по умолчанию из template0:

CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';

4. Теперь снова сделаем template1 шаблоном:

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';

5. (Рекомендация) Документация по PostgreSQL advises рекомендует "замораживать" изменения шаблона функцией VACUUM FREEZE:

\c template1
VACUUM FREEZE;

6. (По желанию) Если вы не хотите, чтобы кто-либо подключался к этому шаблону, присвойте параметру datallowconn значение FALSE:

UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template1';

Примечание: Этот шаг может привести к проблемам при обновлении через pg_upgrade.

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

[postgres]$ createdb blog

Если снова войти в PSQL и проверить базу данных, вы должны увидеть правильную кодировку новой базы данных:

\l

returns

                              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     |

Графические инструменты

phpPgAdmin — Веб-интерфейс для администрирования PostgreSQL.

https://github.com/phppgadmin/phppgadmin || phppgadmin

pgAdmin — Комплексный графический интерфейс для управления PostgreSQL.

https://www.pgadmin.org/ || pgadmin3^AUR или pgadmin4

pgModeler — Инструмент для моделирования баз данных PostgreSQL.

https://pgmodeler.io/ || pgmodeler^AUR

Список инструментов, поддерживающих несколько разных СУБД, можно посмотреть в статье List of applications/Documents#Database tools.

Обновление PostgreSQL

This article or section needs expansion.

Reason: How to upgrade when using third party extensions? (Discuss in Talk:PostgreSQL#pg_upgrade problem if extensions (like postgis) are used)

Для обновления до новой мажорной версии PostgreSQL необходима специальная процедура.

Примечание:

Следуйте официальной документации по обновлению.
Начиная с версии 10.0, PostgreSQL изменил схему версионирования. Раньше мажорными были обновления с 9.x до 9.y. Теперь обновления с 10.x до 10.y считаются минорными, а мажорным является обновление с 10.x до 11.y.

Важно: Приведённые ниже инструкции потенциально могут привести к потере данных. Не запускайте эти команды вслепую без понимания того, что они делают. Сделайте резервную копию перед началом.

Посмотреть текущую версию можно так:

# cat /var/lib/postgres/data/PG_VERSION

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

/etc/pacman.conf

...
IgnorePkg = postgresql postgresql-libs
...

Минорные обновления вполне безопасны. Однако если вы случайно обновитесь до другой мажорной версии, то не сможете получить доступ к данным. Всегда проверяйте домашнюю страницу PostgreSQL, чтобы знать, какие шаги требуются для каждого обновления. Чтобы узнать, почему это так, смотрите политику управления версиями.

Есть два основных способа обновить базу данных PostgreSQL. Подробности читайте в официальной документации.

pg_upgrade

Для тех, кто хочет использовать pg_upgrade, доступен пакет postgresql-old-upgrade, который всегда отстаёт на одну мажорную версию от основного пакета PostgreSQL. Его можно установить параллельно с новой версией PostgreSQL. Для обновления более старых версий PostgreSQL доступны пакеты AUR: postgresql-96-upgrade^AUR, postgresql-95-upgrade^AUR, postgresql-94-upgrade^AUR, postgresql-93-upgrade^AUR, postgresql-92-upgrade^AUR. Прочтите справочную страницу pg_upgrade(1), чтобы понять, какие действия он выполняет.

Обратите внимание, что каталог кластера баз данных не меняется от версии к версии, поэтому перед запуском pg_upgrade необходимо переименовать существующий каталог данных и перейти в новый каталог. Новый кластер баз данных необходимо инициализировать, как описано в разделе #Установка.

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

[postgres]$ psql -l

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | 
 template0 | postgres | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

[postgres]$ psql -c "SHOW data_checksums"

 data_checksums 
----------------
 off
(1 row)

Когда вы будете готовы к обновлению, выполните следующие шаги:

Остановите службу postgresql.service. Проверьте статус службы, чтобы убедиться, что PostgreSQL завершился корректно, иначе pg_upgrade не сможет отработать корректно.

Обновите пакеты postgresql, postgresql-libs и postgresql-old-upgrade.

Переименуйте каталог с кластером и создайте новый каталог:

# mv /var/lib/postgres/data /var/lib/postgres/olddata
# mkdir /var/lib/postgres/data /var/lib/postgres/tmp
# chown postgres:postgres /var/lib/postgres/data /var/lib/postgres/tmp
[postgres]$ cd /var/lib/postgres/tmp
[postgres]$ initdb -D /var/lib/postgres/data

Не забудьте прописать опции --locale, -E и/или --data-checksums по необходимости.

Обновите кластер, выполнив эту команду (замените PG_VERSION на номер старой версии, например 12):

[postgres]$ pg_upgrade -b /opt/pgsql-PG_VERSION/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data

Примечание: Не забудьте обновить файлы конфигурации (например, pg_hba.conf и postgresql.conf) для соответствия старому кластеру.

Запустите службу postgresql.service.

Командой pg_upgrade будут созданы скрипты analyze_new_cluster.sh и delete_old_cluster.sh в каталоге /var/lib/postgres/tmp/ и выведены инструкции по их использованию.

analyze_new_cluster.sh генерирует статистику оптимизатора для нового кластера и должен запускаться от имени пользователя postgres. Для его работы postgresql.service должен быть запущен.
delete_old_cluster.sh просто удаляет каталог /var/lib/postgres/olddata и должен запускаться от имени пользователя, имеющего права записи в /var/lib/postgres (например, от имени root).

Когда обновление будет полностью завершено, каталог /var/lib/postgres/tmp можно будет удалить.

Выгрузка и загрузка вручную

Ещё можно сделать что-то вроде такого (после обновления и установки postgresql-old-upgrade):

Примечание:

В примере показано обновление с PostgreSQL 12; посмотрите в /opt/ установленную у вас версию postgresql-old-upgrade и исправьте команды по необходимости.
Если вы меняли файл pg_hba.conf, вам может понадобиться временно разрешить полный доступ к старому кластеру с локальной системы. После обновления не забудьте прописать нужные вам настройки в новом кластере и перезапустить службу postgresql.service.

# systemctl stop postgresql.service
# mv /var/lib/postgres/data /var/lib/postgres/olddata
# mkdir /var/lib/postgres/data
# chown postgres:postgres /var/lib/postgres/data
[postgres]$ initdb -D /var/lib/postgres/data
[postgres]$ /opt/pgsql-12/bin/pg_ctl -D /var/lib/postgres/olddata/ start
# cp /usr/lib/postgresql/postgis-3.so /opt/pgsql-12/lib/ # Только если установлен postgis
[postgres]$ pg_dumpall -h /tmp -f /tmp/old_backup.sql
[postgres]$ /opt/pgsql-12/bin/pg_ctl -D /var/lib/postgres/olddata/ stop
# systemctl start postgresql.service
[postgres]$ psql -f /tmp/old_backup.sql postgres

Решение проблем

Ускорение мелких транзакций

Если вы используете PostgreSQL на своей локальной машине для разработки и он медленный, то можете попробовать отключить synchronous_commit в конфигурации. Однако, не забывайте про его особенности.

/var/lib/postgres/data/postgresql.conf

synchronous_commit = off

Запретить запись на диск во время бездействия

PostgreSQL периодически обновляет свою статистику, лежащую в файле. По умолчанию этот файл находится на диске, что не даёт отдыхать (и изнашивает) жёсткому диску, заставляя его шуршать. Однако можно легко и безопасно поменять локацию файла внутрь ФС (/run) расположенной в ОЗУ с помощью такой настройки:

/var/lib/postgres/data/postgresql.conf

stats_temp_directory = '/run/postgresql'

Проблемы с pgAdmin 4 после обновления до PostgreSQL 12

Если вы видите ошибки вроде string indices must be integers при навигации по дереву слева или column rel.relhasoids does not exist при просмотре данных, удалите сервер из списка соединений в pgAdmin и добавьте его заново. Без этого pgAdmin продолжает считать его сервером PostgreSQL 11, что и приводит к таким ошибкам.