Паттерны разработки, версионирование схемы, миграции данных
Версионирование схемы базы данных и миграции — критически важны для надёжного развёртывания изменений. В этой теме вы изучите паттерны разработки, миграции данных и лучшие практики.
Миграции — это версионированные SQL-скрипты для изменения схемы БД:
migrations/
├── 001_create_users_table.sql
├── 002_create_posts_table.sql
├── 003_add_email_to_users.sql
├── 004_create_index_on_posts.sql
└── 005_migrate_old_data.sql
{номер}_{описание}.sql
Примеры:
001_create_users_table.sql
002_add_created_at_to_orders.sql
003_migrate_legacy_data.sql
-- Таблица для отслеживания применённых миграций
CREATE TABLE schema_migrations (
version TEXT PRIMARY KEY,
applied_at TIMESTAMPTZ DEFAULT NOW(),
checksum TEXT, -- для проверки целостности
description TEXT
);
-- Или с номером
CREATE TABLE schema_migrations (
id SERIAL PRIMARY KEY,
version INTEGER UNIQUE NOT NULL,
name TEXT NOT NULL,
applied_at TIMESTAMPTZ DEFAULT NOW()
);-- 001_create_users_table.sql
-- +migrate Up
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email TEXT NOT NULL,
password_hash TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_users_email ON users(email);
-- +migrate Down
DROP TABLE IF EXISTS users;-- 002_add_phone_to_users.sql
-- +migrate Up
ALTER TABLE users
ADD COLUMN phone TEXT;
CREATE INDEX idx_users_phone ON users(phone);
-- +migrate Down
ALTER TABLE users
DROP COLUMN IF EXISTS phone;-- 003_change_price_type.sql
-- +migrate Up
-- Изменяем тип price с INTEGER на NUMERIC
ALTER TABLE products
ALTER COLUMN price TYPE NUMERIC(10, 2)
USING (price::NUMERIC / 100); -- конвертация из центов в доллары
-- +migrate Down
ALTER TABLE products
ALTER COLUMN price TYPE INTEGER
USING (price * 100)::INTEGER; -- обратно в центы-- 004_create_index_concurrently.sql
-- +migrate Up
-- CONCURRENTLY не блокирует запись
CREATE INDEX CONCURRENTLY IF NOT EXISTS
idx_orders_user_created
ON orders(user_id, created_at DESC);
-- +migrate Down
DROP INDEX IF EXISTS idx_orders_user_created;-- 005_migrate_legacy_data.sql
-- +migrate Up
-- Перенос данных из старой таблицы
INSERT INTO users (id, username, email, password_hash, created_at)
SELECT
id,
login as username,
email_address as email,
password as password_hash,
registered_at as created_at
FROM legacy_users
ON CONFLICT (id) DO NOTHING;
-- Обновление последовательности
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));
-- +migrate Down
DELETE FROM users WHERE id IN (SELECT id FROM legacy_users);-- 006_partition_events_by_time.sql
-- +migrate Up
-- Создаём партиционированную таблицу
CREATE TABLE events_new (
id BIGSERIAL,
event_time TIMESTAMPTZ NOT NULL,
event_type TEXT,
data JSONB,
PRIMARY KEY (id, event_time)
) PARTITION BY RANGE (event_time);
-- Создаём партиции по месяцам
CREATE TABLE events_2026_01 PARTITION OF events_new
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events_new
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
-- Миграция данных
INSERT INTO events_new (id, event_time, event_type, data)
SELECT id, event_time, event_type, data FROM events;
-- Переименование
ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_new RENAME TO events;
-- +migrate Down
-- Откат сложнее, требует осторожности-- Добавляем столбец deleted_at
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;
-- Индекс для фильтрации удалённых
CREATE INDEX idx_users_not_deleted ON users(id)
WHERE deleted_at IS NULL;
-- Представление для активных пользователей
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;
-- Использование представления
SELECT * FROM active_users WHERE id = 1;-- Универсальная функция аудита
CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO audit_log (
table_name, operation, old_data, new_data, changed_at
) VALUES (
TG_TABLE_NAME,
TG_OP,
CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) END,
CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) END,
NOW()
);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- Применение к таблицам
CREATE TRIGGER audit_users_changes
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_changes();-- Функция для updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Применение
CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION update_updated_at();-- Частичный уникальный индекс
-- Один активный заказ на пользователя
CREATE UNIQUE INDEX idx_one_active_order_per_user
ON orders(user_id)
WHERE status = 'active';
-- Уникальный email только для подтверждённых пользователей
CREATE UNIQUE INDEX idx_users_confirmed_email
ON users(email)
WHERE email_confirmed = true;-- Проверка, что дата окончания больше даты начала
ALTER TABLE projects
ADD CONSTRAINT check_dates
CHECK (end_date >= start_date);
-- Проверка, что хотя бы один столбец заполнен
ALTER TABLE contacts
ADD CONSTRAINT check_contact_info
CHECK (phone IS NOT NULL OR email IS NOT NULL);
-- Проверка формата JSON
ALTER TABLE settings
ADD CONSTRAINT check_settings_json
CHECK (jsonb_typeof(settings) = 'object');-- Таблица с кэшированными счётчиками
ALTER TABLE users
ADD COLUMN post_count INTEGER DEFAULT 0;
-- Триггер для обновления
CREATE OR REPLACE FUNCTION update_user_post_count()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE users SET post_count = post_count + 1
WHERE id = NEW.user_id;
ELSIF TG_OP = 'DELETE' THEN
UPDATE users SET post_count = GREATEST(post_count - 1, 0)
WHERE id = OLD.user_id;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_update_post_count
AFTER INSERT OR DELETE ON posts
FOR EACH ROW EXECUTE FUNCTION update_user_post_count();db/
├── migration/
│ ├── V1__create_users_table.sql
│ ├── V2__create_posts_table.sql
│ └── V3__add_email_index.sql
-- V1__create_users_table.sql
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL
);<!-- db/changelog/001-create-users.xml -->
<databaseChangeLog>
<changeSet id="1" author="dev">
<createTable tableName="users">
<column name="id" type="SERIAL">
<constraints primaryKey="true"/>
</column>
<column name="name" type="TEXT">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
</databaseChangeLog>pip install pgmigrate
pgmigrate -c "postgresql://user:pass@localhost/db" migratesqitch init myproj --uri https://github.com/user/myproj
sqitch add create_users -n 'Create users table'
sqitch deploy
sqitch revert-- Плохо: ошибка если таблица существует
CREATE TABLE users (...);
-- Хорошо: безопасно
CREATE TABLE IF NOT EXISTS users (...);
-- Для индексов
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_name ON table(column);
-- Для столбцов
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'phone'
) THEN
ALTER TABLE users ADD COLUMN phone TEXT;
END IF;
END $$;Всегда пишите Down-миграцию:
-- Up
ALTER TABLE users ADD COLUMN phone TEXT;
-- Down
ALTER TABLE users DROP COLUMN IF EXISTS phone;Развёртывание:
1. Применить миграции на staging
2. Запустить тесты
3. Проверить производительность
4. Применить на production
# Бэкап перед критическими изменениями
pg_dump -U user database > backup_$(date +%Y%m%d_%H%M%S).sql
# Или только схема
pg_dump -U user -s database > schema_backup.sql-- Плохо: долгая транзакция блокирует таблицу
BEGIN;
CREATE INDEX idx_large ON large_table(column); -- может занять минуты
COMMIT;
-- Хорошо: CONCURRENTLY вне транзакции
CREATE INDEX CONCURRENTLY idx_large ON large_table(column);-- Проверка использования столбца перед удалением
SELECT
schemaname,
tablename,
attname as column_name,
n_distinct,
correlation
FROM pg_stats
WHERE tablename = 'users' AND attname = 'old_column';
-- Проверка зависимостей
SELECT
dependent_ns.nspname as dependent_schema,
dependent_rel.relname as dependent_table,
source_ns.nspname as source_schema,
source_attr.attname as source_column
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class as dependent_rel ON pg_rewrite.ev_class = dependent_rel.oid
JOIN pg_class as source_rel ON pg_depend.refobjid = source_rel.oid
JOIN pg_attribute as source_attr ON pg_depend.refobjid = source_attr.attrelid
AND pg_depend.refobjsubid = source_attr.attnum
JOIN pg_namespace as dependent_ns ON dependent_rel.relnamespace = dependent_ns.oid
JOIN pg_namespace as source_ns ON source_rel.relnamespace = source_ns.oid
WHERE source_rel.relname = 'users' AND source_attr.attname = 'old_column';-- 1. Полный откат (все миграции)
-- Выполнить Down всех миграций в обратном порядке
-- 2. Откат до версии
-- Выполнить Down миграций после указанной версии
-- 3. Точечный откат
-- Выполнить Down конкретной миграции-- Миграция 005: add_phone_column
-- Up
ALTER TABLE users ADD COLUMN phone TEXT;
-- Down
ALTER TABLE users DROP COLUMN IF EXISTS phone;
-- Миграция 006: create_posts_table
-- Up
CREATE TABLE posts (...);
-- Down
DROP TABLE IF EXISTS posts;-- Просмотр применённых миграций
SELECT * FROM schema_migrations ORDER BY version;
-- Проверка последних изменений
SELECT
tablename,
last_altered
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY last_altered DESC;
-- Размер таблиц после миграции
SELECT
relname as table_name,
pg_size_pretty(pg_total_relation_size(relid)) as total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;Вы завершили курс! Теперь вы владеете:
Продолжайте практиковаться и изучать PostgreSQL глубже!
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.