ユーザ用ツール

サイト用ツール


サイドバー

Index

はじめてのおつかい




DokuWiki整形記法


PlayGround



serverapps:postgresql:pgtips

tips

SQLの実行時間を計測する

psqlでtimingコマンドを実行すると、SQLの実行時間を計測できる

DB=> \timing
Timing is on.

コマンドを実行した後、SQLを発行すると実行時間が出力される

Postgresqlでテーブルの存在確認

SELECT relname FROM pg_class WHERE relkind = 'r' AND relname = 'テーブル名';

pg_classには、テーブルや その他の列を保持しているすべての情報が格納されているので、「relkind = ‘r’ 」でテーブルに限定する。

relkindの意味

  • r:通常のテーブル
  • i:インデックス
  • S:シーケンス
  • v:ビュー
  • c:複合型
  • s:特別
  • t:TOAST テーブル

全テーブル行数の取得

select relname, n_live_tup from pg_stat_user_tables where schemaname='public';

PostgreSQLをJail内で動かす

ホスト(親)側で以下の設定

rc.conf.local
jail_sysvipc_allow="YES"

すぐに動かすなら

# sysctl security.jail.sysvipc_allowed=1
jail.conf
allow.sysvipc=1;

ReadonlyのRole(ユーザ)の作成

構文

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] TABLE_NAME [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

実例

GRANT SELECT ON ALL TABLES IN SCHEMA public TO RoleName;

Readonlyモード

インスタンス、DB、ユーザ単位で参照モードに設定することが可能

インスタンス

postgresql.conf

default_transaction_read_only=on
service postgresql restart

DB

alter database DB名 set default_transaction_read_only = on;

ユーザ

alter role role名 set default_transaction_read_only = on;

Roleの権限を表示

 SELECT rolname, relname, crud
FROM (SELECT r.oid, r.rolname, c.relname,
       ((CASE WHEN has_table_privilege(r.oid, c.oid, 'INSERT') THEN 'C' ELSE ' ' END) ||
        (CASE WHEN has_table_privilege(r.oid, c.oid, 'SELECT') THEN 'R' ELSE ' ' END) ||
        (CASE WHEN has_table_privilege(r.oid, c.oid, 'UPDATE') THEN 'U' ELSE ' ' END) ||
        (CASE WHEN has_table_privilege(r.oid, c.oid, 'DELETE') THEN 'D' ELSE ' ' END)) AS crud
  FROM pg_roles r, pg_class c
  WHERE c.relkind = 'r'
        AND r.rolcanlogin = TRUE
        AND relnamespace IN
  (SELECT oid
        FROM pg_namespace
        WHERE nspname NOT IN ('pg_catalog','information_schema','pg_toast'))
) t
ORDER BY rolname, relname
;

こんな感じで表示される

    rolname     |   relname  | crud 
----------------+-----------------+------
 postgres       | table_name | CRUD
 admin          | table_name | CRUD
 readonly       | table_name |  R  

ログの言語を英語にする

postgresql.confのlc_messages を変更すれば治る

lc_messages = 'ja_JP.UTF-8'
 ↓
lc_messages = 'C'

CentOSなどでインストールした場合、OSのロケールが日本語のためPostgreSQLのログ出力も日本語でインストールされている

確認方法

psqlで

変更前
postgres=# show lc_messages;
 lc_messages
-------------
 ja_JP.UTF-8
(1 行)
変更後
postgres=# show lc_messages;
 lc_messages
-------------
 C
(1 行)
serverapps/postgresql/pgtips.txt · 最終更新: 2019/11/11 14:44 by hayashi