PostgreSQL на VPS: установка, настройка и оптимизация на Ubuntu

# Установить зависимости
apt install -y curl ca-certificates

# Создать директорию для ключей
install -d /usr/share/postgresql-common/pgdg

# Скачать ключ репозитория
curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail \
  https://www.postgresql.org/media/keys/ACCC4CF8.asc

# Добавить репозиторий
sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
  https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  > /etc/apt/sources.list.d/pgdg.list'

apt update
apt install -y postgresql-16 postgresql-client-16

# Проверить статус
systemctl status postgresql@16-main

# Войти в psql от имени postgres
sudo -u postgres psql

# Внутри psql — установить пароль:
ALTER USER postgres WITH PASSWORD 'СЛОЖНЫЙ_ПАРОЛЬ_ЗДЕСЬ';

# Выйти
\q

sudo -u postgres psql

\l          -- список всех баз данных
\du         -- список пользователей и ролей
\c mydb     -- переключиться на базу mydb
\dt         -- список таблиц в текущей базе
\d users    -- структура таблицы users
\q          -- выход

# Создать пользователя с паролем
sudo -u postgres createuser --pwprompt appuser

# Создать базу данных, владелец — appuser
sudo -u postgres createdb --owner=appuser myapp_db

sudo -u postgres psql

-- Создать пользователя
CREATE USER appuser WITH PASSWORD 'ПАРОЛЬ';

-- Создать базу данных
CREATE DATABASE myapp_db OWNER appuser ENCODING 'UTF8' LC_COLLATE 'ru_RU.UTF-8' LC_CTYPE 'ru_RU.UTF-8' TEMPLATE template0;

-- Выдать права (нужно если база создана без --owner)
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO appuser;

sudo -u postgres psql -d myapp_db

GRANT ALL ON SCHEMA public TO appuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO appuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO appuser;

psql -h localhost -U appuser -d myapp_db

# При запросе пароля — введите пароль, заданный при создании пользователя

# ── Память ───────────────────────────────────────────────
shared_buffers = 512MB               # 25% от 2 ГБ
work_mem = 8MB                       # на каждую операцию сортировки
maintenance_work_mem = 128MB         # VACUUM, CREATE INDEX
effective_cache_size = 1GB           # ~50% RAM

# ── Соединения ───────────────────────────────────────────
max_connections = 100

# ── WAL и чекпоинты ──────────────────────────────────────
wal_buffers = 16MB
checkpoint_completion_target = 0.9

# ── Логирование медленных запросов (рекомендуется) ───────
log_min_duration_statement = 1000    # запросы дольше 1 секунды
log_line_prefix = '%t [%p] %u@%d '  # метаданные в логе

# Проверить синтаксис конфига
sudo -u postgres pg_ctlcluster 16 main status

# Перезапустить PostgreSQL (нужно при изменении shared_buffers)
systemctl restart postgresql@16-main

# Или перечитать конфиг без рестарта (для большинства параметров)
sudo -u postgres psql -c "SELECT pg_reload_conf();"

# Проверить, что параметры применились
sudo -u postgres psql -c "SHOW shared_buffers; SHOW work_mem;"

# Слушать на всех интерфейсах (или укажите конкретный IP)
listen_addresses = '*'

# Формат: TYPE  DATABASE  USER  ADDRESS  METHOD

# Локальные соединения (без изменений):
local   all       postgres                    peer
local   all       all                         md5

# Удалённый доступ — только с конкретного IP приложения:
host    myapp_db  appuser   1.2.3.4/32        scram-sha-256

# Если нужен доступ из подсети (например, все серверы в 10.0.0.0/24):
# host  myapp_db  appuser   10.0.0.0/24       scram-sha-256

# Только для IP приложения:
ufw allow from 1.2.3.4 to any port 5432

# Проверить правила:
ufw status

# Перезапустить PostgreSQL:
systemctl restart postgresql@16-main

# Бэкап одной базы (рекомендуемый формат -Fc — сжатый, поддерживает выборочный restore)
sudo -u postgres pg_dump -Fc myapp_db -f /backup/myapp_db_$(date +%Y%m%d).dump

# Бэкап в читаемом SQL (удобен для переноса данных)
sudo -u postgres pg_dump myapp_db > /backup/myapp_db_$(date +%Y%m%d).sql

# Восстановить в существующую (пустую) базу
pg_restore -h localhost -U postgres -d myapp_db /backup/myapp_db_20260305.dump

# Восстановить из SQL-дампа
psql -U postgres -d myapp_db -f /backup/myapp_db_20260305.sql

#!/bin/bash
BACKUP_DIR="/backup/postgres"
KEEP_DAYS=14    # хранить 14 дней

mkdir -p "$BACKUP_DIR"

# Бэкап всех баз по отдельности
for DB in $(sudo -u postgres psql -tAc "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres'"); do
  sudo -u postgres pg_dump -Fc "$DB" -f "$BACKUP_DIR/${DB}_$(date +%Y%m%d_%H%M).dump"
  echo "$(date): Backed up $DB"
done

# Удалить бэкапы старше KEEP_DAYS дней
find "$BACKUP_DIR" -name "*.dump" -mtime +$KEEP_DAYS -delete

chmod +x /usr/local/bin/pg_backup.sh

# Добавить в crontab (запуск каждый день в 3:00)
crontab -e
# Добавить строку:
0 3 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

# Полный бэкап кластера — нужен при миграции сервера
sudo -u postgres pg_dumpall -f /backup/cluster_$(date +%Y%m%d).sql

# Только роли и пользователи (без данных баз)
sudo -u postgres pg_dumpall --roles-only -f /backup/roles_$(date +%Y%m%d).sql

apt install -y postgresql-contrib-16

shared_preload_libraries = 'pg_stat_statements'

# Дополнительные параметры (опционально):
pg_stat_statements.max = 10000   # хранить топ-10000 запросов
pg_stat_statements.track = all   # отслеживать вложенные запросы тоже

systemctl restart postgresql@16-main

# Создать расширение в нужной базе (нужно для каждой базы отдельно)
sudo -u postgres psql -d myapp_db -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

sudo -u postgres psql -d myapp_db -c "
SELECT
  round(mean_exec_time::numeric, 2) AS avg_ms,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  left(query, 80) AS query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
"

sudo -u postgres psql -d myapp_db -c "
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
"

sudo -u postgres psql -d myapp_db -c "
SELECT
  shared_blks_read AS disk_reads,
  shared_blks_hit  AS cache_hits,
  calls,
  left(query, 80) AS query
FROM pg_stat_statements
WHERE shared_blks_read > 1000
ORDER BY shared_blks_read DESC
LIMIT 10;
"

sudo -u postgres psql -d myapp_db -c "SELECT pg_stat_statements_reset();"

# Статус
systemctl status postgresql@16-main

# Запустить / остановить / перезапустить
systemctl start postgresql@16-main
systemctl stop postgresql@16-main
systemctl restart postgresql@16-main

# Перечитать конфиг без перезапуска
systemctl reload postgresql@16-main

# Лог ошибок (последние 50 строк)
tail -50 /var/log/postgresql/postgresql-16-main.log