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 行)

バージョンの確認

psqlコマンドを確認する

# psql --version

version関数で確認する方法

db01=# SELECT version();

CentOS7で9.6をインストール

標準では9.2

repository登録

# yum -y localinstall https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

インストール

# yum install postgresql96-server

目次