===== Использование PostgreSQL ===== ==== Как запустить службу PostgreSQL. Как управлять службой PostgreSQL ==== Запуск службы PostgreSQL: root@5new:/$ systemctl start postgresql.service Остановка службы PostgreSQL: root@5new:/$ systemctl stop postgresql.service Добавление службы PostgreSQL в автозагрузку: root@5new:/$ systemctl enable postgresql.service Удаление службы PostgreSQL из автозагрузки: root@5new:/$ systemctl disable postgresql.service Для просмотра состояния процесса PostgreSQL: root@5new:/$ systemctl status postgresql.service Альтернативный вариант запуска службы для работы с определённой базой данных следующий: -u ПОЛЬЗОВАТЕЛЬ postgres -D /ПУТЬ/ДО/БАЗЫ/ДАННЫХ Например: root@5new:/$ -u postgres postgres -D /var/lib/postgres/data ==== Как узнать, какая версия PostgreSQL запущена ==== Версию запущенной PostgreSQL не всегда можно определить по установленным пакетам. Например, во время обновления PostgreSQL на некоторых дистрибутивах не заменяет предыдущую версию, а устанавливает новую в дополнении к имеющейся. Иногда у пользователя в корпоративной среде есть доступ через Navicat или phpPgAdmin, но нет доступа к консоли сервера, на котором работает база данных. Для определения версии сервера выполните команду: root@5new:/$ pg_config --version [[https://zalinux.ru/wp-content/uploads/2021/11/pg_config.png|{{https://zalinux.ru/wp-content/uploads/2021/11/pg_config.png?512x286}}]] \\ Альтернативная команда: root@5new:/$ postgres -V Для определения версии клиента: root@5new:/$ psql --version [[https://zalinux.ru/wp-content/uploads/2021/11/psql-version.png|{{https://zalinux.ru/wp-content/uploads/2021/11/psql-version.png?533x242}}]] \\ Ещё один вариант определения версии PostgreSQL: root@5new:/$ sudo -u postgres psql postgres -c 'SELECT version()' | grep PostgreSQL Чуть короче: root@5new:/$ sudo -u postgres psql -c 'SELECT version()' | grep PostgreSQL [[https://zalinux.ru/wp-content/uploads/2021/11/SELECT-version.png|{{https://zalinux.ru/wp-content/uploads/2021/11/SELECT-version.png?827x256}}]] \\ Пример вывода: PostgreSQL 14.0 (Debian 14.0-1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.3.0-11) 10.3.0, 64-bit Если вам нужен только номер версии (например, для скрипта), то используйте следующую команду: root@5new:/$ postgres -V | awk '{print $NF}' Хотя вместо **postgres** можно использовать **postmaster**, использование postgres предпочтительнее, поскольку **postmaster** это устаревший псевдоним для **postgres**. Если вы предпочитаете вариант с SQL, то подключитесь к интерактивному терминалу: root@5new:/$ sudo -u postgres psql И выполните: SELECT version(); Также вам может пригодиться один из следующих вариантов SHOW server_version; SHOW server_version_num; [[https://zalinux.ru/wp-content/uploads/2021/11/SHOW-server_version.png|{{https://zalinux.ru/wp-content/uploads/2021/11/SHOW-server_version.png?993x533}}]] \\ Для любопытных: SHOW all; ==== Как инициализировать базу данных PostgreSQL ==== Остановите службу, если она запущена: systemctl stop postgresql.service Директория **/var/lib/postgres/** должна принадлежать пользователю **postgres**: chown -R postgres:postgres /var/lib/postgres/ Смените пользователя на **postgres**: -i -u postgres Выполните инициализацию БД: initdb --locale $LANG -E UTF8 -D '/var/lib/postgres/data' Если вы столкнулись с ошибкой: initdb: command not found То найдите расположение файла **initdb**: locate initdb И укажите до него полный путь в команде инициализации: /usr/lib/postgresql/14/bin/initdb --locale $LANG -E UTF8 -D '/var/lib/postgres/data' Нажмите **CTRL+D** Запустите службу PostgreSQL: systemctl start postgresql.service Создайте нового пользователя (например, **user**): -u postgres createuser user При желании, вы можете установить пароль для пользователя, это делается командой с ключом **-W**: sudo -u postgres createuser user -W Создайте базу данных (например, **my-first-db**): -u postgres createdb -O user my-first-db ==== Как подключиться к локальному серверу PostgreSQL ==== Для подключения к интерактивному терминалу PostgreSQL используется команда psql. Примеры синтаксиса команд: psql psql БАЗА-ДАННЫХ psql БАЗА-ДАННЫХ ПОЛЬЗОВАТЕЛЬ Для **psql** требуется указать имя пользователя и если он не указан, то пересылается имя текущего пользователя системы, который скорее всего отсутствует в базе данных PostgreSQL, что вызывает ошибку. По умолчанию создаётся пользователь postgres, поэтому без дополнительной настройке вы можете подключиться к серверу PostgreSQL следующим образом: -u postgres psql ==== Какой конфигурационный файл использует PostgreSQL ==== Конфигурационный файл PostgreSQL носит имя **postgresql.conf**. В системе может быть несколько конфигурационных файлов PostgreSQL. Вы можете найти их командой: locate postgresql.conf Что особенно важно, [[https://hackware.ru/?p=5460|systemd]] может использовать свои собственные конфигурационные файлы, например: * **/usr/lib/systemd/system/postgresql@.service.d/kali_postgresql.conf** (путь в Kali Linux) * **/usr/lib/sysusers.d/postgresql.conf** (путь в Arch Linux) Если вы настраиваете PostgreSQL, но после перезапуска службы с помощью systemd (**systemctl**) изменения не применяются, возможно, вы просто редактируете неверный файл. Также конфигурационный файл имеется в директории с базой данных, например: * **/var/lib/postgres/data/postgresql.conf** С пакетом PostgreSQL могут поставляться образцы конфигурационных файлов, например: * **/usr/share/postgresql/14/postgresql.conf.sample** * **/usr/share/postgresql/postgresql.conf.sample** ==== Как обновить базу данных PostgreSQL при переходе на новую версию ==== Сообщение при обновлении пакетов Linux: Configuring postgresql-common Obsolete major version 13 The PostgreSQL version 13 is obsolete, but the server or client packages are still installed. Please install the latest packages (postgresql-14 and postgresql-client-14) and upgrade the existing clusters with pg_upgradecluster (see manpage). Please be aware that the installation of postgresql-14 will automatically create a default cluster 14/main. If you want to upgrade the 13/main cluster, you need to remove the already existing 14 cluster (pg_dropcluster --stop 14 main, see manpage for details). The old server and client packages are no longer supported. After the existing clusters are upgraded, the postgresql-13 and postgresql-client-13 packages should be removed. Please see /usr/share/doc/postgresql-common/README.Debian.gz for details. [[https://zalinux.ru/wp-content/uploads/2021/11/postgresql-common.png|{{https://zalinux.ru/wp-content/uploads/2021/11/postgresql-common.png?1890x303}}]]Оно означает, что в системе 2 установленные версии PostgreSQL: * **postgresql-13** * **postgresql-14** Если запустить службу PostgreSQL командой: systemctl start postgresql И проверить версию командой: sudo -u postgres psql postgres -c 'SELECT version()' | grep PostgreSQL То будет выведено следующее: PostgreSQL 13.4 (Debian 13.4-3) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.3.0-8) 10.3.0, 64-bit [[https://zalinux.ru/wp-content/uploads/2021/11/postgres-version.png|{{https://zalinux.ru/wp-content/uploads/2021/11/postgres-version.png?904x303}}]]То есть по умолчанию используется 13, устаревшая версия. Удаление старых версий пакетов, например, командой: apt remove postgresql-13 postgresql-client-13 ситуацию не меняет. Если вам нужно перенести базы данных из устаревшей версии в новую, то верните устаревшие пакеты, если вы успели их удалить. Последующие действия подразумевают, что вы 1) установили новую версию PostgreSQL, но ещё не использовали её, то есть не сохраняли базы данных, поскольку файлы новой версии будут удалены. 2) хотите перенести старые база данных в новый формат С помощью следующей команды просмотрите доступные кластеры: pg_lsclusters [[https://zalinux.ru/wp-content/uploads/2021/11/pg_lsclusters.png|{{https://zalinux.ru/wp-content/uploads/2021/11/pg_lsclusters.png?1020x250}}]]На скриншоте только один из них **online** (я успел удалить пакет **postgresql-13**), но у вас оба должны быть online, иначе перенос базы данных не удастся. Пример правильного вывода: Ver Cluster Port Status Owner Data directory Log file 13 main 5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log 14 main 5433 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log Как можно увидеть, обе версии 13 и 14 в настоящее время установлены и запущены. Держите в уме, что при переносе старой базы данных в новый формат вам понадобиться двойной объём места на диске, поскольку **pg_upgradecluster** копирует данные. Процедура обновления включает в себя следующее: 1. Удаляем данные новой версии: sudo pg_dropcluster --stop 14 main 2. Запускаем процедуру обновления кластера: sudo pg_upgradecluster 13 main [[https://zalinux.ru/wp-content/uploads/2021/11/pg_upgradecluster.png|{{https://zalinux.ru/wp-content/uploads/2021/11/pg_upgradecluster.png?1005x805}}]]3. Когда операция будет завершена, дважды проверьте, что всё работает 4. Удалите старую версию sudo pg_dropcluster --stop 13 main Это показывает суть обновления кластера. Конечно, в конкретной вашей ситуации могут быть нюансы: другие номера версий, либо другое расположение файлов с базами данных. Вновь проверяем версию: -u postgres pg_upgrade -b /opt/pgsql-13/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data [[https://zalinux.ru/wp-content/uploads/2021/11/postgresql-2.png|{{https://zalinux.ru/wp-content/uploads/2021/11/postgresql-2.png?1043x564}}]]Теперь используется 14, то есть самая последняя версия. ==== В чём разница между postgres и psql ==== **postgres** postgres - это сервер базы данных PostgreSQL. Чтобы клиентское приложение могло получить доступ к базе данных, оно подключается (по сети или локально) к работающему экземпляру postgres. Затем экземпляр postgres запускает отдельный серверный процесс для обработки соединения. Один экземпляр postgres всегда управляет данными только одного кластера базы данных. Кластер базы данных — это набор баз данных, который хранится в общей папке файловой системы («область данных»). В системе может работать более одного экземпляра postgres одновременно, если они используют разные области данных и разные порты связи. Когда postgres запускается, ему необходимо знать расположение области данных. Местоположение должно быть указано параметром -D или переменной среды PGDATA; по умолчанию это значение не установлено. Обычно -D или PGDATA указывает непосредственно на каталог области данных, созданный initdb. Чтобы запустить сервер в однопользовательском режиме, используйте такую команду, как postgres --single -D /usr/local/pgsql/data ДРУГИЕ-ОПЦИИ БАЗА_ДАННЫХ Чтобы запустить postgres в фоновом режиме со значениями по умолчанию, введите: nohup postgres >logfile 2>&1 Чтобы запустить postgres с определенным портом, например, 1234: postgres -p 1234 Чтобы подключиться к этому серверу с помощью **psql**, укажите этот порт с параметром **-p**: psql -p 1234 или установите переменную окружения **PGPORT**: export PGPORT=1234 psql **psql** psql — это интерфейс для PostgreSQL на основе терминала. Он позволяет вам вводить запросы в интерактивном режиме, отправлять их в PostgreSQL и просматривать результаты запросов. В качестве альтернативы ввод может быть из файла или из аргументов командной строки. Кроме того, psql предоставляет ряд мета-команд и различных функций, подобных оболочке, для облегчения написания сценариев и автоматизации широкого спектра задач. Пример запуска **psql**: psql БАЗА ПОЛЬЗОВАТЕЛЬ Запуск psql от пользователя **postgres**, который создаётся по умолчанию: -u postgres psql ===== Ошибки PostgreSQL ===== ==== psql: error: не удалось подключиться к серверу: Нет такого файла или каталога ==== При попытке подключиться к серверу PostgreSQL или выполнить запрос на сервере PostgreSQL, например: -u postgres psql вы можете столкнуться с ошибкой: psql: error: не удалось подключиться к серверу: Нет такого файла или каталога Он действительно работает локально и принимает соединения через Unix-сокет "/run/postgresql/.s.PGSQL.5432"? В англоязычной версии эта ошибка выглядит так: psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory Is the server running locally and accepting connections on that socket? Эта ошибка означает, что служба PostgreSQL не запущена, для её запуска выполните команду: systemctl start postgresql.service Альтернативный вариант запуска службы следующий: -u ПОЛЬЗОВАТЕЛЬ postgres -D /ПУТЬ/ДО/БАЗЫ/ДАННЫХ Например: -u postgres postgres -D /var/lib/postgres/data Другой возможной причиной ошибки может быть то, что **psql** ищет файл сокета в неверной директории: например, файл сокета помещён в **/tmp**, а **psql** ищет его в **/run/postgresql/**. В этом случае вы можете с помощью опции **--host** явно указать директорию, в которой находится сокет: -u postgres psql --host=/tmp ==== FATAL: не удалось создать файл блокировки "/run/postgresql/.s.PGSQL.5432.lock": Нет такого файла или каталога ==== При запуске системы БД, например, следующей командой: -u postgres postgres -D /var/lib/postgres/data Вы можете столкнуться с ошибкой: LOCATION: resolve_symlinks, exec.c:308 2021-11-12 06:12:22.424 MSK [16067] LOG: запускается PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.1.0, 64-bit 2021-11-12 06:12:22.425 MSK [16067] LOG: для приёма подключений по адресу IPv6 "::1" открыт порт 5432 2021-11-12 06:12:22.425 MSK [16067] LOG: для приёма подключений по адресу IPv4 "127.0.0.1" открыт порт 5432 2021-11-12 06:12:22.427 MSK [16067] FATAL: не удалось создать файл блокировки "/run/postgresql/.s.PGSQL.5432.lock": Нет такого файла или каталога 2021-11-12 06:12:22.428 MSK [16067] LOG: система БД выключена [[https://zalinux.ru/wp-content/uploads/2021/11/run-postgresql-error.png|{{https://zalinux.ru/wp-content/uploads/2021/11/run-postgresql-error.png?1006x326}}]]В англоязычной версии ошибка выглядит так: 2021-11-12 01:45:37.232 EST [3709] LOG: starting PostgreSQL 14.0 (Debian 14.0-1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.3.0-11) 10.3.0, 64-bit 2021-11-12 01:45:37.233 EST [3709] LOG: listening on IPv6 address "::1", port 5432 2021-11-12 01:45:37.233 EST [3709] LOG: listening on IPv4 address "127.0.0.1", port 5432 2021-11-12 01:45:37.234 EST [3709] FATAL: could not create lock file "/run/postgresql/.s.PGSQL.5432.lock": No such file or directory 2021-11-12 01:45:37.235 EST [3709] LOG: database system is shut down Причина ошибки в том, что не удалось создать файл блокировки в указанной директории. Путь директории может быть разным в различных дистрибутивах, например, **/var/run/postgresql/** или как в сообщении выше **/run/postgresql/**. Вы можете разрешить проблему двумя способами: - Создать данную директорию (если она отсутствует) и сделать её владельцем пользователя postgres - Отредактировать конфигурационный файл так, чтобы служба пыталась создавать файл блокировки в директории **/tmp**, на которую у всех пользователей есть право записи Первый вариант — создаём директорию **/run/postgresql/** и назначаем её владельцем пользователя **postgres**: sudo mkdir /run/postgresql/ sudo chown -R postgres:postgres /run/postgresql/ Второй вариант — открываем конфигурационный файл **postgresql.conf** (у вас может быть другое расположение) gedit /var/lib/postgres/data/postgresql.conf И добавляем туда следующую запись: unix_socket_directories = '/tmp' ==== sudo: postgres: command not found ==== Если при использовании postgres вы столкнулись с ошибкой: sudo: postgres: command not found то у этой проблемы может быть две возможных причины: 1. Не установлен пакет **postgresql**. Установите его одной из следующих команд. В **Debian, Kali Linux, Linux Mint, Ubuntu** и их производных: apt install postgresql В **Arch Linux, Manjaro, BlackArch** и их производных: pacman -S postgresql 2. Исполнимый файл postgres находится за пределами **$PATH** Это необязательно говорит о проблеме — такой подход может использоваться для возможности иметь на одном компьютере сразу несколько серверов PostgreSQL. Найдите исполнимый файл locate bin/postgres [[https://zalinux.ru/wp-content/uploads/2021/11/bin-postgres.png|{{https://zalinux.ru/wp-content/uploads/2021/11/bin-postgres.png?1021x306}}]]Как можно видеть на скриншоте, исполнимый файл присутствует для двух версий сервера: * **/usr/lib/postgresql/13/bin/postgres** * **/usr/lib/postgresql/14/bin/postgres** Теперь вместо **postgres** используйте полный путь в команде запуска, например: -u postgres /usr/lib/postgresql/14/bin/postgres -D /var/lib/postgres/data ==== psql: ошибка: ВАЖНО: роль "" не существует ==== При попытке запуска интерактивного терминала PostgreSQL psql Вы можете столкнуться с ошибкой: psql: ошибка: ВАЖНО: роль "mial" не существует Имя пользователя может быть другим — там будет показано имя того пользователя, котоырй пытается выполнить вход. В англоязычной версии ошибка выглядит так: psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "mial" does not exist [[https://zalinux.ru/wp-content/uploads/2021/11/postgres-psql.png|{{https://zalinux.ru/wp-content/uploads/2021/11/postgres-psql.png?1026x260}}]]Для **psql** необходимо имя пользователя и если оно не указано явно, то передаётся имя пользователя системы. Но поскольку данный пользователь не существует на сервере PostgreSQL, то возникает указанная выше ошибка. Вы можете создать пользователя с любы именем, как это показано выше и ошибка исчезнет. По умолчанию присутствует пользователь **postgres**, поэтому вы можете подключиться от его имени: -u postgres psql ==== Вы должны указать его расположение в параметре --config-file или -D, либо установить переменную окружения PGDATA ==== При запуске postgres вы можете столкнуться с ошибкой: postgres не знает, где найти файл конфигурации сервера. Вы должны указать его расположение в параметре --config-file или -D, либо установить переменную окружения PGDATA. [[https://zalinux.ru/wp-content/uploads/2021/11/postgres-D.png|{{https://zalinux.ru/wp-content/uploads/2021/11/postgres-D.png?1193x423}}]]В англоязычной версии: postgres does not know where to find the server configuration file. You must specify the --config-file or -D invocation option or set the PGDATA environment variable. Суть ошибки в том, что необходимо указать конфигурационный файл в опции командной строки или в переменной окружения. Как вариант — можно указать путь до базы данных, содержащий конфигурационный файл. Например: -u ПОЛЬЗОВАТЕЛЬ postgres -D /ПУТЬ/ДО/КОНФИГУРАЦИОННОГО/ФАЙЛА Конфигурационный файл называется **postgresql.conf**, но нужно указать не его, а директорию, в которой он содержится. Например: -u postgres postgres -D /var/lib/postgres/data [[https://zalinux.ru/wp-content/uploads/2021/11/postgres.png|{{https://zalinux.ru/wp-content/uploads/2021/11/postgres.png?1357x257}}]] ==== initdb: command not found ==== Смотрите объяснение данной проблемы, а также дополнительные пути устранения в описании аналогичной ошибки: [[#23|sudo: postgres: command not found]] Найдите **initdb** с помощью: locate initdb Пример вывода: * **/usr/lib/postgresql/13/bin/initdb** * **/usr/lib/postgresql/14/bin/initdb** И используйте в ваших командах абсолютный путь до файла **initdb** нужной вам версии, например: /usr/lib/postgresql/14/bin/initdb --locale $LANG -E UTF8 -D '/var/lib/postgres/data'