Бази даних¶

Робота з PostgreSQL в нашій інфраструктурі — підключення, адміністрування, бекапи.

PostgreSQL основи¶

Підключення до контейнера¶

# Вхід в контейнер postgres
docker exec -it postgres bash

# Або одразу запустити psql
docker exec -it postgres psql -U postgres

Основні psql команди¶

-- Список всіх баз даних
\l

-- Підключитися до бази
\c database_name

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

-- Структура таблиці
\d table_name

-- Список користувачів
\du

-- Вихід
\q

Корисні запити¶

-- Розмір бази даних
SELECT pg_size_pretty(pg_database_size('database_name'));

-- Розмір таблиць
SELECT tablename, pg_size_pretty(pg_total_relation_size(tablename::text))
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::text) DESC;

-- Активні з'єднання
SELECT * FROM pg_stat_activity WHERE datname = 'database_name';

-- Кількість з'єднань по базах
SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname;

Створення бази та користувача¶

-- Створити користувача
CREATE USER service_user WITH PASSWORD 'secure_password';

-- Створити базу
CREATE DATABASE service_db OWNER service_user;

-- Надати всі права на базу
GRANT ALL PRIVILEGES ON DATABASE service_db TO service_user;

-- Підключитись до бази та надати права на схему
\c service_db
GRANT ALL ON SCHEMA public TO service_user;

Наші бази даних¶

MAS (Matrix Authentication Service)¶

docker exec -it postgres psql -U postgres -d mas

Основні таблиці:

Таблиця	Опис
`users`	Облікові записи користувачів
`user_emails`	Email адреси користувачів
`user_sessions`	Активні сесії
`oauth2_sessions`	OAuth сесії
`upstream_oauth_links`	Зв'язки з upstream провайдерами

-- Переглянути користувачів
SELECT id, username, created_at FROM users;

-- Активні сесії
SELECT u.username, s.created_at, s.last_active_at
FROM user_sessions s
JOIN users u ON s.user_id = u.id
WHERE s.finished_at IS NULL;

-- Заблокувати користувача
UPDATE users SET locked_at = NOW() WHERE username = 'user_to_lock';

Synapse (Matrix Server)¶

docker exec -it postgres psql -U postgres -d synapse

Основні таблиці:

Таблиця	Опис
`users`	Matrix користувачі
`rooms`	Кімнати
`events`	Всі події (повідомлення, стани)
`room_memberships`	Членство в кімнатах
`devices`	Пристрої користувачів

-- Користувачі
SELECT name, creation_ts, is_guest, admin FROM users;

-- Кімнати з кількістю учасників
SELECT r.room_id, r.creator, COUNT(m.user_id) as members
FROM rooms r
LEFT JOIN room_memberships m ON r.room_id = m.room_id AND m.membership = 'join'
GROUP BY r.room_id, r.creator;

-- Зробити користувача адміном
UPDATE users SET admin = 1 WHERE name = '@user:eliah.one';

Wiki¶

docker exec -it postgres psql -U postgres -d wiki

База метаданих для wiki — сесії, кеш, статистика.

Planka¶

docker exec -it postgres psql -U postgres -d planka

Kanban дошки — проєкти, картки, користувачі.

Nextcloud¶

docker exec -it postgres psql -U postgres -d nextcloud

Файлове сховище — користувачі, файли, shares.

Додавання нового сервісу¶

1. Створення бази та користувача¶

docker exec -it postgres psql -U postgres

-- Створити користувача для сервісу
CREATE USER myservice WITH PASSWORD 'generated_secure_password';

-- Створити базу
CREATE DATABASE myservice OWNER myservice;

-- Надати права
GRANT ALL PRIVILEGES ON DATABASE myservice TO myservice;

\c myservice
GRANT ALL ON SCHEMA public TO myservice;

2. Docker Compose конфігурація¶

services:
  myservice:
    image: myservice:latest
    environment:
      - DATABASE_URL=postgresql://myservice:password@postgres:5432/myservice
      # Або окремі змінні
      - DB_HOST=postgres
      - DB_PORT=5432
      - DB_NAME=myservice
      - DB_USER=myservice
      - DB_PASSWORD=${MYSERVICE_DB_PASSWORD}
    networks:
      - internal
    depends_on:
      - postgres

networks:
  internal:
    external: true

3. Environment variables (.env)¶

# Додати в .env файл
MYSERVICE_DB_PASSWORD=your_secure_password_here

4. Приклад для Python (SQLAlchemy)¶

import os
from sqlalchemy import create_engine

DATABASE_URL = os.getenv("DATABASE_URL")
engine = create_engine(DATABASE_URL)

5. Приклад для Node.js (pg)¶

const { Pool } = require('pg');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

Особливості інфраструктури¶

Shared PostgreSQL¶

Всі сервіси використовують один PostgreSQL контейнер з окремими базами:

postgres (контейнер)
├── mas (база)
├── synapse (база)
├── wiki (база)
├── planka (база)
└── nextcloud (база)

Переваги:
- Менше ресурсів
- Простіше бекапити
- Єдина точка конфігурації

Недоліки:
- Single point of failure
- Спільні ресурси

