今回は、前回の記事で２JCIE-BUから取得できることがわかった各種の値を溜め込むためにラズパイにPostgreSQLをインストールして、セッティングしていきます。

PostgreSQLのインストール

最初何も考えずにsudo apt install postgresを実行したらエラーになりました。

インストール失敗ログ

pi@raspberrypi:~ $ sudo apt install postgresql
パッケージリストを読み込んでいます... 完了
依存関係ツリーを作成しています                
状態情報を読み取っています... 完了
以下の追加パッケージがインストールされます:
  libllvm7 libpq5 postgresql-11 postgresql-client-11 postgresql-client-common postgresql-common sysstat
提案パッケージ:
  postgresql-doc postgresql-doc-11 libjson-perl isag
以下のパッケージが新たにインストールされます:
  libllvm7 libpq5 postgresql postgresql-11 postgresql-client-11 postgresql-client-common postgresql-common sysstat
アップグレード: 0 個、新規インストール: 8 個、削除: 0 個、保留: 0 個。
27.0 MB のアーカイブを取得する必要があります。
この操作後に追加で 105 MB のディスク容量が消費されます。
続行しますか? [Y/n] y
取得:1 http://ftp.jaist.ac.jp/pub/Linux/raspbian-archive/raspbian buster/main armhf libllvm7 armhf 1:7.0.1-8+rpi3+deb10u2 [11.4 MB]
エラー:2 http://raspbian.raspberrypi.org/raspbian buster/main armhf libpq5 armhf 11.12-0+deb10u1      
  404  Not Found [IP: 93.93.128.193 80]
エラー:4 http://raspbian.raspberrypi.org/raspbian buster/main armhf postgresql-client-11 armhf 11.12-0+deb10u1
  404  Not Found [IP: 93.93.128.193 80]
エラー:6 http://raspbian.raspberrypi.org/raspbian buster/main armhf postgresql-11 armhf 11.12-0+deb10u1
  404  Not Found [IP: 93.93.128.193 80]
取得:3 http://ftp.jaist.ac.jp/pub/Linux/raspbian-archive/raspbian buster/main armhf postgresql-client-common all 200+deb10u4 [85.1 kB]
取得:5 http://ftp.jaist.ac.jp/pub/Linux/raspbian-archive/raspbian buster/main armhf postgresql-common all 200+deb10u4 [225 kB]
取得:7 http://ftp.jaist.ac.jp/pub/Linux/raspbian-archive/raspbian buster/main armhf postgresql all 11+200+deb10u4 [61.1 kB]
取得:8 http://ftp.jaist.ac.jp/pub/Linux/raspbian-archive/raspbian buster/main armhf sysstat armhf 12.0.3-2 [513 kB]
12.2 MB を 5秒 で取得しました (2,281 kB/s)
E: http://raspbian.raspberrypi.org/raspbian/pool/main/p/postgresql-11/libpq5_11.12-0+deb10u1_armhf.deb の取得に失敗しました  404  Not Found [IP: 93.93.128.193 80]
E: http://raspbian.raspberrypi.org/raspbian/pool/main/p/postgresql-11/postgresql-client-11_11.12-0+deb10u1_armhf.deb の取得に失敗しました  404  Not Found [IP: 93.93.128.193 80]
E: http://raspbian.raspberrypi.org/raspbian/pool/main/p/postgresql-11/postgresql-11_11.12-0+deb10u1_armhf.deb の取得に失敗しました  404  Not Found [IP: 93.93.128.193 80]
E: いくつかのアーカイブを取得できません。apt-get update を実行するか --fix-missing オプションを付けて試してみてください

ということなので、apt-get updateをしてから、再度apt installを実行してみた。

成功した時のログ

