Zenn
🦅

PostgreSQLで「初めてのSQL」の始め方

2021/10/21に公開
Docker
PostgreSQL
tech

モチベーション

BaaS(Backend as a Service)のsupabaseを使って何か作りたい. 技術スタックの中核であるPostgreSQLをまず勉強しようかなと. 最終目標としてはsupabaseをバックエンドとする簡単なCRUDアプリケーション(CMS?)を作りたいとかなんとか.

想定読者

  • Dockerちょっと分かる
  • 良く分かってないが黒い画面はなんとなく触れる
  • ドキュメント読める

概要

初めてのSQL 第3版ではSakilaというサンプル・データベースが使われています. PostgreSQLにもPostgreSQL Sample Databaseというサンプル・データベースがあります. どちらもDVDレンタル店の貸し出しシステムのためのデータベースです.[1]

PostgreSQLの公式イメージを使って初めてのSQLの学習環境を構築する.

開発環境

VirtualBox上にUbuntu ServerをインストールしてSSHで接続して開発している. エディタはどちらもVisual Studio Code(VSCode)を利用している.

ソフトウェア バージョン
VSCode 1.61.1
VritualBox 6.1.26r145957
Ubuntu Server 20.04.3 LTS

結論

先に結論を書いておく.

サンプル・データのダウンロード

mkdir dvdrental && cd dvdrental
wget https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
unzip dvdrental.zip

この処理自体をコンテナ内で済ませても良いです.

データ投入用コンテナの起動

docker volume create dvdrental
docker run -d --rm --name test-db -v $(pwd):/tmp/data  -v dvdrental:/var/lib/postgresql/data -e POSTGRES_PASSWORD=imsuper postgres:12-bullseye
docker exec -it test-db /bin/bash

データベースの作成

psql -U postgres

として

CREATE DATABASE dvdrental
\q

データの投入

pg_restore -U postgres -d dvdrental /tmp/data/dvdrental.tar

データ利用コンテナの起動

docker run -d --rm --name dvdrental -v dvdrental:/var/lib/postgresql/data -e POSTGRES_PASSWORD=imsuper postgres:12-bullseye

PostgreSQLイメージ

とりあえず公式イメージを使う. Docker公式にもサンプル・アプリとしてPostgreSQLをDocker化するDockerfileがあり参考になると思います.

PostgreSQLコンテナを起動する

まずはPostgreSQLの公式イメージを取得する.

docker pull postgres:12-bullseye
docker image ls

test-dbという名前でコンテナを起動する. POSTGRES_PASSWORDでスーパー・ユーザーのパスワードを設定する.

docker run -d --rm　--name test-db -e POSTGRES_PASSWORD=imsuperuser -p 5432:5432  postgres:12-bullseye

docker psコマンドでtest-dbが実行されているのを確認しよう.

docker ps | grep test-db

データベースの作成

起動したコンテナに入ためにはexecというコマンドでシェルを起動する.

 docker exec -it test-db /bin/bash

iオプションでインタラクティブな状態でコンテナを起動できる. tはpTTY(pseudo TeleTYpe writer)を割り当てるオプションです.

For interactive processes (like a shell), you must use -i -t together in order to allocate a tty for the container process[2]

このようにシェルを起動してコンテナ内部に入るようなケースではtオプションと一緒に使わなくてはいけないようです.

起動したコンテナにデフォルトのユーザーとしてpsqlクライアントを起動する.

 psql -U postgres

dvdrentalというデータベースを作ります.

CREATE DATABASE dvdrental;
\l

List of databasesにdvdrentalがあればデータベースを作れたことになる.

さてコンテナを止めてます.

docker stop test-db

rmオプションをつけているのでこのコマンドを実行すると作成したデータベース等はすべて消えてしまいます. 何らかの方法でこの状態を永続化する必要があります.

コマンド 説明
docker stop test-db test-dbコンテナを停止する
docker rm test-db test-dbコンテナを削除する

サンプルデータの投入

手動

今回はPostgreSQLコンテナ起動後に手動でデータを投入します.

docker-entrypoint-initdb.d

PostgreSQLの公式コンテナのルート直下にはdocker-entrypoint-initdb.dというディレクトリがある. ここにsqlファイルやスタートアップ・スクリプトを置くと起動時に実行してくれる. データベースの初期化などに使える. ここにデータ初期化用のシェル・スクリプトとサンプル・データをマウントしておいても良いと思われます. 具体的な方法は以下を参照しましょう.

いずれにしても問題はこのコンテナを消すとデータが消えてしまうことです.

そもそもどこにデータはどこに保存されるのか

そもそもPostgreSQLのデータはどこにあるのでしょう. デフォルトで/var/lib/postgresql/dataというディレクトリに保存されています. これは環境変数PGDATAで変更できます.

永続化

PostgreSQLコンテナに投入されたサンプルデータを永続化する方法を検討してみましょう.

永続化の方法 (1) イメージ・レイヤ

Dockerイメージは様々な設定をイメージ・レイヤとして積層することで形成されています. つまりサンプルデータはファイルとしてイメージ内に保存することが可能なはずです. docker commitを使うとコンテナ内での変更を新しいイメージの層として加えることができるのです.

が, 公式イメージではできません.

docker postgres with initial data is not persisted over commitsによると/var/lib/postgresql/data/pgdataはVOLUEMEとして指定されているためです. そのためイメージ・レイヤとして書き加える際に除外されるようです.

