PGPractikum
PGPractikum

Справочник команд

Все команды PostgreSQL для практикума по репликации

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

pg_lsclusters

pg_lsclusters

Показать список кластеров PostgreSQL

Команда
pg_lsclusters
Вывод:
Ver Cluster Port Status Owner Data directory               Log file
16  training 5433 online postgres /var/lib/postgresql/16/training /var/log/postgresql/postgresql-16-training.log

pg_ctlcluster

sudo

Управление кластером (старт/стоп/рестарт)

Команда
sudo pg_ctlcluster 16 training start

pg_createcluster

sudo

Создание нового кластера

Команда
sudo pg_createcluster 16 training -- --auth-local=peer --auth-host=md5

pg_dropcluster

sudo

Удаление кластера

Команда
sudo pg_dropcluster 16 training --stop

Репликация

pg_basebackup

sudo

Создание базовой копии для реплики

Команда
sudo -u postgres pg_basebackup -h MASTER_HOST -p 5433 -D /var/lib/postgresql/16/training -U replicator -Fp -Xs -P -R

pg_rewind

sudo

Синхронизация узла после failover

Команда
sudo -u postgres pg_rewind --target-pgdata=/var/lib/postgresql/16/training --source-server='host=NEW_MASTER port=5433 user=replicator dbname=postgres' --progress

pg_stat_replication

sudo

Просмотр состояния репликации на мастере

Команда
sudo -u postgres psql -p 5433 -c "SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn FROM pg_stat_replication;"

pg_stat_wal_receiver

sudo

Просмотр состояния WAL-приёмника на реплике

Команда
sudo -u postgres psql -p 5433 -c "SELECT status, receive_start_lsn, received_lsn, last_msg_send_time FROM pg_stat_wal_receiver;"

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

sudo

Тест подключения к реплике через psql

Команда
sudo -u postgres psql -h REPLICA_HOST -p 5433 -U replicator -c "SELECT pg_is_in_recovery();"
Вывод:
 pg_is_in_recovery 
---------------------
 t
(1 row)

Тест записи на мастере

sudo

Проверка записи данных на мастере

Команда
sudo -u postgres psql -p 5433 -c "INSERT INTO repl_demo(note) VALUES ('test record'); SELECT * FROM repl_demo;"

Проверка чтения на реплике

sudo

Проверка чтения данных на реплике

Команда
sudo -u postgres psql -h REPLICA_HOST -p 5433 -c "SELECT * FROM repl_demo;"
Вывод:
 id |    note     |          created_at          
----+-------------+------------------------------
  1 | test record | 2024-01-15 10:30:00+00
(1 row)

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

sudo

Попытка записи на реплику (должна вернуть ошибку)

Команда
sudo -u postgres psql -h REPLICA_HOST -p 5433 -c "INSERT INTO repl_demo(note) VALUES ('should fail');"
Вывод:
ERROR:  cannot execute INSERT in a read-only transaction

Безопасность

Создание роли репликации

sudo

Создание роли с атрибутом REPLICATION

Команда
sudo -u postgres psql -p 5433 -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'your_password';"

Создание слота

sudo

Создание физического слота репликации

Команда
sudo -u postgres psql -p 5433 -c "SELECT pg_create_physical_replication_slot('training_slot');"

Просмотр слотов

sudo

Проверка активности слотов репликации

Команда
sudo -u postgres psql -p 5433 -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

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

echo

Добавление правила для репликации

Команда
echo 'hostssl replication replicator REPLICA_HOST/32 scram-sha-256' | sudo tee -a /etc/postgresql/16/training/pg_hba.conf

Генерация самоподписанного сертификата

sudo

Создание TLS-сертификата для шифрования

Команда
sudo -u postgres openssl req -new -x509 -days 365 -nodes -text -out server.crt -keyout server.key -subj "/CN=MASTER_HOST"

Проверка сертификата

sudo

Просмотр информации о сертификате

