postgresqlコマンド(運用系)

ここではデータベース環境の運用・保守においてよく使用するコマンドやSQLを示します。

データベースのバックアップ・リストア

(1)plain形式
■バックアップ
pg_dump -h 127.0.0.1 -p 5432 -U postgres -F plain -c -f /tmp/mydatabase_plain.dump mydatabase
■リストア
psql -h 127.0.0.1 -p 5432 -U postgres mydatabase < /tmp/mydatabase_plain.dump

(2)custom形式
■バックアップ
pg_dump -h 127.0.0.1 -p 5432 -U postgres -F custom -c -f /tmp/mydatabase_custom.dump mydatabase
■リストア
pg_restore -h 127.0.0.1 -p 5432 -U postgres -d mydatabase -c /tmp/mydatabase_custom.dump

コネクション確認・切断

■コネクション確認
SELECT pid, usename, application_name, client_addr FROM pg_stat_activity WHERE datname = 'mydatabase';

※「SELECT * FROM pg_stat_activity」はよく使う情報のひとつである。
コネクション状態の他にロングトランザクションプロセスの確認等にも使用される。

■コネクション切断(該当PIDのものを切断)
SELECT pg_terminate_backend(12345);

■コネクション切断(自身以外のものを切断)
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydatabase' AND pid <> pg_backend_pid();


【凡例】
・mydattabase:対象とするデータベース名
・12345:切断するコネクションのPID