Как установить PostgreSQL на VPS: безопасность, удалённый доступ и резервное копирование

В этом руководстве установим PostgreSQL на VPS, создадим отдельную базу данных и пользователя, а затем настроим безопасный удалённый доступ, резервное копирование и несколько базовых параметров производительности.

В процессе:

• Установим PostgreSQL и проверим работу сервиса;
• Создадим отдельного пользователя и базу данных;
• Настроим postgresql.conf и pg_hba.conf;
• Разберём безопасный доступ через приватную сеть и SSH-туннель;
• Настроим firewall так, чтобы не открывать порт 5432 всему интернету;
• Создадим резервную копию базы с помощью pg_dump;
• Восстановим данные из backup-файла;
• Проверим основные параметры производительности PostgreSQL.

По умолчанию PostgreSQL не будет доступен напрямую из интернета. Для удалённого подключения используем SSH-туннель, а для связи между собственными серверами — приватную сеть, если она поддерживается инфраструктурой провайдера.

Что будем настраивать

В этом руководстве развернём PostgreSQL на отдельном VPS и сразу настроим его не только для локальной работы, но и для безопасного удалённого доступа, резервного копирования и базовой оптимизации.

Основная идея — не публиковать PostgreSQL напрямую в интернет без необходимости. Для подключения с рабочего компьютера будем использовать SSH-туннель, а для взаимодействия между серверами — приватную сеть, если она доступна в инфраструктуре провайдера.

Итоговая схема доступа

По умолчанию PostgreSQL будет слушать локальные подключения, а внешние соединения будем разрешать только контролируемым способом.

Схема для SSH-туннеля выглядит так:

Если база используется несколькими VPS внутри одной инфраструктуры, вместо публичного интернета можно задействовать приватную сеть:

В таком случае порт 5432 не требуется открывать для всего интернета. Достаточно разрешить подключения только из доверенной приватной подсети.

Что понадобится для работы

Для выполнения руководства понадобится:

• VPS с Ubuntu 24.04;
• Доступ по SSH с правами sudo;
• PostgreSQL из репозиториев Ubuntu;
• Отдельная база данных и пользователь;
• Доступ к файлам postgresql.conf и pg_hba.conf;
• firewall для ограничения сетевых подключений;
• SSH-клиент на локальном компьютере;
• Утилиты pg_dump, pg_restore и psql.

Для демонстрации будем считать, что PostgreSQL работает на одном VPS, а подключение администратора выполняется через SSH-туннель.

Устанавливаем PostgreSQL

Начнём с установки PostgreSQL и проверки, что сервер базы данных корректно запущен.

Установка пакетов PostgreSQL

Обновим индекс пакетов: sudo apt update

Установим PostgreSQL и набор дополнительных утилит: sudo apt install -y postgresql postgresql-contrib

После установки проверим версию клиента:

psql --version

Затем посмотрим состояние службы:

sudo systemctl status postgresql --no-pager

В выводе сервис должен находиться в состоянии active (exited) или active (running) в зависимости от способа запуска кластера в используемой версии Ubuntu.

Дополнительно можно проверить активные кластеры: pg_lsclusters

Обычно после установки будет создан кластер с именем main, работающий на порту 5432.

Если PostgreSQL установлен и кластер находится в статусе online, можно переходить к проверке подключения.

Проверка кластера и подключения через localhost

В Ubuntu PostgreSQL по умолчанию создаёт системного пользователя postgres, от имени которого можно выполнять административные операции.

Откроем консоль PostgreSQL: sudo -u postgres psql

Если подключение прошло успешно, приглашение изменится примерно на: postgres=#

Проверим информацию о сервере: SELECT version();

И текущий адрес подключения: SELECT inet_server_addr(), inet_server_port();

При локальном подключении через Unix socket адрес может отображаться как NULL — это нормально.

Выйти из psql можно командой: \q

На этом этапе PostgreSQL уже работает локально, но отдельной пользовательской базы и удалённого доступа пока нет. Их настроим дальше.

