qwc-services による Web GIS の構築 #3 PostGIS

QGIS Server と QGIS Web Client 2 (QWC2) を中核とする統合的 Web GIS エコ・システムである qwc-services によって Web GIS を構築する作業記録の第3回です。

第1回では、`qwc-docker` によって `qwc-services` をインストールし、`MapCache` とも連携させて、QGIS のプロジェクトを Web 地図として表示するところまでやりました。

- [qwc-services による Web GIS の構築 #1 qwc-docker](https://qiita.com/softark/items/bb179315d404fdd3cf9f)

第2回では、ユーザの認証と権限管理について調べて、地図やレイヤを閲覧できるユーザを限定したり、ビューワの機能を使用できるユーザを限定したりする方法を確認しました。

- [qwc-services による Web GIS の構築 #2 認証と権限](https://qiita.com/softark/items/e307645aebcd5366d16b)

今回は、`qwc-services` によって実現される「編集」関連の機能に不可欠な要素となる `PostgreSQL` と `PostGIS` を見ていきます。

## 1. PostGIS サーバ

ここで `PostGIS` サーバは、`PostgreSQL` サーバに `PostGIS` 拡張を追加したものを意味します。

`qwc-services` が提供する編集機能はほとんど全て `qwc-data-service` に依拠していますが、このサービスはレイヤのソースが `PostGIS` サーバに保存されていることを前提にしています。つまり、`*.shpfile` などのファイルをソースとするレイヤでは編集機能を使うことが出来ません。属性テーブルや地物フォームを表示するだけなら `*.shpfile` でも構わないじゃないかと思いますが、駄目です。

面倒くさそうだったのでずっと避けてきたのですが、先に進むために `PostGIS` サーバを使えるようにします。

### 1-1. 別のコンテナに PostGIS サーバをインストールする

実のところ `qwc-services` の `qwc-postgis` コンテナには既に `PostGIS` サーバが入っていて `qwc-services` 環境の設定情報（特にユーザ認証や権限管理のデータ）やデモ・データを保持しています。しかし `qwc-postgis` とは独立した `GIS` データ専用の `PostGIS` サーバをインストールする方が良いと思っています。と言うのは、

- `qwc-postgis` コンテナ内の `PostGIS` サーバはコンテナのアップデートや再デプロイ時にデータベースがリセットされる可能性がある
- コンテナ外の `PostGIS` サーバであればコンテナのアップデートや再デプロイの影響を受けない
- コンテナ外の `PostGIS` サーバの方が定期バックアップやデータ同期の仕組みも構築しやすい
- `qwc-services` 用と `GIS` データ用にサーバを分ける方が何かと簡明で管理しやすい

という理由によります。

ところが、`PostGIS` と `MapCache` の相性が良くないようで、両者をホストにインストールすると `httpd` が動かなくなるという障害が出ました。`httpd` が `core` を吐いて倒れるのです。いろいろと対策を試みましたが、

- `MapCache` ... `/lib64/libproj.so.22` に依存
- `PostGIS` その他 ... `/usr/proj95/lib64/libproj.so.25` に依存

という事情があり、二つの `libproj.so` が共存していると `mod_mapcache` が `httpd` のモジュールとしてうまく動作しないのです。

ここに来て、`MapCache` の「ちょっと古い」ところが障害となって現れた感じです。

となると、`PostGIS` サーバはコンテナ内に格納せざるを得ません。すなわち、既存の `qwc-postgis` コンテナの `PostGIS` サーバをそのまま利用するか、または、別途専用のコンテナを作ってそこに `PostGIS` サーバをインストールするか、どちらかです。

ここでは、システムとデータは分けておきたいという理由から、後者を選択します。

#### 1-1-1. docker-compose.yml

`qwc-docker/docker-compose.yml` に以下のように `postgis` のコンテナを追加します。

```diff_yaml:docker-compose.yml
    services:
      
      qwc-postgis:
        ...

+     postgis:
+       image: 'postgis/postgis:17-3.5-alpine'
+       environment:
+         POSTGRES_USER: gisdb
+         POSTGRES_PASSWORD: xxxxxxxx
+         POSTGRES_DB: gisdb
+       ports:
+         - "5432:5432"
+       volumes:
+         - './volumes/gisdb:/var/lib/postgresql/data'
+       healthcheck:
+         test: ["CMD-SHELL", "pg_isready -U gisdb"]
+         interval: 10s
```

- 'postgis/postgis:17-3.5-alpine'
    - `PostgreSQL` のメジャー・バージョンは `17` を指定
    - `PostGIS` のバージョンは `3.5` を指定
- 環境変数
    - `POSTGRES_USER` ... `PostgreSQL` のスーパー・ユーザ名
    - `POSTGRES_PASSWORD` ... スーパー・ユーザのパスワード
    - `POSTGRES_DB` ... データベース名
- ポート設定は `"127.0.0.1:5432:5432"` ではなく `"5432:5432"`
    - これはローカルホストだけでなく、外部からのアクセスも受け入れる予定があるため
- ボリューム・マウント
    - `./volumes/gisdb` ... データベースのデータ・ディレクトリ
        - 初回起動時に自動作成される

参照
- [github postgis/postgis](https://github.com/postgis/docker-postgis)
- [Docker PostGIS and PGAdmin](https://crivetimihai.github.io/geospacial-engineering/setup/)

### 1-2. pgAdmin4

`PostgreSQL` 管理のために `pgAdmin4` をインストールします。

これも `qwc-services` のコンテナに含めます。

#### 1-2-1. docker-compose.yml

`qwc-docker/docker-compose.yml` に以下のように `pgAdmin4` のコンテナを追加します。

```diff_yaml:docker-compose.yml
    services:
      
      qwc-postgis:
        ...
    
+     qwc-pgadmin:
+       image: dpage/pgadmin4:latest
+       ports:
+         - "8082:80"
+       volumes:
+         - ./volumes/pgadmin:/var/lib/pgadmin
+       environment:
+         PGADMIN_DEFAULT_EMAIL: 'webgis@mycomain.com'
+         PGADMIN_DEFAULT_PASSWORD: '********'
+       depends_on:
+         - qwc-postgis

```

#### 1-2-2. volumes/pgadmin

`composer.yml` で指定しているように、`qwc-docker/volumes` 下に `pgAdmin4` 用の共有ボリュームを作成します。

```bash
mkdir qwc-docker/volumes/pgadmin
sudo chown 5050:5050 qwc-docker/volumes/pgadmin
```

`5050` はコンテナ内で `pgAdmin` を実行する `User` の `ID` です。

#### 1-2-3. /etc/nginx/conf.d/webgis.conf

`/etc/nginx/conf.d/webgis.conf` に `pgAdmin4` 用のルーティングを追加します。

```diff_nginx:webgis.conf
        # MapCache
        location /mc {
            proxy_set_header    Host                $host;
            proxy_set_header    X-Real-IP           $remote_addr;
            proxy_set_header    X-Forwarded-For     $proxy_add_x_forwarded_for;
            proxy_set_header    X-Forwarded-Proto   $scheme;
            proxy_pass          http://localhost:8080/mc;
        }
+       # pgAdmin4
+       location /pga/ {
+           proxy_set_header    Host                $http_host;    # 注意!!
+           proxy_set_header    X-Script-Name       /pga;
+           proxy_set_header    X-Scheme            $scheme;
+           proxy_redirect      off;
+           proxy_pass          http://localhost:8082/;
+       }
        # qwc-services
        location / {
            proxy_set_header    Host                $host;
            proxy_set_header    X-Real-IP           $remote_addr;
            proxy_set_header    X-Forwarded-For     $proxy_add_x_forwarded_for;
            proxy_set_header    X-Forwarded-Proto   $scheme;
            proxy_pass          http://localhost:8088;
        }
```

:::note warn
`pgAdmin4` の `proxy_set_header Host` の値は `$host` ではなく `$http_host` である必要があります。
:::

:::note
`proxy_set_header` は面倒くさくても `location` ごとに書く方が安全です。外に出して共有するほうが合理的な場合もありますが、異なる設定を要求するものがある場合はうまく動かないことがあります。
:::

#### 1-2-4. pgAdmin4 で qwc_services の DB に接続

`docker compose up` でコンテナを起動し、`sudo systemctl restart nginx` で `nginx` を再起動すると、`https://webgis.mydomain/pga` で `pgAdmin4` にアクセスできるようになる筈です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/12310/3116a2e1-ae7b-4a49-9907-c39729ac85d3.png)

「新しいサーバを追加」ボタンを押して、`qwc_services` の `DB` に接続します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/12310/e6691605-ecc7-4846-a6fd-f5069119bb91.png)

- 名前 ... `qwc_configdb`

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/12310/c6c4d84d-3e8e-4b7f-b6a2-0f42c16fb010.png)

- ホスト名/アドレス ... `qwc-postgis`
- ポート番号 ... `5432`
- 管理用データベース ... `qwc_services`
- ユーザ名 ... `qwc_admin`
- パスワード ... `qwc_admin`
- パスワードを保存 ... お好みで `ON` にしても可

入力する情報は、`qwc-docker/pg_service.conf` の `[qwc_configdb]` セクションの設定に合わせます。

`Docker` ネットワークを通じてアクセスするため、ホスト名は `qwc-postgis`、ポート番号は `5432` となります。

「保存」ボタンを押すと、サーバが登録されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/12310/51b88c5b-0921-4626-822b-511ca83d1c2d.png)

