PostgreSQL on Docker

【当研究室のシステム】

概要

気軽に試行錯誤って大事だよね．
もはや DB は Docker でインストールする時代！初心者のための DB インストール on Dockerの「開発環境のデータベースのインストールに 5 分以上時間をかけるのは時間の無駄です！」の言葉がとても素敵．

履歴

2024/10/23: docker-compose.ymlからcompose.ymlを使うようになったっぽいので関係するところをアップデート，あと記載間違いを手直し．

方針

使用イメージについて

DockerHubの公式のイメージを使用．
バージョンはlatestではなく固定．
ここではタグ名16.4を使用．

データの永続化について

データの永続化のためにボリュームを使用．
またリストアできるようにデータベースのバックアップを定期的に取る．
注意点としてdocker compose down -vなどボリューム削除のコマンドをしないように気をつけなくては．

設定

とりあえず~/Docker/pgsql/以下での作業を念頭に置く．

各種ファイルの準備

compose.ymlと.envファイルを以下のように作成．

compose.yml

services:
  pgsql_db:
    image: postgres:${POSTGRES_VERSION}
    container_name: ${CONTAINER_NAME}
    hostname: ${HOSTNAME}
    ports:
      - "5432:5432"
    restart: always
    environment:
      - POSTGRES_USER=${USER_NAME}
      - POSTGRES_PASSWORD=${USER_PASS}
    volumes:
      - db_vol:/var/lib/postgresql/data

volumes:
  db_vol:

.env

POSTGRES_VERSION=16.4
CONTAINER_NAME=pgsql_db # お好きに
HOSTNAME=pgsql-db # お好きに
USER_NAME=postgres
USER_PASS=postgres

運用

例として，ホスト名pgsql-db, コンテナ名pgsql_dbとする．

基本的な使い方

コンテナ起動

docker compose up -d

コンテナ終了

docker compose down

データベースへ接続(他のPCなどからの接続)

psql -h pgsql-db -U postgres

注意: psqlのコマンドがインストールされているPCから．なければコンテナ内から実行．

データベースへ接続(コンテナ内から接続)

docker container exec --user postgres -it pgsql_db bash
psql # postgresユーザでログインしているので-hも-Uもいらない

チューニング

使用目的によっては，チューニングによりパフォーマンスがすごく変わるので(変わったので)かなりオススメ．

1. configファイルの雛形入手
   以下のコマンドでconfigファイルの雛形を入手．
   コンテナがない時/コンテナを起動していない時

   docker container run -i --rm postgres cat /usr/share/postgresql/postgresql.conf.sample > ~/Docker/pgsql/postgresql.conf
   

   コンテナ(コンテナ名:pgsql_db)を起動している時

   docker container exec -i pgsql_db cat /usr/share/postgresql/postgresql.conf.sample > ~/Docker/pgsql/postgresql.conf
   

2. パラメータの決定
   PGTuneでPCの諸元を入れてパラメータを手に入れる．
3. 入手したパラメータを~/Docker/pgsql/postgresql.confに貼り付け
4. configファイルが有効になるようにcompose.ymlを以下のように変更
   compose.yml

   services:
     pgsql_db:
       image: postgres:${POSTGRES_VERSION}
       container_name: ${CONTAINER_NAME}
       command: -c 'config_file=/etc/postgresql/postgresql.conf' #追加
       hostname: ${HOSTNAME}
       ports:
         - "5432:5432"
       restart: always
       environment:
         - POSTGRES_USER=${USER_NAME}
         - POSTGRES_PASSWORD=${USER_PASS}
       volumes:
         - db_vol:/var/lib/postgresql/data
         - ./postgresql.conf:/etc/postgresql/postgresql.conf #追加
   
   volumes:
     db_vol:
   

5. 確認
   管理者(postgres)でデータベースに接続し，show shared_buffers;などのコマンドでパラメータが期待通り設定されているか確認．

バックアップ

バックアップはとりましょう．
更にはcronで定期的に，rsyncなどで他のPCにとりましょう．

全部のデータをバックアップ

docker container exec --user postgres pgsql_db pg_dumpall > db.sql

リストア

cat db.sql | docker container exec --user postgres -i pgsql_db psql

リストアは分かりにくいならコンテナ内から実行

リストア(コンテナ内から)

docker container exec --user postgres -it pgsql_db bash
psql -f dbsql postgres

PostgreSQLのバージョンアップ

最新のイメージに更新するだけ(バージョンが変わらない)なら特に問題なし．普通にdocker pull postgres:16.4などで最新イメージを持ってくればよい．
しかしバージョン自体を更新(タグ名を新しいバージョンに)するならおそらく以下の手順が必要．

また一般に他のサービス(ex.wwwなど)も同時に動かしていることが多い．
その場合compose.ymlには他のサービスに関する情報も入っている．
そこで，compose.ymlからデータベースのコンテナに関する情報だけ抜き出したcompose-pgsql.ymlを作成する．

1. compose-pgsql.ymlを作成
2. データのバックアップ(pg_dumpall)
3. コンテナ停止，ボリューム削除

   docker compose down -v
   

4. .env内のPOSTGRES_VERSIONを更新(タグ名を新しいバージョンのものにする)
5. データベースのコンテナ起動

   docker compose up -d -f compose-pgsql.yml
   

6. データのリストア(cat ...)
7. コンテナ再起動

   docker compose down && docker compose up -d
   

その他

パスワードについて

postgresのパスワードは変更しとこう(参考:PostgreSQL データベース パスワードの変更)．

参考ページ

• もはや DB は Docker でインストールする時代！初心者のための DB インストール on Docker
• Dockerでいつでも作り直せるローカルDB(mysql)作ってみた
• PostgreSQLのメモリチューニングはじめの一歩
• PGTune