PostgreSQLのTips

PostgreSQLに関するTipsをメモ。

DB作成、ユーザ作成、デフォルト権限付与、スキーマへの権限付与

dbaユーザとappユーザを作って、appユーザにデフォルト権限を設定する。
詳細はこちらを参照のこと。

psql -U postgres
=> -- ユーザ作成
=> create user hoge_dba password 'hoge_dba';
=> create user hoge_app password 'hoge_app';
=> -- DB作成
=> create database hoge owner hoge_dba;
=> -- デフォルト権限付与
=> alter default privileges in schema public grant all on tables to hoge_app;
=> alter default privileges in schema public grant all on sequences to hoge_app;
=> alter default privileges in schema public grant all on functions to hoge_app;
=> -- スキーマへの権限付与
=> grant all on schema public to hoge_app; -- スキーマへの権限がないとそもそもそのスキーマないのオブジェクトが見えない
=> grant select on all tables in schema public to hoge_app; -- 既存テーブルへのアクセス権限

seq scanやindex scanの回数、フェッチ数等を確認

select * from pg_stat_user_tables

ロック中のプロセスを取得

select
  l.pid
  , db.datname
  , c.relname
  , l.locktype
  , l.mode
from
  pg_locks l
  left join pg_class c on (l.relation=c.relfilenode)
  left join pg_database db on (l.database = db.oid ORDER BY l.pid)

プロセスをkillする（super userのみ）

select pg_terminate_backend(${pid});

実行中SQLを探す

full SQLが見たい場合はsrc/include/pgstat.hの PGSTAT_ACTIVITY_SIZEを変更するらしい

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 <> '<IDLE>'
order by
  start

最後にAnalyzeした日時を確認

select
  relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, schemaname
from
  pg_stat_all_tables
where
  schemaname = 'hoge'
order by
  relname;

インデックスの容量確認

select
  *, pg_size_pretty(pg_relation_size(indexrelid))
from
  pg_stat_user_indexes
where
  idx_scan = 0
  and idx_tup_read = 0
  and idx_tup_fetch = 0
order by
  pg_size_pretty desc

レコードロックせずindex作成

create index concurrently

現在のシーケンス番号確認

select last_value from hoge_seq

シーケンス番号をリセットする

alter sequence hoge_seq restart with 次に取得したい番号

再起SQLのサンプル

2016/06/01から現在までの7日毎の日付リストを生成

with recursive tmp(day) as (
  values (to_timestamp('201606', 'YYYYMM'))
  -- ↑は select to_timestamp('201606', 'YYYYMM') と同義
  union all
  select
    day + '7days'
  from
    tmp
  where
    day + '5days' <= current_timestamp
)
select * from tmp;

文字列をsplitしてテーブルや配列に展開する

-- テーブルへ展開
select regexp_split_to_table('aaa,bbb,ccc', ',');
> "aaa"
> "bbb"
> "ccc"

-- 配列へ展開
select regexp_split_to_array('aaa,bbb,ccc', ',');
> "{aaa,bbb,ccc}"

json配列をテーブルに展開する

lateralをつけるとその関数やサブクエリが、同列の関数やサブクエリよりもあとに実行されるので、他のサブクエリの結果をつかえるようになる

with fuga_table as (
select
  h.id
  , h.name
  , cast(value->'def_id' as text) as def_id
  , cast(value->'def_name' as text) as def_name
  , cast(value->'def_type' as text) as def_type
from
  hoge h,
  lateral json_array_elements(h.fuga_json::json)
where
  h.id < 1000
)
select
  *
from
  fuga_table
where
  def_type = '"text"'

ダンプ & リストア

## dump
# connection_option -> optionの順番
# -F: フォーマット指定 p: plainなSQL, c: カスタムアーカイブ（バイナリ。サイズが小さい）
# -t: 対象テーブルを指定
# -s: DDLだけ出力
# -a: データのみ出力
# --column-inserts カラム名付きinsert文
pg_dump -h {host} -p {port} -U {user} -d {db} -t {table_name} -Fc -f hoge.dmp

## restore
# connection_option -> optionの順番
# optionの内容はpg_dumpとほぼ同じ
pg_restore -h {host} -p {port} -U {user} -d {db} -t {table_name} -Fc hoge.dmp

CSVエクスポート & CSVインポート

psqlのcopyコマンドを利用する方法を記載する

## CSVエクスポート
psql -h {host} -p {port} -U {user} -d {db}
=> \copy {table_name} to '{csv_path}' with csv delimiter ','

## CSVインポイート
psql -h {host} -p {port} -U {user} -d {db}
=> \copy {table_name} (column_names,...) from '{csv_path}' with csv

データフォルダパスの確認

ps aux | grep postgres
-> -Dの後ろがデータフォルダ

pg_hba.confの反映

## 設定ファイルを再読み込みする
pg_ctl reload -D {データフォルダパス}

## postgresql自体を再起動する（serviceコマンドで起動している場合）
service postgresql restart

スキーマ検索パス（search_path）の設定

スキーマ名.オブジェクト名で完全修飾することもできますが、スキーマ名で記載しない場合、スキーマ検索パスに指定されてるスキーマを順番に参照します。
常にスキーマ名で修飾するのは大変なので、スキーマ検索パスを設定する方法を記載します。

psql -h {host} -p {port} -U {user} -d {db}
=> -- search_pathを確認する
=> show search_path;
   search_path   
-----------------
 "$user", public
(1 row)
=> -- セッション内でsearch_pathを設定する
=> set search_path = "hoge", "$user", public;
SET
=> -- ユーザのsearch_pathを設定する
=> alter user {user} set search_path to "hoge", "$user", public;
ALTER ROLE