Использование PostgreSQL
Использование 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
Альтернативная команда:
root@5new:/$ postgres -V
Для определения версии клиента:
root@5new:/$ psql --version
Ещё один вариант определения версии PostgreSQL:
root@5new:/$ sudo -u postgres psql postgres -c 'SELECT version()' | grep PostgreSQL
Чуть короче:
root@5new:/$ sudo -u postgres psql -c 'SELECT version()' | grep PostgreSQL
Пример вывода:
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;
Для любопытных:
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
Что особенно важно, 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.
Оно означает, что в системе 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
То есть по умолчанию используется 13, устаревшая версия.
Удаление старых версий пакетов, например, командой:
apt remove postgresql-13 postgresql-client-13
ситуацию не меняет. Если вам нужно перенести базы данных из устаревшей версии в новую, то верните устаревшие пакеты, если вы успели их удалить.
Последующие действия подразумевают, что вы
-
установили новую версию PostgreSQL, но ещё не использовали её, то есть не сохраняли базы данных, поскольку файлы новой версии будут удалены.
-
хотите перенести старые база данных в новый формат
С помощью следующей команды просмотрите доступные кластеры:
pg_lsclusters
На скриншоте только один из них 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
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
Теперь используется 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 </dev/null &
Чтобы запустить 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: система БД выключена
В англоязычной версии ошибка выглядит так:
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
Как можно видеть на скриншоте, исполнимый файл присутствует для двух версий сервера:
* /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
Для psql необходимо имя пользователя и если оно не указано явно, то передаётся имя пользователя системы. Но поскольку данный пользователь не существует на сервере PostgreSQL, то возникает указанная выше ошибка.
Вы можете создать пользователя с любы именем, как это показано выше и ошибка исчезнет.
По умолчанию присутствует пользователь postgres, поэтому вы можете подключиться от его имени:
-u postgres psql
Вы должны указать его расположение в параметре --config-file или -D, либо установить переменную окружения PGDATA
При запуске postgres вы можете столкнуться с ошибкой:
postgres не знает, где найти файл конфигурации сервера.
Вы должны указать его расположение в параметре --config-file или -D, либо установить переменную окружения PGDATA.
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
initdb: command not found
Смотрите объяснение данной проблемы, а также дополнительные пути устранения в описании аналогичной ошибки: 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'





