Шаг 11 · База данных · 35 минут

PostgreSQL и MySQL на VPS: от установки до бэкапа

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

Что выбрать: PostgreSQL или MySQL

Для подавляющего большинства проектов разница не принципиальна — обе базы бесплатны, стабильны и умеют всё нужное для типового веб-приложения. Выбор чаще определяет не техника, а окружение.

PostgreSQL обычно берут, если приложение написано на Python/Django, Ruby, Go, или если заранее известно, что данные будут сложными — с JSON-полями, геоданными, строгими типами и сложными выборками. У Postgres более строгая и предсказуемая обработка типов данных.

MySQL (и его форк MariaDB) исторически стандарт для PHP-стека — WordPress, большинство CMS и многие готовые PHP-приложения ожидают именно его. Если разворачиваете готовый продукт, документация проекта обычно сама скажет, что нужно.

Если выбора нет и решаете сами — берите PostgreSQL, он не проигрывает MySQL ни в чём и растёт вместе с проектом. Дальше в статье покажем установку обеих, чтобы гайд подходил под любой случай.

Установка PostgreSQL

Ставим из стандартного репозитория Ubuntu — для большинства задач его версии достаточно, отдельный репозиторий PostgreSQL нужен только если требуется самая свежая мажорная версия.

sudo apt update
sudo apt install -y postgresql postgresql-contrib

Пакет postgresql-contrib добавляет полезные расширения (например, генерацию UUID), их лучше поставить сразу. После установки служба уже запущена и добавлена в автозагрузку, но стоит проверить:

sudo systemctl enable --now postgresql
sudo systemctl status postgresql

В выводе должно быть active (running). Если что-то не так — здесь же в логе будет причина, обычно связанная с портом или правами на каталог данных.

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

При установке создаётся системный пользователь postgres — это администратор СУБД, привязанный к одноимённому системному аккаунту Linux. Зайти под ним можно только локально, с сервера, без пароля — это называется peer-аутентификацией: PostgreSQL сверяет имя текущего Linux-пользователя с именем роли в базе.

sudo -u postgres psql

Внутри открывается консоль psql. Создадим роль (пользователя) с паролем и отдельную базу данных под приложение:

CREATE ROLE appuser WITH LOGIN PASSWORD 'сложный_пароль';
CREATE DATABASE appdb OWNER appuser;
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;
\q

Роль с паролем — это уже md5/scram-sha-256 аутентификация: при подключении по паролю PostgreSQL сверяет не системного пользователя, а хеш пароля, хранящийся в базе. Именно так будет заходить приложение — не через peer, а по логину и паролю, как обычно и ожидают драйверы баз данных.

Проверить подключение от имени нового пользователя:

psql -h 127.0.0.1 -U appuser -d appdb

Флаг -h 127.0.0.1 здесь важен: он заставляет клиента подключаться по TCP с паролем, а не пытаться через peer-механизм локального сокета.

Установка MySQL

Если приложению нужен именно MySQL (или его совместимый форк MariaDB), ставим так:

sudo apt update
sudo apt install -y mysql-server
sudo systemctl enable --now mysql

Сразу после установки стоит пройти базовую настройку безопасности — она задаст пароль root, уберёт тестовую базу и анонимных пользователей:

sudo mysql_secure_installation

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

sudo mysql

CREATE DATABASE appdb CHARACTER SET utf8mb4;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'сложный_пароль';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Обратите внимание на 'appuser'@'localhost' — в MySQL пользователь привязан не только к имени, но и к хосту, с которого он подключается. Это отдельный механизм ограничения доступа, независимый от файрвола, и его стоит держать в уме, если позже понадобится доступ извне.

Доступ извне и firewall

По умолчанию обе СУБД слушают только локальный интерфейс: PostgreSQL — параметр listen_addresses = 'localhost' в /etc/postgresql/*/main/postgresql.conf, MySQL — bind-address = 127.0.0.1 в /etc/mysql/mysql.conf.d/mysqld.cnf. Это осознанное решение разработчиков дистрибутива, а не недосмотр — базу данных не открывают в интернет.

Никогда не открывайте порт 5432 (PostgreSQL) или 3306 (MySQL) напрямую в интернет через ufw allow 5432 без ограничения по IP. База данных, доступная всему миру по паролю, — вопрос времени до подбора или утечки, даже при сложном пароле: она станет постоянной целью автоматического сканирования.

Если приложение и база физически на одном сервере — ничего менять не нужно, обмен идёт через localhost, и порт наружу вообще не нужен. Если же приложение работает на другом сервере, есть два нормальных варианта:

1. Разрешить доступ только с конкретного IP второго сервера через ufw:

sudo ufw allow from 203.0.113.10 to any port 5432

2. Более надёжный вариант — не открывать порт наружу вообще, а связать серверы приватной сетью через VPN, и подключаться к базе по внутреннему адресу. Как поднять такую сеть, разобрано в отдельном гайде: настройка VPN на WireGuard.

Подключение из приложения

Большинство фреймворков и библиотек принимают адрес базы одной строкой — DATABASE_URL. Для PostgreSQL она выглядит так:

DATABASE_URL=postgresql://appuser:сложный_пароль@127.0.0.1:5432/appdb

Для MySQL — аналогично, меняется только схема и порт:

DATABASE_URL=mysql://appuser:сложный_пароль@127.0.0.1:3306/appdb

Пароль в такой строке — обычный секрет, и хранить его нужно так же, как любой другой: в файле .env, который не попадает в репозиторий, а не прямо в коде приложения. Если приложение уже запущено в Docker — подробнее о том, зачем нужен .env и как его подключают к контейнеру, есть в гайде про Docker.

Резервное копирование

База без бэкапа — вопрос времени до потери данных: ошибка в миграции, неудачное обновление или банальный человеческий фактор. Сделать разовый дамп несложно.

Для PostgreSQL:

sudo -u postgres pg_dump appdb > appdb_$(date +%F).sql

Для MySQL:

mysqldump -u appuser -p appdb > appdb_$(date +%F).sql

Обе команды создают текстовый файл со структурой и данными — восстановить базу из него можно на любом сервере с той же СУБД. Ручной дамп подходит для разовой подстраховки перед рискованной операцией, но для регулярного расписания и автоматической ротации старых копий использовать cron напрямую неудобно и легко забыть — подробная настройка автоматических бэкапов разобрана в отдельном гайде: резервное копирование по расписанию.

Типовые ошибки

FATAL: password authentication failed for user — опечатка в пароле, либо в pg_hba.conf для этого пользователя настроен peer, а не md5/scram-sha-256.
Access denied for user 'appuser'@'localhost' — пользователь MySQL создан для другого хоста (например, 'appuser'@'%' вместо 'localhost'), либо не выполнен FLUSH PRIVILEGES.
Правки в postgresql.conf или mysqld.cnf не применились — после изменения конфигурационного файла нужен sudo systemctl restart postgresql (или mysql), простого сохранения файла недостаточно.
could not bind IPv4 address / порт уже занят — на сервере уже запущена другая СУБД или второй экземпляр той же самой, проверяется командой sudo ss -tlnp | grep -E '5432|3306'.
Приложение подключается по localhost, а не по 127.0.0.1, и падает с ошибкой сокета — некоторые драйверы трактуют эти адреса по-разному; если что-то не работает, стоит явно попробовать оба варианта.

Что дальше

База готова принимать данные — самое время дать ей применение: развернём файловое хранилище Nextcloud, которое как раз использует такую базу данных →