備忘録的プログラミングリファレンス

データベース・クラスタ（データベース・インスタンス）

　データベース・クラスタとは PostgreSQL 独自の用語で、他ではデータベース・インスタンスと呼ばれたりします。
　データベース管理システムでは CREATE DATABASE によって複数個のデータベースが作成ができるのですが、その複数のデータベースを管理するための機能がデータベース・クラスタです。

　データベース・クラスタは、複数のデータベースを管理することの他にも、データベースへの送受信、ロール（ユーザー）の管理、アクセスログの管理などを行います。

図.データベース・クラスタとデータベース

　データベースクラスタは、CREATE DATABASEクエリで作成される個々のデータベースのことではありません。systemctlやpg_ctlといったコマンドを用いて、設定に基づいてクラスタごとにサーバーとして起動され、データベースとのデータの送受信を仲介する役割を担います。

　データベースクラスタの設定内容は、Ubuntuのパッケージインストールによるデフォルト設定の場合、/etc/postgresql/10/main/などに格納されています。データベース・クラスタの設定内容の保存場所については、以下の現在の状況の確認を参照ください。
（これらの設定ファイルの場所は、オペレーティングシステムやPostgreSQLのインストール方法によって異なる場合があります。現在のデータベースクラスタの保存場所を確認する方法については、関連するドキュメントやPostgreSQLの情報を参照してください。）

　また、1つのオペレーティングシステム（以下、システムと呼びます）上で複数のデータベースクラスタ（インスタンス）を同時に稼働させることが可能です。

図.複数のデータベース・クラスタ

　クラスタ（インスタンス）ごとにロールの管理を分離できるため、1つのシステム上で複数のグループが互いに影響を受けることなく、それぞれのデータベース管理体系を構築できます。

　さらに、複数のクラスタ間で共通のロールを定義し、そのロールにアクセス権限を付与することで、横断的なアクセス制御も実現できます。

　以下ではデータベース・クラスタの追加方法について順次解説していきます。

ページ内 Index

データベース・クラスタを追加作成する前に

　データベース・クラスタを追加作成する前に以下の項目を確認しておきましょう。

  • OS と PostgreSQL のバージョン
  • 現在のスーパーユーザー名
  • 追加するデータベース・クラスタの名称とスーパーユーザー名を決めておく
  • 追加するデータベース・クラスタのためのスーパーユーザーを作成しておく

　以下のような表に追加するデータベース・クラスタの設定内容を記録しておくとよいかもしれません。

データベース・クラスタの設定内容
OS のバージョン
PostgreSQL のバーション
現在のスーパーユーザー
追加データベースクラスタクラスタ名
クラスタのポート番号
スーパーユーザー名
スーパーユーザーのパスワード
備考

OS と PostgreSQL のバージョン

　先に、OS のバージョンと PostgreSQL のバージョンを確認します。
　このページでの使用している PostgreSQL は Ubuntu 22 のディストリビューションのパッケージからインストールしたものです。OS とインストール方法によって PostgreSQL まわりの環境が変わりますので読み替えてください。Ubuntu でのパッケージからのインストールについてはPostgreSQL のインストールを参照してください。

OS のバージョン

　OS のバージョンは以下のように確認します。

OS のバージョン確認
$ cat /etc/os-release
..
NAME="Ubuntu"
VERSION_ID="22.04"
..

　ディストリビューションは Ubuntu でバージョンは 22.04 であることが分かります。

PostgreSQL のバージョン

　PostgreSQL のバージョンは以下のように確認します。

PostgreSQL のバージョン
$ psql -V
psql (PostgreSQL) 10.16 (Ubuntu 10.16-0ubuntu0.18.04.1)

　バージョンは 10 であることが分かります。このバージョン名は以下のデータベース・クラスタの保存場所に関係しています。

　psql --versionでも PostgreSQL のバージョンを確認することができます。

現在のスーパーユーザー名

　現在のスーパーユーザー名を psql にログインをし \du コマンドで確認しておきます。

スーパーユーザーの確認
$ sudo -u postgres psql
# \du
..
postgres      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
..

　上記の例では PostgreSQL をインストールしたままの状態で postgres スーパーユーザーでログインをしています。
　スーパーユーザーは、属性にSuperuserがあるものです。

　もしもスーパーユーザーが postgres でない場合は設定が変えてあるので管理者に聞いたほうがよいでしょう。

データベース・クラスタの名称とスーパーユーザー名の決定

　データベース・クラスタを追加する前に、そのクラスタの名称とスーパーユーザー名を決めておく必要があります。

