PostgreSQL: PostgreSQLバックアップ、復元、高可用性

# カスタム形式（推奨、圧縮、並列復元可能）
pg_dump -h localhost -U postgres -Fc shop_db > /backup/shop_db_$(date +%Y%m%d).dump

# 4並列ワーカーでのディレクトリ形式
pg_dump -h localhost -U postgres -Fd shop_db -j 4 -f /backup/shop_db_dir/

# プレーンSQL形式（人間が読める、復元前に編集可能）
pg_dump -h localhost -U postgres -Fp shop_db > /backup/shop_db.sql

# コマンドが正常に実行されました

# ordersテーブルとorder_itemsテーブルのみバックアップ
pg_dump -h localhost -U postgres -Fc \
  -t orders -t order_items \
  shop_db > /backup/order_tables.dump

# パターンに一致するすべてのテーブルをバックアップ
pg_dump -h localhost -U postgres -Fc \
  -t 'order_*' \
  shop_db > /backup/order_prefix_tables.dump

# 大規模なログテーブルを除外
pg_dump -h localhost -U postgres -Fc \
  --exclude-table='access_log_*' \
  shop_db > /backup/shop_no_logs.dump

# コマンドが正常に実行されました

# 全データベースとロールをバックアップ（プレーンSQLのみ）
pg_dumpall -h localhost -U postgres > /backup/cluster_full_$(date +%Y%m%d).sql

# ロールのみバックアップ（移行に便利）
pg_dumpall -h localhost -U postgres --roles-only > /backup/roles_only.sql

# テーブルスペース定義のみバックアップ
pg_dumpall -h localhost -U postgres --tablespaces-only > /backup/tablespaces.sql

# コマンドが正常に実行されました

# 新しいデータベースに復元
createdb -h localhost -U postgres shop_db_restore
pg_restore -h localhost -U postgres -d shop_db_restore /backup/shop_db.dump

# 並列復元（4ワーカー、ディレクトリ形式）
pg_restore -h localhost -U postgres -d shop_db -j 4 /backup/shop_db_dir/

# クリーン復元（既存オブジェクトを先に削除）
pg_restore -h localhost -U postgres -d shop_db \
  --clean --if-exists /backup/shop_db.dump

# コマンドが正常に実行されました

# アーカイブ内容を一覧表示してテーブルエントリ番号を確認
pg_restore --list /backup/shop_db.dump

# 名前で特定のテーブルのみ復元
pg_restore -h localhost -U postgres -d shop_db \
  -t products -t categories \
  /backup/shop_db.dump

# スキーマのみ復元（データなし）
pg_restore -h localhost -U postgres -d shop_db \
  --section=pre-data /backup/shop_db.dump

# コマンドが正常に実行されました

# pg_dumpall出力からの復元
psql -h localhost -U postgres -f /backup/cluster_full_20250601.sql

# pg_dumpプレーン形式からの復元
createdb -h localhost -U postgres shop_db_new
psql -h localhost -U postgres -d shop_db_new -f /backup/shop_db.sql

# psqlコマンドが正常に実行されました

-- ヘッダー付きCSVにエクスポート
COPY (SELECT order_id, customer_id, total_amount, order_date
      FROM orders
      WHERE order_status = 'completed'
      ORDER BY order_date DESC)
TO '/tmp/completed_orders.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

-- テーブル全体をエクスポート
COPY products TO '/tmp/products.csv' WITH (FORMAT csv, HEADER true);

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- CSVからインポート
COPY products(product_name, unit_price, category, stock_qty)
FROM '/data/new_products.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

-- エラー処理付きインポート（PG 17+）
COPY products(product_name, unit_price, category, stock_qty)
FROM '/data/new_products.csv'
WITH (FORMAT csv, HEADER true, ON_ERROR ignore);

-- SQL文が正常に実行されました

-- バイナリエクスポート（高速、小サイズ）
COPY orders TO '/tmp/orders.bin' WITH (FORMAT binary);

-- バイナリインポート
COPY orders FROM '/tmp/orders.bin' WITH (FORMAT binary);

-- SQL文が正常に実行されました

# postgresql.confの設定
cat >> /etc/postgresql/16/main/postgresql.conf << 'EOF'
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'
archive_timeout = 300
max_wal_senders = 3
EOF

# PostgreSQLを再起動
pg_ctlcluster 16 main restart

# コマンドが正常に実行されました

# 完全物理バックアップ（PITRのベース）
pg_basebackup -h localhost -U replicator \
  -D /backup/base_$(date +%Y%m%d) \
  -Ft -z -P \
  --checkpoint=fast

# -Ft: tar形式
# -z: gzip圧縮
# -P: 進捗表示
# --checkpoint=fast: バックアップ前にチェックポイントを強制

# コマンドが正常に実行されました

# ステップ1: PostgreSQLを停止
pg_ctlcluster 16 main stop

