PostgreSQL 利用ノート¶

Since:: 1996
Official site:: <https://www.postgresql.org/>
Version:: psql 16.3 (Debian 16.3-1.pgdg120+1)

PostgreSQL をとりあえず利用するまでの手順を記す。SQL 学習環境が得られれば十分だ。本稿では Docker を利用するものとする。

以前の PostgreSQL 環境の残滓を一掃する

環境が完全にクリーンである場合にはここを飛ばしてイメージ入手工程を読め。以前、何かの PostgreSQL チュートリアルをやって上手くいかなかった場合にはこの工程が外せない。

おそらく apt を利用して環境構築したはずなので、次の手順に従えばひじょうに上手くいく：

アンインストール手順例¶

$ sudo apt remove --purge postgresql
$ dpkg -l | grep postgres
$ sudo apt remove --purge postgresql-{{,client-}{14,common},contrib}
$ sudo rm -rf {/var/lib/,/var/log/,/etc/}postgresql/
$ sudo deluser postgres

さらにユーザー設定ファイルも削除するべきだと考えられる。後ほど XDG Base Directory 対応と同時に設定ファイルを作り直す想定だ。

イメージを入手してコンテナーを稼働する

最初に Docker Hub から公式イメージを検索して、それをローカルに持ってくる：

PostgreSQL イメージ取得例¶

$ docker search postgresql --filter=is-official=true
$ docker pull postgres
$ docker images --filter reference=postgres

コンテナーに例えば some-postgres などの名前を付けて稼働する。このときに PostgreSQL に対して必要な情報、環境変数を指定する：

PostgreSQL コンテナー稼働コマンド例¶

$ docker run --name some-postgres -e POSTGRES_PASSWORD=secret -d postgres

これで PostgreSQL サーバーが稼働する。あくまでも練習用の手順なので、パスワードの扱いはここではぞんざいだ。

データベースクライアントを実行する

PostgreSQL が稼働しているコンテナーに「入って」クライアント psql を実行したい。

稼働中の PostgreSQL コンテナーで CLI を対話的に起動する例¶

$ docker exec -it -u postgres some-postgres createdb mydb
$ docker exec -it -u postgres some-postgres psql -s mydb
psql (16.3 (Debian 16.3-1.pgdg120+1))
Type "help" for help.

mydb=#

コンテナーを一時停止する

PostgreSQL コンテナーを一時停止、再開するには次のどちらかの組み合わせを実行する：

コンテナー一時停止＆再開例¶

$ docker stop some-postgres
$ docker start some-postgres

$ docker pause some-postgres
$ docker unpause some-postgres

システム資源を一時的に解放する必要がある場合に停止するといい。

SQL などの練習をする

PostgreSQL 公式文書のチュートリアルも有用であるし、キーワード “PostgreSQL Tutorial” などで Google 検索するとそれらしい教材がたくさん見つかる。

ドットファイル

ここで言うドットファイルとは .psqlrc とする。PostgreSQL 環境を Docker コンテナーではなくホストに構築したとするならば、次のようにこのファイルを管理したい。まず、Bash ドットファイル .bashrc で PSQLRC と PSQL_HISTORY を設定する：

PSQLRC と PSQL_HISTORY の設定例¶

export PSQLRC="$XDG_CONFIG_HOME/postgresql/psqlrc"
export PSQL_HISTORY="$XDG_STATE_HOME/postgresql/psql_history"

上記パスのディレクトリー部分に当たるものは mkdir しておく必要がある。ここまで述べた方式はクライアントプログラム psql をホスト環境にインストールしている場合にはそのまま使える。

コンテナー環境の psql を利用する場合。ユーザーは postgres であるとすると、その HOME は /var/lib/postgresql だ。この直下にドットファイルが置かれる。コンテナー稼働開始時にホストファイルを bind-mount すれば行ける。履歴はコンテナーに置いてかまわないと考えるので指定しない。

$ docker run -d \
    --name some-postgres \
    -e POSTGRES_PASSWORD=secret \
    --mount type=bind,source=$PSQLRC,target=/var/lib/postgresql/.psqlrc,readonly \
    postgres

サーバードットファイルに関しても同様に、ホストにカスタム版を置いて bind-mount することが可能だ。ログが欲しい場合などに設定項目を編集することになる。

