PostgreSQLについてのメモ

(2019-10-22更新)

目次:

参考ページ: PostgreSQL 11文書

GUIで使う

pgAdminまたはphpPgAdminを使う。

外部から接続できるようにする

/etc/postgresql/{バージョン}/main/pg_hba.conf に以下を追加する (アドレス部分は適宜書き換える)。

host    all             all             192.168.0.0/24          md5

/etc/postgresql/{バージョン}/main/postgresql.conf に以下の行を追加する。

listen_addresses = '*'

その後、PostgreSQLを再起動またはリロードする。

参考: PostgreSQLのリモート接続設定 - Qiita

データベースを作成する

createdbを使用する。 -Tオプションを使うとデータベースをコピーして作成できる。

createdb mydb
createdb -T original_db new_db

データベースに接続する

psql mydb
psql -h ホスト名 -p ポート番号 -U ロール名 -d データベース名

テーブルを表示する

mydb=# \dt

自動インクリメントフィールドを作成する

データ型をserialにする。

CREATE TABLE mytable (
  id serial PRIMARY KEY,
  ...
);

PRIMARY KEY を付け忘れて後から主キーにするには ALTER TABLE mytable ADD PRIMARY KEY(id); のようにする。

権限

ユーザーと権限の一覧を表示する

mydb=# \dp

ユーザーに権限を与える

データーベースへのアクセス権

GRANT CONNECT ON DATABASE mydb TO user1;

テーブルへのアクセス権

GRANT 権限 ON mytable TO user1;

代表的な権限:

• SELECT: 選択
• INSERT: 挿入
• UPDATE: 更新
• DELETE: 削除

シーケンスへのアクセス権

serial型があるテーブルにレコードを挿入するにはシーケンスへの選択、更新権限が必要です。

• GRANT SELECT ON mytable_id_seq TO user1;
• GRANT UPDATE ON mytable_id_seq TO user1;

名前を変更する

• テーブル名を変更する: ALTER TABLE oldname RENAME TO newname;
• シーケンス名を変更する: ALTER SEQUENCE oldname RENAME TO newname;

主キー用のCONSTRAINTの名前を変更するにはいちどCONSTRAINTを削除してから再度追加する。

ALTER TABLE mytable DROP CONSTRAINT mytable_pkey;
ALTER TABLE mytable ADD PRIMARY KEY (id);

バックアップとリストア

pg_dump mydb > file
psql mydb < file

• pg_dump はSQLスクリプト形式でデータベースの内容を出力する
• pg_dump で出力したデーターは psql にそのまま読み込ませることができる
• pg_dump は--table=テーブル名 オプションを付けることで特定のテーブルに関する情報のみ出力する
• ロールなどの全データーを出力する pg_dumpall がある

pg_dumpをpostgresユーザーで実行し、結果をリダイレクトしたい場合は、sudo suを使用する。

sudo su postgres -c "pg_dump mydb" > file

参照: PostgresSQLのbackup, restore方法まとめ - Qiita

シーケンスを確認、取得、設定する

• 確認する: SELECT * FROM mytable_id_seq;
• 取得する(1繰り上がる): SELECT nextval('mytable_id_seq');
• 設定する: SELECT setval('mytable_id_seq', 5); (シーケンスの現在値を5にする)

参照: 新人にPostgreSQLのシーケンスを理解してもらう - Qiita

データーをCSVファイルとの間で転送する

COPY テーブル名 FROM ファイル名
COPY テーブル名 TO ファイル名
\COPY テーブル名 FROM ファイル名
\COPY テーブル名 TO ファイル名

COPYはサーバーにファイルがある場合、\COPYはクライアントにファイルがある場合。

様々な例。

\COPY colors FROM 'colors.csv' CSV HEADER
\COPY colors FROM 'colors.tsv' CSV DELIMITER E'\t' HEADER
\COPY colors FROM 'colors2.csv' CSV HEADER QUOTE '"'
\COPY colors TO 'foobar.csv' CSV FORCE QUOTE *
\COPY colors TO 'foobar.csv' CSV HEADER FORCE QUOTE id,name

参考: PostgreSQL の COPY コマンドを使いこなす|Everything you do is practice

データベースロール

サーバーのオペレーティングシステムのユーザーとは別にデータベースにユーザーが存在する。 これをデータベースロールという。 データベースロールによって接続や権限を制御する。

登録されているデータベースロールを表示する

以下のようにpg_rolesシステムカタログを確認するか、psqlの\duメタコマンドを実行する。

SELECT rolname from pg_roles;

データベースロールを作成する、削除する

CREATE ROLE name;
DROP ROLE name;

注: ロールの削除前にロールが所有するオブジェクトを削除するか、所有権を別のオブジェクトに移管しなければならない。 実際にはDROP ROLEをした時点でメッセージが表示される。

ログイン権限を持つユーザーを作成するには次のようにする。

CREATE ROLE name LOGIN;
CREATE USER name;

またはコマンドラインから以下のように実行する。

createuser name
dropuser name

データベースロールのパスワードを変更する

ALTER ROLE WITH PASSWORDを使用するかpsqlの\passwordメタコマンドを実行する。

ALTER ROLE name WITH PASSWORD `password`;

\password name

psql

ローカルのpsqlからデータベースロールを指定して接続できるようにする

/etc/postgresql/{バージョン}/main/pg_hba.conf の以下の最後がpeerになっていたらmd5に変更し、PostgreSQLを再起動またはリロードする。

local  all             all                                                md5

参考: PostgreSQL “対向(peer)認証に失敗しました” エラーが出るときの対処法 – 株式会社シーポイントラボ ｜ 浜松のシステム開発会社

psqlからパスワードなしで接続できるようにする

~/.pgpassを作成し、次の内容を書き込む。

ホスト名:ポート番号:データベース名:ユーザ名:パスワード

ローカルで動いているPostgreSQLサーバーのすべてのデータベースにデータベースロールuser1としてパスワードなしで接続するには次のようにする。

localhost:5432:*:user1:password

~/.pgpassの作成後はパーミッションを600にする。

chmod 600 ~/.pgpass

psqlのページャ設定

psql の起動時に環境変数 PAGER に指定されているページャが使われる。

• ページャのオン・オフ
  • \pset pager on - ページャのオン
  • \pset pager off - ページャのオフ
  • \pset pager - ページャのオン・オフをトグル
• ページャを変更する
  • \setenv PAGER less - ページャを less にする