- Servers
    - qwc_configdb
        - データベース
            - qwc_services
                - スキーマ
                    - qwc_config
                        - テーブル

を展開すると、上のスクリーンショットのように、ユーザ認証や権限管理のデータを保管するテーブルが存在することが分ります。

#### 1-2-5. pgAdmin4 で postgis の DB に接続

「新しいサーバを追加」ボタンを押して、`postgis` コンテナの `DB` に接続します。

- 名前 ... `gisdb`
- ホスト名/アドレス ... `postgis`
- ポート番号 ... `5432`
- 管理用データベース ... `gisdb`
- ユーザ名 ... `gisdb`
- パスワード ... `********`
- パスワードを保存 ... お好みで `ON` にしても可

入力する情報は、`qwc-docker/docker-compose.yml` の `postgis` コンテナの設定に合わせます。

`Docker` ネットワークを通じてアクセスするため、ホスト名は `postgis`、ポート番号は `5432` となります。

「保存」ボタンを押すと、サーバが登録されます。

### 1-3. VPN (WireGuard) をセットアップ

`PostGIS` サーバへの安全なアクセスを確保するために `VPN` (`wireGuard`) を導入します。

参考:
- [Install and Configure WireGuard on AlmaLinux 9: Best Guide](https://orcacore.com/install-configure-wireguard-almalinux-9/)

#### 1-3-1. Wireguard Kernel Module を有効にする

手動で `wireguard` モジュールをロードしてみます。
```bash
sudo modprobe wireguard
```

ロードされたかどうか、確認します。
```bash
lsmod | grep wireguard
```

上記の出力結果はこんな感じです。
```
wireguard             118784  0
ip6_udp_tunnel         16384  1 wireguard
udp_tunnel             36864  1 wireguard
curve25519_x86_64      36864  1 wireguard
libcurve25519_generic  45056  2 curve25519_x86_64,wireguard
```
参考文書と少し違います（`libblake2s` が出て来ません）が、まあ問題無いでしょう。

システム起動時に `woreguard` モジュールをロードするように設定します。
```bash
su 
echo wireguard > /etc/modules-load.d/wireguard.conf
```

#### 1-3-2. wireguard-tools をインストールする

`wireguard-tools` をインストールします。
```bash
sudo dnf install wireguard-tools
```

#### 1-3-3. サーバの鍵ペアを生成する

サーバの秘密鍵 `/etc/wireguard/server.key` を作成します。

```bash
su
wg genkey | tee /etc/wireguard/server.key
```

秘密鍵のパーミッションを変更します。
```bash
chmod 0400 /etc/wireguard/server.key
```

サーバの公開鍵 `/etc/wireguard/server.pub` を作成します。
```bash
cat /etc/wireguard/server.key | wg pubkey | tee /etc/wireguard/server.pub
```

:::note warn
参考文書では、サーバでクライアントの鍵ペアも生成していますが、Windows のクライアントの場合は、クライアント側で生成する方が楽なので、説明を割愛します。
:::

#### 1-3-4. Windows クライアントに WireGuard をインストールする

[WireGuard > Installation](https://www.wireguard.com/install/) のページから Windows 用のインストーラをダウンロードしてインストールします。

インストールすると、次のような画面が起ち上がります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/12310/d60e5a14-f201-4355-bbe6-388cfdbeb52b.png)

#### 1-3-5. Windows クライアントの鍵ペアを生成する

「トンネルの追加 > 空のトンネルの追加」を選び、適当に名前を付けて保存します。（トンネルの名前がネットワーク接続の名前にもなりますので、`wg-vpn` などが良いでしょう）

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/12310/ed9adad5-039f-4a74-a65d-f4ac5797d2f5.png)