Команда
sudo -u postgres openssl x509 -in server.crt -text -noout | head -20

Копирование сертификата на реплику

scp

Передача сертификата мастера на реплику

Команда
scp /var/lib/postgresql/16/training/server.crt REPLICA_HOST:/tmp/master-server.crt

Проверка прав доступа

sudo

Просмотр правил pg_hba.conf

Команда
sudo -u postgres psql -p 5433 -c "SELECT type, database, user_name, address, method FROM pg_hba_file_rules WHERE type = 'host';"

Мониторинг

Проверка WAL

sudo

Просмотр сегментов WAL

Команда
sudo -u postgres psql -p 5433 -c "SELECT * FROM pg_ls_waldir() ORDER BY modification DESC LIMIT 10;"

Проверка размера БД

sudo

Просмотр размера базы данных

Команда
sudo -u postgres psql -p 5433 -c "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;"

Просмотр конфигурации

sudo

Проверка параметров репликации

Команда
sudo -u postgres psql -p 5433 -c "SHOW wal_level; SHOW max_wal_senders; SHOW hot_standby;"

Логи репликации

tail

Просмотр логов кластера

Команда
tail -f /var/log/postgresql/postgresql-16-training.log

Проверка отставания реплики

sudo

Расчет отставания реплики в секундах

Команда
sudo -u postgres psql -p 5433 -c "SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0 ELSE EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())::int END AS lag_seconds;"
Вывод:
 lag_seconds 
-------------
           0
(1 row)

Просмотр активных запросов

sudo

Мониторинг активных соединений

Команда
sudo -u postgres psql -p 5433 -c "SELECT pid, usename, application_name, state, query_start, left(query, 50) AS query FROM pg_stat_activity WHERE state = 'active';"

Проверка размера WAL

sudo

Просмотр текущего размера WAL

Команда
sudo -u postgres psql -p 5433 -c "SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')) AS wal_size;"

Проверка replication slots

sudo

Детальная информация о слотах

Команда
sudo -u postgres psql -p 5433 -c "SELECT slot_name, plugin, slot_type, database, active, xmin, catalog_xmin, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots;"

Проверка конфигурации

sudo

Просмотр всех параметров репликации

Команда
sudo -u postgres psql -p 5433 -c "SELECT name, setting, unit, category FROM pg_settings WHERE name IN ('wal_level', 'max_wal_senders', 'wal_keep_size', 'hot_standby', 'wal_log_hints', 'max_replication_slots');"

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

1-- Проверка состояния репликации на мастере
2SELECT
3  client_addr,
4  state,
5  sent_lsn,
6  write_lsn,
7  flush_lsn,
8  replay_lsn,
9  (sent_lsn - replay_lsn) AS replication_lag
10FROM pg_stat_replication;
11
12-- Просмотр слотов репликации
13SELECT
14  slot_name,
15  active,
16  restart_lsn,
17  confirmed_flush_lsn
18FROM pg_replication_slots;
19
20-- Создание тестовой таблицы для проверки
21CREATE TABLE repl_demo (
22  id SERIAL PRIMARY KEY,
23  note TEXT,
24  created_at TIMESTAMPTZ DEFAULT NOW()
25);
26
27-- Проверка роли репликации
28SELECT rolname, rolsuper, rolinherit, rolreplication, rolcanlogin
29FROM pg_roles
30WHERE rolname = 'replicator';
31
32-- Проверка pg_hba.conf для репликации
33SELECT type, database, user_name, address, method
34FROM pg_hba_file_rules
35WHERE type = 'host' AND user_name = 'replicator';
36
37-- Проверка WAL уровня
38SHOW wal_level;
39-- Должно быть: replica
40
41-- Проверка максимального числа WAL-отправителей
42SHOW max_wal_senders;
43-- Рекомендуется: 10 или больше
44
45-- Проверка hot_standby (для чтения на реплике)
46SHOW hot_standby;
47-- Должно быть: on
ПрактикумМодулиСправочникГлоссарийСправка