
## はじめに

ローカル環境で試してみたかったため、Hugging Faceからgoogleの日本語モデルgemma-2-2b-jpn-itモデルをローカルにダウンロードして実行しました。

CPU実行、GPU実行を試し、最後に対話形式にするためのループ処理にしています。

## 概要

1. モデルのダウンロード
2. CPUでの実行
3. GPUでの実行
4. 対話ループの実装

## 環境

- Windows 11
- CPU : 12th Gen Intel(R) Core(TM) i7-12700
- GPU : NVIDIA GeForce RTX 4070 Ti

- Python 3.10.9
- transformers 4.45.1
- accelerate 0.34.2
- torch 2.4.1 (GPU実行時はtorch 2.4.0+cu124)

## 1. モデルのダウンロード

https://huggingface.co/google/gemma-2-2b-jpn-it

リポジトリをクローン、またはFiles and versionsからダウンロード出来ます。

ダウンロードしたものはフォルダにまとめておきます。

`main.py`は後述で作成するソースです。

```none
.
│  main.py
│
└─model
    └─google
        └─gemma-2-2b-jpn-it
                config.json
                generation_config.json
                gitattributes
                model-00001-of-00002.safetensors
                model-00002-of-00002.safetensors
                model.safetensors.index.json
                README.md
                special_tokens_map.json
                tokenizer.json
                tokenizer.model
                tokenizer_config.json
```

## 2. CPUでの実行

必要なパッケージをインストールします。

```python
pip install transformers
pip install accelerate
pip install --upgrade torch
```
ここでtorchを更新しているのは、もし標準で古いtorchが入っていると実行時に以下のエラーが発生する可能性があるためです。

```none
module 'torch' has no attribute '_dynamo'
```

さらに、CPUで実行する場合、modelの指定時にはdevice_mapをcpuへ変更することと、torch_dtypeは削除する必要があります。

モデルはダウンロードしたフォルダへの相対パスにします。

```python
tokenizer = AutoTokenizer.from_pretrained("model/google/gemma-2-2b-jpn-it")
model = AutoModelForCausalLM.from_pretrained(
    "model/google/gemma-2-2b-jpn-it",
#    device_map="auto",
    device_map="cpu",
#    torch_dtype=torch.bfloat16,
)
```
上記部分以外はほぼreadme通りのソースです。

追加でモデルのロード時間と回答時間を計測しています。

```python
from transformers import AutoTokenizer, AutoModelForCausalLM
import torch
import time

start_time_1 = time.time()

# モデルとトークナイザーのロード
tokenizer = AutoTokenizer.from_pretrained("model/google/gemma-2-2b-jpn-it")
model = AutoModelForCausalLM.from_pretrained(
    "model/google/gemma-2-2b-jpn-it",
#    device_map="auto",
    device_map="cpu",
#    torch_dtype=torch.bfloat16,
)

end_time_1 = time.time()
print("------------------------------")
print("モデルロード時間　", end_time_1 - start_time_1)
print("------------------------------")

start_time_2 = time.time()

messages = [
    {"role": "user", "content": "マシーンラーニングについての詩を書いてください。"},
]
inputs = tokenizer.apply_chat_template(messages, return_tensors="pt", add_generation_prompt=True, return_dict=True).to(model.device)

outputs = model.generate(**inputs, max_new_tokens=256)
generated_text = tokenizer.batch_decode(outputs[:, inputs['input_ids'].shape[1]:], skip_special_tokens=True)[0]
print(generated_text.strip())

end_time_2 = time.time()
print("------------------------------")
print("回答時間　", end_time_2 - start_time_2)
print("------------------------------")

```


実行時にtorchの警告文が表示されますが、動作はします。

```none
The 'max_batch_size' argument of HybridCache is deprecated and will be removed in v4.46. Use the more precisely named 'batch_size' argument instead.
Starting from v4.46, the `logits` model output will have the same type as the model (except at train time, where it will always be FP32)
```

結果としては、モデルのロードは約8秒、回答時間は約70秒のようです。

```none
D:\src\python\chat_with_model_test\main>python .\main.py
Loading checkpoint shards: 100%|█████████████████████████████████████████████████████████| 2/2 [00:07<00:00,  3.54s/it]
------------------------------
モデルロード時間　 8.160688638687134
------------------------------
The 'max_batch_size' argument of HybridCache is deprecated and will be removed in v4.46. Use the more precisely named 'batch_size' argument instead.
Starting from v4.46, the `logits` model output will have the same type as the model (except at train time, where it will always be FP32)
## マシーンラーニングの詩

**1.**
データの海、深淵の広がり、
複雑なパターン、隠された知識。
機械の目、無数の計算、
未来を予測、新たな道を開く。

**2.**
学習の旅、複雑な過程、
教師あり、教師なし、様々な方法。
アルゴリズムの力、複雑なネットワーク、
未知の世界を解き明かす。

**3.**
画像認識、音声認識、
予測、分類、様々なタスク。
機械の知恵、人間の夢を形作る、
新しい時代を築く力を持つ。

**4.**
倫理の問いかけ、責任の重さ、
人間と機械の共存、未来の道。
技術の進化、未知の領域へ、
新たな可能性を創造する。
------------------------------
回答時間　 72.89190626144409
------------------------------
```

## 3. GPUでの実行