表示されている「公開鍵」と `PrivateKey` が、この Windows クライアントの鍵ペアです。

#### 1-3-6. 設定ファイルを作成する

サーバ上に構成ファイル `/etc/wireguard/wg0.conf` を作成します。

```ini:wg0.conf
[Interface]
# PostGIS server on Internet (webgis.mydomain)
PrivateKey = xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Address = 192.168.20.11/24
ListenPort = 51820
SaveConfig = false

[Peer]
# QGIS Server on Internet (gissvr.mydomain)
PublicKey = yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
AllowedIPs = 192.168.20.12/32
Endpoint = gissvr.mydomain:51820
# Endpoint = 123.123.123.123:51820

[Peer]
# Windows desktop on home LAN
PublicKey = yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
AllowedIPs = 192.168.20.101/32
Endpoint = myhome.on.ddns:51820

[Peer]
# PostGIS test server on home LAN
PublicKey = zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
AllowedIPs = 192.168.20.21/32
Endpoint = myhome.on.ddns:51830
```

- `[Interface]`
    - `PrivateKey` ... このサーバの秘密鍵 `/etc/wireguard/server.key`
    - `Address` ... このサーバの `WireGuard VPN` 上のアドレス
    - `ListenPort` ... このサーバが `Listen` するポート
    - `SaveConfig` ... `wg0.conf` を自動更新しない（※1）
- `[Peer]`（※2）
    - `PublicKey` ... クライアントの公開鍵
    - `AllowedIPs` ... クライアントの `WireGuard VPN` 上のアドレス
    - `Endpoint` ... クライアントのインターネット上のアドレスとポート（※3,4）

※1 ネット上の文書では `SaveConfig` を `true` にする設定例が一般的ですが、`VPN` を構成する端末が少なく、設定変更の頻度も少ない場合は `false` にしておくことをお奨めします。

※2 `[Peer]` ブロックは必要に応じて好きなだけ追加してください。

※3 `Endpoint` のアドレスは名前で指定しても構いません。

※4 家庭内 LAN など、端末が一意のグローバル・アドレスを持たない環境では、`Dynamic DNS` を使って名前解決をし、更に、ルータのポート・マッピング機能によって特定のポートと特定の端末を関連付けしておく必要があります。

#### 1-3-7. `Port Forwarding` や `NAT` は不要

ネット上の文書では、サーバのポート・フォワーディングを有効にする設定例が一般的ですが、ここでは不要ですので何もしません。

`VPN` サーバでポート・フォワーディングをするのは、`VPN` 経由でインターネットにアクセスする必要がある場合です。ここでは、`QGIS Desktop` と `PostGIS` サーバの間、または、`QGIS Server` と `PostGIS` サーバの間で `SSH` のようなセキュアな二点間通信をしたいだけですので、ポート・フォワーディングは不要です。

同様に多くの記事で見かける `[Interface]` セクションの `PostUp` と `PostDown` の設定も、`VPN` 経由でインターネットに出ていくための `NAT` に関係する処理であり、ここでは不要です。

#### 1-3-8. ファイアウォールを設定する

ファイアウォールの `51820/udp` を開放します

```bash
sudo firewall-cmd --add-port=51820/udp --permanent
```

設定をリロードして変更を適用します。
```bash
sudo firewall-cmd --reload
```

#### 1-3-9. サービスを起動する

```bash
sudo systemctl start wg-quick@wg0.service
sudo systemctl enable wg-quick@wg0.service
```

#### 1-3-10. Docker コンテナ起動への影響？

私の環境では `WireGuard` サービスを有効にしたときに、`docker compose up` が異常終了する場合がありました。常にではなく、たまに異常終了するのです。明確な理由は不明です。

以下の折り畳んだところは、障害の原因はシステム起動時に `WireGuard` サービスと `Docker` サービスが並行して起動することだろうと推測して対策を取ったときの記録です。私のためだけのメモですので、スキップしてください。

<details><summary>どたばたの作業記録</summary>

`docker compose up` が異常終了することがあるのは、システム起動時に `WireGuard` サービスと `Docker` サービスが並行して起動することが原因になっていると推測されるので、`Docker` サービスの起動完了を待ってから `WireGuard` サービスを起動するように設定します。

具体的には、
```bash
sudo systemctl edit wg-quick@wg0.service
```
というコマンドを実行します。

