Лабораторная №3 — ручной сценарий команд из demo.sh

Запускать из корня проекта lab3. Команды даны в том порядке, в котором их удобно вводить руками.

---

Подготовка окружения

cd /Users/ramil/Downloads/рсхд_итмо/lab3

# Подключить ограниченный persistent-диск для pg1
bash ./scripts/storage/ensure-pg1-disk.sh

# Поднять стек
docker compose up -d

# Настроить PCP-доступ внутри pgpool
docker exec pgpool bash -c "echo 'localhost:9898:postgres:postgres' > /root/.pcppass && chmod 600 /root/.pcppass"

---

Этап 1. Конфигурация

# Показать контейнеры
docker compose ps

# Показать узлы pgpool
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SHOW pool_nodes;\""

# Показать репликацию на pg1
docker exec pg1 bash -c "psql -U postgres -d testdb -c \"SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, sync_state FROM pg_stat_replication;\""

# Показать роли pg1 и pg2 напрямую
docker exec pg1 bash -c "psql -U postgres -d testdb -c \"SELECT pg_is_in_recovery() AS is_standby;\""
docker exec pg2 bash -c "psql -U postgres -d testdb -c \"SELECT pg_is_in_recovery() AS is_standby;\""

Балансировка нагрузки

# До
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SHOW pool_nodes;\""

# 12 SELECT через pgpool
for i in $(seq 1 12); do
  docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SELECT d.id, LEFT(d.data, 60) AS data_preview, a.action, a.actor FROM test_data d JOIN test_audit a ON a.test_data_id = d.id ORDER BY a.id DESC LIMIT 1;\""
done

# После
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SHOW pool_nodes;\""

---

Этап 2.1. Подготовка

# Показать структуру двух таблиц
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_schema = 'public' AND table_name IN ('test_data', 'test_audit') ORDER BY table_name, ordinal_position;\""

# Показать начальные строки test_data
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SELECT id, CASE WHEN length(data) > 80 THEN left(data, 80) || '...' ELSE data END AS data, created_at, source FROM test_data ORDER BY id;\""

# Показать начальные строки test_audit
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SELECT id, test_data_id, action, CASE WHEN length(details) > 80 THEN left(details, 80) || '...' ELSE details END AS details, actor, created_at FROM test_audit ORDER BY id;\""

Фоновые клиентские процессы через pgpool

# Клиент 1: 40 итераций, интервал 3 с
docker exec pgpool bash -c "
  for i in \$(seq 1 40); do
    psql -q -h localhost -p 9999 -U postgres -d testdb \
      -c \"SELECT COUNT(*) AS test_data_rows_before_insert FROM test_data;
          SELECT COUNT(*) AS test_audit_rows_before_insert FROM test_audit;
          BEGIN;
          WITH new_row AS (
              INSERT INTO test_data(data,source)
              VALUES('bg_write_\$i','client_1')
              RETURNING id, data, source
          )
          INSERT INTO test_audit(test_data_id, action, details, actor)
          SELECT id, 'bg_insert', data, source
          FROM new_row;
          COMMIT;\" >/dev/null 2>&1
    sleep 3
  done
" &

# Клиент 2: 25 итераций, интервал 5 с
docker exec pgpool bash -c "
  for i in \$(seq 1 25); do
    psql -q -h localhost -p 9999 -U postgres -d testdb \
      -c \"SELECT COUNT(*) AS test_data_rows_before_insert FROM test_data;
          SELECT COUNT(*) AS test_audit_rows_before_insert FROM test_audit;
          BEGIN;
          WITH new_row AS (
              INSERT INTO test_data(data,source)
              VALUES('bg_write_\$i','client_2')
              RETURNING id, data, source
          )
          INSERT INTO test_audit(test_data_id, action, details, actor)
          SELECT id, 'bg_insert', data, source
          FROM new_row;
          COMMIT;\" >/dev/null 2>&1
    sleep 5
  done
" &

# Клиент 3: 60 SELECT COUNT из test_data, интервал 2 с
docker exec pgpool bash -c "
  for i in \$(seq 1 60); do
    psql -q -h localhost -p 9999 -U postgres -d testdb \
      -c 'SELECT COUNT(*) FROM test_data;' >/dev/null 2>&1
    sleep 2
  done
" &

# Клиент 4: 60 SELECT COUNT из test_audit, интервал 2 с
docker exec pgpool bash -c "
  for i in \$(seq 1 60); do
    psql -q -h localhost -p 9999 -U postgres -d testdb \
      -c 'SELECT COUNT(*) FROM test_audit;' >/dev/null 2>&1
    sleep 2
  done
" &

# Проверить, что строки прибавляются
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SELECT COUNT(*) FROM test_data;\""
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SELECT COUNT(*) FROM test_audit;\""

