インフラエンジニアがいざという時に抑えておきたい postgresql コマンド

12/9/2016 - 1:32 AM

インフラエンジニアがいざという時に抑えておきたい postgresql コマンド

Rendered
Source

一旦テーブルの中身を把握したい時に使える一括CSV形式での出力

psql DATABASE_URL -c "select id,title from articles" -A -F,  | pbcopy

-A 位置揃えなし
-F, カンマ区切り
DATABASE_URL は postgres://user:password@host:port/database

DB が重くてレスポンス返ってこない時に対処する流れ

めちゃくちゃ時間がかかるクエリが走っていて、問題が起こっている場合の対処。

問い合わせを行っているWebサーバー側で対応が出来ると良いけど、DBサーバー側で対応の必要がある時。

pg_stat_activity で統計情報を取得する

現在実行中のクエリはpg_stat_activityテーブルが管理しているため確認できる。

列	型	説明
datid	oid	バックエンドが接続するデータベースのOIDです。
datname	name	バックエンドが接続するデータベースの名前です。
pid	integer	バックエンドのプロセスIDです。
usesysid	oid	バックエンドにログインしたユーザの識別子です。
usename	name	バックエンドに接続したユーザの名前です。
application_name	text	バックエンドに接続したアプリケーションの名前です。
client_addr	inet	バックエンドに接続したクライアントのIPアドレスです。このフィールドがNULLである場合、これはクライアントがサーバマシン上のUnixソケット経由で接続されたか、自動バキュームなど内部処理であることを示します。
client_hostname	text	client_addrの逆引き検索により報告された、接続クライアントのホスト名です。 IP接続、かつlog_hostnameが有効である場合にのみこのフィールドは非NULLになります。
client_port	integer	クライアントがバックエンドとの通信に使用するTCPポート、もしUnixソケットを使用する場合は-1です。
backend_start	timestamp with time zone	プロセスが開始、つまりクライアントがサーバに接続した時刻です。
xact_start	timestamp with time zone	プロセスの現在のトランザクションが開始した時刻です。活動中のトランザクションがない場合はNULLです。現在の問い合わせがトランザクションの先頭である場合、この列はquery_start列と同じです。
query_start	timestamp with time zone	現在有効な問い合わせが開始した時刻です。もしstateがactiveでない場合は直前の問い合わせが開始した時刻です。
state_change	timestamp with time zone	stateの最終変更時刻です。
waiting	boolean	バックエンドが現在ロックを待機している場合は真です。
state	text	現在のバックエンドの総体的な状態です。以下のいずれかの値を取ることができます。
		`active:` バックエンドは問い合わせを実行中です。
		`idle:` バックエンドは新しいクライアントからのコマンドを待機しています。
		`idle in transaction:` バックエンドはトランザクションの内部にいますが、現在実行中の問い合わせがありません。
		`idle in transaction (aborted):` この状態はidle in transactionと似ていますが、トランザクション内のある文がエラーになっている点が異なります。
		`fastpath function call:` バックエンドは近道関数を実行中です。
		`disabled:` この状態は、このバックエンドでtrack_activitiesが無効である場合に報告されます。
backend_xid	xid	もしあれば、このバックエンドの最上位のトランザクション識別子。
backend_xmin	xid	現在のバックエンドのxmin。
query	text	バックエンドの最も最近の問い合わせテキストです。 stateがactiveの場合、現在実行中の問い合わせを意味します。その他のすべての状態では、実行済みの最後の問い合わせを示します。

pg_stat_activityはサーバプロセス毎に、そのプロセスの現在の活動に関連する情報を表示する１行を持ちます。
注意: waitingとstate列は独立 しています。バックエンドがactive状態である場合、waitingかもしれませんし、そうでないかもしれません。状態がactiveであり、waitingが真である場合、問い合わせは実行中ですが、システム内の何らかのロックによりブロックされていることを意味します。

pg_stat_activityの中でよく見る項目と便利な関数

pid: プロセスのID
query_start: SQLクエリを実行開始時間
query: 実行しているSQLクエリ
state: 実行状態
substr(query, 0, 50) :http://www.shift-the-oracle.com/sql/functions/substr.html SUBSTR は取り出す文字列をキャラクタ単位で部分文字列の取り出しを行なう。文字列 string の開始位置 position から legnth 文字、または、バイト分の部分文字列 (SUBSTRING) を戻す。query 全部観たいときは substr(query, 0) にする

SELECT pid, query_start, substr(query, 0, 50) FROM pg_stat_activity WHERE state='active' ORDER BY query_start;

  pid  |          query_start          |                      substr                       
-------+-------------------------------+---------------------------------------------------- 
 26368 | 2016-12-09 01:58:27.238954+00 | SELECT pid, query_start, substr(query, 0, 50) FRO

datname, usename, application_name, client_hostname, backend_start, state_change, waiting は適宜追加すると良い。

実行中のクエリを殺す

