Postgresql cluster (with pgpool)を運用している中で使うquery

Postgresqlのclusterの運用で使うqueryをまとめてみました。
pgpool + streaming replication で構築されています。

プライマリサーバ上 (on primary server)

read-slave サーバの一覧を取得 (get list of read-slave servers)

# select * from pg_stat_replication;

analyze, vacuumの実行状況を確認 (check analyze and vacuum execution)

# select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_all_tables WHERE schemaname = 'SCHEMA_NAME' and relname = 'TABLE_NAME';

データベース、テーブルのサイズ確認 (check size of databases and tables)

# select pg_size_pretty(pg_database_size('DATABASE_NAME'));
# select pg_size_pretty(pg_relation_size('TABLE_NAME'));

リードスレイブサーバ上 (on read-slave server)

レプリケーションの遅延を確認 (check replication-delay time)

# select * from pg_stat_replication;

共通 (common)

実行中のクエリを実行時間が長い順に表示 (show running queries order by execution time desc)

# select procpid, start, now() - start AS lap, current_query from (select backendid, pg_stat_get_backend_pid(S.backendid) AS procpid, pg_stat_get_backend_activity_start(S.backendid) AS start, pg_stat_get_backend_activity(S.backendid) AS current_query from (select pg_stat_get_backend_idset() AS backendid) AS S ) AS S where current_query <> '' order by lap desc;

特定のsessionを切断 (terminate some specific session)

PIDは上のqueryを実行するなどして取得 (please get a target PID via query above or some other way)

# select pg_terminate_backend(PID) FROM pg_stat_activity WHERE datname = 'DATABASE_NAME' AND pid <> pg_backend_pid();