PostgreSQL on Docker

【[当研究室のシステム](https://qiita.com/NeK/items/70f994b271b0c1a48590)】

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

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

## 方針
### 使用イメージについて
[DockerHubの公式](https://hub.docker.com/_/postgres)のイメージを使用．
バージョンは`latest`ではなく固定．
ここではタグ名`16.4`を使用．

### データの永続化について
データの永続化のために[ボリュームを使用](https://matsuand.github.io/docs.docker.jp.onthefly/storage/volumes/)．
またリストアできるようにデータベースのバックアップを定期的に取る．
注意点として`docker compose down -v`などボリューム削除のコマンドをしないように気をつけなくては．

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

## 各種ファイルの準備
`compose.yml`と`.env`ファイルを以下のように作成．

```yml: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とする．

## 基本的な使い方

```bash:コンテナ起動
docker compose up -d
```

```bash:コンテナ終了
docker compose down
```

```bash:データベースへ接続(他のPCなどからの接続)
psql -h pgsql-db -U postgres
```

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

```bash:データベースへ接続(コンテナ内から接続)
docker container exec --user postgres -it pgsql_db bash
psql # postgresユーザでログインしているので-hも-Uもいらない
```

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

1. configファイルの雛形入手
以下のコマンドでconfigファイルの雛形を入手．
   ```bash: コンテナがない時/コンテナを起動していない時
   docker container run -i --rm postgres cat /usr/share/postgresql/postgresql.conf.sample > ~/Docker/pgsql/postgresql.conf
   ```
   ```bash: コンテナ(コンテナ名:pgsql_db)を起動している時
   docker container exec -i pgsql_db cat /usr/share/postgresql/postgresql.conf.sample > ~/Docker/pgsql/postgresql.conf
   ```
1. パラメータの決定
[PGTune]でPCの諸元を入れてパラメータを手に入れる．
1. 入手したパラメータを`~/Docker/pgsql/postgresql.conf`に貼り付け
1. configファイルが有効になるようにcompose.ymlを以下のように変更
   ```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:
   ```
1. 確認
管理者(postgres)でデータベースに接続し，`show shared_buffers;`などのコマンドでパラメータが期待通り設定されているか確認．

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

```bash:全部のデータをバックアップ
docker container exec --user postgres pgsql_db pg_dumpall > db.sql
```

```bash:リストア
cat db.sql | docker container exec --user postgres -i pgsql_db psql
```

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

```bash:リストア(コンテナ内から)
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`を作成
1. データのバックアップ(`pg_dumpall`)
1. コンテナ停止，ボリューム削除
   ```bash
   docker compose down -v
   ```
1. `.env`内の`POSTGRES_VERSION`を更新(タグ名を新しいバージョンのものにする)
1. データベースのコンテナ起動
   ```bash
   docker compose up -d -f compose-pgsql.yml
   ```
1. データのリストア(`cat ...`)
1. コンテナ再起動
   ```bash
   docker compose down && docker compose up -d
   ```

## その他
### パスワードについて
postgresのパスワードは変更しとこう(参考:[PostgreSQL データベース パスワードの変更])．

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


[もはや DB は Docker でインストールする時代！初心者のための DB インストール on Docker]:https://qiita.com/ymasaoka/items/ca2bb2cb19ebeafe1ccc
[Dockerでいつでも作り直せるローカルDB(mysql)作ってみた]:https://zenn.dev/jojojo/articles/f1223bb06cf5be
[PostgreSQL データベース パスワードの変更]:https://ftpdocs.broadcom.com/cadocs/0/CA%20Application%20Performance%20Management%209%205-JPN/Bookshelf_Files/HTML/APM_9.5--Installation%20and%20Upgrade%20Guide/1530131.html
[PostgreSQLのメモリチューニングはじめの一歩]:https://www.ecomottblog.com/?p=9220
[PGTune]:https://pgtune.leopard.in.ua/