　ここでは、クラスタの名称はcluster001、そのスーパーユーザー名はpostgres001とします。

データベース・クラスタcluster001
スーパーユーザー名postgres001

クラスタのためのユーザーの追加

　データベース・クラスターを作成する前にクラスターのためのスーパーユーザーを作成します。このスーパーユーザーはシステム（ OS ）におけるユーザーとして追加します。

　スーパーユーザーは PostgreSQL におけるすべての操作を行うことができるユーザーのことです。

　以下の例では、postgres001 というユーザーを追加し、既存のグループ postgres に追加します。ユーザーを追加する方法には、useradd と adduser コマンドがあります。開発用では adduser の方が追加勝手がよいようです。用途によって選択しましょう。

useradd によるユーザー追加

　useradd コマンドではホームディレクトリやシェルの使用がなく、ログイン画面に表示されません。外部からのログインを回避できますが、開発時ではシェルがないと使い勝手が悪いかもしれません。

　useradd コマンドによるユーザーを追加するには、以下のようにします。ここでは、シェル -s とグループ -g をオプションで指定しています。

ユーザーの追加
$ sudo useradd -g postgres -s /bin/bash postgres001

adduser によるユーザー追加

　もしもユーザーのホームディレクトリが必要な場合は adduser コマンドを使用します。

adduser によるユーザーの追加
$ sudo adduser postgres001

　新規に追加したユーザーを既存のユーザー postgres のグループに追加しておきましょう。

ユーザーを既存のグループに追加
$ sudo usermod -g postgres postgres001

追加ユーザーのグループ確認

　postgres グループに追加されたかを確認してみましょう。

ユーザーのグループ確認
$ sudo groups postgres001
postgres001 : postgres

　追加したユーザーの設定を確認するには以下のようにします。

ユーザーの設定確認
$ cat /etc/passwd | grep postgres001
postgres001:x:1007:127:,,,:/home/postgres001:/bin/bash

　行の内容は、左からユーザー名、パスワード（xで表示される）、ユーザーID（UID）、グループID、コメント、ホームディレクトリ、シェルの場所です。

　postgres はデフォルトインストールでの既存のスーパーユーザーです。postgres、postgres001 は環境によって読み替えてください。

データベース・クラスタの追加方法

　データベース・クラスターの作成方法を解説する前に、現在のデータベース・クラスターの保存場所を確認してみます。

データベース・クラスタの設定の保存場所

　データベース・クラスターの設定の保存場所を確認するには、psql の SHOW data_directory; コマンドを実行します。

　例えば、スーパーユーザー postgres が管理するデータベース・クラスタの保存場所を確認するには、以下のように postgres ユーザーで psql にログインし、SHOW data_directory; コマンドを実行します。

$ sudo -u postgres psql
..
postgres=# SHOW data_directory;
data_directory
-----------------------------
/var/lib/postgresql/10/main
..

　このデータベース・クラスターのデータ保存場所は、Ubuntu ver 22.04 のパッケージインストールによる PostgreSQL ver 10 におけるものです。/var/lib/postgresql/10/mainの 10 は PostgreSQL のバージョン名です。main は Ubuntu のパッケージインストールにおけるデフォルトのデータベース・クラスタの名称です。スーパーユーザーはデフォルトの postgres です。

　保存場所はディストリビューションごとに違いがあります。さらに、PostgreSQL のインストールをパッケージからかソースからかによっても変わります。環境によって読み替えてください。
　ユーザー名も postgres または postgresql である場合があります。ユーザー名はインストール時の環境設定やデータベース・クラスタの所有ユーザーによって変わります。

pg_createcluster について

　Ubuntu におけるデータベース・クラスターの追加作成は独自コマンドの pg_createcluster によって行います。まず pg_createcluster コマンドについて確認します。
　initdb と pg_ctl コマンドは Ubuntu のディストリビューションからインストールした PostgreSQL では正常に機能しません。Ubuntu における PostgreSQL の独自コマンドについては資料編：Ubuntu特有のPostgreSQL用コマンドを参照してください。

pg_createcluster 構文
pg_createcluster [options] postgreSQL_version cluster_name

　pg_createcluster には、先に確認した PostgreSQL のバージョン名postgreSQL_versionと任意に決定したクラスター名cluster_nameを指定します。

　コマンドが実行されると、/etc/postgreql/postgreSQL_version/cluster_name/というディレクトリの作成とそのディレクトリ以下に追加したクラスターの共有カタログテーブルの生成と、データベースの作成が実行されます。
　このコマンドでは 新たなポート番号を自動で設定してくれます。任意にポート番号を指定するにはオプション-p port, --port=portを使用します。
　設定されたポート番号は後で確認したほうがよいでしょう。pg_lsclusters コマンドで設定されたクラスタを確認することができます。