すると、次のようなエディタの画面が表示されます。
```ini
### Editing /etc/systemd/system/wg-quick@wg0.service.d/override.conf
### Anything between here and the comment below will become the new contents of the file



### Lines below this comment will be discarded

### /usr/lib/systemd/system/wg-quick@.service
# [Unit]
# Description=WireGuard via wg-quick(8) for %I
# After=network-online.target nss-lookup.target
# Wants=network-online.target nss-lookup.target
# PartOf=wg-quick.target
# Documentation=man:wg-quick(8)
# Documentation=man:wg(8)
# Documentation=https://www.wireguard.com/
# Documentation=https://www.wireguard.com/quickstart/
# Documentation=https://git.zx2c4.com/wireguard-tools/about/src/man/wg-quick.8
# Documentation=https://git.zx2c4.com/wireguard-tools/about/src/man/wg.8
#
# [Service]
# Type=oneshot
# RemainAfterExit=yes
# ExecStart=/usr/bin/wg-quick up %i
# ExecStop=/usr/bin/wg-quick down %i
# ExecReload=/bin/bash -c 'exec /usr/bin/wg syncconf %i <(exec /usr/bin/wg-quick strip %i)'
# Environment=WG_ENDPOINT_RESOLUTION_RETRIES=infinity
#
# [Install]
# WantedBy=multi-user.target
```

冒頭の空行部分に、次のように4行を追加します。
```diff_ini
    ### Editing /etc/systemd/system/wg-quick@wg0.service.d/override.conf
    ### Anything between here and the comment below will become the new contents of the file
    
+   [Unit]
+   After=docker.service
+   [Service]
+   ExecStartPre=/bin/bash -c 'while ! systemctl is-active --quiet docker; do sleep 1; done'
    
    ### Lines below this comment will be discarded
    ...
```

- [Unit] 
    - After=docer.service
        - docker.service より後に起動を開始することを指定
- [Service] 
    - ExecStartPre=...
        - 起動を開始する前に実行することを指定

`After=docker.service` は **起動を開始する順序** を制御しますが、**起動が完了してから** という条件を付けるものではありません。

そこで、`ExcecStartPre` を使って、`docker.service` が `active` になるまで待機してから起動するように指定しています。

編集が完了したら `:wq` で保存してエディタを終了します。

実際にはエディタで見たままの内容のファイルは保存されず、追加した4行だけを内容とするファイルが `/etc/systemd/system/wg-quick@wg0.service.d/override.conf` として作成されます。

設定変更を有効にするために、設定をリロードし、念のためにシステムを再起動します。

```bash
sudo systemctl daemon-reload
sudo reboot
```

:::note warn
`systemd` の構成ファイルにおいて、サービス間の関係を指定する `After` と `Requires` をよく目にしますが、次のことを知っておいて損はしません。
- `After` は起動開始順序を指定するだけで、起動完了を待つものではない
- `Requires` も同様にサービスの依存関係を指定するだけで、起動開始順序を指定したり、起動完了を待ったりするものではない
:::

</details>

上記で行った対策は効果が無かったので、対策前の状態に戻しました。しかし、別の要因が働いたのか、理由はよく分りませんが、現在では障害はほとんど発生しなくなっています。

`docker compose up` で障害が発生した場合は、`sudo systemctl restart docker.service` によって `Docker` サービスを再起動すると `docker compose up` が正常に動作するようになるので、まあ良いか、とことで済ませています。

#### 1-3-11. Windows クライアント側の設定にサーバを追加

先ほど作成して保存したトンネルを編集します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/12310/29cf97b7-9649-4c9b-9415-3c566a2b8c36.png)

```ini
[Interface]
# Windows desktop on home LAN
PrivateKey = xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Address = 192.168.20.101/24
ListenPort = 51820

[Peer]
# PostGIS server on Internet (webgis.mydomain)
PublicKey = yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
AllowedIPs = 192.168.20.11/32
Endpoint = webgis.mydomain:51820
# Endpoint = 123.123.123.123:51820

[Peer]
# PostGIS test server on home LAN
PublicKey = zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
AllowedIPs = 192.168.20.21/32
Endpoint = 192.168.0.33:51830
```

- `[Interface]`
    - `PrivateKey` ... Windows Desktop の秘密鍵
    - `Address` ... Windows Desktop の `WireGuard VPN` 上のアドレス
    - `ListenPort` ... Windows Desktop が `Listen` するポート
- `[Peer]`
    - `PublicKey` ... `Peer` の公開鍵
    - `AllowedIPs` ... `Peer` の `WireGuard VPN` 上のアドレス
    - `Endpoint` ... `Peer` のインターネット上のアドレスとポート（※）

※ `Peer` に Windows desktop と同一の家庭内 LAN 上の端末やサーバを指定する場合は、`Endpoint` に LAN 上のアドレスとポートを指定します。

トンネルの情報を保存して「有効化」すると、サーバとの間で `VPN` 接続が可能になります。

コマンド・プロンプトを開いて、トンネルを有効にしたときだけレスポンスが返ってくることを確認してください。
```
ping 192.168.20.11
```

### 1-4. PostGIS サーバへの VPN 経由アクセスを許可する

#### 1-4-1. ファイアウォールの設定

ポート `5432/tcp` を全面的に開放するのでなく、アドレス範囲を VPN のネットワークに限って開放します。

```bash
sudo firewall-cmd --add-rich-rule='rule family="ipv4" source address="192.168.20.0/24" port port="5432" protocol="tcp" accept' --permanent
sudo firewall-cmd --reload
```

ファイアウォールの状態を確認します。

```bash
sudo firewall-cmd --list-all
```

```
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: ens160
  sources:
  services: cockpit dhcpv6-client http https ssh
  ports: 51820/udp
  protocols:
  forward: yes
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:
        rule family="ipv4" source address="192.168.20.0/24" port port="5432" protocol="tcp" accept
```

#### 1-4-2. Docker のネットワーク設定

`postgis` コンテナのポート設定を確認し、ローカルホストからのアクセスだけでなく外部からのアクセスも許可するようにします。

```diff_yaml:docker-compose.yml
    services:
      
      qwc-postgis:
        ...
        ports:
          - "127.0.0.1:5439:5432"
        ...

      postgis:
        ...
        ports:
-         - "127.0.0.1:5432:5432"
+         - "5432:5432"
        ...

```

`qwc-postgis` についても、必要があれば、`"127.0.0.1:"` の限定を除去してください。

#### 1-4-3. PostGIS サーバへのアクセス許可を変更

