Как установить и настроить PostgreSQL на VPS Linux: полное руководство

PostgreSQL — одна из самых мощных и зрелых реляционных систем управления базами данных (РСУБД) с открытым исходным кодом. Она выделяется строгим соблюдением стандартов SQL, полной поддержкой ACID-транзакций, широкими возможностями расширения и отличной производительностью при работе с большими объёмами данных.

Если вы разворачиваете современные веб-приложения, REST API, ERP-системы или платформы аналитики на VPS NVMe, PostgreSQL может стать оптимальным выбором. Это руководство покажет, как установить, защитить и оптимизировать PostgreSQL на VPS под управлением Ubuntu 22.04 или Debian 12.

Почему PostgreSQL, а не MySQL?

Прежде чем перейти к установке, разберёмся, в чём преимущества PostgreSQL:

Расширенные типы данных: нативная поддержка JSON/JSONB, массивов, hstore, геометрических типов
Строгое соответствие SQL: PostgreSQL точнее следует стандартам SQL
Надёжные транзакции: полная поддержка ACID, включая транзакционный DDL
Мощные расширения: PostGIS (геоданные), pg_trgm (полнотекстовый поиск), TimescaleDB и другие
Масштабируемость: идеален для больших баз данных с продвинутой индексацией (GiST, GIN, BRIN)

Для серьёзных проектов на VPS SSD PostgreSQL обеспечивает надёжную и масштабируемую основу.

Предварительные требования

VPS с Ubuntu 22.04 LTS или Debian 12
Доступ root или пользователь с правами sudo
Минимум 1 ГБ ОЗУ (рекомендуется 2 ГБ и более)
Активное SSH-подключение

Если у вас ещё нет сервера, вы можете заказать VPS Linux у CLIQHOST прямо сейчас.

Шаг 1: Обновление системы

Перед установкой обновите список пакетов и систему:

sudo apt update && sudo apt upgrade -y

Это предотвратит конфликты зависимостей и гарантирует установку актуальных пакетов.

Шаг 2: Установка PostgreSQL

Вариант 1 — Из репозитория Ubuntu/Debian (быстрый способ)

sudo apt install postgresql postgresql-contrib -y

Пакет postgresql-contrib включает полезные расширения: uuid-ossp, pg_stat_statements, hstore и другие.

Вариант 2 — Из официального репозитория PostgreSQL (актуальная версия)

Для установки последней стабильной версии (например, PostgreSQL 16):

# Установка зависимостей
sudo apt install -y gnupg2 wget

# Добавление GPG-ключа
wget -qO - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Добавление репозитория
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | \
  sudo tee /etc/apt/sources.list.d/pgdg.list

# Обновление и установка
sudo apt update && sudo apt install postgresql-16 postgresql-client-16 -y

Проверка установки

sudo systemctl status postgresql
psql --version

Вы должны увидеть: psql (PostgreSQL) 16.x

Шаг 3: Первое подключение — пользователь postgres

PostgreSQL автоматически создаёт системного пользователя postgres, связанного с суперпользователем базы данных. Подключитесь следующим образом:

sudo -i -u postgres
psql

Или напрямую:

sudo -u postgres psql

Вы увидите приглашение postgres=#. Для выхода используйте \q.

Шаг 4: Защита установки PostgreSQL

Это наиболее важный шаг. Незащищённая установка — серьёзная уязвимость.

4.1 Установка пароля для суперпользователя

sudo -u postgres psql
ALTER USER postgres WITH PASSWORD 'ОченьНадёжныйПароль!2024';
\q

4.2 Создание выделенного пользователя (рекомендуется)

Никогда не используйте суперпользователя postgres напрямую в приложениях. Создавайте отдельных пользователей с минимальными привилегиями:

CREATE USER app_user WITH PASSWORD 'ПарольПриложения!456';
CREATE DATABASE app_db OWNER app_user;
GRANT ALL PRIVILEGES ON DATABASE app_db TO app_user;

4.3 Настройка файла pg_hba.conf

Файл pg_hba.conf управляет аутентификацией. Откройте его:

sudo nano /etc/postgresql/16/main/pg_hba.conf

Для безопасных локальных подключений убедитесь, что используется метод scram-sha-256:

# TYPE  DATABASE  USER  ADDRESS      METHOD
local   all       all                scram-sha-256
host    all       all   127.0.0.1/32 scram-sha-256
host    all       all   ::1/128      scram-sha-256

После изменений перезапустите сервис:

sudo systemctl restart postgresql

4.4 Отключение удалённого доступа по умолчанию

Если удалённый доступ к PostgreSQL не нужен, оставьте listen_addresses со значением localhost в postgresql.conf:

sudo nano /etc/postgresql/16/main/postgresql.conf

listen_addresses = 'localhost'

Если удалённый доступ необходим, укажите точный IP-адрес и защитите порт 5432 с помощью правильно настроенного файрвола.

Шаг 5: Базовые операции в PostgreSQL

Создание и просмотр баз данных

-- Список баз данных
\l

-- Создание базы данных
CREATE DATABASE new_project;

-- Подключение к базе данных
\c new_project

-- Удаление базы данных
DROP DATABASE new_project;

Создание таблиц

