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

PostgreSQL (Postgres) — мощная СУБД для сложных приложений: поддерживает JSON, полнотекстовый поиск, оконные функции и многое другое. Предпочтение разработчиков Laravel, Django, Ruby on Rails.

Установка PostgreSQL 16

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

sudo systemctl enable postgresql
sudo systemctl start postgresql

# Проверить версию
psql --version

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

# Войти под postgres
sudo -u postgres psql

-- Создать пользователя
CREATE USER myuser WITH PASSWORD 'НадёжныйПароль';

-- Создать базу
CREATE DATABASE myapp OWNER myuser;

-- Выдать права
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;

\q

Настройка удалённого доступа

# Разрешить подключения
sudo nano /etc/postgresql/16/main/postgresql.conf
# listen_addresses = '*'

# Настроить аутентификацию
sudo nano /etc/postgresql/16/main/pg_hba.conf
# Добавить в конец:
# host  all  all  0.0.0.0/0  md5

sudo systemctl restart postgresql
sudo ufw allow 5432/tcp

Базовая оптимизация postgresql.conf

shared_buffers = 256MB          # 25% RAM
effective_cache_size = 768MB    # 75% RAM
work_mem = 16MB
maintenance_work_mem = 64MB
max_connections = 100

Оптимизация postgresql.conf

# /etc/postgresql/16/main/postgresql.conf
# Для сервера с 8 ГБ RAM

max_connections = 100
shared_buffers = 2GB            # 25% RAM
effective_cache_size = 6GB      # 75% RAM
work_mem = 10MB                 # RAM / max_connections / 2
maintenance_work_mem = 512MB
wal_buffers = 16MB
checkpoint_completion_target = 0.9
random_page_cost = 1.1          # SSD
effective_io_concurrency = 200  # SSD

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

Установка PostgreSQL

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

# Запустить и включить автозапуск
sudo systemctl enable --now postgresql

# Проверить
sudo systemctl status postgresql

Основные операции через psql

# Войти как postgres (суперпользователь)
sudo -u postgres psql

-- Создать пользователя
CREATE USER myapp WITH PASSWORD 'SecurePass123!';

-- Создать БД
CREATE DATABASE myapp_db OWNER myapp;

-- Выдать права
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp;

-- Выйти
\q

Подключение и навигация

# Подключиться к конкретной БД
psql -U myapp -d myapp_db -h localhost

-- Список БД
\l

-- Список таблиц
\dt

-- Описание таблицы
\d tablename

-- Выполнить SQL из файла
\i /path/to/script.sql

Бэкап и восстановление

# Бэкап одной БД
pg_dump -U myapp myapp_db > backup_$(date +%Y%m%d).sql

# Бэкап всех БД
sudo -u postgres pg_dumpall > all_databases.sql

# Восстановление
psql -U myapp -d myapp_db < backup.sql