pi@raspberrypi:~ $ sudo apt-get update
取得:1 http://archive.raspberrypi.org/debian buster InRelease [32.6 kB]
取得:2 http://raspbian.raspberrypi.org/raspbian buster InRelease [15.0 kB]                                          
取得:3 http://archive.raspberrypi.org/debian buster/main armhf Packages [393 kB]
取得:4 http://raspbian.raspberrypi.org/raspbian buster/main armhf Packages [13.0 MB]
取得:5 http://raspbian.raspberrypi.org/raspbian buster/contrib armhf Packages [58.8 kB]                                                       
取得:6 http://raspbian.raspberrypi.org/raspbian buster/non-free armhf Packages [104 kB]                                                       
13.6 MB を 19秒 で取得しました (712 kB/s)                                                                                                     
パッケージリストを読み込んでいます... 完了
pi@raspberrypi:~ $ sudo apt install postgresql
パッケージリストを読み込んでいます... 完了
依存関係ツリーを作成しています                
状態情報を読み取っています... 完了
以下の追加パッケージがインストールされます:
  libllvm7 libpq5 postgresql-11 postgresql-client-11 postgresql-client-common postgresql-common sysstat
提案パッケージ:
  postgresql-doc postgresql-doc-11 libjson-perl isag
以下のパッケージが新たにインストールされます:
  libllvm7 libpq5 postgresql postgresql-11 postgresql-client-11 postgresql-client-common postgresql-common sysstat
アップグレード: 0 個、新規インストール: 8 個、削除: 0 個、保留: 13 個。
27.0 MB 中 14.8 MB のアーカイブを取得する必要があります。
この操作後に追加で 105 MB のディスク容量が消費されます。
続行しますか? [Y/n] y
取得:1 http://ftp.jaist.ac.jp/pub/Linux/raspbian-archive/raspbian buster/main armhf libpq5 armhf 11.13-0+deb10u1 [155 kB]
取得:2 http://ftp.jaist.ac.jp/pub/Linux/raspbian-archive/raspbian buster/main armhf postgresql-client-11 armhf 11.13-0+deb10u1 [1,304 kB]
取得:3 http://ftp.jaist.ac.jp/pub/Linux/raspbian-archive/raspbian buster/main armhf postgresql-11 armhf 11.13-0+deb10u1 [13.3 MB]
14.8 MB を 7秒 で取得しました (2,117 kB/s)                                                                                                    
パッケージを事前設定しています ...
以前に未選択のパッケージ libllvm7:armhf を選択しています。
(データベースを読み込んでいます ... 現在 98850 個のファイルとディレクトリがインストールされています。)
.../0-libllvm7_1%3a7.0.1-8+rpi3+deb10u2_armhf.deb を展開する準備をしています ...
libllvm7:armhf (1:7.0.1-8+rpi3+deb10u2) を展開しています...
以前に未選択のパッケージ libpq5:armhf を選択しています。
.../1-libpq5_11.13-0+deb10u1_armhf.deb を展開する準備をしています ...
libpq5:armhf (11.13-0+deb10u1) を展開しています...
以前に未選択のパッケージ postgresql-client-common を選択しています。
.../2-postgresql-client-common_200+deb10u4_all.deb を展開する準備をしています ...
postgresql-client-common (200+deb10u4) を展開しています...
以前に未選択のパッケージ postgresql-client-11 を選択しています。
.../3-postgresql-client-11_11.13-0+deb10u1_armhf.deb を展開する準備をしています ...
postgresql-client-11 (11.13-0+deb10u1) を展開しています...
以前に未選択のパッケージ postgresql-common を選択しています。
.../4-postgresql-common_200+deb10u4_all.deb を展開する準備をしています ...
'postgresql-common による /usr/bin/pg_config から /usr/bin/pg_config.libpq-dev への退避 (divert)' を追加しています
postgresql-common (200+deb10u4) を展開しています...
以前に未選択のパッケージ postgresql-11 を選択しています。
.../5-postgresql-11_11.13-0+deb10u1_armhf.deb を展開する準備をしています ...
postgresql-11 (11.13-0+deb10u1) を展開しています...
以前に未選択のパッケージ postgresql を選択しています。
.../6-postgresql_11+200+deb10u4_all.deb を展開する準備をしています ...
postgresql (11+200+deb10u4) を展開しています...
以前に未選択のパッケージ sysstat を選択しています。
.../7-sysstat_12.0.3-2_armhf.deb を展開する準備をしています ...
sysstat (12.0.3-2) を展開しています...
postgresql-client-common (200+deb10u4) を設定しています ...
libpq5:armhf (11.13-0+deb10u1) を設定しています ...
postgresql-client-11 (11.13-0+deb10u1) を設定しています ...
update-alternatives: /usr/share/man/man1/psql.1.gz (psql.1.gz) を提供するために自動モードで /usr/share/postgresql/11/man/man1/psql.1.gz を使います
postgresql-common (200+deb10u4) を設定しています ...
supported-versions: WARNING! Unknown distribution: raspbian
debian found in ID_LIKE, treating as Debian
ユーザ postgres をグループ ssl-cert に追加