永続化の方法 (2) ボリューム

では公式のイメージではどのような方法でデータの永続化をするのでしょうか.

docker run -d \
    --name some-postgres \
    -e POSTGRES_PASSWORD=mysecretpassword \
    -e PGDATA=/var/lib/postgresql/data/pgdata \
    -v /custom/mount:/var/lib/postgresql/data \
    postgres

vオプションはvolumeを指定しています. /custom/mountはホストOSのディレクトリを指します.　このようにホストのパスを指定した場合マウント・ボリュームとなります.　PGDATAはPostgreSQLのデータが保存されるフォルダです.

このフォルダをホスト上にマウントしているので, /var/lib/postgresql/data/pgdataに保存されたデータはすべてマウントした/custom/mountに保存されます. このディレクトリを毎回マウントすればデータ投入済みのコンテナを起動することができます.

データの投入

マウント・ボリュームでデータを投入してみましょう.

サンプルデータをローカルフォルダにダウンロードしてきます.

mkdir dvdrental && cd dvdrental
wget https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
unzip dvdrental.zip

するとdvdrental.tarが解凍されます.

docker run -d --rm \
    --name some-postgres \
    -e POSTGRES_PASSWORD=imsuper \
    -v $(pwd):/tmp/data \
    -v dvdrental:/var/lib/postgresql/data
    postgres:12-bullseye

すでに説明しましたがdvdrentalというデータベースを作成します.

docker exec -it test-db /bin/bash

からコンテナに入り

psql -U postgres

としてpsqlを起動します. そしてデータベースを作成します.

CREATE DATABASE dvdrental;

psqlとのセッションを\qで終わらせて,

pg_restore -U postgres -d dvdrental /tmp/data/dvdrental.tar

としてデータを投入します. 再度psqlを起動して\lでデータベースの一覧を表示します. dvdrentalが一覧に入っていればオッケーです.

問題はWindowsではこの方法はとれなさそうということでしょうか.

ボリュームとは?

ボリュームというコンテナと切り離されたデータ領域を作成しそこにコンテナのデータを保存することができます. この領域はDockerが管理してくれます.

匿名ボリューム(anonymous volume)

何も指定しなくても匿名ボリューム(anonymous volume)というのが実は作られています. まずは現在あるボリュームの一覧を確認してみましょう.

docker volume ls | wc

次にvオプションは特に指定しないでコンテナを起動し同じ処理を再度実行する.

docker run -d --rm --name test-db POSTGRES_PASSWORD=imsuper postgres:12-bullseye
docker volume ls | wc

行数が増えており一覧には英数字の列が名前として割り振らたボリュームが追加されている. あるいはvオプションに適当な絶対パスを指定しても同じことです.

docker run -d --rm --name test-db -v /pg-store -e　POSTGRES_PASSWORD=imsuper postgres:12-bullseye

これらはrmオプションが付いているので自動的に削除される.

To automatically remove anonymous volumes, use the --rm option. For example, this command creates an anonymous /foo volume. When the container is removed, the Docker Engine removes the /foo volume but not the awesome volume.[3]

このため永続化には利用できません.

名前付きボリューム (Named Volume)

vオプションのvolume-name:/volume-pathで指定するとボリュームに名前を付けることができる.

docker run -d --rm --name test-db -v dvdrental:/var/lib/postgresql/data -e POSTGRES_PASSWORD=imsuper postgres:12-bullseye

この場合はコンテナを停止しても消えません. なぜならホストOSにマウントされているからです.

docker volume inspect dvdrental

を実行してMountpointを確認してアクセスするとホスト上に存在することが分かります.つまりやってることはマウント・ボリュームと同じでdockerが勝手に管理してくれるわけです.

名前付きボリュームはあらかじめ作成しておくこともできます.

docker volume create dvdrental
docker volume ls

これをマウントします.

docker run -d --rm --name test-db -v dvdrental:/var/lib/postgresql/data -v $(pwd):/tmp/data -e POSTGRES_PASSWORD=imsuper postgres:12-bullseye

ここでデータベースを作ってデータを投入します. 後はこのdvdrentalボリュームを指定してコンテナを起動すればデータが投入されているはずです.

docker run -d --rm --name dvdrental -v dvdrental:/var/lib/postgresql/data -e POSTGRES_PASSWORD=imsuper postgres:12-bullseye

データ・ボリューム (data volume)[4]

データ・ボリュームはデータ用のコンテナを立ててそのボリュームを利用する手法のようです. Docker公式の用語ではないようですが書籍やブログ記事などでは使われたりするのを見かけます. 少なくともそのような特別なボリュームが存在するわけではありません.

docker run -it --rm --volumes-from test-db busybox /bin/bash

のようにvolumes-fromオプションで指定してするとtest-dbのボリュームが勝手にマウントされます.

詳しい使い方はBackup, restore, or migrate data volumesを参考にしてみてください.

まとめ

後は初めてのSQLを買ってやってみましょう. 私もやります.

Reference

脚注

  1. 多分同じものと思います. ↩︎

  2. Foreground ↩︎

  3. Remove anonymous volumes ↩︎

  4. 公式でdata volumeという用語が使われているのはBackup, restore, or migrate data volumesぐらいでしょうか. ↩︎

Discussion