ラズパイ４に docker-composeをインストールし、SQLite3データベースのPostgreSQLへのデータ移行を自動化するシェルスクリプトを紹介します。
以下、ラズパイ４は [Raspbery Pi 4 ModelB]、ラズパイゼロは [Raspberry Pi Zero WH] として話をすすめます。
ラズパイゼロはヘッドレスOSで極めて性能が低いためSQLite3データベースに記録した気象データを可視化することはできません。
気象データを可視化するためには３６５日稼働するサーバー機が必要になりますが、そんな用途にコンパクトなラズパイ４はピッタリです。
この記事では下記に示すシステムのラズパイ４ (本番機) がどのようにして構築されたかを、この記事用に作成したシェルスクリプトを通して説明します。
ラズパイ４ (本番機) へのデータ移行についての詳細は下記GitHubリポジトリでご覧になれます
GitHub(pipito-yukio) UDP Weather Sensor packet monitor for Raspberry pi 4
下記 Qiita 投稿記事にシステムサービス化の方法を掲載しております。事前に読んでいただくとこれから説明するデータ移行の自動化についての理解の助けになると思います。
(Qiita) ラズパイ Pythonアプリケーションをシステムサービス化する
ラズパイ４(試験機)用の検証環境の作成
記事執筆にあたり検証環境の作成にはクリーンインストール直後にバックアップしたイメージをSDカードをリストアするようにしています。
そうすることで別の検証時にインストールしたライブラリの影響を排除することが可能になります。
以下は開発PCに保存しているラズパイの本番用と開発用イメージのバックアップです
$ ls -lrt --time-style long-iso
合計 56683748
-rwxrwxrwx 1 nobody nogroup 25814709629 2021-12-14 16:57 PiOS-lite-zerodev-20211214.2.img.gz
-rwxrwxrwx 1 nobody nogroup 2059210762 2023-04-12 12:07 PiOS-64bit-raspi-4-2023-04-12.img.gz
-rwxrwxrwx 1 nobody nogroup 2061923654 2023-04-28 11:47 PiOS-64bit-raspi-4-2023-04-28_0_old.img.gz
-rw-rw-r-- 1 nobody nogroup 2331170906 2023-09-04 17:28 PiOS-64bit-raspi-4-dev-2023-09-04.img.gz
-rw-rw-r-- 1 nobody nogroup 25777081653 2023-10-07 13:21 PiOS-lite-zero-2023-10-07.img.gz
SDカードがマウントされたデバイスを確認します。私の環境では /dev/sdc です。
$ lsblk -e7 | grep sdc | grep -v grep
sdc 8:32 1 59.5G 0 disk
├─sdc1 8:33 1 256M 0 part /media/yukio/boot
└─sdc2 8:34 1 59.2G 0 part /media/yukio/rootfs
検証用のSDカードにOS (開発用) のイメージをリストアします。約40分位かかります。
$ gunzip --stdout PiOS-64bit-raspi-4-dev-2023-09-04.img.gz | sudo dd bs=4M of=/dev/sdc
0+836226 レコード入力
0+836226 レコード出力
31293702144 bytes (31 GB, 29 GiB) copied, 2481.74 s, 12.6 MB/s
自動化スクリプトを実行する環境
-
OS: Debian GNU/Linux 11 (bullseye) 64bit
下記にOSのバージョン、CPU情報 (一部割愛)、メモリ情報を示します
pi@raspi-4-dev:~ $ lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description: Debian GNU/Linux 11 (bullseye)
Release: 11
Codename: bullseye
# OS bit
pi@raspi-4:~ $ uname -m
aarch64
# CPU Specification
pi@raspi-4-dev:~ $ lscpu
Architecture: aarch64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 4
On-line CPU(s) list: 0-3
Thread(s) per core: 1
Core(s) per socket: 4
Socket(s): 1
Vendor ID: ARM
Model: 3
Model name: Cortex-A72
#...省略...
# Memory
pi@raspi-4-dev:~ $ free -m
total used free shared buff/cache available
Mem: 3794 104 3498 1 191 3617
Swap: 99 0 99
※本番機(8GB)との差はメモリ容量(4GB)のみとなります。
- OSインストールとアップデートが完了し追加のライブラリはインストールしていない
-
インターネット接続ができ、プライベートネットワーク内でSSHログインができること
※1 開発PCのSSH公開キーが ラズパイ４(試験機) の .ssh/authorized_keys に追加済み
※2 ネットワーク接続は有線接続が望ましい (Wi-Fiの場合は相当時間がかかります) - プライベートネットワーク内のみで稼働していること(公開サーバーでない)
インストールするリソース一覧
installer/
├── 1_inst_libraries.sh # システムライブラリ等インストールスクリプト
├── 2_create_postgresql_container.sh # PostgreSQLのDockerコンテナビルド(生成)スクリプト
├── 3_migrate_weatherdb.sh # データ移行スクリプト
├── bin
│ ├── dkr_import_weather.sh # CSVファイルをPostgreSQLにインホートするシェルスクリプト
│ └── postgres-docker.sh # dockerコンテナ起動・破棄シェルスクリプト
├── data # PostgreSQLのデータ移行に必要なdockerコンテナ用シェルスクリプト
│ └── sql
│ └── weather
│ ├── csv # SQLite3データベースから出力するCSV格納ディレクトリ
│ ├── getcsv_sqlite_device.sh # 気象センサーデバイスCSV出力スクリプト
│ ├── getcsv_sqlite_weather.sh # 気象データCSV出力スクリプト
│ ├── import_csv.sh # PostgreSQLへのCSVインポートスクリプト
│ ├── import_csv_with_drop_constraint.sh # 制約を外してCSVインポートするスクリプト
│ └── sqlite3db # 気象データベースファイル格納ディレクトリ
│ └── MinMaxRec.sql # 開始・最終レコード確認SQL
├── db # PostgreSQLデータベースディレクトリ
├── docker # docker-compose用リソース
│ └── postgres
│ ├── .env # 環境変数ファイル (RaspbrryPi 固有)
│ ├── Dockerfile
│ ├── docker-compose.yml
│ └── initdb
│ ├── 10_createdb_sensors.sql # 気象データベース生成DDL
│ └── 11_weather_db.sql # テーブル生成DDL
└── work
└─── etc
├── default # サービス起動時の環境設定ファイル
│ └── postgres-docker
└── systemd
└── system # サービスユニットファイル格納ディレクトリ
├── cleanup-postgres-docker.service # dockerコンテナ破棄サービス
└── postgres-docker.service # dockerコンテナ起動サービス
上記リソース一覧のソースコードは下記 GitHub リポジトリで公開しております。
GitHub(pipito-yukio) qiita-posts: RaspberryPi/migrate_sqlite3_to_postgresql_in_docker
ラズパイゼロ(本番機)で記録したSQLite3データベースは下記 GitHub リポジトリで公開しております。
GitHub(pipito-yukio) matplotlib_knowhow/src/pandas-read_sql/db/sqlite3
インストーラー (シェルスクリプト)
- システムライブラリインストール、アプリケーションサービス設定
- PostgreSQLサーバーコンテナビルド(生成)
- データ移行 (SQLite3 => PostgreSQL)
主要な格納ディレクトリ
- data
- SQLite3データベースCSV出力シェルスクリプト
- PostgreSQLにCSVをインポートするdockerコンテナ内でのみ動作するシェルスクリプト
- db: PosgtreSQLデータベースファイル格納ディレクトリ
- docker: docker-compose関連資材
0. 事前設定
ラズパイゼロ(本番機) から最新の気象データベース(SQLite3)をパスワード入力なしでラズパイ４(試験機)にコピーするための事前設定を行います。(下図参照)
著者の持っている４台のラズパイ(4 ModelB x 2、ゼロ x 2)にはキーボードもモニターも接続していないため、管理作業は開発PC(Ubuntu)のターミナルから実施しています。
0-1 ラズパイ４(試験機) の事前設定
開発PCからラズパイ４(試験機)にSSHログインして作業
ssh-keygenコマンドでSSHの秘密鍵と公開鍵を作成する
※1 passphrase, same passphrase の入力でEnterのみを入力
※2 パスフレーズ入力なしで Secure Copy (scp) が可能になる
pi@raspi-4-dev:~ $ cd .ssh
pi@raspi-4-dev:~/.ssh $ ls -lrt --time-style long-iso
合計 4
-rw------- 1 pi pi 570 2023-09-04 16:02 authorized_keys
# ssh-keygenコマンド
pi@raspi-4-dev:~/.ssh $ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/home/pi/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/pi/.ssh/id_rsa
Your public key has been saved in /home/pi/.ssh/id_rsa.pub
The key fingerprint is:
SHA256:rRqKIm+8Icm5hyKYwYHPygWuPHRbkGQneeJmtaMBGjM pi@raspi-4-dev
The key's randomart image is:
+---[RSA 3072]----+
| . |
|E. * + |
|o+= B . |
|oo B o . |
|oo= + . S . |
|o=++ . . |
|OB= o . . |
|X*+= . o |
|+=*.. . |
+----[SHA256]-----+
pi@raspi-4-dev:~/.ssh $ ls -lrt --time-style long-iso
合計 12
-rw------- 1 pi pi 570 2023-09-04 16:02 authorized_keys
-rw-r--r-- 1 pi pi 568 2023-11-17 10:11 id_rsa.pub
-rw------- 1 pi pi 2602 2023-11-17 10:11 id_rsa
/etc/hostsにラズパイゼロのIPアドレスとホスト名を追記する
※ 追加前に所有者をrootからpiユーザに変更し、追加後にrootユーザに戻します
# /etc に移動しhostsの内容を確認
pi@raspi-4-dev:~/.ssh $ cd /etc/
pi@raspi-4-dev:/etc $ cat hosts
127.0.0.1 localhost
::1 localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
127.0.1.1 raspi-4-dev
# オリジナルのコピーを作成する
pi@raspi-4-dev:/etc $ sudo cp hosts hosts_org
# 一時的にファイルを pi ユーザに変更
pi@raspi-4-dev:/etc $ sudo chown pi.pi hosts
# ラズパイゼロ(本番機)のホスト名とIPアドレスを追記
pi@raspi-4-dev:/etc $ echo "192.168.0.12 raspi-zero" >>hosts
# 追加した内容を確認
pi@raspi-4-dev:/etc $ tail -n 2 hosts
127.0.1.1 raspi-4-dev
192.168.0.12 raspi-zero
# ファイルの所有者をrootに戻す
pi@raspi-4-dev:/etc $ sudo chown root.root hosts
pi@raspi-4-dev:/etc $ ls -l --time-style long-iso hosts
-rw-r--r-- 1 root root 158 2023-11-17 10:38 hosts
# ユーザーホームに戻る
pi@raspi-4-dev:/etc $ cd ~/
pi@raspi-4-dev: $
0-2 ラズパイゼロ(本番機)の事前設定
0-2 (1) 開発PC上での作業
ラズパイ４(試験機)で作成したSSH公開鍵を開発PCのローカルにコビー
$ scp pi@raspi-4-dev:~/.ssh/id_rsa.pub .
id_rsa.pub 100% 568 868.1KB/s 00:00
ローカルにコビーしたSSH公開鍵をリネームしてラズパイゼロ(本番機)にコピー
$ scp id_rsa.pub pi@raspi-zero:~/.ssh/id_rsa_raspi-4-dev.pub
id_rsa.pub 100% 568 223.4KB/s 00:00
0-2 (2) ラズパイゼロ(本番機)での作業
開発PCからコピーされた公開鍵を authorized_keys に追加する
※追加した鍵の内容は一部省略しています
# 開発PCからラズパイゼロ(本番機)にSSHログインする
$ ssh pi@raspi-zero
pi@raspi-zero:~ $ cd .ssh
pi@raspi-zero:~/.ssh $ ls -lrt --time-style long-iso
合計 8
-rw-r--r-- 1 pi pi 0 2023-10-07 11:14 known_hosts
-rw------- 1 pi pi 570 2023-11-16 16:17 authorized_keys
-rw-r--r-- 1 pi pi 568 2023-11-17 10:22 id_rsa_raspi-4-dev.pub
# 公開鍵を追記
pi@raspi-zero:~/.ssh $ cat id_rsa_raspi-4-dev.pub >>authorized_keys
# 追加した内容を確認: ２行目の pi@raspi-4-dev
pi@raspi-zero:~/.ssh $ cat authorized_keys
ssh-rsa AAAAB3N ...長いので省略... drw03tv2qLsTk= yukio@Dell-T7500
ssh-rsa AAAAB3NzaC1yc ...長いので省略... xFwlc= pi@raspi-4-dev
ラズパイ４(試験機)からラズパイゼロにSSHログインして確認
※ 0-2 (1)でログインしたままです
ラズパイゼロ(本番機)にSSHログイン後、下記プロンプトで yes 入力
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
pi@raspi-4-dev:~ $ ssh pi@raspi-zero
The authenticity of host 'raspi-zero (192.168.0.12)' can't be established.
ECDSA key fingerprint is SHA256:h5vFhvICqLRDVz0kDLlIlygzvCKVog7S+wIR7uWqzM8.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added 'raspi-zero,192.168.0.12' (ECDSA) to the list of known hosts.
Linux raspi-zero 5.10.63+ #1459 Wed Oct 6 16:40:27 BST 2021 armv6l
...一部メッセージ省略...
空のパスフレーズでSSHログインできたので準備完了です。ラズパイゼロからログアウトします。
pi@raspi-zero:~ $ exit
ログアウト
Connection to raspi-zero closed.
0-3. 開発PCでインストーラーアーカイブを作成しラズパイ４(試験機)にコピー
$ tar czf ../migrate_sqlite3db.tar.gz \
> 1_inst_libraries.sh 2_create_postgresql_container.sh 3_migrate_weatherdb.sh \
> bin/ data/ docker/ logs/ work/
# １つ上に上がって
$ cd ..
# ラズパイ４(試験機)にコピー
$ scp migrate_sqlite3db.tar.gz pi@raspi-4-dev:~/
migrate_sqlite3db.tar.gz 100% 9565 6.8MB/s 00:00
0-4. ラズパイ４(試験機)でインストーラーアーカイブを解凍
解凍前の既存のディレクトリを確認する
pi@raspi-4-dev:~ $ ls -lrt --time-style long-iso
合計 44
drwxr-xr-x 2 pi pi 4096 2023-05-03 12:02 Bookshelf
drwxr-xr-x 2 pi pi 4096 2023-09-04 14:59 Desktop
drwxr-xr-x 2 pi pi 4096 2023-09-04 14:59 Videos
drwxr-xr-x 2 pi pi 4096 2023-09-04 14:59 Downloads
drwxr-xr-x 2 pi pi 4096 2023-09-04 14:59 Documents
drwxr-xr-x 2 pi pi 4096 2023-09-04 15:17 Templates
drwxr-xr-x 2 pi pi 4096 2023-09-04 15:17 Music
drwxr-xr-x 2 pi pi 4096 2023-09-04 15:17 Pictures
-rw-r--r-- 1 pi pi 9699 2023-11-17 16:08 migrate_sqlite3db.tar.gz
インストーラーアーカイブを解凍する ※既存のディレクトリは省略しています
# アーカイブ解凍
pi@raspi-4-dev:~ $ tar xzf migrate_sqlite3db.tar.gz
pi@raspi-4-dev:~ $ ls -lrt --time-style long-iso
# ...既存のディレクトリは省略...
drwxr-xr-x 3 pi pi 4096 2023-11-07 14:17 docker
drwxr-xr-x 2 pi pi 4096 2023-11-07 14:17 db
drwxr-xr-x 3 pi pi 4096 2023-11-15 09:22 logs
drwxr-xr-x 3 pi pi 4096 2023-11-15 09:29 work
drwxr-xr-x 3 pi pi 4096 2023-11-15 11:07 data
drwxr-xr-x 3 pi pi 4096 2023-11-15 15:58 bin
-rwxr-xr-x 1 pi pi 433 2023-11-16 10:58 2_create_postgresql_container.sh
-rwxr-xr-x 1 pi pi 1898 2023-11-16 10:59 3_migrate_weatherdb.sh
-rwxr-xr-x 1 pi pi 2289 2023-11-17 14:06 1_inst_libraries.sh
-rw-r--r-- 1 pi pi 9699 2023-11-17 16:08 migrate_sqlite3db.tar.gz
1. アプリケーションスクリプト(シェルスクリプト)
PostgreSQLサーバーコンテナの起動方法については、下記GitHubサイトに詳しい説明が有りますのでそちらをご覧ください。
GitHub(pipito-yukio) UDP Weather Sensor packet monitor for Raspberry pi 4: 「3-3. PostgreSQLコンテナイメージ生成スクリプト」
1-1. PostgresSQLサーバコンテナの起動と破棄
1-1-1. docker-composeコマンド実行シェルスクリプト
postgres-docker.sh
#!/bin/bash
docker_compose=$(which docker-compose)
case "$1" in
start)
echo "docker-directory: $2"
cd $2 # Directory in docker-compose.yml
$docker_compose up -d
sleep 1
systemd-notify --ready
echo "PostgreSQL($2) container ready!"
sleep 1
# 実行中のコンテナ確認
container_ls=`docker container ls`
echo "$container_ls"
# After udp-weather-mon.service, other webapplication service
cd ~
;;
stop)
# At shutdown
cd $2
$docker_compose down
echo "PostgreSQL container down."
cd ~
;;
*)
exit 1
;;
esac
systemd-notifyの使用方法について
CentOS 7 システム管理ガイド 秀和システム ISBN978-4-7980-4491-0
1.3.4 systemd-nofify を使う ※詳しい説明と使用例が掲載されています。
(ask Ubuntu: How to use systemd notify https://askubuntu.com/questions/1120023/how-to-use-systemd-notify 英語ですが使用例がのっています。)
(1) start) は下記コマンドを実行するのと同じです。
※ コマンド実行では systemd-notify --ready は不要です。
# コンテナ起動
$ docker-compose up -d
Creating network "12_default" with the default driver
Creating postgres-12 ... done
# 実行中のコンテナ確認
$ docker container ls
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
35cf9374dd70 12_postgres "docker-entrypoint.s…" 31 seconds ago Up 27 seconds 0.0.0.0:5432->5432/tcp, :::5432->5432/tcp postgres-12
(2) stop) は下記コマンドを実行するのと同じです。
# コンテナ破棄
$ docker-compose down
Stopping postgres-12 ... done
Removing postgres-12 ... done
Removing network 12_default
1-1-2 PostgreSQLサーバーコンテナのサービスユニット定義
(1) サービスユニットが参照する環境変数
postgres-docker
docker-composeファイルの格納先ディレクトリ
BUILD_PATH=/home/pi/docker/postgres
(2) コンテナ起動のサービスユニット定義
postgres-docker.service
- システムサービス docker.service 起動後に実行する
1 本番機では２つのアプリケーションサービスの前に実行する設定を行っています。
2 今回はアプリケーションサービスをインストールしないので定義していません。
3 サービス型 notify: 起動処理が終了すると systemd に起動終了を通知する。
※後続の(Beforeで定義された)アプリケーションサービスの起動がブロックされる。
3 環境変数ファイル参照し、シェルスクリプト(1-1-1) の引数にビルドパスを設定します。
[Unit]
Description=PostgreSQL Start service with docker
After=docker.service
[Service]
Type=notify
TimeoutStartSec=0
NotifyAccess=all
EnvironmentFile=/etc/default/postgres-docker
ExecStart=/bin/sh -c "/home/pi/bin/postgres-docker.sh start $BUILD_PATH"
User=pi
[Install]
WantedBy=multi-user.target
■■ 参考までにラズパイ４(本番機)のユニット定義ファイルを下記に示します■■
ソースコードは下記GitHubで公開しております。
GitHub(pipito-yukio) raspi4_apps/src/installer/work/etc/systemd/system
Before=UDPパケットモニターサービス 気象データ可視化Flaskアプリケーションサービス
Before=udp-weather-mon.service webapp-plot-weatherr.service
[Unit]
Description=PostgreSQL Start service with docker
Before=udp-weather-mon.service webapp-plot-weatherr.service
After=docker.service
[Service]
Type=notify
TimeoutStartSec=0
NotifyAccess=all
EnvironmentFile=/etc/default/postgres-12-docker
ExecStart=/bin/sh -c "/home/pi/bin/postgres-12-docker.sh start $BUILD_PATH"
User=pi
[Install]
WantedBy=multi-user.target
● コンテナ起動サービスに影響を受けるアプリケーションサービス
(A) UDPパケットモニターサービス udp-weather-mon.service
[Unit]
Description=UDPClient Weather data monitor service
After=postgres-12-doker.service
[Service]
Type=simple
ExecStart=/home/pi/bin/udp_monitor_from_weather_sensor.sh
User=pi
[Install]
WantedBy=multi-user.target
(B) 気象データ可視化Flaskアプリケーションサービス
webapp-plot-weather.service
[Unit]
Description=Flask webapp PlotWeather service
After=postgres-12-docker.service
[Service]
Type=idle
# FLASK_PROD_PORT
EnvironmentFile=/etc/default/webapp-plot-weather
ExecStart=/home/pi/PlotWeatherForRaspi4/start.sh prod >/dev/null
User=pi
[Install]
WantedBy=multi-user.target
■■以上 ラズパイ４(本番機) のサービスユニット定義ファイル■■
(3) コンテナ破棄のサービスユニット定義
cleanup-postgres-docker.service
[Unit]
Description=Cleanup PostgreSQL docker container
[Service]
Type=oneshot
RemainAfterExit=true
ExecStart=/bin/true
ExecStop=/bin/sh -c "/home/pi/bin/postgres-docker.sh stop $BUILD_PATH"
User=pi
[Install]
WantedBy=multi-user.target
1-2. data配下のデータ移行用のシェルスクリプト
ソースのみ掲載いたします。
下記 Qiita 投稿記事でスクリプトについて詳しく説明をしておりますので覧ください。
(Qiita) SQLite3データベースのレコードをCSVファイル保存するシェルスクリプトを作る
(Qiita) CSVファイルをDockerコンテナ内のPostgreSQLにインポートするシェルスクリプトを作る
(1) センサーデバイステーブルのCSVファイル出力スクリプト (SQLite3)
getcsv_sqlite_device.sh
#!/bin/bash
get_csv() {
cat<<-EOF | sqlite3 "$PATH_WEATHER_DB" -csv
SELECT id, name, name FROM t_device ORDER BY id;
EOF
}
# init option value
csv_filepath="${1}/t_device.csv"
header='"id","name","description"'
echo $header > "${csv_filepath}"
get_csv >> "${csv_filepath}"
if [ $? = 0 ]; then
echo "Output device csv to ${csv_filepath}"
row_count=$(cat "${csv_filepath}" | wc -l)
row_count=$(( row_count - 1))
echo "Record count: ${row_count}"
else
echo "Output error" 1>&2
fi
(2) 気象データテーブルのCSVファイル出力スクリプト (SQLite3)
getcsv_sqlite_weather.sh
#!/bin/bash
# CSV形式の出力: get_csv "${where}"
get_csv() {
where="$1";
cat<<-EOF | sqlite3 "$PATH_WEATHER_DB" -csv
SELECT
did,
datetime(measurement_time, 'unixepoch', 'localtime'),
temp_out, temp_in, humid, pressure
FROM
t_weather
WHERE
${where}
ORDER BY did, measurement_time;
EOF
}
# getcsv_sqlite_weather.sh 2021-01-01 ~/Downloads/csv
# All parameter required
where="(datetime(measurement_time, 'unixepoch', 'localtime') >= '"${1}"')"
csv_filepath="${2}/t_weather.csv"
header='"did","measurement_time","temp_out","temp_in","humid","pressure"'
echo $header > "${csv_filepath}"
get_csv "${where}" >> "${csv_filepath}"
if [ $? = 0 ]; then
echo "Output t_weather csv to ${csv_filepath}"
row_count=$(cat "${csv_filepath}" | wc -l)
row_count=$(( row_count - 1))
echo "Record count: ${row_count}"
else
echo "Output error"
fi
(3-1) CSVファイルインボートスクリプト(PostgreSQL: 通常)
import_csv.sh
#!/bin/bash
# 気象データCSVをテーブルにインポート
cat<<-EOF | psql -Udeveloper sensors_pgdb --echo-all
copy weather.t_device
FROM '${1}/data/sql/weather/csv/t_device.csv' DELIMITER ',' CSV HEADER;
copy weather.t_weather
FROM '${1}/data/sql/weather/csv/t_weather.csv' DELIMITER ',' CSV HEADER;
EOF
(3-2) CSVファイルインボートスクリプト(PostgreSQL: テーブル制約を解除)
import_csv_with_drop_constraint.sh
#!/bin/bash
# 気象データCSVをテーブルにインポート
# PK制約をドロップ
cat<<-EOF | psql -Udeveloper sensors_pgdb
ALTER TABLE weather.t_weather DROP CONSTRAINT pk_weather;
ALTER TABLE weather.t_weather DROP CONSTRAINT fk_device;
EOF
sleep 1
# データインポート
# t_device.csv into t_device table
# t_weather.csv into t_weather table
cat<<-EOF | psql -Udeveloper sensors_pgdb --echo-all
copy weather.t_device
FROM '${1}/data/sql/weather/csv/t_device.csv' DELIMITER ',' CSV HEADER;
copy weather.t_weather
FROM '${1}/data/sql/weather/csv/t_weather.csv' DELIMITER ',' CSV HEADER;
EOF
sleep 2
# PK制約を戻す
cat<<-EOF | psql -Udeveloper sensors_pgdb
ALTER TABLE weather.t_weather
ADD CONSTRAINT pk_weather PRIMARY KEY (did, measurement_time);
ALTER TABLE weather.t_weather
ADD CONSTRAINT fk_device FOREIGN KEY (did) REFERENCES weather.t_device (id);
EOF
1-3. docker 配下の docker-compose関連リソース
(1-1) 気象データベース生成DDL (PostgreSQL)
10_createdb_sensors.sql
CREATE ROLE developer WITH LOGIN PASSWORD 'your_dev_passwd';
--install pgcrypto that is required superuser.
ALTER ROLE developer WITH SUPERUSER;
CREATE DATABASE sensors_pgdb WITH OWNER=developer ENCODING='UTF-8' LC_COLLATE='ja_JP.UTF-8' LC_CTYPE='ja_JP.UTF-8' TEMPLATE=template0;
GRANT ALL PRIVILEGES ON DATABASE sensors_pgdb TO developer;
(1-2) 気象データテーブル生成DDL (PostgreSQL)
11_weather_db.sql
\connect sensors_pgdb
CREATE SCHEMA IF NOT EXISTS weather;
CREATE TABLE IF NOT EXISTS weather.t_device(
id INTEGER NOT NULL,
name VARCHAR(20) UNIQUE NOT NULL,
description VARCHAR(128) NOT NULL,
CONSTRAINT pk_device PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS weather.t_weather(
did INTEGER NOT NULL,
measurement_time timestamp NOT NULL,
temp_out REAL,
temp_in REAL,
humid REAL,
pressure REAL
);
ALTER TABLE weather.t_weather ADD CONSTRAINT pk_weather PRIMARY KEY (did, measurement_time);
ALTER TABLE weather.t_weather ADD CONSTRAINT fk_device FOREIGN KEY (did) REFERENCES weather.t_device (id);
ALTER SCHEMA weather OWNER TO developer;
ALTER TABLE weather.t_device OWNER TO developer;
ALTER TABLE weather.t_weather OWNER TO developer;
(2-1) Dockerファイル
Dockerfile
FROM postgres:12-alpine
COPY initdb/*.sql /docker-entrypoint-initdb.d/
(2-2) docker-compose定義ファイル
docker-compose.yml
version: '3'
services:
postgres:
build: .
container_name: postgres
env_file: ./.env
ports:
- "5432:5432"
volumes:
- "${HOST_PG_VOLUME}/postgresql/data:/var/lib/postgresql/data"
- "${HOST_HOME}/data/sql:${CONTAINER_HOME}/data/sql"
environment:
- POSTGRES_USER=${PG_USER}
- POSTGRES_PASSWORD=${PG_PASSWD}
(2-3) docker-compose環境変数定義ファイル
.env
#COMPOSE_PROJECT_NAME=postgres-12
HOST_PG_VOLUME=/home/pi/db
#DB_NAME=sensors_pgdb
PG_USER=postgres
PG_PASSWD=your_gp_passwd
# display locale
LANG=C
# locale
LANGUAGE=ja_JP:ja
LC_ALL=ja_JP.UTF-8
# Time zone
TZ=Asia/Tokyo
# volume mount
HOST_HOME=/home/pi
CONTAINER_HOME=/home/pi
1-4. bin 配下のシェルスクリプト
このシェルスクリプトのオプションの指定方法に関しては下記Qiita投稿記事をご覧ください。
(Qiita) Linux getoptコマンド 長いオプションの仕様に注意
dkr_import_weather.sh
#!/bin/bash
readonly SCRIPT_NAME=${0##*/}
VALID_OPTION="drop-constraint"
print_error()
{
cat << END 1>&2
$SCRIPT_NAME: $1
Try --help option
END
}
print_help()
{
cat << END
Usage: $SCRIP_NAME OPTIONS
Execute OPTIONS
-o drop-constraint: Optional CSV import with DROP CONSTRAINT over 10000 record.
--help display this help and exit
Example:
$SCRIPT_NAME
$SCRIPT_NAME -o drop-constraint
END
}
params=$(getopt -o 'o:' -l help -n "$SCRIPT_NAME" -- "$@")
# Check command exit status
if [[ $? -ne 0 ]]; then
echo 'Try --help option for more information' 1>&2
exit 1
fi
eval set -- "$params"
# init option value
input_option=
# Positional parameter count: $#
while [[ $# -gt 0 ]]
do
case "$1" in
-o)
input_option=$2
shift 2
;;
--'help')
print_help
exit 0
;;
--)
shift
break
;;
*)
echo 'Internal error!' >&2
exit 1
;;
esac
done
echo "input_option: ${input_option}"
drop_constraint=
if [ -n "$input_option" ]; then
# 短いオプションが入力されていたら、完全一致チェック
if [ "$VALID_OPTION" == "$input_option" ]; then
# 有効
drop_constraint=true
else
# 入力エラー
echo "Error: short option -o is not match 'drop-constraint'" 1>&2
exit 1
fi
fi
echo "drop_constraint: ${drop_constraint}"
if [[ ${drop_constraint} == true ]]; then
docker exec -it postgres sh -c "${HOME}/data/sql/weather/import_csv_with_drop_constraint.sh ${HOME}"
else
docker exec -it postgres sh -c "${HOME}/data/sql/weather/import_csv.sh ${HOME}"
fi
2. 自動化シェルスクリプト
2-1. システムライブラリインストール
1_inst_libraries.sh
※実行結果をチェックする処理はコード説明では省略しています
下記はOSインストール直後の /etc/hostsファイル ※外部からはHTTPなどでアクセスできない
127.0.0.1 localhost
::1 localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
127.0.1.1 raspi-4-dev
(1) /etc/hosts に プライベートIPアドレスとホスト名.local を追加する
※1 Webアプリケーションをインストールする場合は IPアドレスとホスト名の定義が必要です
※2 内部ネットワーク内のPCから各種ツールを使ってデータベースの保守が可能になります
追加される行は 192.168.0.20 raspi-4-dev.local です
#!/bin/bash
# execute before export my_passwd=xxxxxx
ip_addr=$(ifconfig eth0 | grep "inet " | awk '{ print $2 }')
host_in_hosts=$(cat /etc/hosts | grep 127.0.1.1 | awk '{ print $2 }')
host_in_hosts="${host_in_hosts}.local"
add_dot_host="${ip_addr} ${host_in_hosts}"
echo $my_passwd | { sudo --stdin chown pi.pi /etc/hosts
echo $add_dot_host>>/etc/hosts
sudo chown root.root /etc/hosts
}
(2) インストール前にアップデートとシステムライブラリのインストール
・ sqlite3: SQLite3データベースからCSVファイル出力するために使用
・ docker-compose ※docker engineも同時にインストールされる
echo $my_passwd | { sudo --stdin apt-get -y update
sudo apt-get -y install sqlite3 docker-compose
}
(3) pi ユーザを dockerグループに追加する【重要】
・ dockerの管理コマンドの実行には root 権限が必要
・ pi ユーザーがdockerの管理コマンドを実行できるようにする
詳細は下記ドキュメントを参照してください。
Docker-docs-ja: Docs » Docker Engine » インストール » Debian: root以外のアクセス指定
# pi user add docker execute
echo $my_passwd | sudo --stdin gpasswd -a $USER docker
(4) PostgreSQLコンテナ起動・破棄サービスの設定
echo $my_passwd | { sudo --stdin cp ~/work/etc/default/postgres-docker /etc/default
sudo cp ~/work/etc/systemd/system/postgres-docker.service /etc/systemd/system
sudo cp ~/work/etc/systemd/system/cleanup-postgres-docker.service /etc/systemd/system
sudo systemctl enable postgres-docker.service
sudo systemctl enable cleanup-postgres-docker.service
}
【重要】このシェルスクリプト実行後に一旦ログアウトする必要が有ります。
※ ログアウトしないと 「pi ユーザを dockerグループに追加する」処理が反映されません。
2-2. PostgreSQLコンテナビルド(生成)
2_create_postgresql_container.sh
・ DockerリポジトリからPostgreSQLのイメージをダウンロードしコンテナをビルドする
・ コンテナが生成されたら一旦コンテナを破棄する
#!/bin/bash
# Create PostgreSQL container with sensors_pgdb database and weather data tables.
cd ~/docker/postgres
docker-compose up --build -d
docker-compose down
cd ~/
echo "Done."
2-3. データ移行 (SQLiteデータベース >> PostgreSQL)
3_migrate_weatherdb.sh
(1) データ移行開始日のチェック ※必須
#!/bin/bash
# This script execute before 1_inst_libraries.sh execute and logout terminal.
# before export my_passwd=xxxxx
# [usage] ./3_migrate_weatherdb.sh from-date
# from-date required (exmaple) 2022-01-01
if [ -z "$1" ]; then
echo "Required from-date (exmaple) 2022-01-01 !" 1>&2
exit 1
fi
(2) ラズパイゼロ(本番機) から最新の気象データベースファイルをダウンロード
scp pi@raspi-zero:~/db/weather.db ~/data/sql/weather/sqlite3db
(3) 最新の気象データベースからCSVファイルを出力
・ データ移行スクリプト格納ディレクトリに移動
・ SQLite3データベースの格納先を環境変数に設定 ※次のCSV出力スクリプトで参照する
・ センサーデバイステーブルのCSV出力シェルスクリプト実行
・ 気象データテーブルのCSV出力シェルスクリプト実行
cd ~/data/sql/weather
# SQLite3データベースの格納先を環境変数に設定
export PATH_WEATHER_DB=~/data/sql/weather/sqlite3db/weather.db
# CSVの出力先
OUTPUT_PATH=~/data/sql/weather/csv
# 気象センサーテーブルをCSVに出力
./getcsv_sqlite_device.sh "$OUTPUT_PATH"
# 気象データテーブルをCSVに出力
./getcsv_sqlite_weather.sh "$1" "$OUTPUT_PATH"
(4) PostgreSQLコンテナ起動しCSVファイルをインポートする
・ docker-compose格納ディレクトリに移動
・ PostgreSQLコンテナ起動
・ CSVファイルインポートシェルスクリプト(テーブル制約を解除する)実行
・ インポート完了後にコンテナを破棄する
cd ~/docker/postgres
docker-compose up -d
# wait starting pg_ctl in container.
sleep 2
# 制約を外してインポート
~/bin/dkr_import_weather.sh -o drop-constraint
# コンテナ破棄
docker-compose down
(5) CSVファイルインポートが正常終了なら再起動
echo "Database migration success."
echo "rebooting."
echo $my_passwd |sudo --stdin reboot
3. シェルスクリプトの実行
3-1. システムライブラリのイントールとアプリケーションサービス設定
シェルスクリプトの実行には sudo 権限が必要です。
・ パスワード環境変数を設定 export my_passwd=xxxxx
pi@raspi-4-dev:~ $ export my_passwd=your_raspi_password
pi@raspi-4-dev:~ $ ./1_inst_libraries.sh
※アップデートとライブラリのダウンロード出力は省略します。
2023-11-18 08:38:44 >Script START
# システムアップデートとシステムライブラリのダウンロードは出力を省略...
docker.io (20.10.5+dfsg1-1+deb11u2) を設定しています ...
グループ `docker' (グループ ID 124) を追加しています...
完了。
# dockerサービスとdockerソケットをシステムに設定
Created symlink /etc/systemd/system/multi-user.target.wants/docker.service → /lib/systemd/system/docker.service.
Created symlink /etc/systemd/system/sockets.target.wants/docker.socket → /lib/systemd/system/docker.socket.
libintl-xs-perl (1.26-3+deb11u1) を設定しています ...
python3-jsonschema (3.2.0-3) を設定しています ...
docker-compose (1.25.0-1) を設定しています ...
man-db (2.9.4-2) のトリガを処理しています ...
Install system libraries >> status=0
ユーザ pi をグループ docker に追加
# PostgreSQLコンテナ起動・廃棄サービスの設定
Created symlink /etc/systemd/system/multi-user.target.wants/postgres-docker.service → /etc/systemd/system/postgres-docker.service.
Created symlink /etc/systemd/system/multi-user.target.wants/cleanup-postgres-docker.service → /etc/systemd/system/cleanup-postgres-docker.service.
2023-11-18 08:40:16 >Script END
Done, Require logout this terminqal!
ここで一旦ラズパイ４(試験機)からログアウトします。
pi@raspi-4-dev:~ $ exit
ログアウト
Connection to raspi-4-dev closed.
※リブートしません。リブートするとPostgreSQLコンテナサービスが起動されてしまいます。
【注意】ログアウトせずに次のスクリプトを実行するとどうなるか下記にエラーを示します。
・(1) $ id: この時点でdockerグループに参加していないことを確認します。
# id で参加しているグループを確認する ※dockerグループが設定されていない
pi@raspi-4-dev:~ $ id
uid=1000(pi) gid=1000(pi) groups=1000(pi),4(adm),20(dialout),24(cdrom),27(sudo),29(audio),44(video),46(plugdev),60(games),100(users),104(input),106(render),108(netdev),117(lpadmin),997(gpio),998(i2c),999(spi)
・(2) ログアウトせずにわざと後続のシェルスクリプトを実行します。
pi@raspi-4-dev:~ $ ./2_create_postgresql_container.sh
2023-11-19 18:32:11 >Script START
Couldn't connect to Docker daemon at http+docker://localhost - is it running?
If it's at a non-standard location, specify the URL with the DOCKER_HOST environment variable.
#...以下メッセージは省略いたします...
上記エラーは結構広く知らています。実行権限がない旨のメッセージを出力してくればすぐ気がつくんだけどね。
DOCKER COMMUNITY FORUMS: Couldn’t connect to Docker daemon at http+docker://localhost - is it running?
一旦ログアウトしてから再ログインするとエラーなく次のシェルスクリプトを実行することができます。
3-2. PostgreSQLコンテナをビルド (生成) する
ラズパイ４ (試験機) にSSHログインします
pi ユーザーが 124(docker) グループに入っていることを確認します。
$ ssh pi@raspi-4-dev
# グループ **124(docker) が追加されています
pi@raspi-4-dev:~ $ id
uid=1000(pi) gid=1000(pi) groups=1000(pi),4(adm),20(dialout),24(cdrom),27(sudo),29(audio),44(video),46(plugdev),60(games),100(users),104(input),106(render),108(netdev),117(lpadmin),124(docker),997(gpio),998(i2c),999(spi)
このスクリプトは pi ユーザーのみで実行できます
pi@raspi-4-dev:~ $ ./2_create_postgresql_container.sh
2023-11-18 08:41:33 >Script START
Creating network "postgres_default" with the default driver
Building postgres
Step 1/2 : FROM postgres:12-alpine
12-alpine: Pulling from library/postgres
Digest: sha256:4599f620d3d79e781ec294b2675b68b33a1bfb3827672106a0abd92231e0c1b9
Status: Downloaded newer image for postgres:12-alpine
---> e68626aff107
Step 2/2 : COPY initdb/*.sql /docker-entrypoint-initdb.d/
---> 3bf8f6737f4b
Successfully built 3bf8f6737f4b
Successfully tagged postgres_postgres:latest
Creating postgres ... done
Docker create PostgreSQL container >> status=0
Stopping postgres ... done
Removing postgres ... done
Removing network postgres_default
2023-11-18 08:42:54 >Script END
Done.
3-3. データ移行
・ my_passwdを環境変数に設定する ※データ移行が正常に完了した再起動します
・ 引数に移行開始日をISO8601形式(ハイフン区切り)で設定します
pi@raspi-4-dev:~ $ export my_passwd=your_raspi_password
pi@raspi-4-dev:~ $ ./3_migrate_weatherdb.sh 2021-10-01
データ移行件数は 115,058 件
トータル処理時間は約20秒位です
CSVファイル出力: 2秒
CSVファイルインポート: 5秒
2023-11-18 08:45:40 >Script START
scp paspi-zero:weather.db into sqlite3db directory >> status=0
2023-11-18 08:45:45 >weather.db copied
Output device csv to /home/pi/data/sql/weather/csv/t_device.csv
Record count: 1
Output t_weather csv to /home/pi/data/sql/weather/csv/t_weather.csv
Record count: 115058
export SQLite3 db to csv files. >> status=0
2023-11-18 08:45:47 >CSV output complete
Creating network "postgres_default" with the default driver
Creating postgres ... done
docker-compose up -d >> status=0
2023-11-18 08:45:52 >CSV importing...
input_option: drop-constraint
drop_constraint: true
ALTER TABLE
ALTER TABLE
copy weather.t_device
FROM '/home/pi/data/sql/weather/csv/t_device.csv' DELIMITER ',' CSV HEADER;
COPY 1
copy weather.t_weather
FROM '/home/pi/data/sql/weather/csv/t_weather.csv' DELIMITER ',' CSV HEADER;
COPY 115058
ALTER TABLE
ALTER TABLE
Execute dkr_import_weather.sh >> status=0
2023-11-18 08:45:57 >CSV import completed
Stopping postgres ... done
Removing postgres ... done
Removing network postgres_default
2023-11-18 08:46:00 >Script END
Database migration success.
rebooting.
Connection to raspi-4-dev closed by remote host.
Connection to raspi-4-dev closed.
4. 再起動後の確認
4-1. アプリケーションサービスの稼働
ラズパイ４ (試験機) に SSHログインします
$ ssh pi@raspi-4-dev
4-1-(1) PostgreSQLコンテナ起動サービス
正常稼働しています
pi@raspi-4-dev:~ $ systemctl status postgres-docker.service
● postgres-docker.service - PostgreSQL Start service with docker
Loaded: loaded (/etc/systemd/system/postgres-docker.service; enabled; vendor preset: enabled)
Active: inactive (dead) since Sat 2023-11-18 08:46:19 JST; 22min ago
Process: 743 ExecStart=/bin/sh -c /home/pi/bin/postgres-docker.sh start $BUILD_PATH (code=exited, status=0/SUCCESS)
Main PID: 743 (code=exited, status=0/SUCCESS)
CPU: 2.109s
11月 18 08:46:10 raspi-4-dev sh[744]: docker-directory: /home/pi/docker/postgres
11月 18 08:46:13 raspi-4-dev sh[746]: Creating network "postgres_default" with the default driver
11月 18 08:46:13 raspi-4-dev sh[746]: Creating postgres ...
11月 18 08:46:17 raspi-4-dev systemd[1]: Started PostgreSQL Start service with docker.
11月 18 08:46:17 raspi-4-dev sh[746]: [40B blob data]
11月 18 08:46:17 raspi-4-dev sh[744]: PostgreSQL(/home/pi/docker/postgres) container ready!
11月 18 08:46:19 raspi-4-dev sh[744]: CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS >
11月 18 08:46:19 raspi-4-dev sh[744]: c4db8a407e2b postgres_postgres "docker-entrypoint.s…" 6 seconds ago Up 4 seconds 0.0.0.0:54>
11月 18 08:46:19 raspi-4-dev systemd[1]: postgres-docker.service: Succeeded.
11月 18 08:46:19 raspi-4-dev systemd[1]: postgres-docker.service: Consumed 2.109s CPU time.
4-1-(2) PostgreSQLコンテナ破棄サービス
正常稼働しています
pi@raspi-4-dev:~ $ systemctl status cleanup-postgres-docker.service
● cleanup-postgres-docker.service - Cleanup PostgreSQL docker container
Loaded: loaded (/etc/systemd/system/cleanup-postgres-docker.service; enabled; vendor preset: enabled)
Active: active (exited) since Fri 2023-11-17 16:59:06 JST; 5min ago
Process: 404 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 404 (code=exited, status=0/SUCCESS)
CPU: 12ms
11月 17 16:59:06 raspi-4-dev systemd[1]: Starting Cleanup PostgreSQL docker container...
11月 17 16:59:06 raspi-4-dev systemd[1]: Finished Cleanup PostgreSQL docker container.
4-2. データ移行結果の確認
検索開始日 2021-10-01 以降のレコード件数を確認
「3-3. データ移行」で出力された件数と一致しました
pi@raspi-4-dev:~ $ docker exec -it postgres bin/bash
e2681c504304:/# echo "SELECT COUNT(*) FROM weather.t_weather WHERE did=1 AND measurement_time>='2021-10-01';" | psql -Udeveloper sensors_pgdb
count
--------
115058
(1 row)
5. 結論
この記事用に作成したシェルスクリプトをラスパイ４(試験機)にインストールし、データ移行の自動化を検証いたしました。
データ移行処理だけでも相当な作業量になります。いちいちコマンドを打ち込んでいては大変です。さらに途中コマンドミスなどでデータ移行に失敗すると目も当てられないことになります。
今回のように dockerコンテナ起動サービスと複数のアプリケーションサービスを連携させるのはかなり難しいタスクになります。
一連の作業を実行するシェルスクリプトを作ることによりインストールを実行する管理者の負担を大幅に削減することができます。さらには別のシステム構築にこれらのシェルスクリプトをテンプレートとして再利用できます。
ラズパイ4 (本番機) の気象データ可視化システム構築で使用したインストーラーのソースコードを下記 GitHub リポジトリで公開しております。
GitHub(pipito-yukio) raspi4_apps/src/installer