Як встановити PostgreSQL на VPS і безпечно підключатися

Сам PostgreSQL ставиться однією командою. Помиляються зазвичай із віддаленим доступом: відкривають порт 5432 усьому світу, ставлять пароль і дивуються, чому логи забиті спробами входу. У цьому гайді ставимо Postgres на Ubuntu 24.04, створюємо базу й розбираємо два безпечні способи підключитися ззовні.

1. Встановлення

apt update && apt install -y postgresql
systemctl status postgresql --no-pager

В Ubuntu 24.04 це PostgreSQL 16. За замовчуванням він слухає лише localhost — так і залиште, якщо немає причин інакше.

2. Користувач і база

sudo -u postgres psql <<'SQL'
CREATE USER app WITH PASSWORD 'use-a-long-random-password';
CREATE DATABASE appdb OWNER app;
SQL

Застосунки на тому самому сервері підключаються за рядком postgresql://app:PASSWORD@localhost:5432/appdb.

3. Підключення з ноутбука: SSH-тунель (радимо)

На сервері нічого відкривати й налаштовувати не треба. Тунель прокидає локальний порт через SSH:

ssh -N -L 5433:localhost:5432 root@203.0.113.10

На NAT-тарифі додайте свій персональний SSH-порт із панелі:

ssh -N -p 20123 -L 5433:localhost:5432 root@your-nat-host

Поки тунель працює, спрямуйте клієнт бази даних на localhost:5433. Усе шифрує SSH, а порт 5432 лишається закритим для інтернету. У GUI-клієнтах на кшталт DBeaver і pgAdmin є вкладка «SSH tunnel», яка робить те саме.

4. Прямий доступ для іншого сервера (білий список)

Якщо серверу застосунку десь іще потрібно підключатися напряму, дозвольте лише його IP. Для цього сервер бази має бути на тарифі з виділеним IP: NAT-тарифи не приймають вхідних з'єднань, окрім SSH.

У /etc/postgresql/16/main/postgresql.conf:

listen_addresses = '*'

У кінець /etc/postgresql/16/main/pg_hba.conf:

hostssl  appdb  app  198.51.100.20/32  scram-sha-256

Відкрийте файрвол для цієї однієї адреси й перезапустіть:

ufw allow from 198.51.100.20 to any port 5432 proto tcp
systemctl restart postgresql

hostssl вимагає зашифрованого з'єднання — пакет Ubuntu постачається із самопідписаним сертифікатом, тож клієнтам варто підключатися з sslmode=require.

5. Розумне первинне налаштування

Для сервера з 2 ГБ RAM у postgresql.conf:

shared_buffers = 512MB
effective_cache_size = 1536MB
work_mem = 8MB

Після зміни перезапустіть. Більшості невеликих застосунків цього вистачить; далі налаштовуйте, дивлячись на реальні запити.

6. Бекапи

Нічний дамп зі зберіганням тиждень:

mkdir -p /var/backups/pg && chown postgres /var/backups/pg
cat > /etc/cron.d/pg-backup <<'EOF'
30 3 * * * postgres pg_dump -Fc appdb > /var/backups/pg/appdb-$(date +\%u).dump
EOF

-Fc дає стиснутий дамп, який відновлюється через pg_restore. Копіюйте дампи й за межі сервера; Managed Backups додають щоденні точки відновлення всієї машини.

Пов'язане

• VPS для бази PostgreSQL
• VPS для баз даних
• Як передавати файли на VPS — щоб переносити дампи
• Бекап VPS за допомогою restic: шифрування й копія поза сервером