　pg_createcluster コマンドを実行する前にデータベース・クラスターごとのスーパーユーザーの追加とそのグループの設定はしておいたほうがよいことが解説されています。
　先にデータベース・クラスターごとのスーパーユーザーの追加とそのグループ設定しておくようにしましょう。

データベース・クラスタの設定データの作成例

　クラスタ用のユーザーを追加したら、pg_createcluster コマンドでデータベース・クラスタのための設定データを自動で作成します。

　以下の例では、上記で作成したスーパーユーザー postgres001、そのユーザーが所属するグループ postgres、確認した PostgreSQL のバージョンと新たなデータベース・クラスタ名を指定しています。

データベース・クラスタの設定データの作成
$ sudo pg_createcluster -u postgres001 -g postgres 10 cluster001
..
Creating new PostgreSQL cluster 10/cluster001 ...
/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/cluster001 --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres001".
This user must also own the server process.

The database cluster will be initialized with locale "ja_JP.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "ja_JP.UTF-8"
The default text search configuration will be set to "simple".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/10/cluster001 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Asia/Tokyo
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/cluster001 -l logfile start

Ver Cluster    Port Status Owner       Data directory                    Log file
10  cluster001 5433 down   postgres001 /var/lib/postgresql/10/cluster001 /var/log/postgresql/postgresql-10-cluster001.log

　作成したデータベースクラスタが pg_ctl で起動できるような表示されますが、Ubuntu のパッケージインストールでは正常に機能しません。systemctl コマンドを使用します。詳しくは以下を参照してください。

　追加したデータベース・クラスタの設定ファイルは/var/lib/postgresql/10/cluster001/以下にあります。
　そのディレクトリを覗くと、ユーザーが postgres001、グループが postgres になっています。初期状態ではユーザーのみが閲覧と編集できます。

作成されたデータベース・クラスタの設定ファイル
sudo ls -l /var/lib/postgresql/10/cluster001/
..
-rw------- 1 postgres001 postgres    3  4月 11 10:07 PG_VERSION
drwx------ 5 postgres001 postgres 4096  4月 11 10:07 base
drwx------ 2 postgres001 postgres 4096  4月 11 10:17 global
..

　作成したクラスタの現況を pg_lsclusters コマンドで確認します。

データベース・クラスタの設定の確認
$ pg_lsclusters
Ver Cluster    Port Status Owner       Data directory                    Log file
..
10  cluster001 5433 down postgres001 /var/lib/postgresql/10/cluster001 /var/log/postgresql/postgresql-10-cluster001.log
..

　クラスタ cluster001 の設定データが作成できていることが確認できます。ポート番号が 5433 でユーザーが postgres001、設定ファイルが/var/lib/postgresql/10/cluster001となっています。
　Status が online であれば起動中、down であれば停止中です。

　上記の（例）データベース・クラスタの作成で pg_ctl でクラスタを起動できるように表示されますが、Ubuntu のパッケージインストールでの PostgreSQL では以下のように想定通りには起動しないようです。

pg_ctl での起動によるエラー
$ sudo -u postgres001 /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/cluster001 -l logfile start
waiting for server to start..../bin/sh: 1: cannot create logfile: Permission denied
 stopped waiting
pg_ctl: could not start server
Examine the log output.

　systemctlコマンドで起動します。

データベース・クラスタの起動

　作成したクラスタは、以下のようにsystemctlコマンドで起動します。

データベース・クラスタの起動
$ sudo systemctl start postgresql@10-cluster001.service

　起動しているかを確認してみます。

データベース・クラスタの稼働確認
$ sudo systemctl status postgresql@10-cluster001.service
● postgresql@10-cluster001.service - PostgreSQL Cluster 10-cluster001
     Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime; >
     Active: active (running) since Sun 2024-04-07 09:05:00 JST; 1min 25s ago
    Process: 7504 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 10>
   Main PID: 7509 (postgres)
      Tasks: 7 (limit: 9344)
     Memory: 17.0M
        CPU: 191ms
     CGroup: /system.slice/system-postgresql.slice/postgresql@10-cluster001.ser>
             ├─7509 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/>
             ├─7511 "postgres: 10/cluster001: checkpointer process   " "" "" "">
             ├─7512 "postgres: 10/cluster001: writer process   " "" "" "" "" "">
             ├─7513 "postgres: 10/cluster001: wal writer process   " "" "" "" ">
             ├─7514 "postgres: 10/cluster001: autovacuum launcher process   " ">
             ├─7515 "postgres: 10/cluster001: stats collector process   " "" "">
             └─7516 "postgres: 10/cluster001: bgworker: logical replication lau>

 4月 07 09:04:58 user-PC systemd[1]: Starting PostgreSQL Cluster 10-cluster001...
 4月 07 09:05:00 user-PC systemd[1]: Started PostgreSQL Cluster 10-cluster001.