Создаём базу данных и пользователя

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

Создание отдельного пользователя PostgreSQL

Откроем psql от имени системного пользователя postgres: sudo -u postgres psql

Создадим нового пользователя: CREATE USER appuser WITH PASSWORD 'change_this_password';

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

Проверить созданную роль можно командой: \du

В списке должна появиться роль appuser.

Создание базы данных и назначение владельца

Теперь создадим отдельную базу данных и назначим нового пользователя её владельцем: CREATE DATABASE appdb OWNER appuser;

Проверим список баз: \l

В таблице должна присутствовать база appdb, а в столбце владельца — appuser.

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

Проверка подключения к новой базе

Проверим, что новый пользователь может подключиться к своей базе через localhost: psql -h 127.0.0.1 -U appuser -d appdb

Введите пароль, заданный при создании роли.

При успешном подключении приглашение изменится примерно на: appdb=>

Проверим текущего пользователя и базу: SELECT current_user, current_database();

Ожидаемый результат: appuser | appdb

Выйдем из psql: \q

Теперь база и пользователь готовы. Следующий этап — сетевые и серверные параметры PostgreSQL.

Настраиваем postgresql.conf

Основная конфигурация экземпляра PostgreSQL хранится в postgresql.conf. Через этот файл задаются сетевые параметры, использование памяти, лимиты подключений и многие другие настройки сервера.

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

Где находится основной конфигурационный файл

Узнать путь можно непосредственно через PostgreSQL: sudo -u postgres psql -t -P format=unaligned -c "SHOW config_file;"

На Ubuntu путь обычно выглядит примерно так: /etc/postgresql/16/main/postgresql.conf

Версия в каталоге может отличаться.

Аналогично можно узнать путь к pg_hba.conf: sudo -u postgres psql -t -P format=unaligned -c "SHOW hba_file;"

Перед изменением конфигурации лучше будет создать резервную копию:

sudo cp /etc/postgresql/16/main/postgresql.conf \

  /etc/postgresql/16/main/postgresql.conf.bak

Замените 16 на фактически установленную версию.

Настройка listen_addresses

По умолчанию PostgreSQL на Ubuntu обычно принимает TCP-подключения только локально. Текущее значение можно проверить: sudo -u postgres psql -c "SHOW listen_addresses;"

Если для администрирования используется только SSH-туннель, менять listen_addresses вообще не требуется: клиент подключается к локальному порту VPS через защищённый SSH-канал.

Если PostgreSQL должен принимать подключения от другого VPS через приватную сеть, откроем конфигурацию: sudo nano /etc/postgresql/16/main/postgresql.conf

И зададим, например: listen_addresses = 'localhost,172.30.16.205'

Здесь 172.30.16.205 приведён только как пример приватного адреса сервера с установленной СУБД. В своей конфигурации необходимо использовать фактический адрес интерфейса приватной сети.

Можно указать: listen_addresses = '*'

но это заставит PostgreSQL слушать все доступные интерфейсы. Само по себе это ещё не разрешает подключения, однако такой вариант требует особенно внимательно настроить pg_hba.conf и firewall. Для сервера базы данных безопаснее ограничить список нужными интерфейсами.

После изменения параметра потребуется перезапуск PostgreSQL.

Базовые параметры производительности

Кроме сетевой конфигурации, в postgresql.conf можно задать несколько базовых параметров использования памяти.

Для небольшого VPS с несколькими гигабайтами RAM в качестве отправной точки можно использовать, например:

shared_buffers = 512MB

work_mem = 8MB

maintenance_work_mem = 128MB

effective_cache_size = 1536MB

max_connections = 100

Эти значения не являются универсальной оптимальной конфигурацией. Их необходимо подбирать с учётом объёма RAM, количества одновременных соединений, характера запросов и нагрузки приложения.

Кратко назначение параметров:

• shared_buffers — память, выделяемая PostgreSQL под собственный кеш страниц;
• work_mem — лимит памяти для отдельных операций сортировки и хеширования;
• maintenance_work_mem — память для операций обслуживания, включая VACUUM, создание индексов и некоторые другие задачи;
• effective_cache_size — оценка доступного файлового кеша, которую планировщик учитывает при выборе плана запроса;
• max_connections — максимальное количество одновременных подключений.

После редактирования сохраним файл и перезапустим PostgreSQL: sudo systemctl restart postgresql

Проверим, что сервис поднялся без ошибок: sudo systemctl status postgresql --no-pager

И убедимся, что параметры применились:

sudo -u postgres psql -c "SHOW listen_addresses;"

sudo -u postgres psql -c "SHOW shared_buffers;"

sudo -u postgres psql -c "SHOW work_mem;"

На этом этапе PostgreSQL уже подготовлен к следующему уровню сетевой настройки. Дальше ограничим, какие именно клиенты имеют право подключаться, через pg_hba.conf.

Настраиваем pg_hba.conf

Файл pg_hba.conf определяет, какие клиенты могут подключаться к PostgreSQL, к каким базам и под какими пользователями. Даже если сервер слушает сетевой интерфейс через listen_addresses, само подключение не будет разрешено, пока для него нет подходящего правила в pg_hba.conf.

Как PostgreSQL проверяет клиентов

Правила в pg_hba.conf обрабатываются сверху вниз. PostgreSQL использует первое правило, которое подходит под тип подключения, базу, пользователя и адрес клиента.

Строка обычно имеет такой вид: TYPE  DATABASE  USER  ADDRESS  METHOD

Например: host  appdb  appuser  10.10.0.0/24  scram-sha-256

Такое правило означает:

• Разрешить TCP-подключение;
• Только к базе appdb;
• Только пользователю appuser;
• Только из подсети 10.10.0.0/24;
• Для аутентификации использовать scram-sha-256.

Текущий путь к файлу можно проверить командой: sudo -u postgres psql -t -P format=unaligned -c "SHOW hba_file;"

Перед редактированием лучше сделать резервную копию:

sudo cp /etc/postgresql/16/main/pg_hba.conf \

  /etc/postgresql/16/main/pg_hba.conf.bak

Замените 16 на установленную версию PostgreSQL.

Разрешение локальных подключений

Стандартная конфигурация Ubuntu уже содержит правила для локальных подключений через Unix socket и localhost.

Например:

Для подключения через SSH-туннель особенно важно правило для 127.0.0.1/32, потому что после прохождения через туннель PostgreSQL видит соединение как локальное.

Если пользователь appuser подключается по паролю, можно оставить общее правило для localhost или сделать его более узким: host    appdb    appuser    127.0.0.1/32    scram-sha-256

Так доступ будет разрешён только к нужной базе и только нужному пользователю.

Доступ из приватной сети

Если к PostgreSQL должно обращаться приложение на другом VPS через приватную сеть, добавим правило для конкретной подсети.

Например: host    appdb    appuser    172.30.16.0/24    scram-sha-256

Вместо всей подсети ещё безопаснее разрешить только конкретный IP сервера приложения: host    appdb    appuser    172.30.16.50/32    scram-sha-256

Такой вариант уменьшает количество узлов, с которых PostgreSQL вообще принимает попытки аутентификации.

После изменения pg_hba.conf достаточно перечитать конфигурацию: sudo systemctl reload postgresql

Либо выполнить: sudo -u postgres psql -c "SELECT pg_reload_conf();"

Почему не стоит разрешать 0.0.0.0/0

Иногда в примерах можно встретить правило: host    all    all    0.0.0.0/0    scram-sha-256

Оно разрешает попытки подключения с любого IPv4-адреса.

Даже при парольной аутентификации такая конфигурация без необходимости увеличивает поверхность атаки: публичный PostgreSQL могут постоянно сканировать, перебирать учётные данные и нагружать лишними соединениями.

Для рабочей инфраструктуры лучше использовать один из трёх вариантов:

• localhost + SSH-туннель;
• конкретный приватный IP;
• доверенную приватную подсеть.