`qwc-docker/volumes/gisdb/pg_hba.conf` に次のように一行追加して、`PostGIS` サーバへの `WireGuard VPN` 経由のアクセスを許可します。

```diff_conf:pg_hba.conf
    # TYPE  DATABASE        USER            ADDRESS                 METHOD
+   host    gisdb           gisdb           192.168.20.0/24         trust
```

- `ADDRESS` ... `WireGuard VPN` のネットワーク

### 1-5. QGIS Desktop から PostGIS サーバに接続する

#### 1-5-1. Windows に pg_service.conf を設置する

どこでも良いのですが、どこかに安全なフォルダ（例えば、`C:\Users\Public\postgresql`）を作成して、その中に `pg_service.conf` というファイルを作成します。そして、このファイルに `postgis` コンテナ内の `PostgreSQL` サーバに接続するための情報を記載しておきます。

```ini:pg_service.conf
[igis-postgis]
host=192.168.20.11
port=5432
dbname=gisdb
user=gisdb
password=********
sslmode=disable
```

`qwc-services` の `qwc-docker/pg_service.conf` と同じ書式です。

- `[igis-postgis]` ... `PostgreSQL` 接続のサービス名
    - 任意の名前を指定
- `host` ... ホストのアドレスまたは名前
- `port` ... ポート番号
- `dbname` ... データベース名
- `user` ... 接続に使用するユーザ名
- `password` ... パスワード
- `sslmode` ... SSL モード

コマンド・プロンプトまたは `Windows PowerShell` を開き、下記のコマンドを実行して環境変数 `PGSERVICEFILE` を登録します。

```powershell
setx PGSERVICEFILE C:\Users\Public\postgresql\pg_service.conf
```

#### 1-5-2. データベース接続の登録

`QGIS Desktop` で `PostGIS` サーバへの接続を新規登録します。

- データソース・マネージャ > PostgreSQL > 新規

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/12310/045e3f99-aa18-46c9-964e-3694fb700549.png)

- 名前 ... 適当に
- サービス ... `pg_service.conf` に登録したサービス名

その他の項目は `pg_service.conf` から補完されます。上書きして変更したい場合以外は入力する必要はありません。

「接続テスト」をしてオーケーなら「OK」でデータベース接続を登録します。

:::note warn
`pg_service.conf` を使用しない場合は、以下の項目を入力する必要があります。

- ホスト ... `PostGIS` サーバの `VPN` 上のアドレス
- ポート番号 ... `PostGIS` サーバのポート番号
- データベース ... `PostGIS` サーバのデータベース名
- SSLモード ... `disabled`
- セッション ROLE ... データベース操作に使うユーザ名
- 認証 ... データベース接続のユーザ名とパスワード
:::

#### 1-5-3. データベース接続のテスト（データのエクスポート）

テストとして、レイヤのデータを `PostGIS` サーバにエクスポートしてみます。

- プロセシング・ツール > Database > Export to PostgreSQL

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/12310/3ef007b7-1408-4d51-848b-fae615d2c2d3.png)

- レイヤ ... エクスポートするレイヤを指定
- データベース（接続名） ... 先ほど登録した `igis-postgis` を選択
- スキーマ ... `public`
- エクスポートするテーブル ... 空白にするとレイヤ名がそのまま使われる
- 主キー ... オプション

「実行」ボタンを押すとレイヤのデータがエクスポートされます。

#### 1-5-4. データベース接続のテスト（レイヤの読み込み）

エクスポートしたレイヤのデータを `PostGIS` サーバから読み込んでみます。

- データソース・マネージャ > PostgreSQL

`igis-postgis` を選んで接続し、`public` スキーマを展開して、先ほどエクスポートしたテーブルを選択して「追加」ボタンを押します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/12310/79dec7c7-93d1-45b1-b2dd-6874902404d8.png)

これでプロジェクトに `PostGIS` サーバにあるレイヤ・データが追加されました。

### 1-6. pgAdmin4 から PostGIS サーバに接続する

`postgis` コンテナ内の `PostGIS` サーバは既に `pgAdmin4` に登録されていますので、下のスクリーン・ショットのように、先ほど登録したテーブルを見ることが出来ます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/12310/1e2691ad-e7e7-414a-9349-67bfed6ab442.png)

この接続は `Docker` ネットワーク上のホスト名を使って登録しましたが、今では `VPN` 上のアドレスをホスト名として使って登録することも可能です。

## 2. データの移行

現在 `ShapeFile` で保持している `GIS` データを全て `PostGIS` のデータベースにエクスポートします。

数が少ないなら、上記の「データベース接続のテスト（データのエクスポート）」でやったように、ちまちまと一つずつエクスポートしても構わないのですが、スクリプトを使ってまとめてエクスポートするのが効率的です。

### 2-1. PowerShell の環境設定

`Windows PowerShell` を使いますので、そのために `PATH` 環境変数に `QGIS` や `GDAL` などの実行ファイルのパスを追加します。

`PowerShell` を開いて次のコマンドを実行します。

```powershell
[System.Environment]::SetEnvironmentVariable("Path", $env:Path + ";C:\OSGeo4W\bin;C:\OSGeo4W\apps\gdal\bin;C:\OSGeo4W\apps\Python312", [System.EnvironmentVariableTarget]::User)
```

上記で `C:\OSGeo4W` は `OSGeo4W` のインストール先ディレクトリです。別の場所にインストールした場合は適宜読み替えてください。64-bit 版の場合は `OSGeo4W64` となっている筈です。

いったん `PowerShell` を終了して、再度 `PowerShell` を開いて、次のコマンドを実行します。

```powershell
ogr2ogr --version
```
`PATH` の設定が正常に出来ている場合は、以下のように `GDAL` のバージョンが表示されます。
```
GDAL 3.9.3, released 2024/10/07
```

### 2-2. ogr2ogr で shapefile を PostGIS にインポート

一つの `shapefile`（とその従属するファイル）を `PostGIS` にインポートするためには、次のようにします。

