PostgreSQLはMySQLとならぶオープンソースのRDBで、化学系では特にRDKitとの相性が良く使われているようです。最低限の知識を備忘録としてまとめました。
Installation
公式HPに沿ってインストール
インストールが完了すると、OSにpostgresユーザーが作成される。また、データベースにもpostgresユーザーが作成される。OSのpostgresユーザーはpasswdコマンドでパスワードを変更しておく。はじめ、OSのpostgresユーザーからパスワードレスでデータベースに接続できる。
$ psql
psql (14.11)
Type "help" for help.
postgres=#
ここで、以下のSQL文によりpostgresユーザーのパスワードを変更しておく。
postgres=# alter role postgres with password 'postgres';
上の例だと、パスワードは「postgres」に設定される。
他のOSユーザーからデータベースユーザーpostgresでログインすることを許可するには、/var/lib/pgsql/14/data/pg_hba.confを開いて以下の「peer」を「password」に書き換える。
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer --> passwordに書き換え
その後、再起動する。
データベースへの接続
psql -h ホスト名 -p ポート番号 -U ロール名 -d データベース名 -W
-Wはパスワードを使ってログインする際のフラグ
ホスト名は省略するとローカルホスト、ポート番号は省略するとデフォルト（5432）、ロール名は省略するとOSのユーザ名、データベース名は省略するとロール名と同名のデータベース、となる。
データベース
CREATE DATABASE データベース名;
データベース一覧を表示するには、メタコマンドで\l
postgres-# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
もしくはselect * from pg_database;
スキーマ
PostgresQLではデータベースの中にスキーマがあり、その中にテーブル等が作られる。デフォルトでpublicスキーマが作成される。スキーマを作成するには、
CREATE SCHEMA スキーマ名;
スキーマ一覧を表示するには、メタコマンド\dnが便利。
postgres-# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
test | postgres
(2 rows)
テーブル
スキーマ名を指定してテーブルを作成するには、
CREATE TABLE スキーマ名.テーブル名 (
id INTEGER,
name VARCHAR(10)
);
スキーマを指定しないとデフォルトでpublicスキーマに作成される。
テーブル一覧を表示するには、メタコマンドで\dt スキーマ名.*を使う。
postgres-# \dt test.*
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
test | test_table | table | postgres
(1 row)
ロール
ロール作成：
CREATE ROLE ロール名 WITH LOGIN PASSWORD 'password';
なお、PostgreSQLにおいてはロールはグループとしてもユーザーとしても使われる。
ロール一覧はメタコマンド\du
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
web_anon | Cannot login | {}
もしくはselect * from pg_role;
ロールへの権限付与：
GRANT 権限 ON 対象 TO ロール;
例：データベースの全権限を付与
GRANT ALL PRIVILEGES ON DATABASE データベース名 TO ロール名;
参考：