目次
PostgreSQLの基本的な管理を見ていきます。
基本的な管理はSQLとして psql のプロンプトで行うものを取り扱います。
initdb や createdb dropuser のようなシェルコマンドは今回扱いません。
インストール前に、日本語を扱う場合のポイント。
日本語環境が設定されて無いときは、日本語環境を作っておく。
時刻も日本JSTにしておく。
Debian / ubuntu の例
dpkg-reconfigure locales
dpkg-reconfigure tzdata
日本語環境の設定してからインストールするのが無難。
sudo apt install postgersql sudo
psql の対話型シェルの起動
postgres ユーザーが作成されるので、最初の処理は postgresql ユーザーで行う。
root@:~# sudo -u postgres psql
ユーザーの管理。
ユーザーの管理を psql コマンドで行うことができます。postgresql user と role がありますが、歴史的経緯みたいなものだと思っていればいいです。
ユーザーは login role のエイリアスだったはず。
ユーザーの追加削除と確認
ユーザーの一覧は psql で \du を使います。 du は display user とでも覚えときましょう。
実行サンプル
postgres ユーザー１つだけが存在します。
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
ユーザーの追加。
ユーザーの追加は、 SQL で CREATE USER を使えば可能です。
CREATE USER "takuya_1st" PASSWORD 'j0qViQjCzQMFfqH7';
シングルクォーテーション｀ダブルクォーテーションに注意する！！PSQLは厳密に区別してる。
ユーザ追加サンプル
ユーザーを追加して、結果を確認。一つ増えるのがわかります。
postgres=# CREATE USER "takuya_1st" PASSWORD 'j0qViQjCzQMFfqH7';
postgres=# \du
takuya_1st | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
ユーザーを削除して、結果を確認。
ユーザーの削除は、 SQLのDROP USER 文でできます。
DROP USER "takuya_1st" ;
ユーザー削除サンプル
ユーザーを削除して、ユーザー一覧から消えたことがわかります。
postgres=# DROP USER "takuya_1st" ;
postgres=# \du
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
パスワードの変更
パスワードの変更もSQLを使って可能です。
postgres=# ALTER USER postgres WITH PASSWORD 'rHBrECDNjbWax1IZ';
ALTER ROLE
初期ユーザーの postgres のパスワードを変えてみるなど。
ROLEロール。create user と create role の違い。
postgresql では ユーザのことを role と呼びます。 LOGIN できるロールのことをユーザーと呼ぶ。と覚えてください。
歴史的経緯によるとでも思っておけばいいと思います。UNIXのユーザーようにPostgreSQLのユーザーはグループをもっていて、グループにロールで管理されています。１つのデータベースのインスタンスを多数のクライアントで共有していた名残りでしょう。個人でデータベースを作る、個人でプログラムから使うときなど、はあまり気にしなくていいと思います。
ロールの作成と確認
これは、create user と同じ
CREATE ROLE "outline_Admin" PASSWORD '01UNeD86gwVmRDRG';
データベースを作成する
日本語UTF-8に対応したテンプレートを作る。
シェル環境変数が LANG=ja_JP.UTF-8なら自動で作られるのだけど。作られていないとUTF-8でも行けそうなん。
気持ち悪いなら、日本語対応したテンプレートを作っておくといいかもしれない。
CREATE DATABASE template1 LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' ENCODING 'UTF8' TEMPLATE template0;
CREATE DATABASE template2 LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' ENCODING 'UTF8' TEMPLATE template0;
UPDATE pg_database SET datistemplate = true where datname = 'template2';
データベースの一覧
対話プロンプト psql のコマンドで \l でデータベースの一覧を見れます。 List database の L(エル)と覚えておけばいいでしょう。
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
ここで出てくる template は言語ごとのCollationが設定されたテンプレートです。debian/ubuntu の場合 LANG=ja_JP.UTF-8のbashから apt install postgres すると postgres のaptインストールスクリプトで自動生成されます。
データベースの切替え
データベースを切り替えるにはつぎのように psql コマンド \c で可能です。
\c databae_name
これは、mysql の use DATABASENAME に相当する。
実行サンプル
切り替えたときのサンプル。プロンプトが変わるのでわかる。
postgres=# \c outline
You are now connected to database "outline" as user "postgres".
outline=# \q
データベースを作成。
SQLでデータベースを作成することができます。
CREATE DATABASE my_database;
データベースの作成サンプル
outline という名前でデータベースを作って一覧を見てみます。
postgres=# create database outline;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
outline | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
データベースを削除する
SQL 構文でデータベースを削除することができます。
DROP DATABASE my_database;
実行サンプル
outline という名前のデータベースを削除し、一覧を見てみます。
postgres=# drop database outline;
DROP DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
オーナー指定してデータベースを作る
SQLでデータベースを作るときに所有者（オーナー）を指定して作ることができます。
CREATE DATABASE takuya_database OWNER "takuya";
これは非常によく使います。
実行サンプル
ユーザーを作成し、続けてそのユーザーのデータベースを作成します。
postgres=# CREATE USER "outline_Admin" PASSWORD 'kj4zzkvVXuU7MlSL';
CREATE ROLE
postgres=# create database outline owner "outline_Admin";
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+------------------------+----------+-------------+-------------+-----------------------
outline | outline_Admin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
データベース作成時：文字コードを指定
文字コードを指定してつくると便利です。
テンプレートを使って作る事が多いです。
CREATE DATABASE outline LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' ENCODING 'UTF8'
CREATE DATABASE outline LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' ENCODING 'UTF8' TEMPLATE template0;
template が en_US だし困ったなぁってときに、日本語でDBを作成しておくとトラブルが減る*1
実行サンプル
オーナー指定とテンプレート指定、文字コード指定を「全部合わせ」てデータベースを作成している例です。
CREATE DATABASE outline owner "outline_Admin" LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' ENCODING 'UTF8' TEMPLATE template0;
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+------------------------+----------+-------------+-------------+-----------------------
outline | outline_Admin | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
repost | repost | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
おさらい psql コマンド
データベースの切り替え
postgres=# \c outline
### データベースの一覧
postgres=# \l
### ユーザー一覧
postgres=# \du
おさらい SQL での管理。
# ユーザー作成
CREATE USER "takuya_1st" PASSWORD 'j0qViQjCzQMFfqH7';
## パスワード変更
ALTER USER postgres WITH PASSWORD 'rHBrECDNjbWax1IZ';
# データベース作成
CREATE DATABASE takuya_database ;
CREATE DATABASE takuya_database OWNER "takuya";
CREATE DATABASE outline owner "takuya" LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' ENCODING 'UTF8'
2020-07-19
更新
