PostgreSQLの便利技をまとめてみた

2017/03/17

PostgreSQLの基本操作、便利ワザ

コマンドラインでPostgreSQLの操作を行う際の基本的なオペレーションをまとめていきますー

過去記事とのかぶりは勘弁して下さい・・

実行中のSQL表示

現在どんなクエリが走っているときは下記のクエリを流しましょう。

SELECT
     pid
    , start
    , now() - start AS lap
    , current_query
FROM (
     SELECT backendid
          , pg_stat_get_backend_pid(S.backendid) AS pid
          , 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 <> ''
 AND current_query <> ''
ORDER BY lap DESC;

SQL停止

「実行中のSQLの表示」のクエリを実行した時に出力されたのpid がプロセスIDです。
そのプロセスIDを指定してSQLを停止できます。

SELECT pg_cancel_backend(プロセスID);
SELECT pg_cancel_backend(69053);

テーブルのGRANT情報表示

テーブルの権限がどのように設定されているかがわかります。

テーブルの容量表示

テーブルでどのくらいの容量を食っているか確認する時用のクエリ。

SELECT
   relname,
   reltuples as rows,
   (relpages * 8192) as bytes,
   ( (relpages * 8192) / (1024 * 1024) ) AS mbytes
FROM
   pg_class
WHERE
   relname = 'center_link';

DBの容量表示

DB全体でどのくらい容量を食っているか確認する時用のクエリ

SELECT
   relname,
   reltuples as rows,
   relpages as pages,
   (relpages * 8192) as bytes,
   ( (relpages * 8192) / (1024 * 1024) ) AS mbytes
FROM
   pg_class
WHERE
     relname !~ \'^pg_\'
 AND relkind = \'r\'
ORDER BY rows DESC;

カラム名の検索

たとえば、"email"というカラムを含むテーブルを検索することができます。

SELECT
   a.attrelid
  ,a.attname
  ,a.atttypid
  ,c.relname
  ,c.reltoastrelid
FROM
   pg_class c
  ,pg_attribute a
WHERE
     a.attrelid=c.oid
 AND attname=\'email\'
ORDER BY relname;