```powershell
ogr2ogr -f "PostgreSQL" PG:"dbname=<dbname> user=<user> password=<password> host=<host> port=<port>" "<\path\to\shapefiles\shapfile.shp>" -nln <table_name> -overwrite -progress
```

例えば、こんな感じです。
```powershell
ogr2ogr -f "PostgreSQL" PG:"dbname=gisdb user=gisdb password=xxxxxxxx host=192.168.20.11 port=5432"  \devs\webgis\projects\shapefiles\area.shp -nln area -overwrite -progress
```
出力結果
```
0...10...20...30...40...50...60...70...80...90...100 - done.
```

しかし、これだと `QGIS Desktop` でちまちまやるのと大差が無いので、もう少し工夫をします。

### 2-3. ogr2ogr で shapefile を PostGIS にインポート（一括処理版）

スクリプト・ファイルを作成して、ディレクトリ下の全ての `shapefile` をまとめて `PostGIS` データベースにインポートするようにします。

#### 2-3-1. スクリプト格納場所

`C:\scripts` というディレクトリを作って、その下にスクリプトを格納することにします。
```powershell
mkdir c:\scripts
```

便利なように、パスを通しておきます。
```powershell
[System.Environment]::SetEnvironmentVariable("Path", $env:Path + ";c:\scripts", [System.EnvironmentVariableTarget]::User)
```

`PowerShell` の再起動が必要です。

#### 2-3-2. 実行ポリシーの変更

`PowerShell` の実行ポリシーを変更します。
```powershell
Set-ExecutionPolicy RemoteSigned -Scope CurrentUser
```
実行ポリシーの既定値は `Restricted` で、そのままだと、スクリプトを実行することが出来ません。

- Restricted（デフォルト） ... スクリプト実行禁止
- RemoteSigned ... ローカルのスクリプトは実行可、ダウンロードしたスクリプトは署名が必要
- Unrestricted ... すべてのスクリプトが実行可能（セキュリティリスクあり）
- Bypass ... 完全にチェックなし（危険）

`Get-ExecutionPolicy` で現在の実行ポリシーを取得できます。

`PowerShell` の再起動は必要ありません。

#### 2-3-3. 一括インポートのスクリプト

`C:\scripts\shp2table.ps1` を以下の内容で作成します。

```powershell:shp2table.ps1
Param (
    [string]$dir = "",
    [string]$dbname = "gisdb",
    [string]$schema = "public",
    [string]$user = "gisdb",
    [string]$password = "********",
    [string]$address = "192.168.20.11",
    [string]$port = "5432"
)

# ディレクトリを指定しなかった場合はエラー
if (-not $dir) {
    Write-Host "Error: Shapefile が格納されているディレクトリが指定されていません。"
    Write-Host "Usage: shp2table.ps1 -dir <Shapefile格納ディレクトリ>`n"
    exit 1
}

# ディレクトリが存在しない場合はエラー
if (-not (Test-Path $dir)) {
    Write-Host "Error: 指定したディレクトリが存在しません。"
    Write-Host "Usage: shp2table.ps1 -dir <Shapefile格納ディレクトリ>`n"
    exit 1
}

Write-Host "Shapefile Directory: $dir"
Write-Host "PostGIS: dbname = $dbname, schema = $schema, user = $user, host = $address, port = $port"

# 変換（危険な文字をすべてアンダースコアに）
$schema = $schema -replace "[\W]", "_"  # `\W` は「英数字と_以外の文字」

# 数字から始まる場合、プレフィクスを追加
if ($schema -match "^\d") {
    $schema = "s_" + $schema
}

# スキーマが存在しなければ作成
$schemaCheckCmd = "psql -U $user -h $address -p $port -d $dbname -t -c `"SELECT schema_name FROM information_schema.schemata WHERE schema_name = '$schema';`""
$schemaExists = Invoke-Expression $schemaCheckCmd

if (-not $schemaExists.Trim()) {
    Write-Host "`nスキーマ '$schema' が存在しないため作成します ..."
    $createSchemaCmd = "psql -U $user -h $address -p $port -d $dbname -c `"CREATE SCHEMA IF NOT EXISTS $schema;`""
    Invoke-Expression $createSchemaCmd
}

# Shapefile を処理
$shapefiles = Get-ChildItem "$dir\*.shp"

foreach ($shp in $shapefiles) {
    $shpName =  [System.IO.Path]::GetFileName($shp.Name)
    $tableName = [System.IO.Path]::GetFileNameWithoutExtension($shp.Name)

    # 日本語を含む名前のファイルはスキップ
    if ($tableName -match "[\p{IsCJKUnifiedIdeographs}\p{IsHiragana}\p{IsKatakana}]") {
        Write-Host "`nWarning: '$shpName' は処理をスキップします。"
        Write-Host "日本語を含む名前のファイルは処理対象外です。"
        continue
    }

    # 変換（危険な文字をすべてアンダースコアに）
    $tableName = $tableName -replace "[\W]", "_"  # `\W` は「英数字と_以外の文字」
    # 数字から始まる場合、プレフィクスを追加
    if ($tableName -match "^\d") {
        $tableName = "t_" + $tableName
    }
    Write-Host "`nImporting $shpName to $schema.$tableName ..."

    # 既存のテーブルを削除
    $dropTableCmd = "psql -U $user -h $address -p $port -d $dbname -c 'DROP TABLE IF EXISTS $schema.$tableName CASCADE;'"
    Invoke-Expression $dropTableCmd

    # ogr2ogr でインポート
    ogr2ogr -f "PostgreSQL" PG:"dbname=$dbname user=$user password=$password host=$address port=$port" $shp.FullName -nln "$schema.$tableName" -overwrite -progress
}