Creating config file /etc/postgresql-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
  en_gb
Removing obsolete dictionary files:
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service.
libllvm7:armhf (1:7.0.1-8+rpi3+deb10u2) を設定しています ...
sysstat (12.0.3-2) を設定しています ...

Creating config file /etc/default/sysstat with new version
update-alternatives: /usr/bin/sar (sar) を提供するために自動モードで /usr/bin/sar.sysstat を使います
Created symlink /etc/systemd/system/multi-user.target.wants/sysstat.service → /lib/systemd/system/sysstat.service.
postgresql-11 (11.13-0+deb10u1) を設定しています ...
Creating new PostgreSQL cluster 11/main ...
/usr/lib/postgresql/11/bin/initdb -D /var/lib/postgresql/11/main --auth-local peer --auth-host md5
データベースシステム内のファイルの所有者は"postgres"となります。
このユーザがサーバプロセスも所有する必要があります。

データベースクラスタはロケール"ja_JP.UTF-8"で初期化されます。
そのためデフォルトのデータベース符号化方式はUTF8に設定されました。
initdb: ロケール"ja_JP.UTF-8"用の適切なテキスト検索設定が見つかりません
デフォルトのテキスト検索設定はsimpleに設定されました。

データベージのチェックサムは無効です。

既存のディレクトリ/var/lib/postgresql/11/mainの権限を修正します ... 完了
サブディレクトリを作成します ... 完了
max_connectionsのデフォルト値を選択します ... 100
shared_buffersのデフォルト値を選択します ... 128MB
selecting default timezone ... Asia/Tokyo
動的共有メモリの実装を選択します ... posix
設定ファイルを作成します ... 完了
ブートストラップスクリプトを実行します ... 完了
ブートストラップ後の初期化を行っています ... 完了
データをディスクに同期します...完了

成功しました。以下のようにしてデータベースサーバを起動できます。

    pg_ctlcluster 11 main start

Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 down   postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
update-alternatives: /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) を提供するために自動モードで /usr/share/postgresql/11/man/man1/postmaster.1.gz を使います
postgresql (11+200+deb10u4) を設定しています ...
systemd (241-7~deb10u8+rpi1) のトリガを処理しています ...
man-db (2.8.5-2) のトリガを処理しています ...
libc-bin (2.28-10+rpt2+rpi1) のトリガを処理しています ...

インストールに成功した時のメッセージの通りにコマンドを実行してみた。

pi@raspberrypi:~ $ sudo pg_ctlcluster 11 main start
pi@raspberrypi:~ $ sudo su postgres
postgres@raspberrypi:/home/pi$ psql
psql (11.13 (Raspbian 11.13-0+deb10u1))
"help" でヘルプを表示します。

postgres=# exit
postgres@raspberrypi:/home/pi$

データベースユーザの追加

pi@raspberrypi:~ $ sudo su - postgres
postgres@raspberrypi:~$ createuser --pwprompt --interactive pgadmin
新しいロールのためのパスワード: 
もう一度入力してください：
新しいロールをスーパーユーザにしますか？ (y/n)y
postgres@raspberrypi:~$

