Как администрировать базы данных PostgreSQL

О нас: Персональный сайт Тимофея Бугаевского и компании Зетка Интерактив

Руководства: Обширная коллекция технических руководств, написанных с точки зрения опытного разработчика. Каждая статья содержит подробные объяснения, практические примеры кода и паттерны для продакшена.

Базы данных: Администрирование баз данных, оптимизация и паттерны

PostgreSQL — самая продвинутая в мире реляционная база данных с открытым исходным кодом, которая используется повсюду: от небольших приложений до корпоративных систем. Эффективное администрирование PostgreSQL требует понимания как ежедневных операций, так и расширенных возможностей. В этом руководстве рассматриваются ключевые аспекты администрирования PostgreSQL с точки зрения senior-разработчика.

Почему важны навыки администрирования PostgreSQL

Администрирование базы данных позволяет:

Оптимизировать производительность: более быстрые запросы, лучший пользовательский опыт
Обеспечивать целостность данных: надёжные резервные копии и восстановление
Устранять неполадки: быстро выявлять проблемы
Обеспечивать безопасность: корректный контроль доступа и шифрование
Масштабироваться: справляться с ростом данных и трафика

Подключение и базовые операции

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

# Подключиться от имени пользователя postgres
psql -U postgres
# Подключиться к конкретной базе данных
psql -U postgres -d mydatabase
# Подключиться к удалённому серверу
psql -h hostname -p 5432 -U username -d database
# Формат строки подключения
psql "postgresql://user:password@host:5432/database?sslmode=require"

Базовые команды psql

-- Список баз данных
\l
-- Подключиться к базе данных
\c mydatabase
-- Список таблиц
\dt
-- Список всех таблиц, включая системные
\dt *.*
-- Описание структуры таблицы
\d tablename
-- Список индексов
\di
-- Список пользователей/ролей
\du
-- Показать текущего пользователя
SELECT current_user;
-- Показать текущую базу данных
SELECT current_database();
-- Выйти из psql
\q

Управление пользователями и правами

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

-- Создать пользователя
CREATE USER appuser WITH PASSWORD 'securepassword';
-- Создать пользователя с параметрами
CREATE USER admin WITH
PASSWORD 'adminpass'
CREATEDB
CREATEROLE
LOGIN;
-- Изменить пользователя
ALTER USER appuser WITH PASSWORD 'newpassword';
-- Выдать права superuser (используйте осторожно)
ALTER USER admin WITH SUPERUSER;
-- Удалить пользователя
DROP USER appuser;
-- Список пользователей
SELECT usename, usesuper, usecreatedb FROM pg_user;

Права на базу данных

-- Создать базу данных
CREATE DATABASE myapp OWNER appuser;
-- Выдать право на подключение
GRANT CONNECT ON DATABASE myapp TO appuser;
-- Выдать все привилегии на базу данных
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;
-- Отозвать привилегии
REVOKE ALL PRIVILEGES ON DATABASE myapp FROM appuser;

Права на таблицы

-- Выдать SELECT на конкретную таблицу
GRANT SELECT ON users TO readonly_user;
-- Выдать несколько прав
GRANT SELECT, INSERT, UPDATE ON users TO appuser;
-- Выдать права на все таблицы в схеме
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
-- Задать права по умолчанию для будущих таблиц
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_user;
-- Просмотреть права на таблицу
SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_name = 'users';

Полезные запросы

Текущие выполняющиеся запросы

-- Показать активные запросы
SELECT
pid,
datname,
usename,
state,
query,
age(clock_timestamp(), query_start) AS duration
FROM pg_stat_activity
WHERE state != 'idle'
AND query NOT LIKE '%pg_stat_activity%'
ORDER BY duration DESC;
-- Показать длительно выполняющиеся запросы (> 5 минут)
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'
AND state != 'idle';

Завершение запросов

-- Отменить запрос (мягко)
SELECT pg_cancel_backend(pid);
-- Завершить соединение (принудительно)
SELECT pg_terminate_backend(pid);
-- Завершить все соединения с базой данных (кроме вашего)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydatabase'
AND pid != pg_backend_pid();

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

-- Размеры баз данных
SELECT
datname,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
-- Размеры таблиц
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename::regclass)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

Информация об индексах

-- Список всех индексов
SELECT
schemaname,
tablename,
indexname,
indexdef
FROM pg_indexes
WHERE schemaname = 'public';
-- Статистика использования индексов
SELECT
schemaname,
relname AS tablename,
indexrelname AS indexname,
idx_scan AS times_used,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
-- Найти неиспользуемые индексы
SELECT
schemaname || '.' || relname AS table,
indexrelname AS index,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
idx_scan AS times_used
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique
AND idx_scan < 50
ORDER BY pg_relation_size(i.indexrelid) DESC;

Географические запросы PostGIS

PostgreSQL с расширением PostGIS обрабатывает географические данные:

