PostgreSQL

フリーのRDBMS

インストール

brew install postgresql

ユーザとロール

ロールは権限の主体。ユーザはログイン可能なロールの一種という位置付け。CREATE USERはCREATE ROLEの別名で、LOGIN属性付でCREATE ROLEを行う。

ユーザとパスワードによるログイン

初期状態では、UNIXのユーザと同じ名前のデータベースユーザが、データベースユーザと同じ名前のデータベースにアクセスすることしかできないように設定されている。MySQL等と同様に任意のデータベースユーザ名で Postgres にログインを可能にするには pg_hba.conf を書き直す。

以下の設定でローカルホストから任意のユーザ名で任意のデータベースにアクセスできる。

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         all                               md5 
host    all         all         127.0.0.1/32          md5

pg_hba.conf は上から順に評価し最初に適用された行のメソッドが用いられる点に注意。複数の条件に当てはまる場合はそのなかの一番上のMETHODが使われる。pg_hbaを設定したのにまだIDENT authentication failed for user …といわれる場合は順番にも注意して見直す。

ネットワーク越しでアクセス

デフォルトではlocalhostからのアクセスしか受け付けない。

hogeデータベースをfugaというユーザによる192.168.1.0/24からパスワード認証でアクセス許可するよう設定してみる

pg_hba.conf（CentOSでyumインストールの場合 /var/lib/pgsql/data/pg_hba.conf にある）を修正

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         all                               md5 
host    hoge        fuga        192.168.1.0/24        md5

postgresql.conf（CentOSでyumインストールの場合 /var/lib/pgsql/data/postgresql.conf にある）を修正

listen_addresses = ‘*’

* はすべてのホストからのTCP接続を受け付ける設定。pg_hba.confの設定をしていれば無制限にアクセスされる事は無いが、こちらで制限するとTCPレベルで遮断できるのでより安全である。

スキーマ

データベース内にスキーマを作成できる。スキーマは名前空間のようなものでスキーマが異なれば同じ名前のテーブルを作成できる。テーブルを特定するには、スキーマ指定とテーブル名の指定が必要。スキーマを省略すると public というスキーマが選択されたことになる。

テーブル空間

テーブル毎に、テーブルを作成する物理的な場所を指定できる。よく使われるテーブルはHDDではなくSSDに作成するといったことができる。

データ使用量を調べる

deleteした領域は解放されず残っている。DBのレコード数のわりにデータ使用量が多い時は vaccum すべし

SELECT  pg_size_pretty(pg_database_size('database_name'));

vacuum analyze schema_name.table_name;
 
vacuum FULL schema_name.table_name;

プロセス

実行中のプロセス一覧

SELECT * FROM pg_stat_activity;

実行中のプロセスをkillする

実行中のジョブを検索してpidを調べて、以下を実行

SELECT pg_cancel_backend(<pid>);

lastval

mysqlのlast_insert_idに相当

SELECT lastval();

truncate

truncateがトランザクション内で実行される。

test=# BEGIN;
BEGIN
test=# SELECT COUNT(1) FROM xxx.items;
     100
test=# TRUNCATE test.hoge;
test=# SELECT COUNT(1) FROM xxx.items;
     0
test=# ROLLBACK;
ROLLBACK
test=# SELECT COUNT(1) FROM xxx.items;
     100

`delete from xxx;` の代わりに有り難く利用しよう。deleteの場合、データ領域を解放しないが、truncateは解放するので削除後にvacuumする必要がなくなる。

copy

データをcsvに出力したり、csvから読み込んだりできる。メタコマンド版と通常コマンド版がある

メタコマンド版: クライアント側のファイルシステムで入出力される
コマンド版: サーバ側のファイルシステムに入出力される

dockerで動かしている場合などは、ホスト側でpsqlを立ち上げて、メタコマンドで実行するとホスト側とやりとりできる

Cat Paw Software

目次