wadaxでPostGresQLのDB引っ越し

なんだかんだ苦労した。スーパーユーザー権限ないしね。

バックアップ

.pgpass

localhost:5432:DB名:ユーザー名:パスワード

wadax_backup.sh

/usr/bin/pg_dump -h localhost -p 5432 -U <ユーザー名> <DB名> > /var/www/vhosts/ドメイン/backup.dump

wadax_backup.php (これを呼び出してwadax_backup.shを動かす)

<?php
 $output = shell_exec("/usr/bin/sh /var/www/vhosts/ドメイン/wadax_backup.sh");
?>

リストア

テーブルを根こそぎ削除してから復元する

事前準備

backup.dumpを古いサーバーからコピーしておくこと

.pgpass

localhost:5432:DB名:ユーザー名:パスワード

cleanup.sql

-- cleanup.sql （最初に読み込む）
DO $$ DECLARE
    r RECORD;
BEGIN
    -- Drop all foreign key constraints
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
        EXECUTE 'ALTER TABLE IF EXISTS ' || quote_ident(r.tablename) || ' DROP CONSTRAINT IF EXISTS ' || quote_ident(r.tablename) || '_pkey CASCADE';
    END LOOP;

    -- Drop all tables
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;

    -- Drop all sequences
    FOR r IN (SELECT relname FROM pg_class WHERE relkind = 'S') LOOP
        EXECUTE 'DROP SEQUENCE IF EXISTS ' || quote_ident(r.relname) || ' CASCADE';
    END LOOP;

    -- Drop all functions
    FOR r IN (
        SELECT routine_name, routine_schema
        FROM information_schema.routines
        WHERE routine_schema = 'public'
    ) LOOP
        EXECUTE 'DROP FUNCTION IF EXISTS ' || quote_ident(r.routine_name) || ' CASCADE';
    END LOOP;
END $$;

wadax_restore.sh

#!/bin/bash

PG_HOST="localhost"
PG_PORT="5432"
PG_USER="ユーザー名"
PG_DB="DB名"
DUMP_FILE="/var/www/vhosts/ドメイン/backup.dump"
CLEAN_FILE="/var/www/vhosts/ドメイン/cleanup.sql"
LOG_FILE="/var/www/vhosts/ドメイン/pg_restore_logfile.log"

echo "=== CLEANING EXISTING DB OBJECTS ==="
psql -h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" -d "$PG_DB" -f "$CLEAN_FILE" > /dev/null 2>&1

echo "=== RESTORING FROM DUMP FILE ==="
psql -h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" -d "$PG_DB" -f "$DUMP_FILE" > "$LOG_FILE" 2>&1

if [ $? -eq 0 ]; then
    echo "=== RESTORE COMPLETED SUCCESSFULLY ==="
else
    echo "=== RESTORE FAILED. CHECK LOG: $LOG_FILE ==="
fi

wadax_restore.php (wadax_restore.shを実行するもの。タイムアウト対策してる)

<?php
$cmd = "/usr/bin/sh /var/www/vhosts/ドメイン/wadax_restore.sh";
$timeout = 600; // タイムアウト秒数

$descriptorspec = [
    0 => ["pipe", "r"], // stdin
    1 => ["pipe", "w"], // stdout
    2 => ["pipe", "w"], // stderr
];

// プロセス開始
$process = proc_open($cmd, $descriptorspec, $pipes);

if (is_resource($process)) {
    // ノンブロッキング設定
    stream_set_blocking($pipes[1], false);
    stream_set_blocking($pipes[2], false);

    $start = time();
    $stdout = '';
    $stderr = '';

    while (true) {
        $status = proc_get_status($process);
        if (!$status['running']) {
            break;
        }

        // 出力を随時回収
        $stdout .= stream_get_contents($pipes[1]);
        $stderr .= stream_get_contents($pipes[2]);

        if (time() - $start > $timeout) {
            proc_terminate($process); // ソフト終了
            sleep(1);
            $status = proc_get_status($process);
            if ($status['running']) {
                proc_terminate($process, 9); // 強制終了 (SIGKILL)
            }
            $stderr .= "\n[Timeout after {$timeout} seconds]";
            break;
        }

        usleep(100000); // 0.1秒待つ
    }

    // 終了後の残り出力を回収
    $stdout .= stream_get_contents($pipes[1]);
    $stderr .= stream_get_contents($pipes[2]);

    fclose($pipes[0]);
    fclose($pipes[1]);
    fclose($pipes[2]);
    proc_close($process);

    // 出力をまとめる
    $output = "=== STDOUT ===\n" . $stdout . "\n=== STDERR ===\n" . $stderr;

    echo nl2br(htmlspecialchars($output)); // HTML用表示（必要な場合）
} else {
    echo "Failed to start process.\n";
}
?>

なにかあったら pg_restore_logfile.log をみて確認すること。