-- Включить PostGIS
CREATE EXTENSION postgis;
-- Сохранить точку
INSERT INTO locations (name, geom)
VALUES ('Office', ST_SetSRID(ST_MakePoint(-73.935242, 40.730610), 4326));
-- Преобразовать геометрию в GeoJSON
SELECT
name,
ST_AsGeoJSON(ST_Transform(geom, 3857)) AS geojson
FROM locations;
-- Найти точки в пределах радиуса (в метрах)
SELECT name
FROM locations
WHERE ST_DWithin(
geom::geography,
ST_SetSRID(ST_MakePoint(-73.935242, 40.730610), 4326)::geography,
1000 -- 1000 метров
);
-- Проверить, находится ли точка внутри полигона
SELECT ST_Contains(area.geom, point.geom)
FROM areas area, points point
WHERE area.name = 'Manhattan' AND point.name = 'Office';
-- Создать буфер вокруг точки
SELECT ST_Buffer(
ST_Transform(geom, 3857),
500 -- Радиус 500 метров
) FROM locations WHERE name = 'Office';

Операции с JSON

-- Создать таблицу с JSON-столбцом
CREATE TABLE events (
id SERIAL PRIMARY KEY,
data JSONB NOT NULL
);
-- Вставить JSON-данные
INSERT INTO events (data) VALUES
('{"type": "click", "page": "/home", "user_id": 123}');
-- Запросить поля JSON
SELECT
data->>'type' AS event_type,
data->>'page' AS page,
(data->>'user_id')::int AS user_id
FROM events;
-- Фильтровать по полю JSON
SELECT * FROM events
WHERE data->>'type' = 'click';
-- Проверить, существует ли ключ
SELECT * FROM events
WHERE data ? 'user_id';
-- Обновить поле JSON
UPDATE events
SET data = jsonb_set(data, '{processed}', 'true')
WHERE id = 1;
-- Переименовать свойство JSON
UPDATE events
SET data = (data - 'old_key') || jsonb_build_object('new_key', data->'old_key')
WHERE data ? 'old_key';

Резервное копирование и восстановление

pg_dump

# Резервная копия одной базы данных
pg_dump -U postgres mydatabase > backup.sql
# Резервная копия со сжатием
pg_dump -U postgres -Fc mydatabase > backup.dump
# Резервная копия конкретных таблиц
pg_dump -U postgres -t users -t orders mydatabase > tables.sql
# Резервная копия только схемы
pg_dump -U postgres --schema-only mydatabase > schema.sql
# Резервная копия только данных
pg_dump -U postgres --data-only mydatabase > data.sql

pg_restore

# Восстановить из SQL-файла
psql -U postgres mydatabase < backup.sql
# Восстановить из пользовательского формата
pg_restore -U postgres -d mydatabase backup.dump
# Восстановить в новую базу данных
createdb -U postgres newdatabase
pg_restore -U postgres -d newdatabase backup.dump
# Показать содержимое резервной копии
pg_restore -l backup.dump

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

#!/bin/bash
# backup.sh — ежедневное резервное копирование PostgreSQL
DATE=$(date +%Y-%m-%d)
BACKUP_DIR="/backups/postgres"
DATABASES="myapp production"
mkdir -p "$BACKUP_DIR"
for DB in $DATABASES; do
pg_dump -U postgres -Fc "$DB" > "$BACKUP_DIR/${DB}_${DATE}.dump"
# Хранить только последние 7 дней
find "$BACKUP_DIR" -name "${DB}_*.dump" -mtime +7 -delete
done
# Опционально: загрузить в S3
# aws s3 sync "$BACKUP_DIR" s3://my-bucket/postgres-backups/

Настройка производительности

EXPLAIN ANALYZE

-- Проанализировать выполнение запроса
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id;
-- Подробное использование буферов
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM large_table WHERE indexed_column = 'value';

Создание индексов

-- B-tree индекс (по умолчанию, самый распространённый)
CREATE INDEX idx_users_email ON users(email);
-- Частичный индекс
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
-- Составной индекс
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
-- GIN индекс для JSONB
CREATE INDEX idx_events_data ON events USING GIN(data);
-- GiST индекс для географических данных
CREATE INDEX idx_locations_geom ON locations USING GIST(geom);
-- Конкурентное создание индекса (не блокирует таблицу)
CREATE INDEX CONCURRENTLY idx_large_table ON large_table(column);

Vacuum и Analyze

-- Vacuum для конкретной таблицы
VACUUM ANALYZE users;
-- Полный vacuum (освобождает больше места, блокирует таблицу)
VACUUM FULL users;
-- Analyze для планировщика запросов
ANALYZE users;
-- Проверить, когда vacuum выполнялся в последний раз
SELECT
schemaname,
relname,
last_vacuum,
last_autovacuum,
vacuum_count
FROM pg_stat_user_tables;

Сброс пароля

# Отредактировать pg_hba.conf, чтобы разрешить локальный trust
# Изменить: local all all peer
# На: local all all trust
# Перезапустить PostgreSQL
sudo systemctl restart postgresql
# Подключиться без пароля
psql -U postgres
# Сбросить пароль
ALTER USER postgres WITH PASSWORD 'newpassword';
# Вернуть pg_hba.conf и перезапустить

Ключевые выводы

Отслеживайте активные запросы: выявляйте медленные запросы на ранней стадии
Используйте EXPLAIN ANALYZE: понимайте планы выполнения запросов
Индексируйте стратегически: балансируйте скорость чтения и накладные расходы на запись
Регулярные резервные копии: автоматизированные, протестированные, вне площадки
Регулярно выполняйте vacuum: предотвращайте раздувание таблиц
Защищайте доступ: принцип наименьших привилегий

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