# ステップ2: 既存のデータディレクトリをクリア
rm -rf /var/lib/postgresql/16/main/*

# ステップ3: ベースバックアップを復元
tar -xzf /backup/base_20250601/base.tar.gz \
  -C /var/lib/postgresql/16/main/

# ステップ4: リカバリ設定を作成
cat >> /var/lib/postgresql/16/main/postgresql.auto.conf << 'EOF'
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_time = '2025-06-01 02:59:00'
recovery_target_action = 'promote'
EOF

# ステップ5: リカバリシグナルファイルを作成
touch /var/lib/postgresql/16/main/recovery.signal

# ステップ6: PostgreSQLを起動（ターゲット時刻まで復旧）
pg_ctlcluster 16 main start

# PostgreSQLは02:59:00までWALを再生し、その後プロモートします

# コマンドが正常に実行されました

# プライマリ側：postgresql.conf
wal_level = replica
max_wal_senders = 5
wal_keep_size = '1GB'

# プライマリ側：pg_hba.conf
echo 'host replication replicator 192.168.1.0/24 md5' >> pg_hba.conf

# プライマリ側：レプリケーションユーザーを作成
psql -c "CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'RepP@ss';"

# スタンバイ側：ベース��ックアップを取得
pg_basebackup -h primary_host -U replicator \
  -D /var/lib/postgresql/16/main -Fp -Xs -P -R

# -R: standby.signalを作成し自動設定
# スタンバイは起動時にプライマリに自動接続します

# psqlコマンドが正常に実行されました

-- パブリッシャー側（ソースデータベース）
CREATE PUBLICATION pub_orders FOR TABLE orders, order_items;

-- またはスキーマ内の全テーブルをパブリッシュ
CREATE PUBLICATION pub_all FOR ALL TABLES;

-- サブスクライバー側（ターゲットデータベース）
CREATE SUBSCRIPTION sub_orders
  CONNECTION 'host=primary_host dbname=shop_db user=replicator password=RepP@ss'
  PUBLICATION pub_orders;

-- レプリケーション状態を確認
SELECT * FROM pg_stat_replication;       -- パブリッシャー側
SELECT * FROM pg_stat_subscription;      -- サブスクライバー側

CREATE TABLE

-- プライマリでレプリケーション遅延を確認
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS replication_lag
FROM pg_stat_replication;

-- スタンバイがリカバリモードかを確認
SELECT pg_is_in_recovery();

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

#!/bin/bash
# shop_dbの日次バックアップスクリプト

BACKUP_DIR="/backup/daily"
DATE=$(date +%Y%m%d_%H%M%S)
RETAIN_DAYS=7

# 完全論理バックアップ（pg_dumpカスタム形式）
pg_dump -h localhost -U postgres -Fc -Z6 \
  shop_db > ${BACKUP_DIR}/shop_db_${DATE}.dump

# ロールを個別にバックアップ
pg_dumpall -h localhost -U postgres --roles-only \
  > ${BACKUP_DIR}/roles_${DATE}.sql

# 古いバックアップをクリーンアップ（7日間保持）
find ${BACKUP_DIR} -name "*.dump" -mtime +${RETAIN_DAYS} -delete
find ${BACKUP_DIR} -name "*.sql" -mtime +${RETAIN_DAYS} -delete

echo "バックアップ完了: shop_db_${DATE}.dump"

# コマンドが正常に実行されました

# pg_dumpバックアップが読み取り可能か確認
pg_restore --list /backup/shop_db.dump > /dev/null
if [ $? -eq 0 ]; then
  echo "バックアップ検証OK"
else
  echo "バックアップ破損 - 運用チームに警告！"
fi

# WALアーカイブが遅延していないか確認
psql -c "SELECT pg_current_wal_lsn(), pg_last_archive_lsn();"

# psqlコマンドが正常に実行されました

-- ステップ1: 事故時刻とデータ損失を確認
SELECT pg_current_wal_lsn();  -- 現在のLSNを記録
SELECT COUNT(*) FROM products WHERE category = 'Electronics';  -- 損失を確認

-- ステップ2: WALアーカイブに必要なセグメントがあるか確認
SELECT pg_last_archive_lsn();
-- 02:59 AM時点のLSN以上であるべき

# ステップ3: 状態を保持するため即座にPostgreSQLを停止
pg_ctlcluster 16 main stop

# ステップ4: 現在のWALファイルを保存（削除しないでください！）
cp -r /var/lib/postgresql/16/main/pg_wal /tmp/pg_wal_backup/

# ステップ5: ベースバックアップを復元
rm -rf /var/lib/postgresql/16/main/*
tar -xzf /backup/base_20250531/base.tar.gz \
  -C /var/lib/postgresql/16/main/

# ステップ6: 完全なリカバリのため保存したWALを戻す
cp /tmp/pg_wal_backup/* /var/lib/postgresql/16/main/pg_wal/

# ステップ7: PITRターゲットを設定
cat >> /var/lib/postgresql/16/main/postgresql.auto.conf << 'EOF'
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_time = '2025-06-01 02:59:00'
recovery_target_action = 'promote'
EOF

touch /var/lib/postgresql/16/main/recovery.signal

# ステップ8: 起動して検証
pg_ctlcluster 16 main start

# ステップ9: リカバリを検証
psql -c "SELECT COUNT(*) FROM products WHERE category = 'Electronics';"

-- ステップ10: リカバリ成功後、新しいバックアップを取得
-- データ整合性確認後にpsqlで実行
SELECT pg_switch_wal();  -- クリーンなアーカイブポイントのためにWAL切り替えを強制

# ステップ11: 将来のPITR用に新しいベースバックアップを取得
pg_basebackup -h localhost -U postgres \
  -D /backup/base_$(date +%Y%m%d) -Ft -z -P