殺したいクエリのpidさえ分かれば、以下のクエリでプロセスを殺すことが出来る

SELECT pg_cancel_backend(847);

SIGINT にあたる。

これでも死なない場合は、

SELECT pg_terminate_backend(847);

SIGTERM に相当するので死ぬはず。
※使うときは自己責任!!!!

大量に殺したい時(※使うときは自己責任!!!!)

同じようなクエリが複数発生している場合

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE substr(query, 0, 50) = 'sql 文'

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE substr(query, 0, 50) = 'sql 文'

条件にマッチするもの全てを殺すことが出来る。

inactive を問答無用で一括削除

 SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE state != 'active' ORDER BY query_start;

 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state != 'active' ORDER BY query_start;

問答無用で動いている query すべて消す(※使うときは自己責任!!!!)

 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state='active' AND pid <> pg_backend_pid();

pid <> pg_backend_pid()

これがないと自分自身を消しにいくため対象から外す！

`db:migrate`時に以下のクエリを実行しても結果が返ってこない状態

ALTER TABLE "article" ADD COLUMN "updated_by" integer DEFAULT 0 NOT NULL`

以下のようにstate = activeのままで残ってしまっている状態。

=> SELECT pid, query_start, substr(query, 0, 200) FROM pg_stat_activity WHERE state='active' ORDER BY query_start limit 50;
  pid  |          query_start          |                                                          substr
-------+-------------------------------+--------------------------------------------------------------------------------------------------------------------------
 27679 | 2015-11-13 03:49:06.057732+00 | ALTER TABLE "article" ADD COLUMN "updated_by" integer DEFAULT 0 NOT NULL
 22383 | 2015-11-13 03:49:12.738348+00 | SELECT pid, query_start, substr(query, 0, 200) FROM pg_stat_activity WHERE state='active' ORDER BY query_start limit 50;
(2 rows)

stateを active 以外も確認する

=> SELECT pid, query_start, substr(query, 0, 200) FROM pg_stat_activity WHERE state != 'active' ODER BY query_start LIMIT 30;

  pid  |        state        |          query_start          |                                               substr                                                
-------+---------------------+-------------------------------+----------------------------- ------------------------------------------------------------------------
 10575 | idle in transaction | 2015-11-11 03:33:40.675587+00 | SELECT  "jobs".* FROM "jobs"  WHERE "jobs"."deleted_at" IS NULL AND "jobs"."user_id" = $1 AND "jobs
 30356 | idle                | 2015-11-12 13:43:26.010795+00 |             SELECT attr.attname                                                                    +
       |                     |                               |             FROM pg_attribute attr                                                                 +
       |                     |                               |             INNER JOIN pg_constr
 17047 | idle                | 2015-11-12 14:10:04.15198+00  | SELECT  "projects".* FROM "projects"  WHERE "projects"."deleted_at" IS NULL AND "projects"."id" = $
  4591 | idle                | 2015-11-13 01:18:02.595226+00 | SELECT pg_cancel_backend(915);
 20262 | idle                | 2015-11-13 02:00:19.977307+00 | SELECT  "profiles".* FROM "profiles"  WHERE "profiles"."user_id" = $1 LIMIT 1
 30027 | idle                | 2015-11-13 02:00:22.280172+00 | SELECT "users".* FROM "users"  WHERE "users"."id" IN (123)
 20287 | idle                | 2015-11-13 03:00:19.565167+00 | SELECT "users".* FROM "users"  WHERE "users"."id" IN (234, 345)
 20263 | idle                | 2015-11-13 03:00:23.19501+00  | SELECT "users".* FROM "users"  WHERE "users"."id" IN (456, 567)
 21243 | idle                | 2015-11-13 03:13:14.598247+00 | ALTER TABLE "article" ADD COLUMN "updated_by" integer DEFAULT 0 NOT NUL;
 22383 | idle                | 2015-11-13 03:50:32.823274+00 | SELECT pid, query_start, substr(query, 0, 200) FROM pg_stat_activity WHERE state='active' ORDER BY

idleおよびidle in transactionなクエリを殺していくとalter tableできるようになる。

SELECT pg_terminate_backend(PID);
or
SELECT pg_cancel_backend(PID);

Cacher is the code snippet organizer for pro developers

We empower you and your team to get more done, faster

インフラエンジニアがいざという時に抑えておきたい postgresql コマンド

一旦テーブルの中身を把握したい時に使える一括CSV形式での出力

DB が重くてレスポンス返ってこない時に対処する流れ

pg_stat_activity で統計情報を取得する

pg_stat_activityの中でよく見る項目と便利な関数

実行中のクエリを殺す

大量に殺したい時(※使うときは自己責任!!!!)

問答無用で動いている query すべて消す(※使うときは自己責任!!!!)

db:migrate時に以下のクエリを実行しても結果が返ってこない状態

`db:migrate`時に以下のクエリを実行しても結果が返ってこない状態