データベースを作成

sample_dbというデータベースを作成します。

postgres@raspberrypi:~$ psql
psql (11.13 (Raspbian 11.13-0+deb10u1))
"help" でヘルプを表示します。

postgres=# \l
                                         データベース一覧
   名前    |  所有者  | エンコーディング |  照合順序   | Ctype(変換演算子) |     アクセス権限
-----------+----------+------------------+-------------+-------------------+-----------------------
 postgres  | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       |
 template0 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres          +
           |          |                  |             |                   | postgres=CTc/postgres
 template1 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres          +
           |          |                  |             |                   | postgres=CTc/postgres
(3 行)

postgres=# create database sample_db;
CREATE DATABASE

postgres=# \l
                                         データベース一覧
   名前    |  所有者  | エンコーディング |  照合順序   | Ctype(変換演算子) |     アクセス権限
-----------+----------+------------------+-------------+-------------------+-----------------------
 postgres  | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       |
 sample_db | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       |
 template0 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres          +
           |          |                  |             |                   | postgres=CTc/postgres
 template1 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres          +
           |          |                  |             |                   | postgres=CTc/postgres
(4 行)

postgres=#

テーブルの作成

サンプルとして、シリアルナンバーと名前をもつテーブルを作成します。
両方とも空（NULL）の状態のデータは作れないように制限しています。

postgres@raspberrypi:~$ psql -U pgadmin -d sample_db -h localhost
ユーザ pgadmin のパスワード:
psql (11.13 (Raspbian 11.13-0+deb10u1))
SSL 接続 (プロトコル: TLSv1.3、暗号化方式: TLS_AES_256_GCM_SHA384、ビット長: 256、圧縮: オフ)
"help" でヘルプを表示します。

sample_db=# create table sample( id serial NOT NULL, name text NOT NULL, PRIMARY KEY(id) );
CREATE TABLE
sample_db=# \dt
            リレーション一覧
 スキーマ |  名前  |    型    |  所有者
----------+--------+----------+----------
 public   | sample | テーブル | postgres
(1 行)

sample_db=# \d
                 リレーション一覧
 スキーマ |     名前      |     型     |  所有者
----------+---------------+------------+----------
 public   | sample        | テーブル   | postgres
 public   | sample_id_seq | シーケンス | postgres
(2 行)

sample_db=# \d sample
                            テーブル "public.sample"
  列  |   型    | 照合順序 | Null 値を許容 |             デフォルト
------+---------+----------+---------------+------------------------------------
 id   | integer |          | not null      | nextval('sample_id_seq'::regclass)
 name | text    |          | not null      |
インデックス:
    "sample_pkey" PRIMARY KEY, btree (id)

sample_db=#

カラムの追加

ALTER TABLE テーブル名 ADD COLUMN カラム名データ型;

sample_db=# ALTER TABLE sample ADD COLUMN date timestamp;
ALTER TABLE
sample_db=# \d sample
                                      テーブル "public.sample"
  列  |             型              | 照合順序 | Null 値を許容 |             デフォルト
------+-----------------------------+----------+---------------+------------------------------------
 id   | integer                     |          | not null      | nextval('sample_id_seq'::regclass)
 name | text                        |          | not null      |
 date | timestamp without time zone |          |               |
インデックス:
    "sample_pkey" PRIMARY KEY, btree (id)

カラムの削除

sample_db=# ALTER TABLE sample DROP COLUMN date;
ALTER TABLE
sample_db=# \d sample
                            テーブル "public.sample"
  列  |   型    | 照合順序 | Null 値を許容 |             デフォルト
------+---------+----------+---------------+------------------------------------
 id   | integer |          | not null      | nextval('sample_id_seq'::regclass)
 name | text    |          | not null      |
インデックス:
    "sample_pkey" PRIMARY KEY, btree (id)

PostgreSQLの設定

