Установка и защита PostgreSQL
Установите PostgreSQL 18 из официального репозитория на Debian или Ubuntu: роли и права, удалённый доступ по TLS, автоматические резервные копии и проверка восстановления.
На этой странице
Ваши значения
Укажите их один раз, и команды в руководстве обновятся.
Это руководство объясняет, как установить PostgreSQL на ваш VPS или выделенный сервер из официального репозитория проекта, создать роли с ограниченными правами, при необходимости открыть зашифрованный удалённый доступ и настроить резервное копирование, которое вы сможете восстановить.
В итоге PostgreSQL будет слушать только локальные подключения, у каждого приложения будет своя роль, а ежедневная резервная копия будет создаваться и проверяться тестом восстановления.
Заказать сервер
Для размещения вашей базы данных HostMyServers предлагает несколько тарифов:
Предварительные требования
- Сервер под управлением Debian 12 или 13 либо Ubuntu 22.04 или 24.04
- Доступ по SSH с пользователем, у которого есть
sudo - Обновлённая система (
sudo apt update && sudo apt upgrade) - Только для прямого удалённого доступа: доменное имя, указывающее на сервер
Порты, которые нужно открыть
| Назначение | Порт | Нужно открывать? |
|---|---|---|
| Приложения на том же сервере | 5432/tcp локально | Нет |
| Удалённый доступ через SSH-туннель (рекомендуется) | 22/tcp или ваш порт SSH | Уже открыт для администрирования |
| Прямой удалённый доступ по TLS | 5432/tcp | Да, только с IP-адреса ваших клиентов |
Установка PostgreSQL из официального репозитория
Debian и Ubuntu поставляют PostgreSQL, но его версия остаётся неизменной на протяжении всего срока жизни дистрибутива. Репозиторий PGDG (apt.postgresql.org) содержит все поддерживаемые версии.
На момент написания последняя основная версия — PostgreSQL 18 (18.7). Актуальную версию смотрите в политике версий.
-
Установите необходимые инструменты и загрузите ключ репозитория:
Терминалsudo apt install -y curl ca-certificates gpg sudo install -d /usr/share/postgresql-common/pgdg sudo curl -fsSL -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc https://www.postgresql.org/media/keys/ACCC4CF8.asc -
Проверьте отпечаток ключа:
Терминалgpg --show-key /usr/share/postgresql-common/pgdg/apt.postgresql.org.ascОн должен совпадать с
B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8. Если это не так, остановитесь. -
Добавьте репозиторий:
Терминал. /etc/os-release sudo tee /etc/apt/sources.list.d/pgdg.sources > /dev/null <<EOF Types: deb URIs: https://apt.postgresql.org/pub/repos/apt Suites: ${VERSION_CODENAME}-pgdg Architectures: $(dpkg --print-architecture) Components: main Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc EOF -
Установите сервер:
Терминалsudo apt update sudo apt install -y postgresql-18Пакет автоматически создаёт и запускает экземпляр с именем
18/main.
Проверка установки
pg_lsclusters
sudo systemctl status postgresql@18-main --no-pager
sudo -u postgres psql -c "SELECT version();"
sudo ss -tlnp | grep 5432pg_lsclusters должен показать экземпляр 18 main со статусом online. ss должен показать прослушивание на 127.0.0.1:5432 и [::1]:5432, а не на 0.0.0.0.
| Элемент | Расположение |
|---|---|
| Конфигурация | /etc/postgresql/18/main/ |
| Дополнительные параметры | /etc/postgresql/18/main/conf.d/ |
| Правила доступа | /etc/postgresql/18/main/pg_hba.conf |
| Данные | /var/lib/postgresql/18/main/ |
| Журнал | /var/log/postgresql/postgresql-18-main.log |
Как работает аутентификация
Файл pg_hba.conf определяет, кто может подключаться, к какой базе и каким способом. PostgreSQL читает его строки сверху вниз и применяет первую подходящую. Если этот способ не срабатывает, следующие строки не проверяются.
Выведите действующие правила:
sudo -u postgres psql -c "SELECT line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules;"В новой установке вы должны увидеть два способа:
peerдля локальных подключений через сокет: имя системного пользователя должно совпадать с именем роли. Поэтомуsudo -u postgres psqlработает без пароля.scram-sha-256для TCP-подключений с127.0.0.1и::1: запрашивается пароль.
Создание ролей и базы данных
Не используйте суперпользователя postgres в ваших приложениях. В следующем примере создаются база appdb, роль-владелец app_owner и роль только для чтения app_reader.
-
Создайте роли. Каждая команда запрашивает пароль, поэтому он не попадает в историю оболочки:
Терминалsudo -u postgres createuser --pwprompt app_owner sudo -u postgres createuser --pwprompt app_reader -
Создайте базу:
Терминалsudo -u postgres createdb --owner=app_owner appdb -
Ограничьте доступ к этой базе:
Терминалsudo -u postgres psql -d appdbSQLREVOKE ALL ON DATABASE appdb FROM PUBLIC; GRANT CONNECT ON DATABASE appdb TO app_reader; GRANT USAGE ON SCHEMA public TO app_reader; GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader; ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO app_reader; \qПоследняя инструкция также даёт
app_readerправо на чтение будущих таблиц, которые создастapp_owner. -
Проверьте подключение с паролем:
Терминалpsql -h 127.0.0.1 -U app_owner -d appdb -c "CREATE TABLE restore_check (id int PRIMARY KEY, note text);" psql -h 127.0.0.1 -U app_owner -d appdb -c "INSERT INTO restore_check VALUES (1, 'sauvegarde OK');" psql -h 127.0.0.1 -U app_reader -d appdb -c "SELECT * FROM restore_check;"Попытка записи от имени
app_readerдолжна завершиться ошибкойpermission denied for table restore_check. Таблицаrestore_checkпонадобится ниже для проверки восстановления.
Удалённый доступ к базе
Рекомендуемый вариант: SSH-туннель
Туннель не требует изменений ни в PostgreSQL, ни в брандмауэре. На вашем компьютере выполните:
ssh -N -L 5433:127.0.0.1:5432 ВАШ_ПОЛЬЗОВАТЕЛЬ@IP_ВАШЕГО_СЕРВЕРАНе закрывайте эту команду, затем подключите клиент к локальному порту 5433:
psql -h 127.0.0.1 -p 5433 -U app_owner -d appdbПродвинутый вариант: прямое подключение по TLS
Используйте этот вариант, если внешний сервис не может работать через SSH. Для него нужны доменное имя, например db.ваш-домен.com, и действительный сертификат: получить его с помощью Certbot можно по нашему руководству Установка SSL-сертификата Let's Encrypt.
-
Создайте скрипт, который копирует сертификат для PostgreSQL и будет запускаться при каждом продлении:
Терминалsudo nano /etc/letsencrypt/renewal-hooks/deploy/postgresql.shТерминал#!/bin/sh set -e DOMAIN="db.ваш-домен.com" [ "$RENEWED_LINEAGE" = "/etc/letsencrypt/live/$DOMAIN" ] || exit 0 install -o postgres -g postgres -m 644 "$RENEWED_LINEAGE/fullchain.pem" /etc/postgresql/18/main/server.crt install -o postgres -g postgres -m 600 "$RENEWED_LINEAGE/privkey.pem" /etc/postgresql/18/main/server.key systemctl reload postgresql@18-main -
Сделайте его исполняемым и запустите в первый раз:
Терминалsudo chmod 755 /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh sudo RENEWED_LINEAGE=/etc/letsencrypt/live/db.ваш-домен.com /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh -
Создайте файл конфигурации:
Терминалsudo nano /etc/postgresql/18/main/conf.d/90-remote-access.confКонфигурацияlisten_addresses = '*' ssl = on ssl_cert_file = '/etc/postgresql/18/main/server.crt' ssl_key_file = '/etc/postgresql/18/main/server.key' ssl_min_protocol_version = 'TLSv1.2' -
Добавьте в конец
/etc/postgresql/18/main/pg_hba.confправило, ограниченное одной базой, одной ролью и одним адресом:Текстhostssl appdb app_owner ВАШ_IP/32 scram-sha-256hostsslотклоняет любое незашифрованное подключение. Для адреса IPv6 используйте суффикс/128. -
Разрешите в брандмауэре только ваш адрес. С UFW:
Терминалsudo ufw allow from ВАШ_IP to any port 5432 proto tcp -
Перезапустите PostgreSQL, так как
listen_addressesне применяется при простой перезагрузке конфигурации:Терминалsudo systemctl restart postgresql@18-main sudo -u postgres psql -c "SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;"Вторая команда не должна вернуть ни одной строки.
-
Подключитесь с вашего компьютера клиентом
psqlверсии 16 или выше:Терминалpsql "host=db.ваш-домен.com port=5432 dbname=appdb user=app_owner sslmode=verify-full sslrootcert=system"Затем убедитесь, что сеанс зашифрован:
SQLSELECT ssl, version FROM pg_stat_ssl WHERE pid = pg_backend_pid();
Автоматизация резервного копирования
pg_dump экспортирует базу без её остановки, но не сохраняет ни роли, ни их пароли. Поэтому следующий скрипт также экспортирует эти глобальные объекты командой pg_dumpall --globals-only.
-
Создайте каталог назначения:
Терминалsudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql -
Создайте скрипт:
Терминалsudo nano /usr/local/sbin/postgresql-backup.shТерминал#!/bin/bash set -euo pipefail umask 077 DEST="/var/backups/postgresql" RETENTION_DAYS=14 STAMP="$(date +%F_%H%M%S)" TARGET="$DEST/$STAMP" mkdir -p "$TARGET" pg_dumpall --globals-only -f "$TARGET/globals.sql" psql -AtX -d postgres -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate" | while IFS= read -r db; do pg_dump -Fc -d "$db" -f "$TARGET/$db.dump" done find "$DEST" -mindepth 1 -maxdepth 1 -type d -mtime +"$RETENTION_DAYS" -exec rm -rf {} + -
Создайте службу:
Терминалsudo chmod 755 /usr/local/sbin/postgresql-backup.sh sudo nano /etc/systemd/system/postgresql-backup.serviceКонфигурация[Unit] Description=PostgreSQL backup After=postgresql.service [Service] Type=oneshot User=postgres Group=postgres ExecStart=/usr/local/sbin/postgresql-backup.sh -
Создайте таймер:
Терминалsudo nano /etc/systemd/system/postgresql-backup.timerКонфигурация[Unit] Description=Daily PostgreSQL backup [Timer] OnCalendar=*-*-* 02:30:00 RandomizedDelaySec=10min Persistent=true [Install] WantedBy=timers.target -
Включите его и запустите первую резервную копию:
Терминалsudo systemctl daemon-reload sudo systemctl enable --now postgresql-backup.timer sudo systemctl start postgresql-backup.service sudo systemctl status postgresql-backup.service --no-pager sudo -u postgres ls -lR /var/backups/postgresql
Проверка восстановления
Резервная копия имеет ценность, только если вы уже успешно её восстанавливали. Для этого теста используется временная база, и рабочая база не затрагивается.
STAMP="$(sudo -u postgres ls -1 /var/backups/postgresql | tail -n 1)"
sudo -u postgres createdb restore_test
sudo -u postgres pg_restore --no-owner --no-privileges --exit-on-error -d restore_test "/var/backups/postgresql/$STAMP/appdb.dump"
sudo -u postgres psql -d restore_test -c "SELECT * FROM restore_check;"
sudo -u postgres dropdb restore_testВы должны увидеть строку sauvegarde OK. Повторяйте эту проверку регулярно и сравнивайте также несколько важных для вашего приложения таблиц.
Восстановление после сбоя
На новом сервере с установленным PostgreSQL либо после остановки приложений и удаления повреждённой базы:
STAMP="2026-11-18_023000"
sudo -u postgres psql -X -f "/var/backups/postgresql/$STAMP/globals.sql" postgres
sudo -u postgres dropdb --if-exists appdb
sudo -u postgres pg_restore --create --exit-on-error -d postgres "/var/backups/postgresql/$STAMP/appdb.dump"
sudo -u postgres psql -d appdb -c "ANALYZE;"Замените значение STAMP нужным каталогом. Ошибка role "postgres" already exists при восстановлении ролей является нормальной.
Обновление PostgreSQL
Минорные версии (например, с 18.6 до 18.7) исправляют ошибки и уязвимости. Они устанавливаются вместе с обновлением системы и перезапускают службу:
sudo apt update
sudo apt upgradeНовая основная версия устанавливается рядом со старой и требует миграции. Сначала запустите резервное копирование, затем измените номера версий:
OLD=18
NEW=19
sudo systemctl start postgresql-backup.service
sudo apt install -y "postgresql-$NEW"
pg_lsclusters
sudo pg_dropcluster --stop "$NEW" main
sudo pg_upgradecluster -v "$NEW" "$OLD" main
pg_lsclusterspg_dropcluster здесь удаляет пустой экземпляр, созданный новым пакетом: убедитесь с помощью pg_lsclusters, что в нём нет ничего полезного. Затем pg_upgradecluster помещает новую версию на прежний порт и сохраняет старую в остановленном состоянии. Когда вы проверите свои приложения, удалите её:
sudo pg_dropcluster "$OLD" main
sudo apt purge "postgresql-$OLD" "postgresql-client-$OLD"Не забудьте изменить номер версии в скрипте продления сертификата, если вы им пользуетесь.
Если что-то пошло не так
FATAL: no pg_hba.conf entry for host
Ни одно правило не соответствует адресу, роли, базе или шифрованию. В сообщении указаны значения, полученные сервером. Убедитесь, что они соответствуют вашей строке hostssl, включая реальный публичный IP-адрес клиента, затем перезагрузите конфигурацию:
sudo systemctl reload postgresql@18-mainFATAL: password authentication failed for user
Пароль неверен либо у роли его нет. Задайте его заново из административного сеанса:
sudo -u postgres psql -c "\password app_owner"FATAL: Peer authentication failed for user
Вы подключаетесь через локальный сокет с ролью, отличной от вашего системного пользователя. Добавьте -h 127.0.0.1, чтобы использовать пароль, либо запустите команду от нужного пользователя через sudo -u.
Connection refused или превышено время ожидания
pg_lsclusters
sudo ss -tlnp | grep 5432
sudo ufw status
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.logConnection refused обычно означает, что PostgreSQL остановлен или не слушает на этом адресе. Превышение времени ожидания скорее указывает на брандмауэр.
permission denied for table или for schema public
У роли нет необходимых прав. Выполните GRANT заново, подключившись к нужной базе. Для существующих таблиц, созданных другой ролью, ALTER DEFAULT PRIVILEGES недостаточно: он касается только будущих таблиц.
Служба не запускается после изменения
Журнал указывает файл и строку, вызвавшие проблему:
sudo journalctl -u postgresql@18-main -n 50 --no-pager
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.logПри использовании собственного сертификата проверьте также, что server.key принадлежит postgres и имеет права 600.
could not change directory to "/root"
Это сообщение появляется, когда вы запускаете sudo -u postgres из каталога, который postgres не может прочитать. Оно безвредно: перейдите в /tmp, и оно исчезнет.