Write-Host "`n全ての shapefile がインポートされました。`n"
```

スクリプトの使用方法は `"shp2table.ps1 -dir <Shapefile格納ディレクトリ>"` です。格納ディレクトリを指定しなかったり、そのディレクトリが存在しなかったりするとエラーになり、使用方法が表示されます。

その他のパラメータも指定可能ですが、デフォルト値が提供されますので、通常指定する必要はありません。デフォルト値は環境に合せて調整してください。

- スキーマ名、テーブル名は `PostgreSQL` での使いやすさを考えて、変換を行います
    - 英数字と `_` （アンダースコア）以外の文字は `_` に変換
    - 先頭が数字で始るものは `s_` または `t_` のプレフィクスを追加
    - 日本語を含むものは処理をスキップ
- スキーマ名とテーブル名が同じテーブルが既に存在する場合は、削除してからインポート処理をします
- データのカテゴリーに合せてスキーマを指定するといろいろと便利です

## 3. プロジェクトの移行

`QGIS` プロジェクトのベクタ・レイヤのソースを `Shapefile` から `PostGIS` のテーブルに置き換えます。

残念ながらレイヤのソースだけを入れ換えることは出来ないようなので、次のような手順で作業を進めます。

1. `Layer A` の `Shapefile` に相当するテーブルを `PostGIS` サーバから探して読み込み、レイヤ・ツリーに追加する（`Layer B`）
1. スタイルのコピー
    - `Layer A` のスタイルをクリップボードにコピーする
    - クリップボードにコピーしたスタイルを `Layer B` に貼り付ける
1. プロジェクトのプロパティ > `QGIS Server`
    - `WMTS` のタブで `Layer B` を `Layer A` と同じ設定にする
    - `WFS/OAPIF` のタブで `Layer B` を `Layer A` と同じ設定にする
1. `Layer B` のレイヤのプロパティ
    - `フィールド` のタブで `Layer A` と同じ値を入力する
    - `QGIS Server` のタブで `Layer A` と同じ値を入力する
1. `Layer A` を削除する
1. `Layer B` の名前を `Layer A` の名前に変更する
1. プロジェクトを上書き保存（または名前を付けて保存）する

### 3-1. データベースからレイヤを読み込む

`Layer A` に相当するテーブルを `PostGIS` サーバから読み込みます。

- データソース・マネージャ > PostgreSQL

`gisdb` を選んで接続し、`public` スキーマを展開して、テーブルを選択して「追加」ボタンを押します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/12310/79dec7c7-93d1-45b1-b2dd-6874902404d8.png)

これでプロジェクトに `PostGIS` サーバから `Layer B` が追加されました。

### 3-2. スタイルのコピー

レイヤ・ツリー上で、`Layer A` を選び、右クリック・メニュー > スタイル > スタイルをコピー > 全スタイルカテゴリを選びます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/12310/c2ae1b20-4b8e-40bf-91bd-f2813eb21253.png)

そして、`Layer B` を選び、右クリック・メニュー > スタイル > スタイルを貼り付け > 全スタイルカテゴリを選びます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/12310/8897130f-08ec-420f-a81b-991edbe1f306.png)

以上で `Layer A` のスタイルが `Layer B` にコピーされます。

### 3-3. プロジェクトのプロパティ > QGIS Server

#### 3-3-1. WMTS

`Layer A` のチェック状態を `Layer B` にも適用します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/12310/d22d0510-8a60-4d91-886a-6e4a2268b285.png)

#### 3-3-2. WFS/OAPIF

`Layer A` のチェック状態を `Layer B` にも適用します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/12310/06156559-8dc4-4e48-89b9-19f97831609f.png)

### 3-4. レイヤのプロパティ

この部分は `Layer A` と `Layer B` を並べて見ることが出来ないので、かなり面倒くさい作業になります。

#### 3-4-1. フィールド

「設定」欄のドロップダウンを操作して、`Layer A` と同じ設定にします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/12310/601d5b4a-f6b6-440e-9598-32bf726fe019.png)

#### 3-4-2. QGIS Server

以下の項目について、`Layer A` と同じ内容を入力します。

- 短い名前
- タイトル
- 要約
- キーワード

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/12310/ebc0f9d6-8a73-4fa4-88b0-ac3481022d67.png)

他にも設定している項目があれば、同じように入力します。

### 3-5. 面倒くさい

プロジェクトの移行作業は、上で見たように、特に難しい所は無いのですが、手作業になる部分があるため、面倒くさい作業になります。対象となるレイヤの数が多い場合は特にそうです。

スクリプトによる自動化も考えられますが、そのスクリプトを作成する作業もまた輪を掛けて面倒くさそうなので、どっちの面倒くささを取るか悩ましいところです。

出来るだけ早めに `Shapefile` への依存から脱却して `PostGIS` を使い始めるのが良いですね。

## 4. qwc-services に新しい DB 接続を登録する

`QGIS` プロジェクトのレイヤが `PostGIS` のテーブルをソースとする場合、`PostGIS` サーバへの接続情報がプロジェクト・ファイルに埋め込まれます。

接続情報としては、ホスト名（またはアドレス）、ポート番号、データベース名、接続に使用するユーザ名とパスワードなどが必要になります。それらを全てプロジェクト・ファイルに埋め込む必要があるかというと、`qg_service.conf` の「サービス名」を使って接続する場合はサービス名だけを埋め込めば良いということになっています。

前段で `shapefile` から `PostGIS` に移行したプロジェクトでは、`PostGIS` サーバへの接続情報はサービス名を使って記述されています。

従って、`qwc-services` 側でも、対応するサービスのエントリを `pg_service.conf` に追加登録する必要があります。

### 4-1. qwc-services の pg_service.conf を更新する

`qwc-services` の `pg_service.conf` に `[igis-postgis]` エントリを追加します。

```diff_ini:pg_service.conf
    [qwc_configdb]
    host=qwc-postgis
    port=5432
    dbname=qwc_services
    user=qwc_admin
    password=qwc_admin
    sslmode=disable
    
    [qwc_geodb]
    host=qwc-postgis
    port=5432
    dbname=qwc_services
    user=qwc_service_write
    password=qwc_service_write
    sslmode=disable