# Показать активность в pg_stat_activity
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SELECT pid, usename, client_addr, state, LEFT(query, 55) AS current_query FROM pg_stat_activity WHERE datname='testdb' AND pid != pg_backend_pid() ORDER BY query_start;\""

# Ручная транзакция записи через pgpool в две таблицы
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"BEGIN;
WITH new_row AS (
    INSERT INTO test_data(data, source)
    VALUES ('manual_write_demo', 'operator')
    RETURNING id, data, source
),
audit_row AS (
    INSERT INTO test_audit(test_data_id, action, details, actor)
    SELECT id, 'manual_write', data, source
    FROM new_row
    RETURNING id AS audit_id, test_data_id, actor
)
SELECT
    (SELECT id FROM new_row) AS test_data_id,
    (SELECT audit_id FROM audit_row) AS audit_id,
    (SELECT actor FROM audit_row) AS actor;
COMMIT;\""

# Показать связанные строки из двух таблиц
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SELECT d.id, CASE WHEN length(d.data) > 80 THEN left(d.data, 80) || '...' ELSE d.data END AS data, d.source, a.id AS audit_id, a.action, a.actor, a.created_at FROM test_data d JOIN test_audit a ON a.test_data_id = d.id ORDER BY d.id DESC, a.id DESC LIMIT 5;\""

# Проверить репликацию на pg2
docker exec pg2 bash -c "psql -U postgres -d testdb -c \"SELECT (SELECT COUNT(*) FROM test_data) AS data_rows_on_pg2, (SELECT COUNT(*) FROM test_audit) AS audit_rows_on_pg2;\""

---

Этап 2.2. Симуляция сбоя

# Проверить ограниченный диск pg1 на хосте
df -h "/tmp/lab3-pg1-data"
hdiutil info | grep -F "/tmp/lab3-pg1-data"
du -sh "/tmp/lab3-pg1-data/"

# Заполнить диск мусорным файлом на pg1
docker exec pg1 bash -c "dd if=/dev/zero of=/var/lib/postgresql/data/junk bs=1M 2>&1; echo '[dd завершён]'"

# Проверить статус контейнера
docker inspect -f '{{.State.Status}}' pg1

# Если pg1 ещё жив — форсировать запись / WAL / checkpoint
df -h "/tmp/lab3-pg1-data"
docker exec pg1 bash -lc "psql -U postgres -d testdb -v ON_ERROR_STOP=0 \
  -c \"INSERT INTO test_data(data, source) VALUES (repeat('X', 1048576), 'enospc_trigger');\" \
  -c \"SELECT pg_switch_wal();\" \
  -c \"CHECKPOINT;\""

# Повторять до падения pg1
# (каждая итерация может занимать несколько секунд из-за ENOSPC — PostgreSQL
#  пытается записать WAL на полный диск и зависает в retry-цикле)
for i in $(seq 1 20); do
  docker exec pg1 bash -lc "psql -U postgres -d testdb -v ON_ERROR_STOP=0 \
    -c \"INSERT INTO test_data(data, source) VALUES (repeat('X', 1048576), 'enospc_trigger');\" \
    -c \"SELECT pg_switch_wal();\" \
    -c \"CHECKPOINT;\"" >/dev/null 2>&1 || true
  docker inspect -f '{{.State.Status}}' pg1
  sleep 1
done

---

Этап 2.3. Обработка сбоя

# Показать ошибки в логах pg1
docker logs pg1 2>&1 | grep -iE "(PANIC|FATAL|ERROR|no space|enospc|disk full|cannot|failed)" | tail -20
docker logs pg1 2>&1 | tail -20

# Ждать автоматический failover через pgpool
for i in $(seq 1 45); do
  docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SHOW pool_nodes;\"" 2>/dev/null | grep " 0 " | grep -c "down" || true
  docker exec pg2 bash -c "psql -U postgres -d testdb -c \"SELECT pg_is_in_recovery();\"" 2>/dev/null
  docker inspect -f '{{.State.Status}}' pg1 2>/dev/null
  sleep 1
done

# Если авто-failover не произошёл — выполнить вручную
docker exec pg2 bash -c "psql -U postgres -d testdb -c \"SELECT pg_promote();\""
docker exec pgpool bash -c "pcp_detach_node -h localhost -p 9898 -U postgres -n 0"

# Показать состояние после failover
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SHOW pool_nodes;\""
docker exec pg2 bash -c "psql -U postgres -d testdb -c \"SELECT pg_is_in_recovery() AS is_standby, now() - pg_postmaster_start_time() AS uptime_since_failover;\""

# Показать данные после failover
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SELECT d.id, CASE WHEN length(d.data) > 80 THEN left(d.data, 80) || '...' ELSE d.data END AS data, d.source, a.id AS audit_id, a.action, a.actor FROM test_data d JOIN test_audit a ON a.test_data_id = d.id ORDER BY d.id DESC, a.id DESC LIMIT 5;\""

