逆引きPostgresql

postgres_memo.md

逆引きPostgresql

導入(OSX)

インストール

$ brew install postgresql

立ち上げ

$ brew services start postgresql

再起動

$ brew services restart postgresql

pg_hba.confの見つけ方

$ ps aux | grep postgres
/usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres

これの-Dの中に入ってる

CLI

ローカル接続

$ psql $db_name $user_name

リモート接続

$ psql -h $host -p $port -U $user_name $db_name 

今接続しているサーバーのポートを調べる

> SELECT * FROM pg_settings WHERE name = 'port';

終了

> \q

ロール

今ログインしているユーザー

> SELECT current_user;

ロール一覧

> \du
> SELECT * from pg_user;

ロール作成

> CREATE ROLE username WITH LOGIN PASSWORD 'quoted password' [OPTIONS]
今のロールがCREATEROLE権限を持っているかsuper userじゃ無いとこれは出来ない。

super userにする

> ALTER USER user_name with SUPERUSER;

これを実行できるのはsuper userだけ super userではなくするにはwith NOSUPERUSER

ロールパスワード変更

> \password user_name

ただし、この設定よりもpg_hba.confの設定の方が強い。 methodがtrustになってる接続経路ではパスワード設定は意味が無くなる また、localhostはUNIXドメインソケット、127.0.0.1はIPv4で繋がる模様。passwordにすれば平文が使われる。

scram-sha-256パスワードを使う

> SET password_encryption = 'scram-sha-256';
> SELECT * from pg_authid;

これでrolpasswordの種別を確認。適宜\password user_nameでアップデート。 pg_hba.confでmethodをscram-sha-256にすればその接続経路はこれでエンコードされる

ロール変更

>  ALTER ROLE user_name CREATEDB;

複数貼り付ける場合はスペース区切り。剥がす時は項目名の前にNOを付ける https://www.postgresql.org/docs/9.5/static/sql-alterrole.html

プロセス

プロセス状況のログを見る

> SELECT * FROM pg_stat_activity;

接続プロセスを殺す

> SELECT pg_cancel_backend(process_id);

DB

今接続しているDB

> SELECT current_database();

DBを作成

> CREATE DATABASE database_name;

DB一覧を表示

> \l

DBに接続

> \connect database_name

DBの名前を変更

>  ALTER DATABASE database_name RENAME TO changed_database_name;

これはオーナー、またはsuperuserじゃないと出来ない。

DBの権限を作成

> GRANT ALL PRIVILEGES ON DATABASE database_name TO user_name;

スキーマ

スキーマは簡単に言えば権限管理できるディレクトリのようなものに相当する。ただし、入れ子には出来ない。スキーマに属さない全てのテーブルは暗黙のpublicスキーマに属する。というより、デフォルトのsearch_pathがpublicになっている。publicスキーマは最初にあるというだけの第一市民なので、set search_pathで無視出来るし削除すら出来る。pg_で始まるスキーマは予約されている。

スキーマのsearch_pathを表示する

> SHOW search_path;

デフォルトは$user, public

現在のsearch_pathを変更する

> SET search_path TO public, my_schema;

カンマ区切りで与えて、上書き

特定のユーザーにスキーマ内にテーブルを作らせないようにする

> REVOKE CREATE ON SCHEMA schema_name FROM user_name;

逆はGRANT TO。PUBLICをuser_nameの代わりに使うと全員から奪える

スキーマの一覧

> \dn
> SELECT * FROM pg_catalog.pg_namespace;

スキーマの作成

> CREATE SCHEMA schema_name;

スキーマの削除

> DROP SCHEMA schema_name;

中身がある時は

> DROP SCHEMA schema_name CASCADE;

テーブル

テーブルの存在を調べる

> SELECT EXISTS ( SELECT 1 FROM information_schema.tables WHERE table_schema = 'schema_name' AND table_name='table_name');

現在のDBのテーブル一覧

> \dt

スキーマをワイルドカードで指定して絞り込む

> \dt schema_name.*

スキーマ単位で調べる別の方法

> SELECT * FROM information_schema.tables WHERE table_schema = s'chema_name'

テーブル定義を参照する

> \d table_name
$ pg_dump database_name -Uuser_name -s -t table_name

タイムゾーン

タイムゾーンを変更する

> SET TIMEZONE TO 'Japan';

node-postgress

prepared statementを使う

queryオブジェクトのnameフィールドを入れればprepared statementとして記憶してくれる

await client.query({ text: 'SELECT NOW()', name: foobar })

parameterized query

queryオブジェクトにvaluesを入れる

await client.query({ text: 'SELECT $1::text',  values: ['fobar']})

objectではなくarrayでrowsを受け取る

ProwMode: 'array'をqueryオブジェクトに追加