Правила pg_hba.conf также стоит дополнять ограничениями на уровне firewall.

Настраиваем firewall

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

Для примера используем ufw.

Если пакет ещё не установлен: sudo apt install -y ufw

Разрешение SSH

Перед включением firewall обязательно разрешим SSH, иначе можно потерять доступ к VPS: sudo ufw allow OpenSSH

Проверим правила: sudo ufw status

После этого firewall можно включить: sudo ufw enable

Доступ к PostgreSQL только из доверенной сети

Если PostgreSQL используется только через SSH-туннель, правило для 5432 вообще не требуется: база остаётся доступной только локально.

Если же к ней подключается другой VPS через приватную сеть, разрешим порт только для нужной подсети: sudo ufw allow from 172.30.16.0/24 to any port 5432 proto tcp

Ещё лучше, если известен конкретный адрес сервера приложения: sudo ufw allow from 172.30.16.50 to any port 5432 proto tcp

Проверим итоговую конфигурацию: sudo ufw status numbered

Пример ожидаемой логики:

22/tcp     ALLOW IN    Anywhere

5432/tcp   ALLOW IN    172.30.16.50

Почему порт 5432 не нужно открывать всему интернету

Команда вида: sudo ufw allow 5432/tcp

разрешит подключения к PostgreSQL с любых адресов, если соответствующие сетевые настройки PostgreSQL также допускают внешний доступ.

Для большинства сценариев это не требуется.

Если администратор подключается к базе со своего компьютера, безопаснее использовать SSH-туннель. Если PostgreSQL нужен другому серверу той же инфраструктуры — приватную сеть и правило только для конкретного IP или подсети.

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

Даже если один уровень настроен слишком широко, остальные продолжают ограничивать доступ. Но на практике лучше сразу делать каждый из них максимально узким под конкретный сценарий.

Подключаемся через SSH-туннель

SSH-туннель позволяет работать с PostgreSQL удалённо, не публикуя порт 5432 в интернет. Клиент подключается к локальному порту на рабочем компьютере, а SSH перенаправляет трафик к PostgreSQL на VPS по уже защищённому соединению.

Как работает SSH-туннель

В нашем случае схема выглядит так:

Публично открывать 5432 при такой схеме не требуется. Снаружи VPS достаточно иметь доступ к SSH-порту, а PostgreSQL может продолжать принимать подключения только через localhost.

Порт 15432 выбран для примера. Можно использовать другой свободный локальный порт.

Создание локального туннеля

На рабочем компьютере откроем отдельное окно PowerShell или терминала и выполним: ssh -i "C:\path\to\private-key.pem" -N -L 15432:127.0.0.1:5432 ubuntu@203.0.113.10

Здесь:

• -N — не запускать удалённую командную оболочку;
• -L — создать локальное перенаправление;
• 15432 — порт на рабочем компьютере;
• 127.0.0.1:5432 — PostgreSQL на стороне VPS;
• 203.0.113.10 — пример публичного IP VPS из документационного диапазона.

В реальной команде необходимо подставить фактический адрес сервера и путь к SSH-ключу.

После запуска команда может не выводить никаких сообщений и просто оставаться активной в терминале. Для SSH-туннеля это нормальное поведение: пока процесс работает, локальный порт остаётся доступен.

В другом окне можно убедиться, что порт прослушивается. Например, в Windows: netstat -ano | findstr :15432

Подключение к PostgreSQL через локальный порт

Теперь PostgreSQL можно использовать так, будто он запущен на локальном компьютере: psql -h 127.0.0.1 -p 15432 -U appuser -d appdb

После ввода пароля откроется консоль базы: appdb=>

Проверим подключение: SELECT current_user, current_database(), inet_server_addr(), inet_server_port();

Первые два значения должны подтвердить, что соединение выполнено от имени appuser с базой appdb.

При этом локально клиент обращается к порту 15432, но непосредственно PostgreSQL продолжает работать на стандартном 5432 внутри VPS.

