Урок 04

PostgreSQL: установка и настройка

Почему PostgreSQL

PostgreSQL — самая мощная открытая реляционная СУБД.

Плюсы:

ACID — транзакции
JSONB — работа с JSON как с типом
Полнотекстовый поиск — встроен
Расширения — PostGIS, pg_trgm, pg_stat_statements
Материализованные представления
Репликация — master-slave, logical
Партиционирование — из коробки

Для веба: PostgreSQL — стандарт для новых проектов. MySQL ещё используется (особенно в legacy), но новое — на Postgres.

Установка

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

postgresql-contrib — дополнительные расширения и утилиты.

Проверка:

sudo systemctl status postgresql
psql --version
# psql (PostgreSQL) 14.x

Версия: обычно Ubuntu даёт 14 или 16. Если нужна свежая — через PPA:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update
sudo apt install -y postgresql-16

Управление сервисом

sudo systemctl start postgresql
sudo systemctl stop postgresql
sudo systemctl restart postgresql
sudo systemctl reload postgresql      # перечитать конфиг
sudo systemctl status postgresql
sudo systemctl enable postgresql

Где файлы

/etc/postgresql/14/main/                    — конфиги
/etc/postgresql/14/main/postgresql.conf     — основной
/etc/postgresql/14/main/pg_hba.conf         — авторизация
/etc/postgresql/14/main/pg_ident.conf

/var/lib/postgresql/14/main/                — данные
/var/log/postgresql/                        — логи
/var/run/postgresql/                        — сокет

Номер версии замени на свой (14 → 16).

Первое подключение

Через sudo -u postgres:

sudo -u postgres psql

Оказываешься в psql — клиенте PostgreSQL.

psql (14.10)
Type "help" for help.

postgres=#

Выход: \q.

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

В psql:

CREATE USER roman WITH PASSWORD 'strong_password_here';

Или через createuser:

sudo -u postgres createuser -P roman

-P — запросит пароль.

Создание БД

В psql:

CREATE DATABASE skills_db OWNER roman;

Через createdb:

sudo -u postgres createdb -O roman skills_db

-O roman — владелец roman.

Права

Суперпользователь:

ALTER USER roman WITH SUPERUSER;

⚠️ Только для разработки. На проде — обычный пользователь.

Обычный пользователь с правами на БД:

GRANT ALL PRIVILEGES ON DATABASE skills_db TO roman;

Подключение

Через psql:

psql -h localhost -U roman -d skills_db

Или по сокету:

psql -U roman -d skills_db

Проверь:

SELECT current_user, current_database();

Основные команды psql

Список:

\l              — список баз
\du             — список пользователей
\dt             — таблицы текущей БД
\d table_name   — описание таблицы
\di             — индексы
\dv             — представления
\df             — функции

Информация:

\conninfo       — инфо о текущем подключении
\encoding       — кодировка
\timing         — включить замер времени
\x              — расширенный вывод

Выполнение:

\i file.sql     — выполнить файл
\e              — открыть редактор
\g              — выполнить последний
\q              — выход

История:

\s              — история команд
\s file         — сохранить историю в файл

Настройка postgresql.conf

/etc/postgresql/14/main/postgresql.conf:

listen_addresses = 'localhost'
port = 5432
max_connections = 100
shared_buffers = 512MB
effective_cache_size = 1536MB
work_mem = 16MB
maintenance_work_mem = 128MB
wal_buffers = 16MB
max_wal_size = 2GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
random_page_cost = 1.1
effective_io_concurrency = 200
log_min_duration_statement = 1000
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '
log_checkpoints = on
log_connections = off
log_disconnections = off
timezone = 'UTC'

Разбор ключевых параметров

listen_addresses

listen_addresses = 'localhost'

Только локальные подключения. Для веба с Nginx на том же сервере — правильно.

Если БД на отдельном сервере:

listen_addresses = '*'

⚠️ Осторожно — открой firewall для нужного IP.

max_connections

Максимум одновременных соединений. По умолчанию 100.

Для Laravel: каждое PHP-FPM-соединение = одно к БД. Если max_children=25, нужно минимум 25, лучше 50.

Правило: max_connections >= pm.max_children + 10.

shared_buffers

Кеш PostgreSQL в памяти. 25% от RAM сервера.

Пример: 2 ГБ RAM → 512MB.

effective_cache_size

Сколько ОС может кешировать данные. Обычно 50-75% RAM.

Пример: 2 ГБ RAM → 1536MB.

Настройка не выделяет память, а сообщает планировщику, сколько доступно.

work_mem

Память на одну операцию сортировки/хеша.

Формула: (RAM - shared_buffers) / (max_connections × 3).

Пример: 2 ГБ RAM, 512 МБ буфер, 100 соединений → 16384 / 300 = 55 МБ. Ставь 16MB — с запасом.

maintenance_work_mem

Память для VACUUM, CREATE INDEX. Обычно 128-256 МБ.

max_wal_size

Размер WAL. Меньше — чаще checkpoint. Больше — реже, но дольше восстановление.

Для прода: 2GB.

random_page_cost

Стоимость случайного чтения.

HDD: 4.0
SSD: 1.1

Для SSD — 1.1. Это заставит планировщик активнее использовать индексы.

log_min_duration_statement

Логировать запросы дольше N миллисекунд:

log_min_duration_statement = 1000

1000 мс = 1 секунда. Всё медленнее — в лог.

Для поиска узких мест:

log_min_duration_statement = 200

Настройка pg_hba.conf

/etc/postgresql/14/main/pg_hba.conf:

# TYPE  DATABASE  USER    ADDRESS       METHOD
local   all       postgres              peer
local   all       all                   md5
host    all       all     127.0.0.1/32  md5
host    all       all     ::1/128       md5

Разбор:

local — Unix-сокет
host — TCP/IP
127.0.0.1/32 — только локально
md5 — пароль
peer — авторизация через ОС (для postgres пользователя)

Безопасность: не открывай 0.0.0.0/0 — это все в интернете.

Применение

sudo systemctl restart postgresql

Или мягко:

sudo systemctl reload postgresql

Подключение Laravel

В .env:

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=skills_db
DB_USERNAME=roman
DB_PASSWORD=strong_password_here

Проверка:

cd /var/www/skills/api
php artisan migrate

Если работает — БД подключена.

Расширения

Проверить доступные:

SELECT * FROM pg_available_extensions;

Установить полезные:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Что дают:

pg_stat_statements — статистика по запросам
pg_trgm — триграммы для полнотекстового поиска
uuid-ossp — генерация UUID

Логи

sudo tail -f /var/log/postgresql/postgresql-14-main.log

Проверка после рестарта:

sudo journalctl -u postgresql -n 50

Проверка подключений

SELECT * FROM pg_stat_activity;

Активные соединения:

SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

Кто и откуда:

SELECT datname, usename, client_addr, state FROM pg_stat_activity;

Частые проблемы

1. FATAL: password authentication failed

Пароль неверный. Проверь в psql:

ALTER USER roman WITH PASSWORD 'new_password';

2. could not connect to server

PostgreSQL не запущен:

sudo systemctl start postgresql

3. too many clients

max_connections мало. Увеличь или используй PgBouncer.

4. SSL connection

Если не нужен локально — отключи в Laravel DB_SSLMODE=disable.

Итог

Минимум для веба:

listen_addresses = 'localhost'
max_connections >= max_children + 10
shared_buffers = 25% RAM
random_page_cost = 1.1 (для SSD)
log_min_duration_statement = 1000
pg_hba только для localhost

Дальше — создание БД и подключение Laravel.