CREATE TABLE clients (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CRUD-операции

-- Вставка
INSERT INTO clients (name, email) VALUES ('Иван Иванов', '[email protected]');

-- Чтение
SELECT * FROM clients WHERE email LIKE '%@example.md';

-- Обновление
UPDATE clients SET name = 'Иван Петров' WHERE id = 1;

-- Удаление
DELETE FROM clients WHERE id = 1;

Полезные команды psql

Команда	Описание
`\l`	Список баз данных
`\c <db>`	Подключение к БД
`\dt`	Список таблиц
`\d <table>`	Структура таблицы
`\du`	Список пользователей
`\timing`	Время выполнения запросов
`\q`	Выход из psql

Шаг 6: Оптимизация производительности PostgreSQL

Значения по умолчанию в postgresql.conf весьма консервативны. На VPS с NVMe-хранилищем их стоит скорректировать для повышения производительности.

Откройте файл конфигурации:

sudo nano /etc/postgresql/16/main/postgresql.conf

Ключевые параметры для настройки

# Память
shared_buffers = 256MB          # 25% доступной ОЗУ
effective_cache_size = 768MB    # 75% общей ОЗУ
work_mem = 16MB                 # на операцию сортировки/хеширования
maintenance_work_mem = 128MB    # для VACUUM, CREATE INDEX

# Подключения
max_connections = 100           # подбирайте под нагрузку приложения

# Checkpoint
checkpoint_completion_target = 0.9
wal_buffers = 16MB

# Полезное логирование
log_min_duration_statement = 500  # логировать запросы дольше 500 мс
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

После изменений:

sudo systemctl reload postgresql

Инструмент PGTune

Воспользуйтесь онлайн-инструментом PGTune для автоматической генерации оптимальных параметров в зависимости от ОЗУ, CPU и типа нагрузки.

Шаг 7: Резервное копирование и восстановление

Регулярные бэкапы критически важны. PostgreSQL предоставляет утилиты pg_dump и pg_dumpall. Если вам нужна профессиональная настройка резервного копирования, воспользуйтесь услугами администрирования серверов CLIQHOST.

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

sudo -u postgres pg_dump app_db > /backup/app_db_$(date +%Y%m%d_%H%M%S).sql

Сжатый бэкап

sudo -u postgres pg_dump -Fc app_db > /backup/app_db_$(date +%Y%m%d).dump

Бэкап всех баз данных

sudo -u postgres pg_dumpall > /backup/all_databases_$(date +%Y%m%d).sql

Восстановление

# Из SQL-файла
sudo -u postgres psql app_db < /backup/app_db_20240115.sql

# Из custom-формата (.dump)
sudo -u postgres pg_restore -d app_db /backup/app_db_20240115.dump

Автоматизация через cron

crontab -e

Добавьте:

0 2 * * * sudo -u postgres pg_dump -Fc app_db > /backup/app_db_$(date +\%Y\%m\%d).dump 2>/var/log/pg_backup.log

Шаг 8: Мониторинг PostgreSQL

Активные и медленные запросы

-- Активные запросы
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

-- Топ медленных запросов (требуется pg_stat_statements)
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Включение расширения pg_stat_statements

sudo nano /etc/postgresql/16/main/postgresql.conf

shared_preload_libraries = 'pg_stat_statements'

Перезапустите и активируйте расширение:

sudo systemctl restart postgresql
sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

Размер баз данных

SELECT pg_database.datname,
       pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

Шаг 9: Обслуживание — VACUUM и REINDEX

PostgreSQL использует механизм хранения MVCC, который требует периодической очистки с помощью VACUUM:

-- Vacuum конкретной таблицы
VACUUM ANALYZE clients;

-- Полный vacuum (блокирует таблицу!)
VACUUM FULL clients;

-- Переиндексация
REINDEX TABLE clients;

Autovacuum включён по умолчанию и выполняет эту задачу автоматически. Для больших баз данных его параметры можно дополнительно настроить в postgresql.conf.

Лучшие практики безопасности

Не открывайте порт 5432 публично — используйте SSH-туннелирование или VPN для удалённого доступа
Регулярно меняйте пароли и используйте сложные комбинации
Принцип минимальных привилегий: каждое приложение получает только необходимые права
Включите SSL для подключений PostgreSQL — установите ssl = on в postgresql.conf
Ведите журнал доступа: включите логирование всех подключений
Обновляйте PostgreSQL для получения патчей безопасности

Для продвинутой защиты на уровне сервера воспользуйтесь услугами администрирования серверов CLIQHOST. Если вы рассматриваете выделенный сервер под нагрузку большой БД, изучите наши управляемые выделенные серверы.

Заключение

PostgreSQL — отличный выбор для любого проекта, которому требуется высокая целостность данных, масштабируемость и гибкость. Правильно установленный и настроенный на вашем VPS Linux, он станет надёжной основой для веб-приложений, API и бизнес-систем.

Нужен производительный сервер для PostgreSQL? Изучите предложения VPS NVMe от CLIQHOST — быстрое NVMe-хранилище, выделенные ресурсы и техническая поддержка. Предпочитаете полностью управляемую среду? Ознакомьтесь с нашими услугами администрирования серверов.

Есть вопросы? Свяжитесь с нами — наша команда готова помочь.

Как установить и настроить PostgreSQL на VPS Linux: полное руководство