Закрыть соединение с PostgreSQL можно командой: \q

А сам SSH-туннель — сочетанием Ctrl+C в окне, где была запущена команда ssh.

Создаём резервную копию с помощью pg_dump

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

Резервная копия одной базы

Для начала добавим в демонстрационную базу небольшой набор данных, чтобы позднее можно было проверить восстановление.

Подключимся к ней: psql -h 127.0.0.1 -U appuser -d appdb

Создадим тестовую таблицу:

CREATE TABLE demo_notes (

    id SERIAL PRIMARY KEY,

    title TEXT NOT NULL

);

Добавим несколько записей:

INSERT INTO demo_notes (title)

VALUES

    ('PostgreSQL VPS guide'),

    ('Backup test'),

    ('Restore test');

Проверим содержимое: SELECT * FROM demo_notes;

После этого выйдем: \q

Создадим каталог для резервных копий: mkdir -p ~/postgresql-backups

Для сохранения базы в custom-формате выполним:

pg_dump -h 127.0.0.1 -U appuser -d appdb \

  -F c \

  -f ~/postgresql-backups/appdb.dump

После ввода пароля pg_dump создаст файл резервной копии.

Проверим его: ls -lh ~/postgresql-backups/appdb.dump

Для дополнительной проверки можно вывести содержимое архива без восстановления: pg_restore -l ~/postgresql-backups/appdb.dump | head

Форматы dump-файлов

pg_dump поддерживает несколько форматов резервной копии. На практике наиболее полезны два.

Обычный SQL-файл создаётся командой:

pg_dump -h 127.0.0.1 -U appuser -d appdb \

  -F p \

  -f ~/postgresql-backups/appdb.sql

Это текстовый файл с SQL-командами. Его удобно просматривать и при необходимости редактировать, а восстановление выполняется через psql.

Custom-формат:

pg_dump -h 127.0.0.1 -U appuser -d appdb \

  -F c \

  -f ~/postgresql-backups/appdb.dump

предназначен для работы с pg_restore. Он удобнее для более гибкого восстановления: можно выбирать отдельные объекты базы и управлять процессом восстановления.

Для большинства регулярных резервных копий одной базы custom-формат является удобным вариантом.

Важно учитывать, что pg_dump создаёт логическую резервную копию конкретной базы, а не полную копию всего экземпляра PostgreSQL. В частности, глобальные объекты кластера, такие как роли, при обычном pg_dump отдельно не сохраняются.

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

Восстанавливаем базу данных

Резервную копию стоит проверять не только фактом создания файла, но и реальным восстановлением. Для этого развернём dump в отдельную тестовую базу и убедимся, что таблицы и данные сохранились корректно.

Создание тестовой базы для восстановления

Создадим новую базу, в которую будем восстанавливать appdb.dump.

Откроем psql от имени администратора: sudo -u postgres psql

Создадим базу: CREATE DATABASE appdb_restore OWNER appuser;

Проверим, что она появилась: \l

После этого выйдем: \q

Восстановление через pg_restore или psql

Если резервная копия была создана в custom-формате -F c, для восстановления используется pg_restore:

pg_restore \

  -h 127.0.0.1 \

  -U appuser \

  -d appdb_restore \

  ~/postgresql-backups/appdb.dump

После ввода пароля начнётся восстановление объектов базы и данных.

Если нужно увидеть более подробный процесс, можно добавить --verbose:

pg_restore \

  --verbose \

  -h 127.0.0.1 \

  -U appuser \

  -d appdb_restore \

  ~/postgresql-backups/appdb.dump

Для обычного SQL-файла вместо pg_restore используется psql:

psql \

  -h 127.0.0.1 \

  -U appuser \

  -d appdb_restore \

  -f ~/postgresql-backups/appdb.sql

То есть выбор команды зависит от формата резервной копии: custom-архивы восстанавливаются через pg_restore, а текстовые SQL-файлы — через psql.

Проверка восстановленных данных

