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

データベース・クラスタ(データベース・インスタンス)

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

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

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

 データベース・クラスタは、データベース管理システムにおいて複数のデータベースを管理します。

 データベース・クラスタは、CREATE DATABASE クエリー文で作成できるデータベースのことではなく、systemctl や pg_ctl でデータベース・クラスタの設定を元にサーバーとして起動し、データベースへの送受信を仲介します。
 データベース・クラスタの設定内容は、Ubuntu でのパッケージインストールのデフォルトの状態では/etc/postgreql/10/main/などにあります。設定内容は OS やインストール方法によって変わります。現在のデータベース・クラスタの保存場所の確認については、以下の現在の状況の確認を参照してみてください。

 データベース・クラスタも複数個を稼働することができ、1つの OS (以下はシステムという)の中に複数のクラスタ(インスタンス)を運用することができます。

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

 クラスタ(インスタンス)ごとにロールの管理が独立しており、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 コマンドではホームディレクトリ、シェルの使用がなく、ログイン画面に表示されません。

ユーザーの追加
$ sudo useradd postgres001
または、追加するユーザーをオプションで既存のスーパーユーザー psotgres のグループに追加し、ログインシェルを /bin/bash に指定します。
ユーザーの追加
$ sudo useradd -g postgres -s /bin/bash postgres001

 もしも、開発者としてホームディレクトリやシェルの使用が必要な場合は adduser コマンドを使用します。そうすることで新規ユーザーを開発用ユーザーとすることもできます。

 新たに追加したユーザーを既存のユーザー 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 は環境によって読み替えてください。

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

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

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

 データベース・クラスタの設定データの保存場所は、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/main10 は PostgreSQL のバージョン名です。main は Ubuntu のパッケージインストールにおけるデフォルトのデータベース・クラスタの名称です。ユーザーはデフォルトでは postgres です。

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

pg_createcluster

 Ubuntu におけるデータベース・クラスターの追加作成は独自コマンドの 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/というディレクトリの作成とそのディレクトリ以下に追加したクラスターの共有カタログテーブルの生成と、データベースの作成が実行されます。
 このコマンドでは 新たなポート番号を自動で設定してくれます。設定されたポート番号は後で確認したほうがよいでしょう。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 では想定通りには起動しないようです。
 systemctlコマンドで起動します。

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コマンドで起動します。

データベース・クラスタの起動
$ 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 でログインします。初期状態では外部からアクセスできません。
 パスワードは OS でのユーザー追加で設定したパスワードです。

postgres001 でログイン
$ su postgres001
パスワード:

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

データベース・クラスタにアクセス
$ psql -p 5433 -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 クラスタに接続できないことが分かります。

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

 もしもスーパーユーザーが 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ファイルの読み込みと実行を参照してください。