# Две записи через новый primary pg2
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"BEGIN;
WITH new_row AS (
    INSERT INTO test_data(data, source)
    VALUES ('after_failover_1', 'pg2-primary')
    RETURNING id, data, source
),
audit_row AS (
    INSERT INTO test_audit(test_data_id, action, details, actor)
    SELECT id, 'after_failover_write', data, source
    FROM new_row
    RETURNING id AS audit_id, test_data_id
)
SELECT
    (SELECT id FROM new_row) AS test_data_id,
    (SELECT audit_id FROM audit_row) AS audit_id;
COMMIT;\""

docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"BEGIN;
WITH new_row AS (
    INSERT INTO test_data(data, source)
    VALUES ('after_failover_2', 'pg2-primary')
    RETURNING id, data, source
),
audit_row AS (
    INSERT INTO test_audit(test_data_id, action, details, actor)
    SELECT id, 'after_failover_write', data, source
    FROM new_row
    RETURNING id AS audit_id, test_data_id
)
SELECT
    (SELECT id FROM new_row) AS test_data_id,
    (SELECT audit_id FROM audit_row) AS audit_id;
COMMIT;\""

# Итоговое состояние после failover
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SELECT (SELECT COUNT(*) FROM test_data) AS total_data_rows, (SELECT COUNT(*) FROM test_audit) AS total_audit_rows, (SELECT MAX(created_at) FROM test_data) AS last_data_write, (SELECT MAX(created_at) FROM test_audit) AS last_audit_write;\""
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SELECT COUNT(*) AS total_data_rows, MAX(id) AS max_data_id, MAX(id) - COUNT(*) AS skipped_ids_due_to_failed_inserts FROM test_data;\""

# Остановить фоновые процессы на хосте
# Подставить реальные PID из jobs / ps
# kill 12345 12346 12347 12348

---

Восстановление pg1

# Остановить pg1 и удалить junk
docker stop pg1
docker run --rm -v "/tmp/lab3-pg1-data:/data" --entrypoint "" postgres:16 bash -c \
  "rm -f /data/junk && ls -lah /data && df -h /data"

# Очистить PGDATA pg1
docker run --rm -v "/tmp/lab3-pg1-data:/data" --entrypoint "" postgres:16 bash -c \
  "rm -rf /data/pgdata && mkdir -p /data/pgdata && chown -R postgres:postgres /data/pgdata"

# Скопировать pg2 → pg1 через pg_basebackup
docker run --rm --network "lab3_pgnet" \
  -v "/tmp/lab3-pg1-data:/var/lib/postgresql/data" \
  --user postgres --entrypoint "" postgres:16 \
  pg_basebackup -h pg2 -p 5432 -U replicator \
  -D /var/lib/postgresql/data/pgdata -Fp -Xs -P -R

# Гарантировать standby.signal и primary_conninfo на pg1
docker run --rm -v "/tmp/lab3-pg1-data:/data" --entrypoint "" postgres:16 bash -c \
  "mkdir -p /data/pgdata && touch /data/pgdata/standby.signal && \
   if ! grep -q primary_conninfo /data/pgdata/postgresql.auto.conf 2>/dev/null; then \
     printf \"%s\n\" \"primary_conninfo = 'host=pg2 port=5432 user=replicator application_name=pg1'\" \
     >> /data/pgdata/postgresql.auto.conf; \
   fi && chown postgres:postgres /data/pgdata/standby.signal /data/pgdata/postgresql.auto.conf"

# Проверить подготовку pg1 как standby
docker run --rm -v "/tmp/lab3-pg1-data:/data" --entrypoint "" postgres:16 bash -c \
  "ls -l /data/pgdata/standby.signal && grep primary_conninfo /data/pgdata/postgresql.auto.conf"

# Запустить pg1 и дождаться готовности
docker start pg1

for i in $(seq 1 30); do
  docker exec pg1 pg_isready -U postgres && break
  sleep 2
done

for i in $(seq 1 15); do
  docker exec pg1 psql -U postgres -d testdb -Atqc "SELECT 1" && break
  sleep 2
done

# Показать роль pg1 и репликацию от pg2 к pg1
docker exec pg1 bash -c "psql -U postgres -d testdb -c \"SELECT pg_is_in_recovery() AS is_standby, pg_postmaster_start_time() AS started;\""
docker exec pg2 bash -c "psql -U postgres -d testdb -c \"SELECT client_addr, state, sent_lsn, replay_lsn, sync_state FROM pg_stat_replication;\""

---

Возврат исходной конфигурации: pg1 primary, pg2 standby

# Остановить pgpool и pg2
docker stop pgpool
docker stop pg2