+    
+   [igis-postgis]
+   host=192.168.20.11
+   port=5432
+   dbname=gisdb
+   user=gisdb
+   password=********
+   sslmode=disable
```

### 4-2. pg_service.conf を使うメリット

ちょっと見ただけでは手間が増えるだけのようにも思えますが、`pg_service.conf` を使ってデータベース接続を管理することには非常に大きなメリットが存在します。

#### 4-2-1. 可搬性・保守性が高くなる

今回は `VPN` 接続によって、Windows Desktop からも Web 上の `qwc-services` からも同じアドレスとポートで同一の `PostGIS` サーバにアクセスが出来ますが、そうでない運用環境である場合も容易に想像できます。例えば、同一の物理サーバであっても、インターネット上のアドレスは異なるとか、そもそも別の物理サーバであるとかです。

そういう場合でも、`QGIS` プロジェクトに埋め込まれている接続情報がサービス名だけであれば、それぞれの環境で適切な `pg_service.conf` を用意すれば対応できます。接続情報を変更した `QGIS` プロジェクト・ファイルの別バージョンを用意する必要はありません。

また、`PostGIS` サーバを別の物理サーバに移転するでも、それぞれの `pg_service.conf` だけを修正すれば済みます。`QGIS` プロジェクト・ファイルには手を触れる必要がありません。

#### 4-2-2. セキュリティが高くなる

`QGIS` プロジェクト・ファイルに物理サーバの情報や接続ユーザ名などを記載する必要がありませんので、セキュリティの面でも非常に有利です。

プロジェクトを他者に譲渡または貸与する場合でも、以下のものを渡すだけで用が足ります。

1. プロジェクト・ファイル本体
1. データベースのダンプ
1. 接続に使うサービス名

接続情報の詳細を開示しなくても、相手側で同じプロジェクトを再現できることに着目して下さい。自分で適当な `pg_service.conf` を作成すれば良いのです。

## 5. PostGIS DB のバックアップ

`cron` で定期的に `PostGIS` DB のバックアップを取ることにします。

バックアップ自体は通常の `PostgreSQL` 同様に `pg_dump` を使いますが、`Docker` コンテナ内にある DB なので、`docker exec` 経由での実行になります。

### 4-1. バックアップ用ディレクトリ

ホストにバックアップ保存用ディレクトリを作成します。

```bash
sudo mkdir /backup
sudo mkdir /backup/postgis
sudo mkdir /backup/qwc-postgis
sudo chmod 777 /backup/postgis /backup/qwc-postgis
```

作成したバックアップ用ディレクトリをコンテナでボリューム・マウントします。

```diff_yaml:docker-compose.yml
    services:
    
      qwc-postgis:
        ...
        volumes:
          - ./volumes/db:/var/lib/postgresql/docker
+         - /backup/qwc-postgis:/backup
        ...       
    
      postgis:
        ...
        volumes:
          - ./volumes/gisdb:/var/lib/postgresql/data
+         - /backup/postgis:/backup
        ...
```

上記では、`qwc-postgis` コンテナ用に `/backup/qwc-postgis`, `postgis` コンテナ用に `/backup/postgis` という二つのディレクトリを用意して、それぞれボリューム・マウントしています。

このあたりの詳細は好みによって変更して差し支えありません。

### 4-2. バックアップのテスト実行

次のコマンドを実行して、バックアップが出来るかどうか、テストします。

```bash:qwc-postgis のバックアップ
docker exec -i qwc-docker-qwc-postgis-1 pg_dump -U qwc_admin -h localhost -p 5432 -F c -b -v -f /backup/qwc_services_backup_$(date +\%Y\%m\%d).dump qwc_services
```
```bash:postgis のバックアップ
docker exec -i qwc-docker-postgis-1 pg_dump -U gisdb -h localhost -p 5432 -F c -b -v -f /backup/gisdb_backup_$(date +\%Y\%m\%d).dump gisdb
```

:::note
どちらもコンテナ内で実行されるので、ホスト（`-h`）は `localhost`、ポート（`-p`）は `5432` になります。
:::

上記を実行して、ホストの `/backup/qwc-postgis` に `qwc_services_backup_20250316.dump`、`/backup/postgis` に `gisdb_backup_20250316.dump` が作成されていることを確認します。

### 4-3. cron でバックアップを定期的に実行

`sudo crontab -e` で以下を追加します。

```
0 3 * * * docker exec -i qwc-docker-qwc-postgis-1 pg_dump -U qwc_admin -h localhost -p 5432 -F c -b -v -f /backup/qwc_services_backup_$(date +\%Y\%m\%d).dump qwc_services
5 3 * * * docker exec -i qwc-docker-postgis-1 pg_dump -U gisdb -h localhost -p 5432 -F c -b -v -f /backup/gisdb_backup_$(date +\%Y\%m\%d).dump gisdb
```
毎日 03:00 に `qwc-postgis` のバックアップを実行し、03:05 に `postgis` のバックアップを実行する設定です。

:::note 
`sudo` で実行したので、登録されたジョブは `root` ユーザとして実行されることになります。

一般に `crontab -e` で `cron` ジョブを登録するときは、`/etc/crontab` を直接に編集する場合と違って実行ユーザを指定しません。`crontab -e` を実行したユーザが `cron` ジョブの実行ユーザとなります。

また、`crontab -e` で `cron` ジョブを登録したときは、`crond` サービスの明示的な再起動は不要です。
:::

### 4-4. 古いバックアップの削除

これも `cron` にやらせます。

`sudo crontab -e` で以下を追加します。

```
0 4 * * * find /backup/qwc-postgis/ -name "qwc_services_backup_*.dump" -mtime +30 -exec rm {} \;
5 4 * * * find /backup/postgis/ -name "gisdb_backup_*.dump" -mtime +30 -exec rm {} \;
```

30日以上前のバックアップは削除するという設定です。

## And so, what's next?

次こそはいよいよ `QWC2 Viewer` の編集機能を試そうと思っていたのですが、予定を変更して、`Flask` によって `QWC2 Viewer` とは独立したページを Web GIS の一部として作成することに挑戦します。

 - [qwc-services による Web GIS の構築 #4 独自ページを追加](https://qiita.com/softark/items/410fca12a8f5ad8ee129)