$ docker run -d \
    --name some-postgres \
    -e POSTGRES_PASSWORD=secret \
    --mount type=bind,source=/path/to/my-postgres.conf,target=/etc/postgresql/postgresql.conf,readonly \
    postgres -c config_file=/etc/postgresql/postgresql.conf

データ格納場所

コンテナー内 /var/lib/postgresql が既定のデータベース格納場所であり、これをホスト側で管理したい場合には bind-mount を適宜指定する。ホスト側のディレクトリーは前もって手動で作成しておく。

Docker Hub 公式イメージ README によると /var/lib/postgresql にマウントする場合、/var/lib/postgresql/data はコンテナーランタイムからのローカルボリュームであるため、マウントされたボリューム上にデータは永続化されないと文書にある。

/path/to は差し当たり $(pwd) にしておけ¶

$ docker run -d \
    --name some-postgres \
    -e POSTGRES_PASSWORD=secret \
    -e PGDATA=/var/lib/postgresql/data/pgdata \
    --mount type=bind,source=/path/to/datadir,target=/var/lib/postgresql/data \
    postgres

この結果、ホスト側ファイルシステム部分である ./data/pgdata にデータベース実体が保存される。コンテナーをいったん廃棄して再度この docker run コマンドを実行すると、データベースが維持できていることが確認できる。

利用者ノート

./data/pgdata の所有権表記が 999 root になる。コンテナーの /etc/passwd を確認するとユーザー postgres に相当する。

まとめ

ここまでの諸々をまとめたコンテナー稼働コマンド¶

$ docker run -d \
    --name some-postgres \
    -e POSTGRES_PASSWORD=secret \
    -e PGDATA=/var/lib/postgresql/data/pgdata \
    --mount type=bind,source=$PWD/datadir,target=/var/lib/postgresql/data \
    --mount type=bind,source=$PSQLRC,target=/var/lib/postgresql/.psqlrc,readonly \
    postgres

こんなコマンドを毎回書いていられないので Docker Compose を利用する。ファイル compose.yaml を次のような内容で用意：

Example of compose.yaml¶

services:
  postgres:
    container_name: some-postgres
    image: postgres
    environment:
      PGDATA: /var/lib/postgresql/data/pgdata
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: secret
    volumes:
      - type: bind
        source: ${PWD}/datadir
        target: /var/lib/postgresql/data
      - type: bind
        source: ${PSQLRC}
        target: /var/lib/postgresql/.psqlrc
        read_only: true

これで docker compose up -d や docker compose down が利用可能になる。

サンプルデータベースを構築する

Load PostgreSQL Sample Database で配布されている SQL 練習用データベースを拝借する。まずホスト側ファイルシステムにアーカイブをダウンロードし、それからそのファイルをコンテナーに転送する。おしまいにコンテナー側で pg_restore コマンドを実行するという流れだ：

ホスト側手順¶

$ curl -O https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
$ unzip dvdrental.zip
$ docker cp dvdrental.tar some-postgres:/tmp/dvdrental.tar

チュートリアルに従い、データベース dvdrental を作成しておく。psql セッションで CREATE DATABASE dvdrental; しておけ。その後ならばデータベースをロードして良い：

コンテナー側手順¶

$ docker exec -it -u postgres some-postgres pg_restore -d dvdrental /tmp/dvdrental.tar

データベース dvdrental を確認する例¶

postgres=# \c dvdrental
You are now connected to database "dvdrental" as user "postgres".
dvdrental=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | actor         | table | postgres
 public | address       | table | postgres
 public | category      | table | postgres
 public | city          | table | postgres
 public | country       | table | postgres
 public | customer      | table | postgres
 public | film          | table | postgres
 public | film_actor    | table | postgres
 public | film_category | table | postgres
 public | inventory     | table | postgres
 public | language      | table | postgres
 public | payment       | table | postgres
 public | rental        | table | postgres
 public | staff         | table | postgres
 public | store         | table | postgres
(15 rows)

コンテナーを廃棄する

PostgreSQL コンテナーが用済みになったらそれを削除することでデータベースも消去される。失いたくない場合には docker run の段階でマウントなどを指定するか、コンテナーにあるデータベースをホスト側に退避させるのだろう。

コンテナーを処分するコマンド例¶

$ docker stop some-postgres
$ docker rm some-postgres

ディスクに余裕がなければイメージも削除する。

ネットワークやログ管理など、未実施の項目が残っているが、納得したのでひとまず終わる。