# Промоутировать pg1 обратно в primary
docker exec pg1 bash -c "psql -U postgres -d testdb -c \"SELECT pg_promote(true, 60);\""
docker exec pg1 bash -c "psql -U postgres -d testdb -c \"SELECT pg_is_in_recovery() AS is_standby, now() - pg_postmaster_start_time() AS uptime_after_promote;\""

# Очистить PGDATA pg2
docker run --rm -v "lab3_pg2_data:/data" --entrypoint "" postgres:16 bash -c \
  "rm -rf /data/pgdata && mkdir -p /data/pgdata && chown -R postgres:postgres /data/pgdata"

# Скопировать pg1 → pg2 через pg_basebackup
docker run --rm --network "lab3_pgnet" \
  -v "lab3_pg2_data:/var/lib/postgresql/data" \
  --user postgres --entrypoint "" postgres:16 \
  pg_basebackup -h pg1 -p 5432 -U replicator \
  -D /var/lib/postgresql/data/pgdata -Fp -Xs -P -R

# Запустить pg2 как standby
docker start pg2

for i in $(seq 1 30); do
  docker exec pg2 pg_isready -U postgres && break
  sleep 2
done

# Проверить роли напрямую
docker exec pg1 bash -c "psql -U postgres -d testdb -c \"SELECT pg_is_in_recovery() AS is_standby;\""
docker exec pg2 bash -c "psql -U postgres -d testdb -c \"SELECT pg_is_in_recovery() AS is_standby;\""

# Дождаться подключения standby к pg1
for i in $(seq 1 20); do
  docker exec pg1 psql -U postgres -d testdb -Atqc "SELECT COUNT(*) FROM pg_stat_replication;" | grep -q '^[1-9]' && break
  sleep 2
done

# Показать репликацию pg1 → pg2
docker exec pg1 bash -c "psql -U postgres -d testdb -c \"SELECT application_name, state, sync_state FROM pg_stat_replication;\""
docker exec pg2 bash -c "psql -U postgres -d testdb -c \"SELECT status, conninfo FROM pg_stat_wal_receiver;\""

---

Финальная проверка через pgpool

# Вернуть pgpool
docker start pgpool
docker exec pgpool bash -c "echo 'localhost:9898:postgres:postgres' > /root/.pcppass && chmod 600 /root/.pcppass"

# Показать pool_nodes
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SHOW pool_nodes;\""

# Показать финальные данные
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SELECT d.id, CASE WHEN length(d.data) > 80 THEN left(d.data, 80) || '...' ELSE d.data END AS data, d.source, a.id AS audit_id, a.action, a.actor, a.created_at FROM test_data d JOIN test_audit a ON a.test_data_id = d.id ORDER BY d.id DESC, a.id DESC LIMIT 10;\""

# Финальная сверка строк и sequence
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SELECT (SELECT COUNT(*) FROM test_data) AS total_data_rows, (SELECT COUNT(*) FROM test_audit) AS total_audit_rows, (SELECT MAX(id) FROM test_data) AS max_data_id, (SELECT MAX(id) FROM test_data) - (SELECT COUNT(*) FROM test_data) AS skipped_data_ids_due_to_failed_inserts;\""

# Финальная запись через восстановленный primary pg1
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"BEGIN;
WITH new_row AS (
    INSERT INTO test_data(data, source)
    VALUES ('recovery_complete', 'pg1-restored-primary')
    RETURNING id, data, source
),
audit_row AS (
    INSERT INTO test_audit(test_data_id, action, details, actor)
    SELECT id, 'recovery_complete', data, source
    FROM new_row
    RETURNING id AS audit_id, test_data_id
)
SELECT
    (SELECT id FROM new_row) AS test_data_id,
    (SELECT audit_id FROM audit_row) AS audit_id;
COMMIT;\""

# 12 SELECT для финальной балансировки
for i in $(seq 1 12); do
  docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SELECT d.id, LEFT(d.data, 60) AS data_preview, a.action, a.actor FROM test_data d JOIN test_audit a ON a.test_data_id = d.id ORDER BY a.id DESC LIMIT 1;\"" >/dev/null
done

# Финальный pool_nodes
docker exec pgpool bash -c "psql -h localhost -p 9999 -U postgres -d testdb -c \"SHOW pool_nodes;\""

# Финальные counts напрямую на pg1 и pg2
docker exec pg1 bash -c "psql -U postgres -d testdb -c \"SELECT (SELECT COUNT(*) FROM test_data) AS data_rows, (SELECT COUNT(*) FROM test_audit) AS audit_rows;\""
docker exec pg2 bash -c "psql -U postgres -d testdb -c \"SELECT (SELECT COUNT(*) FROM test_data) AS data_rows, (SELECT COUNT(*) FROM test_audit) AS audit_rows;\""