...skipping...
● postgresql@10-cluster001.service - PostgreSQL Cluster 10-cluster001
     Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime; >
     Active: active (running) since Sun 2024-04-07 09:05:00 JST; 1min 25s ago
    Process: 7504 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 10>
   Main PID: 7509 (postgres)
      Tasks: 7 (limit: 9344)
     Memory: 17.0M
        CPU: 191ms
     CGroup: /system.slice/system-postgresql.slice/postgresql@10-cluster001.ser>
             ├─7509 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/>
             ├─7511 "postgres: 10/cluster001: checkpointer process   " "" "" "">
             ├─7512 "postgres: 10/cluster001: writer process   " "" "" "" "" "">
             ├─7513 "postgres: 10/cluster001: wal writer process   " "" "" "" ">
             ├─7514 "postgres: 10/cluster001: autovacuum launcher process   " ">
             ├─7515 "postgres: 10/cluster001: stats collector process   " "" "">
             └─7516 "postgres: 10/cluster001: bgworker: logical replication lau>

 4月 07 09:04:58 user-PC systemd[1]: Starting PostgreSQL Cluster 10-cluster001...
 4月 07 09:05:00 user-PC systemd[1]: Started PostgreSQL Cluster 10-cluster001.

　active (running) なら起動中です。以降はサーバーとして稼働し続けます。

　systemctl が使えない環境では、sudo pg_ctlcluster 10 cluster001 start コマンドなどを使用します。

スーパーユーザーが変更してある場合の注意

　もしもスーパーユーザーが postgres から変更してある場合には注意が必要です。
　PostgreSQL の起動、再起動は postgres ユーザーで行うために/var/lib/postgresql/以下のファイルのオーナーまたはグループが postgres でない場合には正常に起動しません。

データベース・クラスタにアクセス

　起動したデータベース・クラスタにアクセスをしてみます。

　クラスタの作成で追加したスーパーユーザー postgres001 でログインをしみてもエラーが返されます。

postgres001 でログイン
$ psql -U postgres001 -p 5433 -d postgres
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5433" failed: FATAL:  Peer authentication failed for user "postgres001"

　これは、初期状態でスーパーユーザーは PostgreSQL サーバーと peer 接続になっているために、一旦ログインしないとアクセスできません。

　スーパーユーザー postgres001 でログイン出来ない場合は、PostgreSQL の設定ファイル pg_hba.conf のスーパーユーザーの認証方法を trust にしておきます。
　pg_hba.conf は、ここでは /etc/postgresql/10/cluster001/ 以下にあります。

pg_hba.conf
$ sudo nano /etc/postgresql/10/cluster001/pg_hba.conf
..
# Database administrative login by Unix domain socket
local   all             postgres001                 trust
..

　trust は認証なしで PostgreSQL サーバーに接続します。

　クラスタを再起動します。

データベース・クラスタの再起動
$ sudo systemctl restart postgresql@10-cluster001.service

　次に、ポート 5433、ユーザー postgres001 を指定して作成したデータベース・クラスタに接続します。このときに、データベースを指定しおく必要があります。pg_createcluster コマンドでは初期データベース名が postgres になるようです。

データベース・クラスタにアクセス
$ psql -p 5433 -U postgres001 -d postgres

　ここでのデータベースを postgres としていますが、postgres001 かもしれません。

　ポート番号だけを指定した場合データベース・クラスタにアクセスするとデフォルトのデータベースに接続しようとします。

データベース・クラスタにアクセス
$ psql -p 5433
psql: connection to server on socket "/var/run/postgresql/.s.PGSQL.5433" failed:
 FATAL:  database "postgres001" does not exist

　このエラーは postgres001 というデータベースが作成されていないことが示されています。

　postgres001 ユーザーとしてログインをしないで cluster001 クラスタにアクセスしようとすると以下のようなエラーがでます。

他ユーザーからデータベース・クラスタにアクセス
$ psql -p 5433 -U postgres001 -d postgres
psql: connection to server on socket "/var/run/postgresql/.s.PGSQL.5433" failed:
 FATAL:  Peer authentication failed for user "postgres001"

