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