[PyTorch](https://pytorch.org/get-started/previous-versions/)を自身の環境に合わせてインストールします。

```python
# CUDA 12.4
pip install torch==2.4.0 torchvision==0.19.0 torchaudio==2.4.0 --index-url https://download.pytorch.org/whl/cu124
```

ソースについては`device_map`と`torch_dtype`を元に戻します。

```python
# モデルとトークナイザーのロード
tokenizer = AutoTokenizer.from_pretrained("model/google/gemma-2-2b-jpn-it")
model = AutoModelForCausalLM.from_pretrained(
    "model/google/gemma-2-2b-jpn-it",
    device_map="auto",
    torch_dtype=torch.bfloat16,
)
```

結果としては、モデルのロードは約5秒、回答時間は約9であり、実行速度が大幅に早くなったことが確認できます。

```none
D:\src\python\chat_with_model_test\main>python .\main.py
Loading checkpoint shards: 100%|█████████████████████████████████████████████████████████| 2/2 [00:03<00:00,  1.89s/it]
------------------------------
モデルロード時間　 5.084553956985474
------------------------------
The 'max_batch_size' argument of HybridCache is deprecated and will be removed in v4.46. Use the more precisely named 'batch_size' argument instead.
Starting from v4.46, the `logits` model output will have the same type as the model (except at train time, where it will always be FP32)
## マシーンラーニングの詩

**1.**
データの海、深淵の広がり、
複雑なパターン、隠された知識。
機械学習、その力強さ、
未来を予測、その道を開く。

**2.**
ニューラルネットワーク、複雑な枝、
学習の旅、その過程は静か。
データから学び、進化する姿、
予測の精度、その力強さ。

**3.**
教師あり学習、正解を導く、
教師なし学習、未知の世界へ。
機械学習、その進化は止まらない、
未来の扉を開く、新たな時代へ。

**4.**
画像認識、音声認識、
複雑なタスク、その答えを見つける。
機械学習、その力強さ、
未来の技術、その可能性を語る。
------------------------------
回答時間　 9.14939260482788
------------------------------
```

## 4. 対話ループの実装

上記ソースではプログラム内で指定した内容1つへの回答のみしか対応できません。

そこで、コマンドプロンプトで入力した内容を対話として保持しつつループする処理として実装します。

```python
from transformers import AutoTokenizer, AutoModelForCausalLM
import torch

# モデルをロード
tokenizer = AutoTokenizer.from_pretrained("model/google/gemma-2-2b-jpn-it")
model = AutoModelForCausalLM.from_pretrained(
    "model/google/gemma-2-2b-jpn-it",
    device_map="auto",
    torch_dtype=torch.bfloat16,
)

# 対話の履歴を保持するリスト
messages = []

def chat_with_model(user_input):
    # ユーザーからの入力をメッセージに追加
    messages.append({"role": "user", "content": user_input})
    
    # モデルに渡すための入力を準備
    inputs = tokenizer.apply_chat_template(messages, return_tensors="pt", add_generation_prompt=True, return_dict=True).to(model.device)

    # モデルによる応答生成
    outputs = model.generate(**inputs, max_new_tokens=1024)
    generated_text = tokenizer.batch_decode(outputs[:, inputs['input_ids'].shape[1]:], skip_special_tokens=True)[0]

    # システムの応答をメッセージに追加
    messages.append({"role": "assistant", "content": generated_text.strip()})

    return generated_text.strip()

# 対話の開始
while True:
    # 質問文を入力
    user_input = input("あなた: ")

    # 対話終了
    if user_input.lower() in ["終了", "exit", "quit"]:
        print("対話を終了します。")
        break

    # 対話履歴リセット
    if user_input.lower() in ["リセット", "reset"]:
        messages = []
        print("アシスタント: 対話履歴をリセットしました。")
        continue

    # 応答文を出力
    response = chat_with_model(user_input)
    print("\n")
    print("アシスタント:", response)
    print("\n")
```

「終了」「exit」「quit」を入力した場合はループを終了し、「リセット」「reset」を入力した場合は対話内容を削除して初期状態にします。

次のような動作になります。

```none
あなた: 挨拶をしてください。

アシスタント: こんにちは！ 😊

何かお手伝いできることはありますか？


あなた: その言葉を英語にしてください。


アシスタント: Here's the translation:

"Hello! 😊  Is there anything I can help you with?"



Let me know if you'd like to try another translation!


あなた: リセット
アシスタント: 対話履歴をリセットしました。
あなた: その言葉を英語にしてください。


アシスタント: すみません、どの言葉を英語にしたいのか教えてください！ 😊


あなた: 終了
対話を終了します。
```

簡単にですが、文脈を覚えて英語変換していることと、対話内容をリセットできていることが確認できます。



 ローカル環境で試してみたかったため、Hugging Faceからgoogleの日本語モデルgemma-2-2b-jpn-itモデルをローカ...

【Windows】ローカル環境でgoogleの日本語モデルgemma-2-2b-jpn-itを実行


## はじめに

DS220+(Synology NAS)を購入したので、DockerでRedmineを起動し、NAS内のbareリポジトリと連携しました。

これでNASを起動するだけでチケットと対応するcommit情報の確認が出来るようになります。あくまで個人向けですが。

元々はDockerでgitlabを使用しようとは思ったのですが、メモリ関係でNASが悲鳴を上げていたため断念しました。

Synology NASの初期設定、Container Manager(Dockerに利用)のインストール、Docker用ユーザ設定、Git Serverのインストールは実施済みとします。

## 概要

まず、RedmineをDockerで起動できることを確認し、その後git連携を行います。

1. フォルダ作成
2. docker-compose.ymlの作成
3. Redmine起動
4. Redmine初期設定
5. bareリポジトリ設定
6. redmineプロジェクトにリポジトリ設定
7. (備考)所有者情報変更のバッチ化
8. (備考)コミットメッセージの参照キーワード

## 環境

- Windows 11 pro
- DS220+(Synology NAS)

## 1. フォルダ作成

最終的なフォルダ構成は以下となります。

```none
NAS:.
└─volume1
   └─docker
      └─redmine ← 今回使用するまとめフォルダ
          ├─git_repositories
          │  └─testrepository.git ← bareリポジトリ
          ├─postgres ← dockerマウント用フォルダ
          │  └─db-data
          ├─redmine ← dockerマウント用フォルダ
          │  ├─files
          │  ├─log
          │  ├─plugins
          │  └─public
          │     └─themes
          ├─docker-compose.yml
          ├─Dockerfile_postgres
          └─Dockerfile_redmine
```

まず、docker-compose.ymlの格納フォルダ、マウント用フォルダを作成します。

PowerShellでNASにSSH接続し、フォルダを作成します。

Docker用ユーザ名、IPアドレス、ポート番号は自身のものに置き換えてください。

ポート番号は\[NASログイン\]-\[コントロールパネル\]-\[端末とSNMP\]で設定している値です。

```bat
ssh user-docker@XXX.XXX.XXX.XXX -p XXXXX
cd /volume1/docker
mkdir ./redmine
mkdir ./redmine/redmine
mkdir ./redmine/redmine/files
mkdir ./redmine/redmine/log
mkdir ./redmine/redmine/plugins
mkdir ./redmine/redmine/public
mkdir ./redmine/redmine/public/themes
mkdir ./redmine/postgresql
mkdir ./redmine/postgresql/data
mkdir ./redmine/git_repositories
```

## 2. docker-compose.ymlの作成

データベースにはPostgreSQLを使用しています。

作成した`docker-compose.yml`は`/volume1/docker/redmine`に格納します。

```none
version: '3'
services:
    redmine:
        build:
            context: .
            dockerfile: Dockerfile_redmine
        container_name: redmine_main
        ports:
            - '33000:3000'
        expose:
            - '3000'
        volumes:
            - type: bind
              source: ./redmine/files
              target: /usr/src/redmine/files
            - type: bind
              source: ./redmine/log
              target: /usr/src/redmine/log
            - type: bind
              source: ./redmine/plugins
              target: /usr/src/redmine/plugins
            - type: bind
              source: ./redmine/public/themes
              target: /usr/src/redmine/public/themes
            - type: bind
              source: ./git_repositories
              target: /var/redmine/git_repositories
        restart: unless-stopped
        environment:
            REDMINE_DB_POSTGRES: postgres
            REDMINE_DB_DATABASE: redmine
            REDMINE_DB_USERNAME: redmine
            REDMINE_DB_PASSWORD: redmine
            REDMINE_PLUGINS_MIGRATE: "true"
            TZ: Asia/Tokyo
        links:
            - postgres

    postgres:
        build:
            context: .
            dockerfile: Dockerfile_postgres
        container_name: redmine_postgres
        volumes:
            - type: bind
              source: ./postgresql/data
              target: /var/lib/postgresql/data
        ports:
            - "54320:5432"
        restart: unless-stopped
        environment:
            POSTGRES_ROOT_PASSWORD: postgres
            POSTGRES_USER: redmine
            POSTGRES_PASSWORD: redmine
            POSTGRES_DATABASE: redmine
            TZ: Asia/Tokyo

volumes:
    redmine:
    postgres:
```

`Dockerfile_redmine`は作成する必要はありませんが、統一させたいので作成しています。

```none
FROM redmine:5.0.5
```

`Dockerfile_postgres`も作成し、`docker-compose.yml`と同フォルダに格納します。

```none
FROM postgres:15.2
RUN localedef -i ja_JP -c -f UTF-8 -A /usr/share/locale/locale.alias ja_JP.UTF-8
ENV LANG ja_JP.utf8
```

## 3. Redmine起動

Synology NASにログインし、Container Managerを起動します。

\[プロジェクト\]-\[作成\]を選択し、全般設定のプロジェクト名(任意)、パスを入力します。

パス項目に`/docker/redmine`を選択すると、格納済みの`docker-compose.yml`を読み込んでくれます。

![コンテナ設定](/images/synology_docker_redmine_git/04_container_manager.jpg)

Webポータル設定については特に利用しないためセットアップ無しにしています。

完了ボタンを押してターミナルに`Exit Code: 0`が表示されればRedmine起動成功です。

## 4. Redmine初期設定

Chrome等PCで`http://XXX.XXX.XXX.XXX:33000/`に接続し、Redmineにログインします。

初期設定だとログインIDとパスワードが共に`admin`だと思います。

新規パスワードを設定後、真っ先に左上タブの管理をクリックし、「デフォルト設定をロード」ボタンをクリックします。

この処理をすることでデフォルト設定をロードし、すぐにRedmineを扱う上で必要な設定が追加されます。

注意ですが、何か登録などしてしまうと「デフォルト設定をロード」ボタンは表示されなくなってしまうため、一番始めに実行しなければいけません。

![redmineデフォルト設定](/images/synology_docker_redmine_git/08_redmine.jpg)

最後にテストプロジェクトとチケットを登録します。

- プロジェクト名：testproject
- チケット１：初期チケット(#1)
- チケット２：修正チケット(#2)

## 5. bareリポジトリ設定

Redmineの中に複数のプロジェクトを登録して運用するため、`git_repositories`フォルダに各プロジェクトのbareリポジトリを格納するようにします。

その後、`git_repositories`フォルダ内にbareリポジトリを作成します。

今回は`testrepository.git`とします。

```powershell
ssh user-docker@XXX.XXX.XXX.XXX -p XXXXX
cd /volume1/docker/redmine/git_repositories
git init --bare --shared testrepository.git
```

次に、bareリポジトリにpushしていきます。

PC側でローカルリポジトリを作成します。

```powershell
# ファイル生成
cd D:/
mkdir ./testrepository
cd ./testrepository

# git初期設定
git init
git config --local user.name "kotememo"
git config --local user.email "kotememo@example.com"
```

Redmine側で表示できるか確認するために、テキストファイルをbareリポジトリにpushします。

```powershell
# 1回目テキストファイル helloworld.txt
# hello world
# redmine
# postgres

# 1回目コミット
git add .
git commit -m "初期コミット refs #1"
git remote add origin ssh://user-docker@XXX.XXX.XXX.XXX:XXXXX/volume1/docker/redmine/git_repositories/testrepository.git
git push -u origin master
```

コミットのコメントの`refs #1`はredmineのチケット番号との紐づけになります。

差分を参照できるかを確認するためにもう一度pushします。

```powershell
# 2回目テキストファイル helloworld.txt(2行目を修正)
# hello world
# redmine_docker
# postgres

# 2回目コミット
git add .
git commit -m "修正コミット refs #2"
git push -u origin master
```

コンテナ内の`testrepository.git`リポジトリを`redmine`が参照できるように所有者を変更します。

親フォルダ`git_repositories`の所有者を変更してもRedmineからは参照できず、個々のbareリポジトリに必要でした。

```bat
ssh user-docker@XXX.XXX.XXX.XXX -p XXXXX
docker exec -it redmine_main bash
cd /var/redmine/git_repositories
chown redmine:redmine -R testrepository.git
exit
```

ただし、再コミットしようとすると権限エラーになってしまうため、一時的に権限を戻す必要がありました。

`chown`で指定する所有者情報が分からない場合は、新規ファイルを配置しコンテナ内で`ls -l`コマンドを実行することで確認できます。

```bat
ssh user-docker@XXX.XXX.XXX.XXX -p XXXXX
docker exec -it redmine_main bash
cd /var/redmine/git_repositories
chown 1026:users -R testrepository.git
exit
```

最終的なフォルダは以下に記載しています。

Dockerコンテナ内にbareリポジトリをマウントするため、gitのbareリポジトリはdockerフォルダ内に生成します。

```none
NAS:.
└─volume1
   └─docker
      └─redmine ← 今回使用するまとめフォルダ
          ├─git_repositories
          │  └─testrepository.git ← bareリポジトリ
          ├─postgres ← dockerマウント用フォルダ
          │  └─db-data
          ├─redmine ← dockerマウント用フォルダ
          │  ├─files
          │  ├─log
          │  ├─plugins
          │  └─public
          │     └─themes
          ├─docker-compose.yml
          ├─Dockerfile_postgres
          └─Dockerfile_redmine
```

## 6. redmineプロジェクトにリポジトリ設定

先ほど作成したtestprojectプロジェクトを開き、\[設定\]-\[リポジトリ\]-\[新しいリポジトリ\]よりbareリポジトリのパスを設定します。

- バージョン管理システム：Git
- リポジトリのパス：`/var/redmine/git_repositories/testrepository.git`

![redmineリポジトリ設定](/images/synology_docker_redmine_git/09_redmine_repository_setting.jpg)

設定すると\[testproject\]-\[リポジトリ\]を開くとコミット情報を参照できます。

![redmineリビジョン](/images/synology_docker_redmine_git/10_redmine_revision.jpg)

`#`欄のリビジョンIDを選択すると関連チケットやファイル内の差分を表示できます。

![redmineリビジョン差分](/images/synology_docker_redmine_git/11_redmine_diff.jpg)

これでコミットした内容の差分をredmineチケットと紐づけて確認できるようになりました。

bareリポジトリの所有者情報には手こずりましたが無事に設定できました。

## 7. (備考)所有者情報変更のバッチ化

bareリポジトリにpushする度に所有者情報を変更するのは面倒なため、所有者変更とpushをまとめてバッチ化するのもいいかもしれません。

ただし、バッチを用いてssh接続する場合は[公開鍵の設定等](https://kb.synology.com/ja-jp/DSM/tutorial/How_to_log_in_to_DSM_with_key_pairs_as_admin_or_root_permission_via_SSH_on_computers)を実施してパスワードレス状態でssh接続できるようにする必要があります。

- command.bat
```bat
SET COMMITMESSAGE="test commit refs #1"

SET USERNAME=user-docker
SET IP=XXX.XXX.XXX.XXX
SET PORT=XXXXX

git add .
git commit -m %COMMITMESSAGE%

rem dockerコンテナ内のbareリポジトリの所有者をユーザに変更
ssh -tt %USERNAME%@%IP% -p %PORT% < command_ssh_start.sh

git push -u origin master

rem dockerコンテナ内のbareリポジトリの所有者をredmineに変更
ssh -tt %USERNAME%@%IP% -p %PORT% < command_ssh_end.sh

pause
```

- command_ssh_start.sh
```sh
cd /volume1/docker/redmine/git_repositories
docker exec -w /var/redmine/git_repositories redmine_main chown 1026:users -R testrepository.git;exit
```

- command_ssh_end.sh
```sh
cd /volume1/docker/redmine/git_repositories
docker exec -w /var/redmine/git_repositories redmine_main chown redmine:redmine -R testrepository.git;exit
```

## 8. (備考)コミットメッセージの参照キーワード

commitする際に`refs #1`のキーワードでチケット番号を紐づけていました。

この`refs`についてはredmineの\[管理\]-\[設定\]-\[リポジトリ\]の参照用キーワードで設定できます。

![commit参照キーワード](/images/synology_docker_redmine_git/12_redmine_commit_keyword.jpg)


 DS220+(Synology NAS)を購入したので、DockerでRedmineを起動し、NAS内のbareリポジトリと連携しました。...

【Synology NAS】DockerでRedmineを起動しローカルgit連携


## はじめに

普段は数秒で完了するクエリが突然数分レベルで極端に遅くなった時に、直前にautoanalyzeされていないか調査することがあります。

もしクエリ性能が悪くなる直前にautoanalyzeされている場合は、暫定対応として手動でanalyzeしてみると解消する場合があります。

## 概要

1. 統計情報コレクタ`pg_stat_user_tables`ビュー
   <br>autovacuum、autoanalyzeの最終更新時刻を取得

## 環境

- PostgreSQL 14.6

## 1. 統計情報コレクタpg_stat_user_tablesビュー

PostgreSQLデータベース上に用意されている[pg_stat_user(all)_tables](https://www.postgresql.jp/docs/9.4/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW)ビューでvacuumやanalyzeされた最終更新時刻を確認することができます。

```SQL
--統計情報の最終更新時刻
select
    schemaname    --スキーマ名
    , relname     --テーブル名
    , n_live_tup    --有効レコード行の推定値
    , n_dead_tup    --不要レコード行の推定値
    , pg_stat_user_tables.last_vacuum    --手動vacuum最終時刻
    , pg_stat_user_tables.last_autovacuum    --自動vacuum最終時刻
    , pg_stat_user_tables.last_analyze    --手動analyze最終時刻
    , pg_stat_user_tables.last_autoanalyze    --自動analyze最終時刻
from
    pg_stat_user_tables
-- where
--     relname = 'テーブル名'
;
```
データベースが自動でautoanalyzeすることでクエリの実行計画が悪化してしまい、クエリ性能が突然悪化することがあります。

手動で関連テーブルをanalyzeしてみて解消された場合はautoanalyzeが原因と考えられます。

ただし根本原因としてはテーブルのインデックスの設定が悪かったり、SQLのテーブル結合に不足があってインデックスが適切に使用されないことだと思われます。

そのため、最終的にはインデックスの確認やSQLチューニングをする必要があると思います。


 普段は数秒で完了するクエリが突然数分レベルで極端に遅くなった時に、直前にautoanalyzeされていないか調査することがあります。もしク...

【PostgreSQL】統計情報の最終更新時刻を確認する


## はじめに

クロスプラットフォーム開発でFlutterを見つけたのでPostgreSQLへの接続をテストしました。

AndroidスマホのFlutterアプリからローカルネット内のPostgreSQLにinsert/selectしてみました。

Flutterのサンプルアプリの＋ボタンに紐づけてデバッグコンソールでinsert/select出来ることを確認しました。

## 概要

試した時点ではpostgresql2パッケージでのPostgreSQLパスワード方式がmd5のみ対応だったため、PostgreSQL側も設定変更します。

1. PostgreSQL設定
2. Flutterソース
3. 実行結果

## 環境

Windows 11
- PostgreSQL 14.6-1

Androidアプリ
- Flutter 3.10.0-6.0.pre.19
- [postgresql2](https://pub.dev/packages/postgresql2) 1.0.4 (dart package)

## 1. PostgreSQL設定

PostgreSQLのバージョンによってはパスワードの暗号方式がscram-sha-256になっています。

しかし、postgresql2パッケージではmd5のみ対応のようですので設定変更をします。

同時に外部アクセスできる設定も行います。

### 1.1. postgresql.conf

localhost以外からもPostgreSQLに接続できる設定、パスワードの暗号化方式の設定を変更します。

```none
# - Connection Settings -
listen_addresses = '*'

# - Authentication -
password_encryption = md5
```

### 1.2. pg_hba.conf

パスワード認証方式の変更、接続元IPアドレスにローカルネットを追加します。

```none
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.0.0/16            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
```

### 1.3. ファイアウォール設定

`[コントロールパネル>Windows ツール>セキュリティが強化されたWindows Defender ファイアウォール]`のWindows設定で、PostgreSQLの使用ポートを開放します。

1. 受信の規則>新しい規則

PostgreSQLの使用ポートを開放します。

- 規則の種類：ポート
- プロトコルおよびポート：TPC、特定のローカルポート「5432」※PostgreSQL起動時のポート番号
- 操作：接続を許可する
- プロファイル：テストなので全部にチェック「ドメイン、プライベート、パブリック」
- 名前：PostgreSQL※好きな名前で良いため分かりやすくPostgreSQLにしています。

2. 送信の規則>新しい規則

受信と同じ設定を送信側にも設定します。

- 規則の種類：ポート
- プロトコルおよびポート：TPC、特定のローカルポート「5432」※PostgreSQL起動時のポート番号
- 操作：接続を許可する
- プロファイル：テストなので全部にチェック「ドメイン、プライベート、パブリック」
- 名前：PostgreSQL※好きな名前で良いため分かりやすくPostgreSQLにしています。

### 1.4. テーブル情報

＋ボタンをタップした際に繰り上がる数字と、その時の時刻を保存するテーブルを作成します。

今回きりのtestdbデータベースにテーブル作成しています。

```none
create table testtable (count numeric, updatetime timestamp);
```

## 2. Flutterソース

### 2.1. postgresql2パッケージ

pubspec.yamlに[postgresql2](https://pub.dev/packages/postgresql2)を追加し、`Pub get`します。

```none
dependencies:
  flutter:
    sdk: flutter

  # 追加
  postgresql2:
```

### 2.2. testdao.dart

PostgreSQLとの通信をするdartファイル(lib/testdao.dart)を新規作成します。

デバッグコンソール上でinsert/select出来ていることを確認するため、サンプルからinsert/select文を引っ張ってきています。

``` dart
import 'package:postgresql2/postgresql.dart';

class TestDao {
  static const String username = 'postgres';
  static const String password = 'postgres';
  static const String host = '192.168.0.19';
  static const String port = '5432';
  static const String database = 'testdb';
  static const String uri = 'postgres://$username:$password@$host:$port/$database';

  // select文
  Future<void> selectSql() async {
    var sql = "select * from testtable;";

    connect(uri).then((conn) {
      conn.query(sql).toList()
          .then((result) {
            print('result: $result');
          })
          .whenComplete(() {
            conn.close();
          });
    });
  }

  //insert文
  Future<void> insertSql(int count, DateTime updatetime) async {
    var sql = "insert into testtable values (@count, @updatetime);";

    connect(uri).then((conn) {
      conn.query(sql, {'count': count, 'updatetime': updatetime}).toList()
          .then((_) {
            print('done.');
          })
          .whenComplete(() {
            conn.close();
          });
    });
  }
}
```

### 2.2. main.dart

＋ボタンに紐づいている`_incrementCounter()`にinsert/select文を追加します。

非同期関数でそこまで考えていないためinsert/selectが順番に処理されるとは限りません。

```dart
import 'package:flutter/material.dart';
import './testdao.dart';  // 追加

//省略......

class _MyHomePageState extends State<MyHomePage> {
  int _counter = 0;
  TestDao dao = TestDao();  // 追加

  void _incrementCounter() {
    setState(() {
      // This call to setState tells the Flutter framework that something has
      // changed in this State, which causes it to rerun the build method below
      // so that the display can reflect the updated values. If we changed
      // _counter without calling setState(), then the build method would not be
      // called again, and so nothing would appear to happen.
      _counter++;
      dao.insertSql(_counter, DateTime.now());  // 追加
      dao.selectSql();  // 追加
    });
  }

  //省略......
}

```

## 3. 実行結果

＋ボタンを5回タップし、その時のコンソール出力を確認しました。

`done`がinsert完了で、`result`がselect結果となります。

insert/selectの順番はバラバラですが、select結果にカウント数字と時刻がセットされていることが確認できます。

ただし、表示されているタイムスタンプが実際よりもずれています。

```none
Syncing files to device SOV40...
I/flutter (23040): done.
I/flutter (23040): result: [[1.0, 2023-07-03 02:29:15.849426]]
I/flutter (23040): result: [[1.0, 2023-07-03 02:29:15.849426]]
I/flutter (23040): done.
I/flutter (23040): result: [[1.0, 2023-07-03 02:29:15.849426], [2.0, 2023-07-03 02:29:19.368834]]
I/flutter (23040): done.
I/flutter (23040): result: [[1.0, 2023-07-03 02:29:15.849426], [2.0, 2023-07-03 02:29:19.368834], [3.0, 2023-07-03 02:29:21.894974]]
I/flutter (23040): done.
I/flutter (23040): result: [[1.0, 2023-07-03 02:29:15.849426], [2.0, 2023-07-03 02:29:19.368834], [3.0, 2023-07-03 02:29:21.894974], [4.0, 2023-07-03 02:29:27.428673]]
I/flutter (23040): done.
```

psqlでselectしてみても値がセットされていることを確認できます。

psql側のほうが日本時刻としては正しいです。

```none
testdb=# select * from testtable;
 count |         updatetime
-------+----------------------------
     1 | 2023-07-02 17:29:15.849426
     2 | 2023-07-02 17:29:19.368834
     3 | 2023-07-02 17:29:21.894974
     4 | 2023-07-02 17:29:27.428673
     5 | 2023-07-02 17:29:29.721692
(5 行)
```


 クロスプラットフォーム開発でFlutterを見つけたのでPostgreSQLへの接続をテストしました。AndroidスマホのFlutter...

【Flutter】ローカルネット内のPostgreSQLに接続する方法


## はじめに

chatGPTはオンライン必須のため、オフラインで実行可能なcharRWKVを導入してみました。

GPUを使用することを前提にしています。

日本語が含まれているモデルもあるようなので今後が楽しみですね。

## 概要

事前にインストール済みのものもあったため、余分なものも入っているかもしれません。

1. pythonインストール
2. Visual Studio 2022インストール
3. CUDA Toolkitインストール
4. chatRWKVインストール
5. モデルダウンロード
6. ソースコード修正
7. pipインストール
8. 起動

## 環境

- Windows 11 Pro
- NVIDIA GeForce RTX 4070 Ti (VRAM 12GB)
- 12th Gen Intel(R) Core(TM) i7-12700 (RAM 16GB)

## 1. pythonインストール

3.10系であれば大丈夫そうです。

今回は手元にあった[WinPython](https://sourceforge.net/projects/winpython/files/WinPython_3.10/3.10.9.0/)を使用しています。

Winpython64-3.10.9.0.exeをダウンロードし、実行すると任意の場所に解凍できます。

その中の`WPy64-31090\python-3.10.9.amd64`内に`python`があり、`WPy64-31090\python-3.10.9.amd64\Scripts`内に`pip`があります。

## 2. Visual Studio 2022インストール

こちらも事前にインストール済みだったため余分なものも含まれていると思います。

C++によるデスクトップ開発のインストール時オプションに、デフォルトの中にWindows用C++CMakeツールが含まれていればいいと思います。

![VisualStudio画像](/images/install_chatRWKV/visual_studio_2022_c++.png)

## 3. CUDA Toolkitインストール

pytorchのCUDA版を使用するためにインストールしました。

[ChatRWKVのGitHub](https://github.com/BlinkDL/ChatRWKV)では11.7をインストールするよう記載があったため11.7をインストールしています。

[CUDA Toolkitダウンロードページ](https://developer.nvidia.com/cuda-11-7-0-download-archive?target_os=Windows&target_arch=x86_64&target_version=11&target_type=exe_local)よりexe(local)をダウンロード、実行しインストールしました。

## 4. chatRWKVインストール

[GitHubページ](https://github.com/BlinkDL/ChatRWKV)よりソースコードを取得します。

## 5. モデルダウンロード

[rwkv-4-ravenモデル](https://huggingface.co/BlinkDL/rwkv-4-raven/tree/main)から日本語学習されたモデルをダウンロードします。

1B、3B、7B、14Bがありますが、[ChatRWKVのGitHub](https://github.com/BlinkDL/ChatRWKV)のStrategy Guideから判断します。

VRAM12GBで動きそうなものが`cuda fp16i8(VRAM 9G for 7B model.)`であるため、7Bのモデルをダウンロードします。

今回は`RWKV-4-Raven-7B-v10-Eng89%-Jpn10%-Other1%-20230420-ctx4096.pth`をダウンロードし、%エスケープが面倒なため`RWKV-4-Raven-7B-v10-Eng89-Jpn10-Other1-20230420-ctx4096.pth`に名前変更しています。

最後に、選択したStrategyに従ってモデルを変換します。

```batch
PATH=%PATH%;D:\src\python\ChatRWKV\WPy64-31090\python-3.10.9.amd64;
cd D:\src\python\ChatRWKV\ChatRWKV-main
python .\v2\convert_model.py --in .\model\RWKV-4-Raven-7B-v10-Eng89-Jpn10-Other1-20230420-ctx4096.pth --out .\model\raven7B-v10-20230420-cudafp16i8.pth --strategy 'cuda fp16i8'
```

## 6. ソースコード修正

`.\v2\chat.py`を修正します。

1. Strategy

`cuda fp16i8`に変更しました。

```python
変更前
# args.strategy = 'cpu fp32'
args.strategy = 'cuda fp16'
# args.strategy = 'cuda:0 fp16 -> cuda:1 fp16'
# args.strategy = 'cuda fp16i8 *10 -> cuda fp16'
# args.strategy = 'cuda fp16i8'
# args.strategy = 'cuda fp16i8 -> cpu fp32 *10'
# args.strategy = 'cuda fp16i8 *10+'
↓
変更後
# args.strategy = 'cpu fp32'
# args.strategy = 'cuda fp16'
# args.strategy = 'cuda:0 fp16 -> cuda:1 fp16'
# args.strategy = 'cuda fp16i8 *10 -> cuda fp16'
args.strategy = 'cuda fp16i8'
# args.strategy = 'cuda fp16i8 -> cpu fp32 *10'
# args.strategy = 'cuda fp16i8 *10+'
```

2. RWKV_CUDA_ON

GPUを使用するように変更します。

```python
変更前
os.environ["RWKV_CUDA_ON"] = '0' # '1' to compile CUDA kernel (10x faster), requires c++ compiler & cuda libraries
↓
変更後
os.environ["RWKV_CUDA_ON"] = '1' # '1' to compile CUDA kernel (10x faster), requires c++ compiler & cuda libraries
```

3. チャット言語

日本語チャットを使用するように変更します。

```python
変更前
CHAT_LANG = 'English' # English // Chinese // more to come
↓
# CHAT_LANG = 'English' # English // Chinese // more to come
CHAT_LANG = 'Japanese' # English // Chinese // more to come
```

4. モデルのパス

ダウンロード、変換をしたモデルを参照するように変更します。

```python
変更前
if CHAT_LANG == 'English':
    args.MODEL_NAME = '/fsx/BlinkDL/HF-MODEL/rwkv-4-raven/RWKV-4-Raven-14B-v9-Eng99%-Other1%-20230412-ctx8192'
    # args.MODEL_NAME = '/fsx/BlinkDL/HF-MODEL/rwkv-4-raven/RWKV-4-Raven-7B-v10-Eng99%-Other1%-20230418-ctx8192'
    # args.MODEL_NAME = '/fsx/BlinkDL/HF-MODEL/rwkv-4-pile-14b/RWKV-4-Pile-14B-20230313-ctx8192-test1050'

elif CHAT_LANG == 'Chinese': # Raven系列可以对话和 +i 问答。Novel系列是小说模型，请只用 +gen 指令续写。
    args.MODEL_NAME = '/fsx/BlinkDL/HF-MODEL/rwkv-4-raven/RWKV-4-Raven-7B-v9x-Eng49%-Chn50%-Other1%-20230418-ctx4096'
    # args.MODEL_NAME = '/fsx/BlinkDL/HF-MODEL/rwkv-4-novel/RWKV-4-Novel-7B-v1-ChnEng-20230409-ctx4096'

elif CHAT_LANG == 'Japanese':
    args.MODEL_NAME = '/fsx/BlinkDL/HF-MODEL/rwkv-4-raven/RWKV-4-Raven-14B-v8-EngAndMore-20230408-ctx4096'
    # args.MODEL_NAME = '/fsx/BlinkDL/HF-MODEL/rwkv-4-raven/RWKV-4-Raven-7B-v9-Eng86%-Chn10%-JpnEspKor2%-Other2%-20230414-ctx4096'
↓
変更後※パスは'\'ではなく'/'を使用する。また拡張子'.pth'は除く
if CHAT_LANG == 'English':
    # args.MODEL_NAME = '/fsx/BlinkDL/HF-MODEL/rwkv-4-raven/RWKV-4-Raven-14B-v9-Eng99%-Other1%-20230412-ctx8192'
    # args.MODEL_NAME = '/fsx/BlinkDL/HF-MODEL/rwkv-4-raven/RWKV-4-Raven-7B-v10-Eng99%-Other1%-20230418-ctx8192'
    # args.MODEL_NAME = '/fsx/BlinkDL/HF-MODEL/rwkv-4-pile-14b/RWKV-4-Pile-14B-20230313-ctx8192-test1050'
    args.MODEL_NAME = './model/raven7B-v10-20230420-cudafp16i8'

elif CHAT_LANG == 'Chinese': # Raven系列可以对话和 +i 问答。Novel系列是小说模型，请只用 +gen 指令续写。
    # args.MODEL_NAME = '/fsx/BlinkDL/HF-MODEL/rwkv-4-raven/RWKV-4-Raven-7B-v9x-Eng49%-Chn50%-Other1%-20230418-ctx4096'
    # args.MODEL_NAME = '/fsx/BlinkDL/HF-MODEL/rwkv-4-novel/RWKV-4-Novel-7B-v1-ChnEng-20230409-ctx4096'
    args.MODEL_NAME = './model/raven7B-v10-20230420-cudafp16i8'

elif CHAT_LANG == 'Japanese':
    # args.MODEL_NAME = '/fsx/BlinkDL/HF-MODEL/rwkv-4-raven/RWKV-4-Raven-14B-v8-EngAndMore-20230408-ctx4096'
    # args.MODEL_NAME = '/fsx/BlinkDL/HF-MODEL/rwkv-4-raven/RWKV-4-Raven-7B-v9-Eng86%-Chn10%-JpnEspKor2%-Other2%-20230414-ctx4096'
    args.MODEL_NAME = './model/raven7B-v10-20230420-cudafp16i8'
```

## 7. pipインストール

必要なライブラリをインストールします。

pytorchはcuda版をインストールします。

```batch
python -m pip install --upgrade pip
pip install numpy
pip install ninja
pip install rwkv
pip install -r requirements.txt
pip install torch==2.0.0+cu117 -f https://download.pytorch.org/whl/torch_stable.html
```

## 8. 起動

`.\v2\chat.py`を実行することでchatRWKVボットと対話ができます。

```batch
set RWKV_CUDA_ON=1

set PATH=%PATH%D:\src\python\ChatRWKV\WPy64-31090\python-3.10.9.amd64;
set PATH=%PATH%D:\src\python\ChatRWKV\WPy64-31090\python-3.10.9.amd64\Scripts;
set PATH=%PATH%C:\Program Files\Microsoft Visual Studio\2022\Community\VC\Tools\MSVC\14.35.32215\bin\Hostx64\x64;

cd /d D:\src\python\ChatRWKV\ChatRWKV-main
python .\v2\chat.py
```

自分側がBob、chatRWKVボット側がAliceです。

ブログを書くポイントについて聞き、次の質問では1番についてと曖昧な聞き方をしていますが文脈に合った返答が来ています。

```none
Bob: ブログを書く上で気を付けるべきポイントは？
Alice: ブログを書く上で気を付けるべきポイントとして、以下が挙げられます。
1. フォーマットを整える
ブログを書く上で重要なのは、フォーマットを整えることです。フォーマットは、記事のタイトルや見出し、ヘッダーなど、どのような情報を掲載するかによって異なります。
2. コンテンツを考える
ブログを書く上で大切なのは、コンテンツを考えることです。ブログのコンテンツは、主題に関するものである必要があります。
3. スタイルを選択する
ブログのスタイルを選択することも大切です。読者がブログを視聴する場合、フォーマットに合わせたスタイルが選択されます。
4. コンテンツの流れを作る
ブログのコンテンツは、読者が楽しめるように作成されています。また、情報を提供することで、読者に対して自分自身と向き合うような作り方になります。
5. 自己啓発に取り組む
ブログを書く上で大切なのは、自己啓発に取り組むことです。ブログの内容に合わせて、継続的に情報収集や学習を行うことが大切です。
6. 適切な表現力を持つ
ブログで書く際には、適切な表現力が必要です。特に、フォーマットやコンテンツについての解説文などでは、適切な表現力が必要です。
7. ストーリー性を重視する
ブログはストーリー性を持ちましょう。内容が面白くてもストーリー性がないと読者は飽きてしまいます。
8. 継続的に更新する
ブログは日々更新されるものであるため、継続的に更新することが大切です。人気のある記事やエントリーなども交えて、定期的に更新しましょう。
これらのポイントを守ることで、ブログを続けていくことができます。

Bob: 1について詳しく説明してください。
Alice: 1. フォーマットを適切に調整する
ブログのフォーマットは、自分のブログで流れるように設定することが重要です。フォーマットが決まれば、記事を書くだけでなく、作成後もメンバーとのやり取りもスムーズになります。また、フォーマットを選択する際には、視覚的にわかりやすくなるように、スタイルを選択することも大切です。例えば、フォントの大きさやレイアウトなどが違います。

Bob: フォントの大きさはどれくらいが好ましいですか？
Alice: フォントの大きさは、ブログの視覚的な美しさに影響を与えます。フォントの大きさを選ぶ際には、読みやすさと読みやすさ
のバランスを考慮することが重要です。一般的に、フォントの大きさは12ptから14pt程度が最適であるとされています。また、フォントのカラーも考慮して選択することがお勧めです。例えば、ブログのテーマやコンテンツによって適切なフォントを選ぶことが必要になる場合があります。
```

少々違和感もありますが、大枠としては対話できているように思います。

AIの出力結果を鵜呑みにするのは良くないと思いつつも、いくつかはグサッと来るので反省ですね。

また`chat.py`の後半部分にチャットコマンドの説明がありました。

```python
直接入力 --> ボットとチャットする．改行には\\nを使用してください．
+ --> ボットに前回のチャットの内容を変更させる．
+reset --> 対話のリセット．メモリをリセットするために，+resetを定期的に実行してください．

+i インストラクトの入力 --> チャットの文脈を無視して独立した質問を行う．改行には\\nを使用してください．
+gen プロンプトの生成 --> チャットの文脈を無視して入力したプロンプトに続く文章を出力する．改行には\\nを使用してください．
+++ --> +gen / +i の出力の回答を続ける．
++ --> +gen / +i の出力の再生成を行う.

ボットとの会話を楽しんでください。また、定期的に+resetして、ボットのメモリをリセットすることを忘れないようにしてください。
```

例えば対話の途中で`+reset`と応答すると、対話内容をリセットできます。

```none
Bob: +reset
Alice: Chat reset.
```


 chatGPTはオンライン必須のため、オフラインで実行可能なcharRWKVを導入してみました。GPUを使用することを前提にしています。日...

【Windows】chatCWKVをローカルで実行してみた


## はじめに

遅いSQLを検知するために`log_min_duration_statement`パラメータを設定していましたが、実行時間は分かりますが実行当時の実行計画はログに出力されていません。

そのため、後からの調査・改善が困難になる場合があります。

調べたところ`auto_explain`モジュールを利用することで、遅いSQLの実行計画を自動でログに出力できるようなため試してみました。

## 概要

実行時間が一定時間を超えた場合にSQL文をログ出力する`log_min_duration_statement`パラメータはOFFにしておきます。

先に`auto_explain`を試した後に2つのパラメータを同時に設定した場合の挙動を確認します。

1. セッション単位の設定
2. 全体設定
3. `log_min_duration_statement`パラメータと併用するための注意点

## 環境

- Windows 10 Home
- PostgreSQL 14.6

## 1. セッション単位の設定

スーパーユーザのみがセッション中にパラメータを変更できるようです。

以下のSQLを実行し、セッション終了後の別セッションでもう一度実行したところ、パラメータ変更は保持されていませんでした。

そのため、個別のSQLのみ調査対象としたい場合に有効と考えられます。

実行SQL
```sql
SELECT * FROM testtable;

LOAD 'auto_explain';
SET auto_explain.log_min_duration = '0ms';
SET auto_explain.log_analyze = true;

SELECT * FROM testtable;
```

`auto_explain`を設定した後のSQL文のみログ出力されており、2回目セッションの1文目はログ出力されていませんでした。

```none
2023-04-05 21:24:50.205 JST [19940] LOG:  database system is ready to accept connections
2023-04-05 21:25:40.657 JST [14520] LOG:  duration: 0.026 ms  plan:
	Query Text: SELECT * FROM testtable;
	Seq Scan on testtable  (cost=0.00..1.01 rows=1 width=5) (actual time=0.016..0.017 rows=1 loops=1)
2023-04-05 21:31:02.901 JST [19780] LOG:  duration: 0.026 ms  plan:
	Query Text: SELECT * FROM testtable;
	Seq Scan on testtable  (cost=0.00..1.01 rows=1 width=5) (actual time=0.016..0.017 rows=1 loops=1)
```

## 2. 全体設定

全体設定は`data\postgresql.conf`で設定します。

全てのSQLに対して適用されますが、その分ログ出力するため全体的な負荷は上がると思われます。

```batch
# postgresql.conf

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# Add settings for extensions here

session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '0ms'
auto_explain.log_analyze = true;
```

## 3. `log_min_duration_statement`パラメータと併用するための注意点

`data\postgresql.conf`で両方のパラメータを設定します。

この状態でSQL文を実行した場合、`auto_explain`の次に標準のログが出力されていました。

```batch
# postgresql.conf

#------------------------------------------------------------------------------
# REPORTING AND LOGGING
#------------------------------------------------------------------------------

log_min_duration_statement = 0

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# Add settings for extensions here

session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '0ms'
auto_explain.log_analyze = true;
```

`auto_explain`のログ出力に時間がかかるためか2個目の通常ログの実行時間が長くなっています。

```none
2023-04-05 21:48:10.924 JST [18704] LOG:  duration: 0.074 ms  plan:
	Query Text: select * from testtable;
	Seq Scan on testtable  (cost=0.00..1.01 rows=1 width=5) (actual time=0.048..0.051 rows=1 loops=1)
2023-04-05 21:48:10.926 JST [18704] LOG:  duration: 9.262 ms  statement: select * from testtable;
```



 遅いSQLを検知するために`log_min_duration_statement`パラメータを設定していましたが、実行時間は分かりますが実...

【PostgreSQL】SQL実行時の実行計画を自動でログ出力する設定


## はじめに

セッション中のみ保持される一時テーブルが別セッションでも同名で作成できるのか試してみました。

結論としては別セッションであれば作成できるようです。

通常テーブルと各セッションの一時テーブルでそれぞれ登録先のスキーマが異なりました。

## 概要

2個のセッションで同時接続し、トランザクション中に同名テーブル作成を試しました。

各パターンは試した後にテーブルを削除しています。

| &nbsp;&nbsp;&nbsp;&nbsp;後セッション<br>先セッション   | 通常テーブル        | 一時テーブル         |
|:------------|:----------------:|:-------------------:|
| 通常テーブル    | ×             | ○               |
| 一時テーブル    | ○             | ○<br>元々これを確認したかった               |

1. ×【先】通常テーブル作成→【後】通常テーブル作成
2. ○【先】通常テーブル作成→【後】一時テーブル作成
3. ○【先】一時テーブル作成→【後】通常テーブル作成
4. ○【先】一時テーブル作成→【後】一時テーブル作成

## 環境

- Windows 10 Home
- PostgreSQL 14.6

## 1. ×【先】通常テーブル作成→【後】通常テーブル作成

別セッションでテーブル作成する時は待ち状態になります。
commit後はテーブル重複でエラーになります。

セッション1 (通常テーブル作成)
```sql
postgres=# begin;
BEGIN
postgres=*# create table testtable (id numeric);
CREATE TABLE
postgres=*#
```

セッション2 (通常テーブル作成時に待ち状態で待機)
```sql
postgres=# begin;
BEGIN
postgres=*# create table testtable (id numeric);
（待ち状態）
```

セッション1 (commit)
```sql
postgres=*# commit;
COMMIT
postgres=#
```

セッション2 (待ち状態解放、エラー)
```sql
postgres=*# create table testtable (id numeric);
ERROR:  duplicate key value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(testtable, 2200) already exists.
postgres=!#
```

## 2. ○【先】通常テーブル作成→【後】一時テーブル作成

作成可能ですがスキーマがpublicとpg_tempに分かれます。

セッション1 (通常テーブルがpublicスキーマに作成)
```sql
postgres=# begin;
BEGIN
postgres=*# create table testtable (id numeric);
CREATE TABLE
postgres=*# \dt
              リレーション一覧
 スキーマ |   名前    |  タイプ  |  所有者
----------+-----------+----------+----------
 public   | testtable | テーブル | postgres
(1 行)


postgres=*# insert into testtable values(1);
INSERT 0 1
postgres=*#
```

セッション2 (一時テーブルはpg_temp_4スキーマに作成)
```sql
postgres=# begin;
BEGIN
postgres=*# create temporary table testtable (id numeric);
CREATE TABLE
postgres=*# \dt
              リレーション一覧
 スキーマ  |   名前    |  タイプ  |  所有者
-----------+-----------+----------+----------
 pg_temp_4 | testtable | テーブル | postgres
(1 行)


postgres=*# insert into testtable values(2);
INSERT 0 1
postgres=*# select * from testtable;
 id
----
  2
(1 行)


postgres=*# \dt public.*
"public.*"という名前のリレーションは見つかりませんでした。
postgres=*#
```

セッション1 (commit)
```sql
postgres=*# commit;
COMMIT
postgres=#
```

セッション2 (スキーマの指定無しだと一時テーブルのほうを参照)
```sql
postgres=*# \dt public.*
              リレーション一覧
 スキーマ |   名前    |  タイプ  |  所有者
----------+-----------+----------+----------
 public   | testtable | テーブル | postgres
(1 行)


postgres=*# \dt
              リレーション一覧
 スキーマ  |   名前    |  タイプ  |  所有者
-----------+-----------+----------+----------
 pg_temp_4 | testtable | テーブル | postgres
(1 行)


postgres=*# select * from public.testtable;
 id
----
  1
(1 行)


postgres=*# select * from testtable;
 id
----
  2
(1 行)


postgres=*#
```

## 3. ○【先】一時テーブル作成→【後】通常テーブル作成

こちらも作成時にスキーマがpublicとpg_tempに分かれます。

セッション1 (一時テーブル作成)
```sql
postgres=# begin;
BEGIN
postgres=*# create temporary table testtable (id numeric);
CREATE TABLE
postgres=*# \dt
              リレーション一覧
 スキーマ  |   名前    |  タイプ  |  所有者
-----------+-----------+----------+----------
 pg_temp_3 | testtable | テーブル | postgres
(1 行)


postgres=*# insert into testtable values(1);
INSERT 0 1
postgres=*# select * from testtable;
 id
----
  1
(1 行)


postgres=*#
```

セッション2 (通常テーブル作成)
```sql
postgres=# begin;
BEGIN
postgres=*# create table testtable (id numeric);
CREATE TABLE
postgres=*# \dt
              リレーション一覧
 スキーマ |   名前    |  タイプ  |  所有者
----------+-----------+----------+----------
 public   | testtable | テーブル | postgres
(1 行)


postgres=*# insert into testtable values(2);
INSERT 0 1
postgres=*# select * from testtable;
 id
----
  2
(1 行)


postgres=*#
```

セッション2 (先に通常テーブル作成のセッションBをcommit)
```sql
postgres=*# commit;
COMMIT
postgres=#
```

セッション1 (やはりスキーマの指定無しでは一時テーブルが優先されている)
```sql
postgres=*# select * from testtable;
 id
----
  1
(1 行)


postgres=*# select * from public.testtable;
 id
----
  2
(1 行)


postgres=*#
```

## 4. ○【先】一時テーブル作成→【後】一時テーブル作成

一時テーブル作成時にpg_temp_3とpg_temp_4と異なるスキーマに分かれます。

セッション1 (一時テーブル作成)
```sql
postgres=# begin;
BEGIN
postgres=*# create temporary table testtable (id numeric);
CREATE TABLE
postgres=*# \dt
              リレーション一覧
 スキーマ  |   名前    |  タイプ  |  所有者
-----------+-----------+----------+----------
 pg_temp_3 | testtable | テーブル | postgres
(1 行)


postgres=*# insert into testtable values(1);
INSERT 0 1
postgres=*# select * from testtable;
 id
----
  1
(1 行)


postgres=*#
```

セクション2 (一時テーブル作成)
```sql
postgres=# begin;
BEGIN
postgres=*# create temporary table testtable (id numeric);
CREATE TABLE
postgres=*# \dt
              リレーション一覧
 スキーマ  |   名前    |  タイプ  |  所有者
-----------+-----------+----------+----------
 pg_temp_4 | testtable | テーブル | postgres
(1 行)


postgres=*# insert into testtable values(2);
INSERT 0 1
postgres=*# select * from testtable;
 id
----
  2
(1 行)


postgres=*#
```

セクション1 (commit)
```sql
postgres=*# commit;
COMMIT
postgres=#
```

セクション2 (スキーマ指定無しだとセクション2で作成した一時テーブルを参照)
```sql
postgres=*# \dt
              リレーション一覧
 スキーマ  |   名前    |  タイプ  |  所有者
-----------+-----------+----------+----------
 pg_temp_4 | testtable | テーブル | postgres
(1 行)


postgres=*# select * from testtable;
 id
----
  2
(1 行)


postgres=*#
```

ちなみに別セッションの一時テーブルの存在は確認できますが、selectは出来ないです。

セッション2 (別セッションの一時テーブルの中身は覗けない)
```sql
postgres=*# \dt pg_temp_3.*
              リレーション一覧
 スキーマ  |   名前    |  タイプ  |  所有者
-----------+-----------+----------+----------
 pg_temp_3 | testtable | テーブル | postgres
(1 行)


postgres=*# select * from pg_temp_3.testtable;
ERROR:  cannot access temporary tables of other sessions
postgres=!#
```



 セッション中のみ保持される一時テーブルが別セッションでも同名で作成できるのか試してみました。結論としては別セッションであれば作成できるよう...

【PostgreSQL】一時テーブルは別セッションで同時に作成できるか


## はじめに

バイナリ版の組み合わせでRedmineを起動できるようにしました。

Dockerを利用したほうがいいと思いながらも、そもそもインストールに制限がある等の状況を想定しています。

(追記 2023/1/6)

始めはJRubyでRedmineを起動しましたが、Git連携が上手くいかなかったためRubyInstallerで入れ直しました。

そのため、Rubyのパッケージを取得するためのMSYS2のみレジストリにインストールしています。

Redmineの起動まで成功すればMSYS2の無い別環境にもファイルコピーするだけで起動できるはずです。

## 環境

- Windows 10 Home
- 本体 → Redmine 4.2.9
- Ruby → ~~JRuby 9.3.9.0~~ (→RubyInstaller-2.7.7-1)
- OpenJDK → Amazon Corretto 17.0.5.8.1
- データベース → PostgreSQL 14.6.1

## 概要

主に[Redmineのインストールガイド](http://guide.redmine.jp/RedmineInstall/)を参考にしています。

1. 必要データのダウンロード
2. パスの設定ファイル作成
3. PostgreSQLの準備
4. Redmineの設定
5. Redmineの起動バッチ作成


## 1. 必要データのダウンロード

Redmineに必要なツールをダウンロードし、任意のディレクトリに格納します。

以降、このディレクトリを`%REDMINE_ROOT%`と表記します。

~~~none
%REDMINE_ROOT%
├─amazon-corretto-17.0.5.8.1-windows-x64-jdk
├─ImageMagick-7.1.0-56-portable-Q16-x64
├─jruby-dist-9.3.9.0-bin
├─postgresql-14.6-1-windows-x64-binaries
└─redmine-4.2.9
~~~

(追記 2023/1/6)

JRubyではなくRubyInstallerの場合はRubyのパスが変わります。

~~~none
%REDMINE_ROOT%
├─amazon-corretto-17.0.5.8.1-windows-x64-jdk
├─ImageMagick-7.1.0-56-portable-Q16-x64
├─rubyinstaller-2.7.7-1-x64
├─postgresql-14.6-1-windows-x64-binaries
└─redmine-4.2.9
~~~

### 1.1. Redmine

[Redmineダウンロードページ](https://www.redmine.org/projects/redmine/wiki/Download)より`redmine-4.2.9.zip`をダウンロードします。

### 1.2. Ruby

[Redmineと対応Rubyバージョン](https://redmine.jp/tech_note/supported-rubies/)のページより必要なRubyのバージョンを確認します。

~~今回はRedmine4.2.9をインストールするため、Ruby2.6.Xに対応しているバイナリ版Jruby9.3.9.0としました。~~

~~[JRubyのダウンロードページ](https://www.jruby.org/download)より`JRuby 9.3.9.0 Binary .zip`をダウンロードします。~~

(追記 2023/1/6)

JRubyでもRedmine起動は出来ましたが、Git連携が出来ませんでした(Windowsと相性が悪い？)。

そのため、RubyInstallerに切り替えました。

[RubyInstallerのダウンロードページ](https://rubyinstaller.org/downloads/)より7-ZIP ARCHIVES版の`Ruby2.7.7-1`をダウンロードします。

後の工程でRubyの必要パッケージをインストールしますが、Devkit(MSYS2)が必要です。

そのため[MSYS2](https://www.msys2.org/)を事前にインストールしておきます。

### 1.3. OpenJDK

OpenJDKには[Amazon Corretto](https://aws.amazon.com/jp/corretto/)を利用します。

[Amazon Corretto 17のダウンロードページ](https://docs.aws.amazon.com/corretto/latest/corretto-17-ug/downloads-list.html)より`amazon-corretto-17-x64-windows-jdk.zip`をダウンロードします。

### 1.4. PostgreSQL

[PostgreSQLのダウンロードページ](https://www.postgresql.org/download/windows/)よりバイナリ版をダウンロードします。

今回はPostgreSQLバージョン14.6をダウンロードしています。

### 1.5. ImageMagick

エクスポート時の画像操作に必要なようです。念のために入れておきます。

[ImageMagickのダウンロードページ](http://www.imagemagick.org/script/download.php)よりWindowsバイナリ版`ImageMagick-7.1.0-56-portable-Q16-x64.zip`をダウンロードします。

## 2. パスの設定ファイル作成

各実行ファイルのパスを一時的に設定するバッチファイル`settings.bat`を`%REDMINE_ROOT%`ディレクトリに格納します。

~~~batch
set REDMINE_ROOT=%~dp0

set JAVA_HOME=%REDMINE_ROOT%amazon-corretto-17.0.5.8.1-windows-x64-jdk\jdk17.0.5_8
set JRUBY_HOME=%REDMINE_ROOT%jruby-dist-9.3.9.0-bin\jruby-9.3.9.0
set POSTGRES_HOME=%REDMINE_ROOT%postgresql-14.6-1-windows-x64-binaries
set IMAGICK_HOME=%REDMINE_ROOT%ImageMagick-7.1.0-56-portable-Q16-x64
set REDMINE_HOME=%REDMINE_ROOT%redmine-4.2.9

set PATH=%PATH%;%JAVA_HOME%\bin
set PATH=%PATH%;%JRUBY_HOME%\bin
set PATH=%PATH%;%IMAGICK_HOME%

rem PostgreSQLスーパユーザのパスワード（PostgreSQL設定時に合わせる）
set PGPASSWORD=postgres
~~~

(追記 2023/1/6)

JRubyではなくRubyInstallerの場合はRubyのパスが変わります。

~~~batch
set REDMINE_ROOT=%~dp0

set JAVA_HOME=%REDMINE_ROOT%amazon-corretto-17.0.5.8.1-windows-x64-jdk\jdk17.0.5_8
set RUBY_HOME=%REDMINE_ROOT%rubyinstaller-2.7.7-1-x64
set POSTGRES_HOME=%REDMINE_ROOT%postgresql-14.6-1-windows-x64-binaries
set IMAGICK_HOME=%REDMINE_ROOT%ImageMagick-7.1.0-56-portable-Q16-x64
set REDMINE_HOME=%REDMINE_ROOT%redmine-4.2.9

set PATH=%PATH%;%JAVA_HOME%\bin
set PATH=%PATH%;%RUBY_HOME%\bin
set PATH=%PATH%;%IMAGICK_HOME%

rem PostgreSQLスーパユーザのパスワード（PostgreSQL設定時に合わせる）
set PGPASSWORD=postgres
~~~

## 3. PostgreSQLの準備

データクラスタ、ログ、本体の階層は次のようにしています。

~~~none
%REDMINE_ROOT%
└─postgresql-14.6-1-windows-x64-binaries
  ├─data
  ├─log
  └─pgsql
~~~

ここではコマンドのみを記載します。`%REDMINE_ROOT%`は自身で配置したパスに置き換えてください。

データクラスタ作成時のスーパユーザのパスワードは`settings.bat`に合わせて`postgres`とします。

~~~batch
rem データクラスタ作成
cd %REDMINE_ROOT%
call settings
cd %POSTGRES_HOME%
mkdir data
mkdir log
set data_dir="%POSTGRES_HOME%\data"
%POSTGRES_HOME%\pgsql\bin\pg_ctl initdb -D %data_dir% -o "-U postgres -E UTF8 --no-locale -A scram-sha-256 -W"

rem PostgreSQL起動
set data_dir="%POSTGRES_HOME%\data"
set log_file="%POSTGRES_HOME%\log\postgresql.log"
%POSTGRES_HOME%\pgsql\bin\pg_ctl start -w -D %data_dir% -l %log_file% -o "-p 5432"

rem PostgreSQLログイン
%POSTGRES_HOME%\pgsql\bin\psql -h localhost -p 5432 -U postgres -d postgres
~~~

PostgreSQL設定の詳細な手順は過去記事を参照してください。

<div className="card_list">
  <div className="card_part">
  <div className="card_stopper"></div>
    <div className="l-wrapper card-radius" key="install_postgresql_zip_archive">
      <article className="card">
        <a href="/posts/install_postgresql_zip_archive">
          <div className="card_header">
            <p className="card_title">zipアーカイブ版PostgreSQLの導入手順</p>
              <p></p>
          </div>
        <hr/>
          <div className="card_body">
            <p className="card_text">www.kotememo.com</p>
          </div>
        </a></Link>
      </article>
    </div>
  </div>
</div>

<div className="card_list">
  <div className="card_part">
  <div className="card_stopper"></div>
    <div className="l-wrapper card-radius" key="login_postgresql_without_password">
      <article className="card">
        <a href="/posts/login_postgresql_without_password">
          <div className="card_header">
            <p className="card_title">【Windows】PostgreSQLでパスワード入力を省略する方法</p>
              <p></p>
          </div>
        <hr/>
          <div className="card_body">
            <p className="card_text">www.kotememo.com</p>
          </div>
        </a></Link>
      </article>
    </div>
  </div>
</div>

## 4. Redmineの設定

### 4.1. Redmine用のロール、データベース作成

PostgreSQLにスーパユーザpostgresでログインし、Redmine用のロールと空のデータベースを作成します。

テスト的に全て同じredmineにしています。

- ロール名：redmine
- パスワード：redmine
- データベース名：redmine

~~~none
CREATE ROLE redmine LOGIN ENCRYPTED PASSWORD 'redmine' NOINHERIT VALID UNTIL 'infinity';
CREATE DATABASE redmine WITH ENCODING='UTF8' OWNER=redmine;
~~~

### 4.2. データベースへの接続設定

`%REDMINE_ROOT%\redmine-4.2.9\config`内の`database.yml.example`を同ディレクトリに`database.yml`としてコピーします。

その後、`database.yml`の中身を削除し、PostgreSQLの内容のみを記述します。

~~~none
# PostgreSQL configuration example
production:
 adapter: postgresql
 host: localhost
 port: 5432
 database: redmine
 username: redmine
 password: "redmine"
~~~

### 4.3. 依存するソフトウェアのインストール

`%REDMINE_ROOT%\redmine-4.2.9\Gemfile`を開き、一部内容を変更します。

- Windows端末のため後半情報を削除

~~~none
# 修正前
# Windows does not include zoneinfo files, so bundle the tzinfo-data gem
gem 'tzinfo-data', platforms: [:mingw, :x64_mingw, :mswin]

# 修正後
# Windows does not include zoneinfo files, so bundle the tzinfo-data gem
gem 'tzinfo-data'
~~~

- JRubyのためコメントアウト(※RubyInstallerの場合は修正しない)

~~~none
# 修正前
# Optional Markdown support, not for JRuby
group :markdown do
  gem 'redcarpet', '~> 3.5.1'
end

# 修正後
# Optional Markdown support, not for JRuby
# group :markdown do
#   gem 'redcarpet', '~> 3.5.1'
# end
~~~

- PostgreSQLのためアダプタ追加

~~~none
# 修正前
      when /postgresql/
        gem "pg", "~> 1.2.2", :platforms => [:mri, :mingw, :x64_mingw]

# 修正後
      when /postgresql/
        gem "pg", "~> 1.2.2", :platforms => [:mri, :mingw, :x64_mingw]
        gem 'activerecord-jdbcpostgresql-adapter'
~~~


### 4.4. 依存するソフトウェアのインストール

Redmineに必要なgemをインストールします。

`%REDMINE_ROOT%`は自身で配置したパスに置き換えてください。

~~~batch
cd %REDMINE_ROOT%
call settings
cd %REDMINE_HOME%
bundle config build.pg --with-pg-config=%POSTGRES_HOME%\pgsql\bin\pg_config
bundle install --without development test
~~~


### 4.5. セッションストア秘密鍵の生成

コマンドを実行します。

~~~batch
cd %REDMINE_ROOT%
call settings
cd %REDMINE_HOME%
bundle exec rake generate_secret_token
~~~

### 4.6. データベースのテーブル等の生成

PostgreSQLを起動している状態でコマンドを実行します。

~~~batch
cd %REDMINE_ROOT%
call settings
cd %REDMINE_HOME%
set RAILS_ENV=production
bundle exec rake db:migrate
~~~

### 4.7. デフォルトデータの生成

PostgreSQLを起動している状態でコマンドを実行します。

~~~batch
cd %REDMINE_ROOT%
call settings
cd %REDMINE_HOME%
set RAILS_ENV=production
set REDMINE_LANG=ja
bundle exec rake redmine:load_default_data
~~~

### 4.8. インストールの確認

WEBrickによるwebサーバを起動し、正常に動作するか確認します。

成功すれば、ブラウザで `http://localhost:3000/` を開くとRedmineのページが表示されます。

Redmineの初期ユーザ名とパスワードは共に`admin`です。

~~~batch
cd %REDMINE_ROOT%
call settings
cd %REDMINE_HOME%
bundle exec rails server webrick -e production
~~~

## 5. Redmineの起動バッチ作成

Redmineの起動バッチを`%REDMINE_ROOT%`に配置しておけば、次回以降の起動が楽になります。

`PGPASSWORD`にpostgresユーザのパスワード設定後、PostgreSQLを起動しRedmineを起動しています。

終了する場合は`Ctrl+C`です。

~~~batch
cd %~dp0
call settings

set data_dir="%POSTGRES_HOME%\data"
set log_file="%POSTGRES_HOME%\log\postgresql.log"
set PGPASSWORD=postgres
%POSTGRES_HOME%\pgsql\bin\pg_ctl start -w -D %data_dir% -l %log_file% -o "-p 5432"

cd %REDMINE_HOME%
bundle exec rails server webrick -e production
~~~


 バイナリ版の組み合わせでRedmineを起動できるようにしました。Dockerを利用したほうがいいと思いながらも、そもそもインストールに制...

【Windows】インストールせずにバイナリ版でRedmineを起動したい


## はじめに

誤ったUPDATE文を実行し自動commitしてしまったことがありました。

そのため、UPDATE文の更新対象を簡単に確認したいと考えました。

今回検討した方法としては副問い合わせ内のSELECT文で確認後、全体のUPDATE文を実行します。

## 概要

1. 対象データの確認
2. 更新SQLの作成
3. メリットとデメリット

## 対象データの確認

年齢が低い子供の点数にボーナスを加える状況を考えます。

点数テーブル`scoretable`と年齢テーブル`agetable`を無理矢理分けたテーブルがあるとします。

どちらも`id`項目が主キーとなっています。

今回の更新処理に複数テーブルを使用したかったため、テーブル構成については気にしないでください。

点数テーブル`scoretable`

| id  | name        | score         |
|:------------:|:----------------:|:-------------------:|
| 1    | aaa             | 90               |
| 2    | bbb             | 40               |
| 3    | ccc             | 70               |
| 4    | ddd             | 30               |

年齢テーブル`agetable`

| id  | name        | age         |
|:------------:|:----------------:|:-------------------:|
| 1    | aaa             | 33               |
| 2    | bbb             | 7               |
| 3    | ccc             | 35               |
| 4    | ddd             | 6               |

年齢が10歳未満の人の点数を2倍に更新する場合を考えます。

更新後の`score`の値を`score_new`と定義すると、確認用SQLは次のようにします。

~~~SQL
SELECT st.id, st.name, at.age, st.score, st.score * 2 AS score_new 
FROM scoretable st
  LEFT JOIN agetable at ON st.id = at.id
WHERE at.age < 10
~~~

実行結果

| id  | name        | age         | score         | score_new         |
|:------------:|:----------------:|:-------------------:|:-------------------:|:-------------------:|
| 2    | bbb             | 7               | 40               | 80               |
| 4    | ddd             | 6               | 30               | 60               |

10歳未満の2名の点数`score`に対して、更新したい点数`score_new`が2倍の数値であることを確認できます。

この主キー`id`による紐づけを行うことでUPDATEを行いたいと考えます。

## 更新SQLの作成

PostgreSQLのUPDATE文ではFROM句で別テーブルを参照できます。

ただし、UPDATE文ではJOIN句は使用できないため、WHERE句でテーブル結合できます。

対象データ確認SQLを副問い合わせとして参照し、キーで結合すれば一意に定まり更新できます。

~~~SQL
UPDATE scoretable
SET score = st_new.score_new
FROM (
-- 確認用SQL
SELECT st.id, st.name, at.age, st.score, st.score * 2 AS score_new 
FROM scoretable st
  LEFT JOIN agetable at ON st.id = at.id
WHERE at.age < 10
) st_new
-- 主キーによる紐づけ
WHERE scoretable.id = st_new.id
~~~

実行結果

| id  | name        | score         |
|:------------:|:----------------:|:-------------------:|
| 1    | aaa             | 90               |
| 2    | bbb             | 80               |
| 3    | ccc             | 70               |
| 4    | ddd             | 60               |

副問い合わせ内のSELECT文を実行することで更新対象データを確認できます。

問題なければ全体のUPDATE文を実行することでミスを減らすことが出来ると考えます。

## メリットとデメリット

データを確認をしながら行うリカバリ時やパッチ適用時などで慎重になれるかと思います。

他に利用者がいないのであれば、トランザクション中に更新、内容確認をするのが一番だと思います。

### メリット

- UPDATE文の実行結果となりえるデータを部分実行で確認できる。
- 副問い合わせのキーで更新するため、複数のテーブル結合を用いた計算結果でも反映することが出来る。

### デメリット

- 実行速度が低下する。
- 確認用SQL実行と間違えてUPDATE文を実行してしまう可能性は残っている。



 誤ったUPDATE文を実行し自動commitしてしまったことがありました。そのため、UPDATE文の更新対象を簡単に確認したいと考えました...

【PostgreSQL】UPDATE文を実行する前に対象データを確認したい


## はじめに

VB.netのコンソールアプリからNpgsqlでPostgreSQLに接続し、トランザクション中にデータをINSERTしました。

DataTableに抽出したテーブルのデータを加工したものをINSERTしています。

## 概要

SELECT文でデータを抽出し、トランザクション中に加工データをINSERTしています。

1. トランザクション処理
2. SQL結果をDataTableに格納
3. 加工データをINSERT
4. ソースコード全文

## 環境

- Windows 10
- Visual Studio 2019
- Visual Basicコンソールアプリ(.NET Core 3.1)

## 1. トランザクション処理

コネクションを`conn`とした時、トランザクションは`conn.BeginTransaction`で開始できます。

トランザクション中にINSERTしたデータは`Commit()`するまで実際には書き込みが保存されません。

INSERT処理が全て成功した場合は`Commit()`で処理を確定します。

途中で失敗した場合は`Rollback()`することでそれまでの書き込み内容を破棄します。

~~~vb
Dim tran As NpgsqlTransaction = Nothing

Using conn As New NpgsqlConnection(sb.ConnectionString)
    Try
        'PostgreSQLに接続
        conn.Open()

        'トランザクション開始
        tran = conn.BeginTransaction

        'SQL処理
        '......

        'コミット
        tran.Commit()
    Catch e As Exception
        'ロールバック
        If tran IsNot Nothing Then
            tran.Rollback()
        End If
    End Try
End Using
~~~

## 2. SQL結果をDataTableに格納

SELECT文の結果をDataTableに抽出することは以前に行っているため省略します。

<div className="card_list">
  <div className="card_part">
  <div className="card_stopper"></div>
    <div className="l-wrapper card-radius" key="vbnet_postgresql_npgsql">
      <article className="card">
        <a href="/posts/vbnet_postgresql_npgsql">
          <div className="card_header">
            <p className="card_title">【VB.net】NpgsqlでPostgreSQLからSELECT結果を取得</p>
              <p></p>
          </div>
        <hr/>
          <div className="card_body">
            <p className="card_text">www.kotememo.com</p>
          </div>
        </a></Link>
      </article>
    </div>
  </div>
</div>

今回は以下のtesttableテーブルのデータをDataTableに格納したデータを加工して同テーブルにINSERTします。

※ソースコード全文は最後に記載します。

| ID  | NAME        |
|:------------|:----------------|
| 1   | testA             |
| 2   | testB             |
| 3   | testC             |

## 3. 加工データをINSERT

まずINSERT文の中にパラメータを記述している場合は型情報を`cmd.Paramters.Add()`で定義します。

次にDataTableのデータを加工し、パラメータに設定します。

- id → 値を+10
- name → 末尾に"_ADD"を追加

`cmd.ExecuteNonQuery()`で1行ずつINSERTします。

最後に、`tran.Commit()`でデータ挿入を確定させます。

~~~vb
'SQL(INSERT文)
Dim sqlInsert As String = "INSERT INTO testtable (id, name) VALUES (:parameter1, :parameter2)"

Using cmd As New NpgsqlCommand(sqlInsert, conn)
    'SQL内のパラメータ型設定
    cmd.Parameters.Add(New NpgsqlParameter("parameter1", NpgsqlDbType.Integer))
    cmd.Parameters.Add(New NpgsqlParameter("parameter2", NpgsqlDbType.Varchar))

    'DataTableをINSERT
    For i As Integer = 0 To dt.Rows.Count - 1
        'SQL内のパラメータ設定
        cmd.Parameters("parameter1").Value = dt.Rows(i).Item("vbID") + 10
        cmd.Parameters("parameter2").Value = dt.Rows(i).Item("vbNAME") & "_ADD"

        'SQL実行(INSERT)
        cmd.ExecuteNonQuery()
    Next

End Using

'コミット
tran.Commit()
~~~

挿入後、改めてSELECTした結果が以下になります。

~~~none
vbDataTable
vbID vbNAME
1 testA
2 testB
3 testC
11 testA_ADD
12 testB_ADD
13 testC_ADD
~~~

## 4. ソースコード全文

~~~vb
Imports System.Data
Imports Npgsql
Imports NpgsqlTypes

Module Program
    Sub Main(args As String())

        'PostgreSQL接続情報の設定
        Dim sb As New Npgsql.NpgsqlConnectionStringBuilder
        With sb
            .Host = "localhost"     'ホスト名orIPアドレス
            .Database = "testdb"    'データベース名
            .Username = "testuser"  'ユーザ名
            .Password = "password"  'パスワード
            .Port = 5432            'ポート番号
            .Timeout = 20           'タイムアウト接続時間
            .CommandTimeout = 20    'タイムアウト実行時間
        End With

        'DataTableのテーブル名・ヘッダ設定
        Dim dt As New DataTable("vbDataTable")
        dt.Columns.Add("vbID", GetType(Integer))
        dt.Columns.Add("vbNAME", GetType(String))

        'トランザクション
        Dim tran As NpgsqlTransaction = Nothing

        Using conn As New NpgsqlConnection(sb.ConnectionString)

            Try

                'PostgreSQLに接続
                conn.Open()

                'トランザクション開始
                tran = conn.BeginTransaction

                'SQL(SELECT文)
                Dim sqlSelect As String = "SELECT * FROM testtable"

                Using cmd As New NpgsqlCommand(sqlSelect, conn)

                    'SQL実行(SELECT)
                    Using reader As NpgsqlDataReader = cmd.ExecuteReader()

                        If reader.HasRows Then
                            'SELECT結果：1件以上
                            'SELECT内容抽出
                            While (reader.Read())
                                'DataRowをDataTableに格納
                                Dim dr As DataRow = dt.NewRow()
                                dr.Item("vbID") = reader.Item("id")
                                dr.Item("vbNAME") = reader.Item("name")
                                dt.Rows.Add(dr)
                            End While
                        Else
                            'SELECT結果：0件
                            Console.WriteLine("SELECT 0件")
                        End If
                    End Using

                End Using

                'SQL(INSERT文)
                Dim sqlInsert As String = "INSERT INTO testtable (id, name) VALUES (:parameter1, :parameter2)"

                Using cmd As New NpgsqlCommand(sqlInsert, conn)
                    'SQL内のパラメータ型設定
                    cmd.Parameters.Add(New NpgsqlParameter("parameter1", NpgsqlDbType.Integer))
                    cmd.Parameters.Add(New NpgsqlParameter("parameter2", NpgsqlDbType.Varchar))

                    'DataTableをINSERT
                    For i As Integer = 0 To dt.Rows.Count - 1

                        'SQL内のパラメータ設定
                        cmd.Parameters("parameter1").Value = dt.Rows(i).Item("vbID") + 10
                        cmd.Parameters("parameter2").Value = dt.Rows(i).Item("vbNAME") & "_ADD"

                        'SQL実行(INSERT)
                        cmd.ExecuteNonQuery()
                    Next

                End Using

                'コミット
                tran.Commit()

            Catch e As Exception
                'エラーメッセージ
                Console.WriteLine(e.ToString)

                'ロールバック
                If tran IsNot Nothing Then
                    tran.Rollback()
                End If
            End Try

        End Using

    End Sub
End Module

~~~



 VB.netのコンソールアプリからNpgsqlでPostgreSQLに接続し、トランザクション中にデータをINSERTしました。DataT...

【VB.net】NpgsqlでPostgreSQLにINSERTする方法


## はじめに

VB.netのコンソールアプリからNpgsqlでPostgreSQLに接続し、SELECT文の結果を取得しました。

DataTableにデータを格納してから標準出力しています。

## 概要

using句の中でSELECT文を実行しますが、using句の外で定義したDataTableに格納した後で標準出力します。

1. Npgsqlのインストール
2. PostgreSQLに接続
3. SQL結果をDataTableに格納
4. ソースコード全文

## 環境

- Windows 10
- Visual Studio 2019
- Visual Basicコンソールアプリ(.NET Core 3.1)

## 1. Npgsqlのインストール

今回はオープンソースのADO.NETデータプロバイダであるNpgsqlを利用します。

ソリューションエクスプローラの[依存関係]を右クリックし、[NuGetパッケージの管理]を選択します。

表示されたNuGetパッケージマネージャでNpgsqlを検索し、[インストール]を選択することで使用可能になります。

![Npgsqlインストール画像](/images/vbnet_postgresql_npgsql/install_npgsql.png)

## 2. PostgreSQLに接続

まずはIPアドレスやデータベース名などPostgreSQLの接続情報を`NpgsqlConnectionStringBuilder`に記述します。

~~~vb
Dim sb As New Npgsql.NpgsqlConnectionStringBuilder
With sb
    .Host = "localhost"     'ホスト名 or IPアドレス
    .Database = "testdb"    'データベース名
    .Username = "testuser"  'ユーザ名
    .Password = "password"  'パスワード
    .Port = 5432            'ポート番号
End With
~~~

上記の変数を引数にした`NpgsqlConnection`の`Open`関数でPostgreSQLに接続できます。

~~~vb
Using conn As New NpgsqlConnection(sb.ConnectionString)
    conn.Open()
    'SQL実行処理
    '......
End Using
~~~

今回は`ID`、`NAME`で構成された`testtable`テーブルを使用します。

| ID  | NAME        |
|:------------|:----------------|
| 1   | testA             |
| 2   | testB             |
| 3   | testC             |

このテーブルからID=2以外のデータをSELECTするSQLを定義します。

後で値を設定するパラメータをSQL文字列に入れる際には`:`をパラメータの先頭に付けます。

SQL文字列を定義し、SQL内のパラメータを定義した後でSQLを実行できます。

~~~vb
Dim sql As String = "SELECT * FROM testtable WHERE id <> :parameter1"
Using cmd As New NpgsqlCommand(sql, conn)
    'SQL内のパラメータ設定
    cmd.Parameters.Add(New NpgsqlParameter("parameter1", NpgsqlDbType.Integer))
    cmd.Parameters("parameter1").Value = 2

    Using reader As NpgsqlDataReader = cmd.ExecuteReader()
        'SQL結果の処理
        '......
    End Using
End Using
~~~

## 3. SQL結果をDataTableに格納

Using句から出た場所でデータ参照を出来るようにするために、SQL結果をDataTableに格納します。

後続のSQL結果を取得する際にPostgreSQLでの名前を取得することも出来ますが、DataTableで定義したと分かるようにテスト的な名前にしています。

~~~vb
'DataTableのテーブル名・ヘッダ設定
Dim dt As New DataTable("vbDataTable")    'テーブル名
dt.Columns.Add("vbID", GetType(Integer))    'カラム名1
dt.Columns.Add("vbNAME", GetType(String))    'カラム名2
~~~

SQLの結果は1行ずつDataRowに格納したものをDataTableに取り込みます。

~~~vb
Using reader As NpgsqlDataReader = cmd.ExecuteReader()
    'SQL結果の処理
    While (reader.Read())
        'DataRowをDataTableに格納
        Dim dr As DataRow = dt.NewRow()
        dr.Item("vbID") = reader.Item("id")
        dr.Item("vbNAME") = reader.Item("name")
        dt.Rows.Add(dr)
    End While
End Using
~~~

これでDataTableにSQL結果を格納できたため、Usingの外でデータを参照できます。

~~~vb
'DataTableのテーブル名を標準出力
Console.WriteLine(dt.TableName.ToString)

'DataTableのヘッダ情報を標準出力
For i As Integer = 0 To dt.Columns.Count - 1
    Console.Write(dt.Columns(i).ToString & " ")
Next
Console.WriteLine()

'DataTableを標準出力
For i As Integer = 0 To dt.Rows.Count - 1
    Console.Write(dt.Rows(i).Item("vbID").ToString & " ")
    Console.Write(dt.Rows(i).Item("vbNAME").ToString)
    Console.WriteLine()
Next
~~~

実行結果は以下になります。

~~~none
vbDataTable
vbID vbNAME
1 testA
3 testC
~~~

## 4. ソースコード全文

上記の処理に加え、エラーメッセージ出力用のTry-Catch処理やSQL結果の0件処理などを追加しています。

~~~vb
Imports System.Data
Imports Npgsql
Imports NpgsqlTypes

Module Program
    Sub Main(args As String())

        'PostgreSQL接続情報の設定
        Dim sb As New Npgsql.NpgsqlConnectionStringBuilder
        With sb
            .Host = "localhost"     'ホスト名orIPアドレス
            .Database = "testdb"    'データベース名
            .Username = "testuser"  'ユーザ名
            .Password = "password"  'パスワード
            .Port = 5432            'ポート番号
            .Timeout = 20           'タイムアウト接続時間
            .CommandTimeout = 20    'タイムアウト実行時間
        End With

        'SQL(パラメータ付)
        Dim sql As String = "SELECT * FROM testtable WHERE id <> :parameter1"

        'DataTableのテーブル名・ヘッダ設定
        Dim dt As New DataTable("vbDataTable")
        dt.Columns.Add("vbID", GetType(Integer))
        dt.Columns.Add("vbNAME", GetType(String))

        Using conn As New NpgsqlConnection(sb.ConnectionString)

            Try

                'PostgreSQLに接続
                conn.Open()

                Using cmd As New NpgsqlCommand(sql, conn)

                    'SQL内のパラメータ設定
                    cmd.Parameters.Add(New NpgsqlParameter("parameter1", NpgsqlDbType.Integer))
                    cmd.Parameters("parameter1").Value = 2

                    'SQL実行
                    Using reader As NpgsqlDataReader = cmd.ExecuteReader()

                        If reader.HasRows Then
                            'SELECT結果：1件以上
                            'SELECT内容抽出
                            While (reader.Read())
                                'DataRowをDataTableに格納
                                Dim dr As DataRow = dt.NewRow()
                                dr.Item("vbID") = reader.Item("id")
                                dr.Item("vbNAME") = reader.Item("name")
                                dt.Rows.Add(dr)
                            End While
                        Else
                            'SELECT結果：0件
                            Console.WriteLine("SELECT 0件")
                        End If
                    End Using

                End Using

            Catch e As Exception
                'エラーメッセージ
                Console.WriteLine(e.ToString)
                End
            End Try

        End Using

        'DataTableのテーブル名を標準出力
        Console.WriteLine(dt.TableName.ToString)

        'DataTableのヘッダ情報を標準出力
        For i As Integer = 0 To dt.Columns.Count - 1
            Console.Write(dt.Columns(i).ToString & " ")
        Next
        Console.WriteLine()

        'DataTableを標準出力
        For i As Integer = 0 To dt.Rows.Count - 1
            Console.Write(dt.Rows(i).Item("vbID").ToString & " ")
            Console.Write(dt.Rows(i).Item("vbNAME").ToString)
            Console.WriteLine()
        Next

    End Sub
End Module
~~~

## 関連記事

<div className="card_list">
  <div className="card_part">
  <div className="card_stopper"></div>
    <div className="l-wrapper card-radius" key="vbnet_postgresql_npgsql_insert">
      <article className="card">
        <a href="/posts/vbnet_postgresql_npgsql_insert">
          <div className="card_header">
            <p className="card_title">【VB.net】NpgsqlでPostgreSQLにINSERTする方法</p>
              <p></p>
          </div>
        <hr/>
          <div className="card_body">
            <p className="card_text">www.kotememo.com</p>
          </div>
        </a></Link>
      </article>
    </div>
  </div>
</div>


 VB.netのコンソールアプリからNpgsqlでPostgreSQLに接続し、SELECT文の結果を取得しました。DataTableにデー...

【VB.net】NpgsqlでPostgreSQLからSELECT結果を取得


## はじめに

データベース単位のバックアップとリストア(復元)の方法を調査しました。

## 概要

1. データベースのバックアップ
2. データベースの削除
3. データベースの作成
4. データベースのリストア


## 1. データベースのバックアップ

今回バックアップの対象はデータベース`testdb`とし、テーブル`testtable`を保持しているものとします。

```none
testdb=# \dt
              リレーション一覧
 スキーマ |   名前    |  タイプ  |  所有者
----------+-----------+----------+----------
 public   | testtable | テーブル | postgres
(1 行)


testdb=# select * from testtable;
 id | name
----+------
  1 | abc
  2 | def
  3 | ghi
(3 行)
```

まず、PostgreSQLでのバックアップには`pg_dump`コマンドを使用します。

他のオプションを知りたい場合は`pg_dump --help`コマンドで一覧を表示できます。

```batch
bin\pg_dump -f testdb_20220904.dump -F c -v -h localhost -p 5432 -U postgres -w testdb
```

| オプション  | 値                   | 説明                                                 |
|:------------|:---------------------|:-----------------------------------------------------|
| -f          | testdb_20220904.dump | バックアップファイル名（フルパス可）               |
| -F          | c                    | 出力形式（カスタム形式）                                     |
| -v          | なし                 | 冗長モード           |
| -h          | localhost            | ホスト名                                 |
| -p          | 5432                 | ポート番号 |
| -U          | postgres             | ユーザ名                     |
| -w          | なし                 | `PGPASS`ファイルからパスワード自動認証                     |
| 指定        | testdb               | バックアップ対象データベース名                     |

```none
C:\postgresql-binaries\postgresql-13\pgsql-13.7-1>bin\pg_dump -f testdb_20220904.dump -F c -v -h localhost -p 5432 -U postgres testdb
pg_dump: 最後の組み込みOIDは16383
pg_dump: 機能拡張を読み込んでいます
pg_dump: 機能拡張の構成要素を特定しています
pg_dump: スキーマを読み込んでいます
pg_dump: ユーザ定義テーブルを読み込んでいます
pg_dump: ユーザ定義関数を読み込んでいます
pg_dump: ユーザ定義型を読み込んでいます
pg_dump: 手続き言語を読み込んでいます
pg_dump: ユーザ定義集約関数を読み込んでいます
pg_dump: ユーザ定義演算子を読み込んでいます
pg_dump: ユーザ定義アクセスメソッドを読み込んでいます
pg_dump: ユーザ定義演算子クラスを読み込んでいます
pg_dump: ユーザ定義演算子族を読み込んでいます
pg_dump: ユーザ定義のテキスト検索パーサを読み込んでいます
pg_dump: ユーザ定義のテキスト検索テンプレートを読み込んでいます
pg_dump: ユーザ定義のテキスト検索辞書を読み込んでいます
pg_dump: ユーザ定義のテキスト検索設定を読み込んでいます
pg_dump: ユーザ定義の外部データラッパーを読み込んでいます
pg_dump: ユーザ定義の外部サーバーを読み込んでいます
pg_dump: デフォルト権限設定を読み込んでいます
pg_dump: ユーザ定義の照合順序を読み込んでいます
pg_dump: ユーザ定義の変換を読み込んでいます
pg_dump: 型キャストを読み込んでいます
pg_dump: 変換を読み込んでいます
pg_dump: テーブル継承情報を読み込んでいます
pg_dump: イベントトリガを読み込んでいます
pg_dump: 機能拡張構成テーブルを探しています
pg_dump: 継承関係を検索しています
pg_dump: 対象テーブルの列情報を読み込んでいます
pg_dump: テーブル"public.testtable"の列と型を探しています
pg_dump: 子テーブルの継承列にフラグを設定しています
pg_dump: インデックスを読み込んでいます
pg_dump: パーティション親テーブルのインデックスにフラグを設定しています
pg_dump: 拡張統計情報を読み込んでいます
pg_dump: 制約を読み込んでいます
pg_dump: トリガを読み込んでいます
pg_dump: 書き換えルールを読み込んでいます
pg_dump: ポリシを読み込んでいます
pg_dump: reading row-level security policies
pg_dump: パブリケーションを読み込んでいます
pg_dump: パブリケーションの構成要素を読み込んでいます
pg_dump: サブスクリプションを読み込んでいます
pg_dump: ラージオブジェクトを読み込んでいます
pg_dump: データの依存データを読み込んでいます
pg_dump: encoding = UTF8 を保存しています
pg_dump: standard_conforming_strings = on を保存しています
pg_dump: search_path =  を保存しています
pg_dump: データベース定義を保存しています
pg_dump: テーブル "public.testtable"の内容をダンプしています
```

これでデータベースのバックアップファイルが作成されました。

オプション`-w`のパスワード自動認証の設定については過去記事を参照してください。

<div className="card_list">
  <div className="card_part">
  <div className="card_stopper"></div>
    <div className="l-wrapper card-radius" key="login_postgresql_without_password">
      <article className="card">
        <a href="/posts/login_postgresql_without_password">
          <div className="card_header">
            <p className="card_title">【Windows】PostgreSQLでパスワード入力を省略する方法</p>
              <p></p>
          </div>
        <hr/>
          <div className="card_body">
            <p className="card_text">www.kotememo.com</p>
          </div>
        </a></Link>
      </article>
    </div>
  </div>
</div>


## 2. データベースの削除

リストアする前にデータベースの情報をクリアにするために削除します。

ログインしてから`drop database`で削除してもよいですが、ここではログインせずに`psql -c`から直接SQLコマンドを実行します。

```batch
C:\postgresql-binaries\postgresql-13\pgsql-13.7-1>bin\psql -h localhost -p 5432 -U postgres -d postgres -c  "drop database testdb;"
DROP DATABASE
```

こうすることでバッチでデータベース削除～リストアまでを一気に実行できます。


## 3. データベースの作成

こちらも`psql -c`コマンドで空のデータベースを作成します。

リストアの対象テーブルはバックアップ元のデータベース名と異なっていても問題ありません。

```batch
C:\postgresql-binaries\postgresql-13\pgsql-13.7-1>bin\psql -h localhost -p 5432 -U postgres -d postgres -c  "create database testdb;"
CREATE DATABASE
```

## 4. データベースのリストア

最後に`pg_restore`を用いてデータベースをリストアします。

もしバックアップ時のオプション`-F`で出力形式を平文`p`にしている場合は、`pg_restore`が非対応のため平文SQLをそのまま実行しましょう。

他のオプションを知りたい場合は`pg_restore --help`コマンドで一覧を表示できます。

```batch
C:\postgresql-binaries\postgresql-13\pgsql-13.7-1>bin\pg_restore -d testdb -v -h localhost -p 5432 -U postgres -w testdb_20220904.dump
pg_restore: リストアのためデータベースに接続しています
pg_restore: TABLE "public.testtable"を作成しています
pg_restore: テーブル"public.testtable"のデータを処理しています
```

| オプション  | 値                   | 説明                                                 |
|:------------|:---------------------|:-----------------------------------------------------|
| -d          | testdb               | リストア対象データベース名               |
| -v          | なし                 | 冗長モード                                     |
| -h          | localhost            | ホスト名           |
| -p          | 5432                 | ポート番号 |
| -U          | postgres             | ユーザ名                     |
| -w          | なし                 | `PGPASS`ファイルからパスワード自動認証                     |
| 指定        | testdb_20220904.dump | バックアップ対象データベース名                     |

これでデータベースのリストアが完了しました。

```none
testdb=# \dt
              リレーション一覧
 スキーマ |   名前    |  タイプ  |  所有者
----------+-----------+----------+----------
 public   | testtable | テーブル | postgres
(1 行)


testdb=# select * from testtable;
 id | name
----+------
  1 | abc
  2 | def
  3 | ghi
(3 行)
```



 データベース単位のバックアップとリストア(復元)の方法を調査しました。 概要1. データベースのバックアップ2. データベースの削除3. ...

PostgreSQLのDBバックアップ＆リストア


## はじめに

コマンドプロンプトから`psql`を起動する際にはパスワードの入力が求められます。

自身で実行する分には問題ありませんが、バッチ等で自動処理させたい場合にパスワード入力は邪魔となります。

そこで、パスワード入力を省略する方法を調査しました。

## 環境

- Windows 10 Home

## 概要

サーバーサイドで対処する方法とクライアントサイドで対処する方法が存在します。

サーバーサイドはローカルホスト用、クライアントサイドは複数ユーザ用のように思います。

### サーバーサイド

- trust認証設定

### クライアントサイド

- パスワードファイル配置
- 環境変数の設定(非推奨)

## trust認証

サーバーサイドにて認証方式をtrustに設定すれば、指定した範囲においてアクセス許可をすることになります。

ただし、サーバーに接続する人全員に適用されるためローカルホスト内に留めるほうが安全と思います。

まずはユーザ`postgres`にログインしようとするとパスワードが要求されることを確認します。

```none
C:\postgresql-binaries\postgresql-13\pgsql-13.7-1>bin\psql -h localhost -p 5432 -U postgres -d postgres
ユーザ postgres のパスワード:
psql (13.7)
"help"でヘルプを表示します。

postgres=#
```

確認後、ログアウトします。

trust認証に設定するにはサーバーサイドの`pg_hba.conf`を編集します。

`pg_hba.conf`はPostgreSQLをインストールした場所の`data`ディレクトリに格納されています。

バージョンにもよりますが、`pg_hba.conf`の下のほうの表記は以下のようになっています。

```none
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     scram-sha-256
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
```

ユーザ`postgres`に対してtrust認証設定にしていきます。

今回はWindowsなので、IPv4とIPv6にMETHOD欄をtrustとする行を追加します。

上から順番に設定が読み込まれるため、個別指定の行を上部に設定します。

```none
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
host    postgres        postgres        127.0.0.1/32            trust
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    postgres        postgres        ::1/128                 trust
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     scram-sha-256
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
```

この`pg_hba.conf`を保存した状態でユーザ`postgres`にログインしようとすると、パスワード要求無しにログイン出来ます。

```none
C:\postgresql-binaries\postgresql-13\pgsql-13.7-1>bin\psql -h localhost -p 5432 -U postgres -d postgres
psql (13.7)
"help"でヘルプを表示します。

postgres=#
```

これがサーバーサイドで対処する場合の設定方法になります。

## パスワードファイル配置

クライアントサイドではパスワードの情報を記載したファイルを特定の場所に配置しておくことで自動認証が行われます。

trust認証の設定は削除しておきます。

まず、`pgpass.conf`ファイルを作成し、

- IPアドレス
- ポート番号
- データベース名
- ユーザ名
- パスワード

を記載します。

こちらに関しても上から順番に設定されるため個別設定は上部に記載し保存します。

```none
#IPアドレス:ポート番号:データベース名:ユーザ名:パスワード
localhost:5432:postgres:postgres:password
#複数行を定義することも可能
localhost:5433:postgres:postgres:password
#項目内の「:」「\」は、「\」でのエスケープが必要
localhost:5434:postgres:postgres:pass\:\\word
#ワイルドカードも使用可能(ただし定義した順にパスワードが確定するので下のほうで使用)
*:*:postgres:postgres:password
```

Windowsでは、この`pgpass.conf`を`%APPDATA%\postgresql\pgpass.conf`に配置します。

`%APPDATA%`に関しては個人によって多少異なりますがコマンドプロンプト上で表示できます。

以下は一例です。

```bash
C:\>echo %APPDATA%
C:\Users\userName\AppData\Roaming
```

この場合は`C:\Users\userName\AppData\Roaming\postgresql\pgpass.conf`のように配置します。

`pgpass.conf`ファイルを配置した状態でログインしようとすると、パスワード要求無しにログイン出来ます。

```bash
C:\postgresql-binaries\postgresql-13\pgsql-13.7-1>bin\psql -h localhost -p 5432 -U postgres -d postgres
psql (13.7)
"help"でヘルプを表示します。

postgres=#
```

これがクライアントサイドで対処する場合の基本的な設定方法になります。

## 環境変数の設定(非推奨)

環境変数を使用する方法に関しては、他ユーザから環境変数を見られる等セキュリティの観点から推奨されていません。

そのため基本的には上記の`pgpass.conf`を用意する方法が推奨されます。

メモ程度に記載しますが、`psql`ログイン時には環境変数`PGPASSWORD`が設定されていればパスワード認証の際に使用されます。

`psql`コマンドの直前に設定しておくと簡単なテスト実行には使用できそうです。

```bash
C:\postgresql-binaries\postgresql-13\pgsql-13.7-1>SET PGPASSWORD=password

C:\postgresql-binaries\postgresql-13\pgsql-13.7-1>bin\psql -h localhost -p 5432 -U postgres -d postgres
psql (13.7)
"help"でヘルプを表示します。

postgres=#
```



 コマンドプロンプトから`psql`を起動する際にはパスワードの入力が求められます。自身で実行する分には問題ありませんが、バッチ等で自動処理...

【Windows】PostgreSQLでパスワード入力を省略する方法

## はじめに

zipアーカイブ版PostgreSQLのダウンロードからスーパユーザでのログインまで行いました。

## 環境

- Windows 10 Home

## 概要

1. zipアーカイブ版PostgreSQLをダウンロード
2. データベースクラスタ初期化
3. データベースサーバ起動
4. ログイン

## 1. zipアーカイブ版PostgreSQLをダウンロード

PostgreSQLの[ダウンロードページ](https://www.postgresql.org/download/windows/)にてzipファイルをダウンロードします。

今回はVersion 13.7をダウンロードしました。

![PostgreSQLダウンロードページ画像](/images/install_postgresql_zip_archive/postgresql_download_page.png)

![PostgreSQLダウンロードURL画像](/images/install_postgresql_zip_archive/postgresql_download_url.png)



zipファイルを解凍し、`pgsql`ディレクトリの中身を任意の場所(例：`C:\postgresql-binaries\postgresql-13\pgsql-13.7-1`)に移動させます。

その後、任意のディレクトリ(例：`postgresql-13`)に`data`と`log`ディレクトリを作成します。

後の手順で`data`にはデータベースクラスタ、`log`にはサーバログ出力ファイルを格納します。

```none
C:.
└─postgresql-binaries
   └─postgresql-13
      ├─data
      ├─log
      └─pgsql-13.7-1
         ├─bin
         ├─doc
         ├─include
         ├─lib
         ├─pgAdmin4
         ├─share
         ├─StackBuilder
         └─symbols
```

## 2. データベースクラスタ初期化

コマンドプロンプトを開き、カレントディレクトリを`pgsql`に合わせます。

その後、`bin\pg_ctl initdb`コマンドを用いてデータベースクラスタを初期化します。

```bash
cd C:\postgresql-binaries\postgresql-13\pgsql-13.7-1
set data_dir="C:\postgresql-binaries\postgresql-13\data"
bin\pg_ctl initdb -D %data_dir% -o "-U postgres -E UTF8 --no-locale -A scram-sha-256 -W"
```

| オプション  | 値                   | 説明                                                 |
|:------------|:---------------------|:-----------------------------------------------------|
| -D          | `data`ディレクトリパス | データベースクラスタを格納するディレクトリ               |
| -U          | postgres             | スーパユーザ名                                     |
| -E          | UTF8                 | データベース作成時のデフォルトの文字コード           |
| --no-locale |                      | デフォルトのロケール                                 |
| -A          | scram-sha-256        | 認証方式。他には`trust`や`password`等が設定可能 |
| -W          |                      | パスワード入力のプロンプトを表示                     |

コマンドを実行すると作成するスーパユーザのパスワード設定を要求されるので、再入力と合わせて2回入力します。

成功すると最後にデータベースサーバ起動のコマンドが出力されます。

![PostgreSQLデータベースクラスタ初期化画像](/images/install_postgresql_zip_archive/postgresql_initdb.png)

## 3. データベースサーバ起動

### 起動

`bin\pg_ctl start`コマンドを用いてデータベースサーバを起動できます。

```bash
cd C:\postgresql-binaries\postgresql-13\pgsql-13.7-1
set data_dir="C:\postgresql-binaries\postgresql-13\data"
set log_file="C:\postgresql-binaries\postgresql-13\log\postgresql.log"
bin\pg_ctl start -w -D %data_dir% -l %log_file% -o "-p 5432"
```

| オプション  | 値                   | 説明                                                 |
|:------------|:---------------------|:-----------------------------------------------------|
| -w          |                      | データベースサーバ起動処理が完了するまで待機         |
| -p          | 5432                 | ポート番号                                     |
| -D          | `data`ディレクトリパス | 作成したデータベースクラスタのディレクトリパス           |
| -l          | ログファイルパス     | サーバログ出力のファイルパス                         |

![PostgreSQLサーバ起動画像](/images/install_postgresql_zip_archive/postgresql_start.png)

### 稼働確認

サーバが稼働しているかどうかは`bin\pg_ctl status`コマンドで確認できます。

稼働している場合はPID(プロセスID)が出力されます。

```bash
cd C:\postgresql-binaries\postgresql-13\pgsql-13.7-1
set data_dir="C:\postgresql-binaries\postgresql-13\data"
bin\pg_ctl status -D %data_dir%
```

![PostgreSQLサーバ実行確認画像](/images/install_postgresql_zip_archive/postgresql_status.png)

### 停止

サーバを停止させる場合には`bin\pg_ctl stop`コマンドを使用します。

`-m`オプションでは`smart`、`fast`、`immediate`から停止方式を選択できます。

```bash
cd C:\postgresql-binaries\postgresql-13\pgsql-13.7-1
set data_dir="C:\postgresql-binaries\postgresql-13\data"
bin\pg_ctl stop -D %data_dir% -m fast
```

| オプション  | 値                   | 説明                                                 |
|:------------|:---------------------|:-----------------------------------------------------|
| -m          | smart                | 全クライアントの切断待機、バックアップ終了まで待機         |
|             | fast                 | クライアント強制切断、実行中のトランザクションをロールバック |
|             | immediate            | 全サーバプロセスを即座に中断。再起動時にクラッシュリカバリ処理を実行 |

![PostgreSQL停止画像](/images/install_postgresql_zip_archive/postgresql_stop.png)

## 4. ログイン

スーパユーザ`postgres`を作成した際に同名のデータベース`postgres`が作成されています。

このデータベースにログインするには`bin\psql`コマンドを使用します。

パスワードを入力するとログイン出来ます。

また、ログアウトするには`\q`を入力します。

```bash
cd C:\postgresql-binaries\postgresql-13\pgsql-13.7-1
bin\psql -h localhost -p 5432 -U postgres -d postgres
```

| オプション  | 値                   | 説明                                                 |
|:------------|:---------------------|:-----------------------------------------------------|
| -h          | localhost            | IPアドレス                                           |
| -p          | 5432                 | ポート番号                                           |
| -U          | postgres             | ユーザ名                                             |
| -d          | postgres             | データベース名                                       |

![PostgreSQLログイン画像](/images/install_postgresql_zip_archive/postgresql_login.png)

`\du`コマンドを使用するとロール一覧を表示できます。

```none
postgres=# \du
                                             ロール一覧
 ロール名 |                                   属性                                   | 所属グループ
----------+--------------------------------------------------------------------------+--------------
 postgres | スーパユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}
```

 zipアーカイブ版PostgreSQLのダウンロードからスーパユーザでのログインまで行いました。 環境- Windows 10 Home ...