Docker мережа¶

Сервіси підключаються до postgres через внутрішню Docker мережу:

Host: postgres (ім'я контейнера)
Port: 5432

Не використовуйте localhost — це буде вказувати на сам контейнер сервісу.

Connection string формат¶

postgresql://user:password@host:port/database

# Приклади
postgresql://mas:password@postgres:5432/mas
postgresql://synapse:password@postgres:5432/synapse

З додатковими параметрами:

postgresql://user:password@postgres:5432/database?sslmode=disable&connect_timeout=10

Права доступу (GRANT)¶

-- Повні права на базу
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

-- Права на існуючі таблиці
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;

-- Права на майбутні таблиці
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO username;

-- Тільки читання
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Backup та Restore¶

Бекап однієї бази¶

# З контейнера
docker exec postgres pg_dump -U postgres -d database_name > backup.sql

# Стиснутий бекап
docker exec postgres pg_dump -U postgres -Fc database_name > backup.dump

Бекап всіх баз¶

docker exec postgres pg_dumpall -U postgres > all_databases.sql

Restore¶

# З SQL файлу
docker exec -i postgres psql -U postgres -d database_name < backup.sql

# З dump файлу
docker exec -i postgres pg_restore -U postgres -d database_name < backup.dump

# Створити базу та відновити
docker exec -i postgres createdb -U postgres new_database
docker exec -i postgres pg_restore -U postgres -d new_database < backup.dump

Автоматизація через cron¶

Створіть скрипт /opt/scripts/backup-postgres.sh:

#!/bin/bash
BACKUP_DIR="/opt/backups/postgres"
DATE=$(date +%Y-%m-%d_%H-%M)
RETENTION_DAYS=7

# Бекап кожної бази
for DB in mas synapse wiki planka nextcloud; do
    docker exec postgres pg_dump -U postgres -Fc $DB > "$BACKUP_DIR/${DB}_${DATE}.dump"
done

# Видалити старі бекапи
find $BACKUP_DIR -name "*.dump" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: $DATE"

Додати в crontab:

# Щодня о 3:00
0 3 * * * /opt/scripts/backup-postgres.sh >> /var/log/postgres-backup.log 2>&1

Перевірка бекапів¶

# Перевірити що файл не порожній
ls -lh /opt/backups/postgres/

# Перевірити вміст dump
docker exec -i postgres pg_restore -l < backup.dump

# Тестове відновлення в тимчасову базу
docker exec postgres createdb -U postgres test_restore
docker exec -i postgres pg_restore -U postgres -d test_restore < backup.dump
docker exec postgres dropdb -U postgres test_restore

Troubleshooting¶

Connection refused¶

# Перевірити чи запущений контейнер
docker ps | grep postgres

# Перевірити логи
docker logs postgres

# Перевірити чи слухає порт
docker exec postgres pg_isready -U postgres

Можливі причини:
- Контейнер не запущений
- Неправильний hostname (використовуйте postgres, не localhost)
- Контейнери в різних мережах

Authentication failed¶

# Перевірити користувача
docker exec -it postgres psql -U postgres -c "\du"

# Скинути пароль
docker exec -it postgres psql -U postgres -c "ALTER USER username WITH PASSWORD 'new_password';"

Можливі причини:
- Неправильний пароль
- Користувач не існує
- Користувач не має прав на базу

Too many connections¶

# Перевірити кількість з'єднань
docker exec -it postgres psql -U postgres -c "SELECT count(*) FROM pg_stat_activity;"

# Побачити активні з'єднання
docker exec -it postgres psql -U postgres -c "SELECT pid, usename, datname, state, query_start FROM pg_stat_activity;"

# Завершити idle з'єднання
docker exec -it postgres psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle' AND query_start < NOW() - INTERVAL '1 hour';"

Збільшити ліміт в postgresql.conf:

max_connections = 200

Disk full¶

# Перевірити використання диску
docker exec postgres df -h /var/lib/postgresql/data

# Розмір баз
docker exec -it postgres psql -U postgres -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC;"

# Очистити WAL логи (обережно!)
docker exec postgres pg_controldata /var/lib/postgresql/data | grep "Latest checkpoint"

Рішення:
- Видалити старі бекапи
- Очистити великі таблиці
- Збільшити диск

Повільні запити¶

# Увімкнути логування повільних запитів
docker exec -it postgres psql -U postgres -c "ALTER SYSTEM SET log_min_duration_statement = 1000;"
docker exec -it postgres psql -U postgres -c "SELECT pg_reload_conf();"

# Переглянути активні запити
docker exec -it postgres psql -U postgres -c "SELECT pid, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC;"

Корисні аліаси¶

Додайте в ~/.bashrc:

alias pgcli='docker exec -it postgres psql -U postgres'
alias pgmas='docker exec -it postgres psql -U postgres -d mas'
alias pgsynapse='docker exec -it postgres psql -U postgres -d synapse'
alias pgbackup='docker exec postgres pg_dumpall -U postgres'

Що далі¶

Docker — основи контейнеризації
Моніторинг — налаштування алертів
Caddy — reverse proxy для сервісів