Подключимся к восстановленной базе: psql -h 127.0.0.1 -U appuser -d appdb_restore

Проверим список таблиц: \dt

В нём должна присутствовать таблица: demo_notes

Теперь убедимся, что восстановились и записи: SELECT * FROM demo_notes;

Ожидаемый результат:

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

Для рабочих систем имеет смысл периодически проводить такие проверки автоматически или на отдельном тестовом сервере. Само наличие файлов backup ещё не гарантирует, что они действительно пригодны для восстановления.

Базовая настройка производительности

PostgreSQL достаточно хорошо работает с настройками по умолчанию, однако несколько параметров обычно стоит адаптировать под ресурсы конкретного VPS.

Не существует универсальных значений, подходящих для любого сервера. Производительность зависит от объёма RAM, количества CPU, размера базы, характера запросов и числа одновременно работающих клиентов.

shared_buffers

Параметр shared_buffers определяет объём памяти, который PostgreSQL использует под собственный буферный кеш.

Проверить текущее значение можно командой: SHOW shared_buffers;

Для небольших выделенных серверов в качестве отправной точки часто выбирают значение порядка 20–25% доступной RAM, но это не жёсткое правило.

Например: shared_buffers = 512MB

Не стоит автоматически назначать PostgreSQL почти всю оперативную память: часть RAM нужна операционной системе, файловому кешу и другим процессам.

work_mem

Work_mem задаёт объём памяти, который может использовать отдельная операция сортировки, хеширования или построения некоторых промежуточных результатов.

Проверка: SHOW work_mem;

Например: work_mem = 8MB

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

Поэтому чрезмерно большое значение work_mem при большом количестве подключений способно привести к заметному росту потребления RAM.

maintenance_work_mem

Maintenance_work_mem используется для операций обслуживания, например создания индексов, VACUUM и некоторых вариантов ALTER TABLE.

Проверить значение: SHOW maintenance_work_mem;

Для небольшого VPS можно начать, например, с: maintenance_work_mem = 128MB

Поскольку такие операции обычно выполняются реже, чем обычные пользовательские запросы, этому параметру часто можно выделить больше памяти, чем work_mem.

effective_cache_size

Effective_cache_size не резервирует память напрямую.

Этот параметр сообщает планировщику PostgreSQL, какой объём данных предположительно может находиться в кеше PostgreSQL и операционной системы.

Проверить значение: SHOW effective_cache_size;

Например: effective_cache_size = 1536MB

Планировщик использует эту оценку при выборе между разными способами выполнения запросов, в том числе при принятии решения об использовании индексов.

Поэтому effective_cache_size следует воспринимать именно как оценку доступного кеша, а не как отдельно выделяемый PostgreSQL участок RAM.

max_connections

Max_connections задаёт максимальное количество одновременных подключений к PostgreSQL.

Проверка: SHOW max_connections;

Например: max_connections = 100

Повышать этот параметр «с запасом» не всегда полезно. Каждое соединение требует ресурсов, поэтому сотни или тысячи прямых подключений могут заметно увеличить потребление памяти.

Если приложению требуется большое количество короткоживущих соединений, зачастую лучше использовать пул подключений, например PgBouncer, а не просто увеличивать max_connections.

После изменения параметров, требующих перезапуска, применим конфигурацию: sudo systemctl restart postgresql

И проверим значения сразу одной командой:

sudo -u postgres psql -c "

SHOW shared_buffers;

SHOW work_mem;

SHOW maintenance_work_mem;

SHOW effective_cache_size;

SHOW max_connections;

"

Либо уже из открытой консоли psql:

SHOW shared_buffers;

SHOW work_mem;

SHOW maintenance_work_mem;

SHOW effective_cache_size;

SHOW max_connections;

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

Заключение

PostgreSQL на VPS можно развернуть за несколько минут, но для рабочей среды одной установки недостаточно. Важно сразу продумать, откуда база должна принимать подключения, какие пользователи получают доступ и как будет выполняться восстановление после сбоя.