今回は、一台のラズパイで全部済ませるつもりなので必要ないとは思いますが一応。

他のマシンからのアクセスを許可する

postgresql.conf

sudo vi /etc/postgresql/11/main/postgresql.conf

listenaddressesがコメントにされていたので、

/etc/postgresql/11/main/postgresql.conf

# listen_addresses = 'localhost'         # what IP address(es) to listen on;

コメントを外して、アドレスをlocalhostから*に変更する。

/etc/postgresql/11/main/postgresql.conf

listen_addresses = '*'         # what IP address(es) to listen on;

pg_hba.conf

pi@raspberrypi:~ $ sudo vi /etc/postgresql/11/main/pg_hba.conf

以下のようにローカルネットワークからのアクセスを許可するために一行追加する。

/etc/postgresql/11/main/pg_hba.conf

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.10.0/24         md5

PostgreSQLの再起動

2つのファイルを更新したら、PostgreSQLを再起動する。

pi@raspberrypi:~ $ sudo pg_ctlcluster 11 main restart

PythonからPostgreSQLを使う

pip3を最新のものにする

pi@raspberrypi:~ $ pip3 install --upgrade pip
Looking in indexes: https://pypi.org/simple, https://www.piwheels.org/simple
Collecting pip
  Downloading https://files.pythonhosted.org/packages/90/a9/1ea3a69a51dcc679724e3512fc2aa1668999eed59976f749134eb02229c8/pip-21.3-py3-none-any.whl (1.7MB)
    100% |????????????????????????????????| 1.7MB 234kB/s
Installing collected packages: pip
Successfully installed pip-21.3

psycopg2をインストールする

pi@raspberrypi:~ $ pip3 install psycopg2
WARNING: pip is being invoked by an old script wrapper. This will fail in a future version of pip.
Please see https://github.com/pypa/pip/issues/5599 for advice on fixing the underlying issue.
To avoid this problem you can invoke Python with '-m pip' instead of running pip directly.
Defaulting to user installation because normal site-packages is not writeable
Looking in indexes: https://pypi.org/simple, https://www.piwheels.org/simple
Collecting psycopg2
  Downloading https://www.piwheels.org/simple/psycopg2/psycopg2-2.9.1-cp37-cp37m-linux_armv7l.whl (505 kB)
     |????????????????????????????????| 505 kB 213 kB/s
Installing collected packages: psycopg2
Successfully installed psycopg2-2.9.1

PythonでPostgreSQLに接続する

最初にテーブルに入っているデータを取得して表示、データを追加、再度データを取得して表示という簡単なサンプルを実行してみます。

sample.py

import psycopg2

con = psycopg2.connect( database='sample_db', user='pgadmin', password='xxxxxx', host='localhost', port=5432)

cur = con.cursor();

cur.execute('SELECT * FROM sample;')
result = cur.fetchall()
print( result )

cur.execute("INSERT INTO sample(name) VALUES ('\"test02\"');" )

cur.execute('SELECT * FROM sample;')
result = cur.fetchall()
print( result )

con.commit()

cur.close()
con.close()

sampleテーブルにはすでに、id:1、name:test01が入っている状態です。
サンプルプログラムでは、name:test02を追加しました。
サンプルを実行すると、idは自動採番されtest02が追加されます。

実行結果

pi@raspberrypi:~ $ python3 sample.py
[(1, '"test01"')]
[(1, '"test01"'), (2, '"test02"')]

確認

pi@raspberrypi:~ $ psql -U pgadmin -d sample_db -h localhost -p 5432
ユーザ pgadmin のパスワード:
psql (11.13 (Raspbian 11.13-0+deb10u1))
SSL 接続 (プロトコル: TLSv1.3、暗号化方式: TLS_AES_256_GCM_SHA384、ビット長: 256、圧縮: オフ)
"help" でヘルプを表示します。

sample_db=# select * from sample;
 id |   name
----+----------
  1 | "test01"
  2 | "test02"
(2 行)