　初期状態では、データベースと外部との認証ができず cluster001 クラスタに接続できないことが分かります。

　クラスタごとの接続設定は pg_hba.conf ファイルにあります。接続設定が peer の場合は、ログインした状態でなければ PostgreSQL に接続することができません。
　詳しくはログイン認証とロールを参照してください。

スーパーユーザーが変更してある場合の注意

　もしもスーパーユーザーが postgres から変更してある場合には.s.PGSQL.****という socket のためのファイルはtmp以下に作成されます。この場合はユーザーやポート番号が合っていてもアクセスできません。
　ソケットのファイルが作成される場所をtmpから/var/run/postgresqlに変更する必要があるかもしれません。設定ファイルは/etc/postgresql/[version]/[cluster_name]/postgresql.confです。

socket の場所を変更
#unix_socket_directories = '/tmp'       # comma-separated list of directories
unix_socket_directories = '/var/run/postgresql'        # comma-separated list of directories

　他にも/var/lib/postgresql/以下のファイルのオーナーまたはグループを postgres に変更する必要があるかもしれません。postgres ユーザーを完全に代替えしていない限りは、PostgreSQL の起動、再起動は postgres ユーザーで実行するためです。

データベース・クラスタの削除

　Ubuntu のパッケージからインストールした PostgreSQL でデータベース・クラスタを削除するには pg_dropcluster コマンドを使用します。

　pg_dropcluster コマンドはクラスタの設定データ、ログファイルといった関連するファイルをすべて削除します。/var/run/postgresql あるいは /tmp 以下の関連するファイルも削除します。削除には十分に注意してください。

データベース・クラスタの削除
$ sudo pg_dropcluster 10 cluster001

　もしも削除対象のクラスタが稼働中の場合はエラーになります。稼働中のクラスタを強制的に停止して削除するには --stop オプションを付加します。

　削除できたか確認するには、pg_lsclusters コマンドを使用します。削除が成功していれば該当するクラスタは表示されません。

データベース・クラスタの確認
$ pg_lsclusters
Ver Cluster    Port Status Owner       Data directory                    Log file
..

データベース・クラスタとロール

　データベース・クラスタごとにロールの管理を行うことができます。ロールはユーザー管理のことで、データベースではロールといいます。PostgreSQL ではユーザーはすべてロールとして管理します。

　まずは、既存のロールを確認してみます。

　以下は、postgres001 ユーザーでシステムでログインをし、psql でポートを指定してログインできるデータベース postges を指定しています。

$ su postgres001
..
$ psql -p 5433 -d postgres
psql (10.16 (Ubuntu 10.16-0ubuntu0.18.04.1))
Type "help" for help.

postgres001=#

　psql で cluster001 データベース・クラスタにログインできたら、\duコマンドでロールを確認します。

ロールの出力
	List of roles
Role name  |                         Attributes                         | Member of
-------------+------------------------------------------------------------+-----------
postgres001 | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

　ここでは作成したばかりのデータベース・クラスタであるため、postgres001 ユーザーのみしかありません。
　このことからデータベース・クラスタごとにロール（ユーザー）管理がされることが分かります。

　ロールについて詳しくは「PostgreSQLユーザー（ロール）管理」を参照してください。

データベース・クラスタにデータベースを作成

　作成したデータベース・クラスタに新たなデータベースを作成してみます。

　データベースを作成するには、データベース・クラスタにログインして CREATE DATABASE クエリを実行します。特別なことはありません。

　以下は、postgres001 ユーザーでシステムでログインをし、psql でポートを指定してログインできるデータベース postges を指定しています。

作成したデータベース・クラスタにログイン
$ su postgres001
..
$ psql -p 5433 -d postgres
psql (10.16 (Ubuntu 10.16-0ubuntu0.18.04.1))
Type "help" for help.

postgres001=#

　psql で作成したデータベース・クラスタにログインできたら、CREATE DATABASE でデータベースを作成してみます。
　データベース名は postgres001 としています。

データベースの作成
postgres=# CREATE DATABASE postgres001;
CREATE DATABASE

　作成したデータベースを確認するには\lコマンドを実行します。

データベースの確認
postgres=# \l
..
postgres001 | postgres001 | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
..

　データベースの中にテーブルやスキーマなどを作成していきます。
　データベースやテーブルなどを定義したファイルを読み込み・実行することで一括でデータベースを作成することもできます。

　データベースの作成方法についてはデータベースの管理（一覧、作成、接続、削除）を参照してください。

　ファイルの読み込みによるデータベースの作成には\iコマンドを使用します。詳しくは、SQLファイルの読み込みと実行を参照してください。