В этом руководстве мы установили PostgreSQL, создали отдельную базу и пользователя, настроили postgresql.conf и pg_hba.conf, ограничили сетевой доступ через firewall и рассмотрели два безопасных сценария подключения: приватную сеть между серверами и SSH-туннель для удалённого администрирования. При этом порт 5432 не пришлось открывать всему интернету.

Также мы создали резервную копию базы через pg_dump, восстановили её в отдельную базу и проверили сохранность данных. В завершение настроили несколько базовых параметров памяти и подключений. Для реальной нагрузки эти значения стоит рассматривать только как отправную точку и корректировать по результатам мониторинга PostgreSQL и самого VPS.

FAQ

Нужно ли открывать порт 5432 в интернет?

В большинстве случаев — нет. Для административного доступа удобнее использовать SSH-туннель, а для взаимодействия между собственными VPS — приватную сеть.

Публичный доступ к 5432 имеет смысл только тогда, когда он действительно необходим архитектуре приложения. В таком случае его следует ограничивать firewall, правилами pg_hba.conf и доверенными IP-адресами.

Чем отличаются postgresql.conf и pg_hba.conf?

postgresql.conf управляет общими параметрами сервера PostgreSQL: сетевыми интерфейсами, памятью, количеством подключений и другими настройками.

pg_hba.conf определяет, кто именно имеет право подключаться: к какой базе, под каким пользователем, с какого адреса и каким способом проходить аутентификацию.

Для внешнего подключения обычно необходимо корректно настроить оба файла.

Нужно ли менять listen_addresses для SSH-туннеля?

Нет. Если SSH-туннель перенаправляет соединение на 127.0.0.1:5432 на стороне VPS, PostgreSQL может продолжать слушать только localhost.

Изменять listen_addresses требуется, например, когда к базе должен напрямую подключаться другой сервер через приватную сеть.

Почему PostgreSQL не принимает подключение после изменения listen_addresses?

Одного listen_addresses недостаточно. Необходимо проверить:

• Подходящее правило в pg_hba.conf;
• Настройки firewall;
• Правильность IP-адреса и порта;
• Был ли перезапущен PostgreSQL после изменения параметров, которым требуется restart.

Полезно также проверить состояние сервиса и журнал PostgreSQL.

Что лучше использовать для backup: SQL или custom-формат?

SQL-файл удобен тем, что представляет собой обычный текст с SQL-командами и восстанавливается через psql.

Custom-формат pg_dump -F c используется вместе с pg_restore и предоставляет больше возможностей при восстановлении отдельных объектов. Для регулярных резервных копий одной базы он часто удобнее.

Сохраняет ли pg_dump пользователей PostgreSQL?

Обычный pg_dump сохраняет содержимое и структуру конкретной базы данных, но не все глобальные объекты кластера.

Роли и другие глобальные объекты при необходимости можно сохранять отдельно, например с помощью pg_dumpall --globals-only.

Как часто нужно делать резервные копии?

Это зависит от того, сколько данных допустимо потерять при сбое. Если допустимая потеря составляет сутки — может быть достаточно ежедневных backup. Если несколько часов или минут — потребуется более частое резервное копирование или дополнительные механизмы, например архивирование WAL и Point-in-Time Recovery.

Важно не только создавать копии, но и периодически проверять реальное восстановление.

Как подобрать shared_buffers и work_mem?

Универсальных значений нет. Они зависят от объёма RAM, количества соединений и характера запросов.

shared_buffers обычно настраивается как заметная, но не подавляющая часть доступной памяти, а с work_mem следует быть особенно осторожным: этот лимит может использоваться одновременно несколькими операциями и несколькими подключениями.

Для нагруженной базы параметры лучше корректировать по результатам мониторинга, а не только по готовым формулам из интернета.

Список источников

1. PostgreSQL Documentation — Server Administration: Client Authentication
2. PostgreSQL Documentation — Server Configuration
3. PostgreSQL Documentation — pg_dump
4. PostgreSQL Documentation — pg_restore