infra-study .org
← Part 4 目次 実機演習を予約
演習進捗0 / 4未完了
⏱ 約 80 分
Part 4Chapter 02/サーバ構築

PostgreSQL を用意し、残高を安全に更新する

PostgreSQL を導入し、不正な値を拒む制約、更新をまとめて確定する transaction、同時更新を防ぐ行ロックによって、残高とイベントを安全に更新し、確定状態を証拠から説明します。

教材 ✓ 公開中登録不要 実機演習 ― 提供していない読んで学べます
所要時間約 80 分（概念 15 分＋演習 65 分）
前提知識Part 1 のファイル・権限操作、Part 2 の接続確認、Part 3 の systemd・ログ、Part 4 Chapter 1 のパッケージ取得経路
使用機材データベースサーバ db01、操作端末 workstation01、LAN 内のパッケージ取得経路
関連章前: Part 4 / Chapter 1「nginx を入れて、サービスの入口を公開する」／後: Part 4 / Chapter 3「Web、アプリ、DB をつないで、タップを処理する」

入口の後ろに、壊れないデータ層を置く

Chapter 1 では nginx を導入し、別ノードから要求が届く入口を作りました。しかし、入口が応答するだけでは、残高やタップ履歴を安全に保管できません。本章では db01 に PostgreSQL（データベース管理システム）を導入し、残高とイベントをひとまとまりで更新するデータ層を作ります。

Part 4 の App が扱う残高と event ID（1 回のタップ処理を見分ける識別子）は、すべて PostgreSQL に記録します（ほかの場所には持ちません）。更新は DB のトランザクション（transaction：複数の更新を「全部成功」か「全部なかったこと」のどちらかにまとめる仕組み）で守ります。このほか、制約（不正な値を DB が拒否する決まり）と行ロック（同じ行を同時に更新させない仕組み）も使います。共有ファイルの提供・書き込み・後始末は Part 6 で扱うため、本章の演習には含めません。

期待される出力
workstation01
      │ SSH
      ▼
    db01 ───── PostgreSQL
                 ├─ cards（残高）
                 └─ tap_events（処理履歴）

この章のゴール: PostgreSQL を導入して接続し、制約・transaction・行ロックによって残高と event ID を安全に更新できる。さらに、未確定 transaction と確定済みデータの違いを証跡で説明できる。

この章で作る DB は、街の改札サービスの「残高」を守る層にあたります。街の全体像と、街の言葉・技術の言葉の対応は Part 0（この教材で作る街）にまとめています。

本章では、次の範囲は扱いません。

この章を終えるとできること

データを「保存できた」だけでなく、競合と失敗を含めて状態を説明できるようになります。本章で身につく 4 動作 は次のとおりです。

01
① PostgreSQL を導入して接続する
LAN 内の取得経路を確認し、service・待受 socket・psql 接続を別々に確かめる。
02
② 壊れない更新を書く
制約と transaction を使い、残高とイベントを同時に確定する。
03
③ 競合を観測する
2 つの psql セッションで同じ行を操作し、行ロックによる待ちと重複拒否を確認する。
04
④ transaction の境界を確認する
保存先と未確定 transaction を確認し、切断後に確定済みデータだけが残ることを確かめる。

DB の整合性と共有ファイルを分けて考える

1. transaction：複数の変更をひとまとまりにする

交通 IC カード型サービスでは、残高を減らす処理と「どの event を処理したか」の記録が対になります。残高だけ減って履歴が無い、または履歴だけあって残高が変わらない状態は、処理の途中で接続が切れたときに起こり得ます。

transaction は BEGIN から COMMIT までの変更をひとまとまりにします。途中で処理を続けられなくなれば、未確定の変更は rollback されます。本章では、次の境界を SQL の実行順で確認します。

期待される出力
BEGIN
  ├─ 対象カードをロックする
  ├─ 残高が足りる場合だけ減額する
  └─ event ID を履歴へ登録する
COMMIT ── ここで残高と履歴が同時に確定する

2. 行ロックと待ち：同時更新を順番にする

2 つの接続が同じ残高を同時に読み、それぞれが古い値を基に更新すると、片方の変更を失う危険があります。SELECT ... FOR UPDATE は transaction が終わるまで対象行をロックし、同じ行を更新しようとする別の transaction を待たせます。

「待つ」は故障ではありません。先に処理している transaction の確定結果を見てから次の処理を進めるための動作です。ただし待ちが長いときは、誰がどの transaction を開いたままにしているかを確認する必要があります。

3. 制約：異常な値を後から探すのではなく、入れさせない

CHECK (balance >= 0) は負の残高を拒否し、PRIMARY KEY (event_id) は同じ event ID の二重登録を拒否します。アプリ側の確認だけに頼ると、別の経路から SQL が実行された場合や、同時実行の隙間で不正な値が入る可能性があります。

制約はデータの最後の防波堤です。エラーが出たこと自体を失敗と決めつけず、「守るべき条件に反したため DB が拒否した」という成功の証拠として読みます。

PostgreSQL のデータディレクトリと、他ノードが読み書きする共有領域は責務が異なります。DB の整合性は PostgreSQL が管理し、共有領域の隔離と後始末は Part 6 で扱います。

📔 コラム · rollback の境界は VM の境界

db01 の snapshot を戻せば、VM 内の package、schema（テーブルや制約などの DB の構造）、DB の変更は戻せます。一方、別装置に書いた共有ファイルは VM snapshot の外側に残り得ます。Part 4 では実際に書き込まず、この外部残留の実装と検査を Part 6 へ引き渡します。

演習：残高更新と transaction の境界を観測する

演習環境

項目値
構築対象db01
操作端末workstation01（STEP 05 は左右 2 枚の端末を使う）
接続方法workstation01 から ssh deployer@db01
必要権限sudo。package 導入、PostgreSQL 管理ユーザでの操作に使う
パッケージ取得署名済み内部 APT リポジトリ（http://172.16.10.22/ubuntu）
所要時間約 65 分（STEP 1〜8 の実行）

🛟 失敗しても安全: DB 内の未確定変更は SQL の ROLLBACK で戻します。章を終えるときは、STEP 08 末尾の削除手順で本章が追加した DB と PostgreSQL を消し、構築前の状態へ戻ったことを確認します。

LAN 内の取得経路を確認して PostgreSQL を導入する

db01 は本章で初めて使うサーバです。ここでは第 1 章の STEP 01 と同じ 4 段階を省略せずに実行します。公開鍵の取得、別経路での fingerprint 照合、既存取得先を変更しない確認、専用 .sources の作成を順に行い、最後に apt update の取得先を確かめます。

ほかの操作を始める前に、db01 1 台で構築前の manifest（ファイルの場所・権限・更新日時などを一覧にしたもの）と package 一覧を採ります。manifest の対象は path・type・mode・uid・gid・size・mtime です。章末に同じ条件でもう一度採り、4 分類の列挙後に差分を確認します。

bash · 構築前 manifest
# db01 で、章のほかの操作より先に実行する
sudo find /etc /var/lib /srv /opt /usr/local "$HOME" \
  -not -path '/var/lib/apt/*' \
  -not -path '/var/lib/dpkg/*' \
  -not -path '/var/lib/systemd/*' \
  -not -path '/var/log/*' \
  -not -path '/var/cache/*' \
  -not -name '.bash_history' \
  -not -path '/etc/passwd-' -not -path '/etc/group-' \
  -not -path '/etc/shadow-' -not -path '/etc/gshadow-' \
  -not -path '/var/lib/fwupd' -not -path '/var/lib/fwupd/*' \
  -not -path '/etc/fwupd' -not -path '/etc/fwupd/*' \
  -not -path '/var/lib/ucf' -not -path '/var/lib/ucf/*' \
  -not -path '/var/lib/landscape' -not -path '/var/lib/landscape/*' \
  -not -path '/etc/ld.so.cache' \
  -not -path '/var/lib/ubuntu-release-upgrader/release-upgrade-available' \
  -not -path '/var/lib/snapd/state.json' \
  -not -path '/var/lib/snapd/maintenance.json' \
  -not -path '/var/lib/logrotate/status' \
  -printf '%p\t%y\t%m\t%U\t%G\t%s\t%T@\n' 2>/dev/null \
  | LC_ALL=C sort \
  | sudo tee /tmp/part4-manifest-before.tsv >/dev/null
dpkg-query -W -f='${binary:Package}\n' | LC_ALL=C sort > /tmp/part4-packages-before.tsv
test -s /tmp/part4-packages-before.tsv

1. 公開鍵を取得する

bash · コマンド
ssh deployer@db01
REPO_URI='http://172.16.10.22/ubuntu'
curl --fail --show-error --head "$REPO_URI/dists/noble/InRelease"
curl --fail --show-error "$REPO_URI/infra-study-archive-keyring.asc" \
  -o /tmp/infra-study-archive-keyring.asc

2. fingerprint を別経路の正本と照合する

鍵を取得した HTTP 経路だけを信頼の起点にしません。取得した鍵の fingerprint を表示し、HTTPS の教材ページ に掲載された正本と全桁で照合します。

bash · コマンド
GPG_HOME="$(mktemp -d)"
chmod 700 "$GPG_HOME"
gpg --homedir "$GPG_HOME" --show-keys --with-fingerprint --keyid-format long \
  /tmp/infra-study-archive-keyring.asc

EXPECTED_FINGERPRINT='089E07BDFFA26DB7C7203016297FAD1317CA1DC4'
ACTUAL_FINGERPRINT="$(gpg --homedir "$GPG_HOME" --show-keys --with-colons \
  /tmp/infra-study-archive-keyring.asc | awk -F: '$1=="fpr" {print $10; exit}')"
test "$ACTUAL_FINGERPRINT" = "$EXPECTED_FINGERPRINT"
fingerprint_status=$?
rm -rf -- "$GPG_HOME"
echo "fingerprint match=$fingerprint_status"
test "$fingerprint_status" -eq 0

fingerprint match=0 であることを確認してから次へ進みます。一致しなければ鍵を配置せず、演習を止めます。確認には専用の一時 GNUPGHOME を使い、既存の ~/.gnupg には触れません。

3. 既存の取得先を確認し、変更しない

先に現在有効な source を確認します。本章は次の手順で章専用ディレクトリだけを APT に読ませるため、既存 source の削除・移動・改名は行いません。

bash · コマンド
sudo grep -HnE '^[[:space:]]*(deb |Types:|URIs:)' \
  /etc/apt/sources.list \
  /etc/apt/sources.list.d/*.list \
  /etc/apt/sources.list.d/*.sources 2>/dev/null || true

4. 専用 source を作り、取得先を確認する

bash · コマンド
sudo install -d -m 0755 /etc/apt/keyrings
sudo install -o root -g root -m 0644 \
  /tmp/infra-study-archive-keyring.asc \
  /etc/apt/keyrings/infra-study-archive-keyring.asc

sudo install -d -m 0755 /etc/apt/sources.list.d.part4
sudo tee /etc/apt/sources.list.d.part4/infra-study.sources > /dev/null <<EOF
Types: deb
URIs: $REPO_URI
Suites: noble
Components: part4
Architectures: amd64
Signed-By: /etc/apt/keyrings/infra-study-archive-keyring.asc
EOF

sudo chmod 0644 /etc/apt/sources.list.d.part4/infra-study.sources
cat /etc/apt/sources.list.d.part4/infra-study.sources

# 章専用 source だけを見る。既存の取得先には触れない
APT_ONLY_PART4=(
  -o Dir::Etc::sourcelist=/dev/null
  -o Dir::Etc::sourceparts=/etc/apt/sources.list.d.part4
  -o APT::Get::List-Cleanup=0
)

sudo env LC_ALL=C apt-get "${APT_ONLY_PART4[@]}" update 2>&1 | tee /tmp/apt-update-db01.log
grep -E '^(Get|Hit):' /tmp/apt-update-db01.log
apt-cache "${APT_ONLY_PART4[@]}" policy postgresql postgresql-client
sudo apt-get "${APT_ONLY_PART4[@]}" install postgresql postgresql-client
期待される出力
HTTP/1.1 200 OK
Server: nginx/1.24.0 (Ubuntu)
Date: Tue, 18 Aug 2026 01:52:30 GMT
Content-Type: application/octet-stream
Content-Length: 2699
Last-Modified: Sun, 16 Aug 2026 01:35:52 GMT
Connection: keep-alive
ETag: "6a8113f8-a8b"
Accept-Ranges: bytes

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed

  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
  0  2699    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed

  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
100   425  100   425    0     0   363k      0 --:--:-- --:--:-- --:--:--  415k
pub   ed25519/297FAD1317CA1DC4 2026-08-05 [SC] [expires: 2027-08-05]
      Key fingerprint = 089E 07BD FFA2 6DB7 C720  3016 297F AD13 17CA 1DC4
uid                            infra-study lab archive <archive@lab.invalid>

fingerprint match=0
--- before ---
/etc/apt/sources.list.d/ubuntu.sources:43:Types: deb
/etc/apt/sources.list.d/ubuntu.sources:44:URIs: http://archive.ubuntu.com/ubuntu
/etc/apt/sources.list.d/ubuntu.sources:51:Types: deb
/etc/apt/sources.list.d/ubuntu.sources:52:URIs: http://security.ubuntu.com/ubuntu
--- after ---
Types: deb
URIs: http://172.16.10.22/ubuntu
Suites: noble
Components: part4
Architectures: amd64
Signed-By: /etc/apt/keyrings/infra-study-archive-keyring.asc

WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

Get:1 http://172.16.10.22/ubuntu noble InRelease [2699 B]
Get:2 http://172.16.10.22/ubuntu noble/part4 amd64 Packages [14.5 kB]
Fetched 17.2 kB in 0s (121 kB/s)
Reading package lists...
Building dependency tree...
Reading state information...
All packages are up to date.
Get:1 http://172.16.10.22/ubuntu noble InRelease [2699 B]
Get:2 http://172.16.10.22/ubuntu noble/part4 amd64 Packages [14.5 kB]
postgresql:
  Installed: (none)
  Candidate: 16+257build1.1
  Version table:
     16+257build1.1 500
        500 http://172.16.10.22/ubuntu noble/part4 amd64 Packages
postgresql-client:
  Installed: (none)
  Candidate: 16+257build1.1
  Version table:
     16+257build1.1 500
        500 http://172.16.10.22/ubuntu noble/part4 amd64 Packages
Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/16/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Setting up postgresql (16+257build1.1) ...
Processing triggers for man-db (2.12.0-4build2) ...
Processing triggers for libc-bin (2.39-0ubuntu8.8) ...

Running kernel seems to be up-to-date.

No services need to be restarted.

No containers need to be restarted.

No user sessions are running outdated binaries.

No VM guests are running outdated hypervisor (qemu) binaries on this host.
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
     Active: active (exited) since Tue 2026-08-18 01:53:56 UTC; 8s ago
   Main PID: 2964 (code=exited, status=0/SUCCESS)
        CPU: 3ms

導入結果だけでなく、apt update が http://172.16.10.22/ubuntu の InRelease と Packages だけを取得したことを確認します。外部の配布元が 1 件でも表示されたら導入を止め、有効な source を再確認します。

service・待受 socket・接続を別々に確認する

まず systemd が管理する service の状態を確認します。次に TCP の待受 socket を確認し、最後に PostgreSQL 管理ユーザとして psql へ接続します。

bash · コマンド
systemctl status postgresql --no-pager
sudo ss -lntp | grep postgres
sudo -u postgres psql -c 'SELECT version();'

SELECT version() の表示が pager の (END) で止まったら、q を押して psql の表示へ戻ります。固まったわけではありません。

期待される出力
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
     Active: active (exited) since Tue 2026-08-18 01:53:56 UTC; 24s ago
   Main PID: 2964 (code=exited, status=0/SUCCESS)
        CPU: 3ms
LISTEN 0      200        127.0.0.1:5432      0.0.0.0:*    users:(("postgres",pid=3851,fd=7))
LISTEN 0      200            [::1]:5432         [::]:*    users:(("postgres",pid=3851,fd=6))
                                                                 version
------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.14 (Ubuntu 16.14-0ubuntu0.24.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
(1 row)

3 つは同じ確認ではありません。systemctl は管理状態、ss は通信の受付、psql は DB として問い合わせへ応答できることを見ています。

残高と event ID を守る schema を作る

演習用 DB を作り、cards と tap_events を定義します。残高が負にならない条件と event ID の一意性を DB 自身に守らせます。

bash · コマンド
sudo -u postgres createdb transit_lab

sudo -u postgres psql -P pager=off -d transit_lab <<'SQL'
CREATE TABLE cards (
    card_id text PRIMARY KEY,
    balance integer NOT NULL CHECK (balance >= 0)
);

CREATE TABLE tap_events (
    event_id text PRIMARY KEY,
    card_id text NOT NULL REFERENCES cards(card_id),
    amount integer NOT NULL CHECK (amount > 0),
    created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO cards (card_id, balance)
VALUES ('card-demo', 1000);

\d cards
\d tap_events
TABLE cards;
SQL
期待される出力
CREATE TABLE
CREATE TABLE
INSERT 0 1
                Table "public.cards"
 Column  |  Type   | Collation | Nullable | Default
---------+---------+-----------+----------+---------
 card_id | text    |           | not null |
 balance | integer |           | not null |
Indexes:
    "cards_pkey" PRIMARY KEY, btree (card_id)
Check constraints:
    "cards_balance_check" CHECK (balance >= 0)
Referenced by:
    TABLE "tap_events" CONSTRAINT "tap_events_card_id_fkey" FOREIGN KEY (card_id) REFERENCES cards(card_id)

                       Table "public.tap_events"
   Column   |           Type           | Collation | Nullable | Default
------------+--------------------------+-----------+----------+---------
 event_id   | text                     |           | not null |
 card_id    | text                     |           | not null |
 amount     | integer                  |           | not null |
 created_at | timestamp with time zone |           | not null | now()
Indexes:
    "tap_events_pkey" PRIMARY KEY, btree (event_id)
Check constraints:
    "tap_events_amount_check" CHECK (amount > 0)
Foreign-key constraints:
    "tap_events_card_id_fkey" FOREIGN KEY (card_id) REFERENCES cards(card_id)

  card_id  | balance
-----------+---------
 card-demo |    1000
(1 row)

CHECK と主キー（各行を見分けるための列）が表示されることを確認します。これらは処理後に異常を探す仕組みではなく、不正な状態への変更を入口で拒否する仕組みです。

残高とイベントを 1 transaction で確定する

対象カードをロックし、残高が足りる場合だけ減額して、同じ transaction 内で event を記録します。各文を 1 行ずつ読みながら実行してください。

bash · コマンド
sudo -u postgres psql -P pager=off -d transit_lab
sql · コマンド
BEGIN;
SELECT card_id, balance
FROM cards
WHERE card_id = 'card-demo'
FOR UPDATE;

UPDATE cards
SET balance = balance - 120
WHERE card_id = 'card-demo'
  AND balance >= 120
RETURNING card_id, balance;

INSERT INTO tap_events (event_id, card_id, amount)
VALUES ('event-first', 'card-demo', 120);

COMMIT;

SELECT card_id, balance FROM cards WHERE card_id = 'card-demo';
SELECT event_id, card_id, amount FROM tap_events WHERE event_id = 'event-first';
期待される出力
BEGIN
  card_id  | balance
-----------+---------
 card-demo |    1000
(1 row)

  card_id  | balance
-----------+---------
 card-demo |     880
(1 row)

UPDATE 1
INSERT 0 1
COMMIT
  card_id  | balance
-----------+---------
 card-demo |     880
(1 row)

  event_id   |  card_id  | amount
-------------+-----------+--------
 event-first | card-demo |    120
(1 row)

UPDATE 1 と INSERT 0 1 は、どちらも COMMIT まで未確定です。残高と履歴が同時に確定したことを、最後の 2 つの SELECT で確認します。

2 つの接続で行ロックの待ちと重複拒否を観測する

端末を左右に並べ、両方から db01 へ接続します。先に左側で対象行をロックし、transaction を開いたままにします。

左側の psql で実行します。

sql · コマンド
BEGIN;
SELECT card_id, balance
FROM cards
WHERE card_id = 'card-demo'
FOR UPDATE;

UPDATE cards
SET balance = balance - 100
WHERE card_id = 'card-demo'
  AND balance >= 100
RETURNING card_id, balance;

INSERT INTO tap_events (event_id, card_id, amount)
VALUES ('event-lock-a', 'card-demo', 100);
期待される出力
BEGIN
  card_id  | balance
-----------+---------
 card-demo |     880
(1 row)

  card_id  | balance
-----------+---------
 card-demo |     780
(1 row)

UPDATE 1
INSERT 0 1

左側を COMMIT せず、右側の psql で同じ行をロックします。

sql · コマンド
BEGIN;
SELECT card_id, balance
FROM cards
WHERE card_id = 'card-demo'
FOR UPDATE;

右側は結果を表示せず、ロックが解放されるまで待ち続けます。終了コードは表示されません。右側を待たせたまま、左側の psql で次の SQL を直接実行し、接続状態を観測します。

sql · 左側で観測
SELECT pg_stat_clear_snapshot();
SELECT pid, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE datname = 'transit_lab'
ORDER BY pid;

左側は BEGIN したまま COMMIT していないため、同じ transaction の中では pg_stat_activity の結果が使い回されます。1 文目の pg_stat_clear_snapshot() は、その結果を捨てて取り直すための操作です。中身が空の 1 行（(1 row)）が表示されれば正常です。同じ確認をやり直すときも、この 2 文を続けて実行してください。表示された行のうち、右側の接続が wait_event_type=Lock、wait_event=transactionid になっていることを確認します。pid は実行のたびに変わります。確認できたら、左側で確定します。

sql · コマンド
COMMIT;

左側の確定後、右側の SELECT が返り、確定後の残高 780 を読めることを確認します。右側は変更せずに終了します。

期待される出力 · 右側
  card_id  | balance
-----------+---------
 card-demo |     780
(1 row)
sql · コマンド
ROLLBACK;

続いて、確定済みの event ID をもう一度登録します。

sql · コマンド
INSERT INTO tap_events (event_id, card_id, amount)
VALUES ('event-lock-a', 'card-demo', 100);
期待される出力
ERROR:  duplicate key value violates unique constraint "tap_events_pkey"
DETAIL:  Key (event_id)=(event-lock-a) already exists.

重複登録のエラーは、同じ event を二重処理させない制約が働いた証拠です。右側の prompt が transaction 失敗状態になった場合は ROLLBACK; して通常状態へ戻します。

transaction と接続の状態を読み分ける

STEP 05 では、右側の接続が待っている間に、左側の psql で pg_stat_activity を直接確認しました。ここでは観測結果を、画面の見え方と DB 内部の状態へ対応づけます。

観測意味切り分け
右側に結果が出ない先行 transaction の終了待ち通信断や service 停止と決めつけない
wait_event_type=LockDB 内のロック待ちwait_event と先行 transaction を確認する
左側の COMMIT 後に結果が返るロックが解放された確定後の残高を読めたことを確認する

PostgreSQL の保存先と確定状態を確認する

PostgreSQL のデータディレクトリと、確定済みの残高・event を確認します。データディレクトリを移動する操作は行いません。

bash · コマンド
sudo -u postgres psql -Atc 'SHOW data_directory;'
sudo -u postgres psql -P pager=off -d transit_lab -c \
  "SELECT card_id, balance FROM cards ORDER BY card_id;"
sudo -u postgres psql -P pager=off -d transit_lab -c \
  "SELECT event_id, card_id, amount FROM tap_events ORDER BY event_id;"
期待される出力
/var/lib/postgresql/16/main
  card_id  | balance
-----------+---------
 card-demo |     780
(1 row)

   event_id   |  card_id  | amount
--------------+-----------+--------
 event-first  | card-demo |    120
 event-lock-a | card-demo |    100
(2 rows)

cards と tap_events は同じ transaction で確定します。App は残高とイベントをこの DB だけに記録し、ローカルファイルへ別の残高を持ちません。

未確定 transaction を壊し、確定済みデータだけが残ることを確認する + GOAL 🎯

まず psql で残高を更新し、COMMIT せずに接続を切ります。

sql · コマンド
BEGIN;
SELECT card_id, balance
FROM cards
WHERE card_id = 'card-demo'
FOR UPDATE;

UPDATE cards
SET balance = balance - 50
WHERE card_id = 'card-demo'
  AND balance >= 50
RETURNING card_id, balance;

\q

再接続し、切断前に表示された未確定の残高ではなく、最後に commit した残高へ戻っていることを確認します。

bash · コマンド
sudo -u postgres psql -P pager=off -d transit_lab \
  -c "SELECT card_id, balance FROM cards WHERE card_id = 'card-demo';"
期待される出力
BEGIN
  card_id  | balance
-----------+---------
 card-demo |     780
(1 row)

  card_id  | balance
-----------+---------
 card-demo |     730
(1 row)

UPDATE 1
  card_id  | balance
-----------+---------
 card-demo |     780
(1 row)

最後に、最後に commit した event と残高が対応し、切断した transaction の変更が残っていないことを確認します。証跡を保存したら、次の削除手順で本章が追加した構築物を消します。

ゴール到達 ✓　① PostgreSQL の稼働を service・socket・接続で確認できた / ② 制約・transaction・行ロックで残高更新を守れた / ③ 未確定 transaction を切断し、確定済みデータだけが残ることを確認できた。

演習の証跡を保存したら、自分で追加した構築物を削除します。先に DB transit_lab を削除し、その後で PostgreSQL 本体を削除します。package を先に消すと dropdb を実行する手段がなくなるため、順序を逆にしてはいけません。

STEP 01 で配置した infra-study.sources と keyring は本章の構築物なので削除します。既存の APT source は最初から変更していないため、復元操作や終了時の無限定 apt update は行いません。fingerprint 確認用の専用 GNUPGHOME は STEP 01 内ですでに削除済みで、既存の ~/.gnupg は変更していません。

package は章開始時の一覧との差分を取り、PostgreSQL 導入で確認済みの名前だけかを検査します。想定外の package が 1 つでもあれば、削除せずに停止します（「想定外の package: …」と「SSH 接続は維持したまま処理を中止しました」が表示されます）。検査を通った差分だけを名指しで削除します。apt autoremove は使いません。章の外で auto 扱いになった package まで対象になり、clean snapshot の状態によって削除範囲が変わるためです。削除後に /etc/ssl/private の group と mode を基準値へ戻してから、postgres ユーザの不在を確認します。

bash · コマンド
(
test -s /tmp/part4-packages-before.tsv || { echo '構築前の package 一覧がありません' >&2; exit 1; }
mapfile -t chapter_packages < <(dpkg-query -W -f='${binary:Package}\n' | LC_ALL=C sort | comm -13 /tmp/part4-packages-before.tsv -)
unexpected=()
for pkg in "${chapter_packages[@]}"; do
  case "${pkg%:amd64}" in
    libcommon-sense-perl|libjson-perl|libjson-xs-perl|libllvm17t64|libpq5|libtypes-serialiser-perl|postgresql|postgresql-16|postgresql-client|postgresql-client-16|postgresql-client-common|postgresql-common|ssl-cert) ;;
    *) unexpected+=("$pkg") ;;
  esac
done
((${#unexpected[@]} == 0)) || { printf '想定外の package: %s\n' "${unexpected[@]}" >&2; exit 1; }

sudo -u postgres dropdb --if-exists transit_lab
sudo -u postgres psql -Atqc "SELECT count(*) FROM pg_database WHERE datname = 'transit_lab';"
# package を消してもデータは残る。先に cluster を落とす
pg_lsclusters --no-header
pg_lsclusters --no-header | awk '{print $1, $2}' | while read -r version cluster; do
  sudo pg_dropcluster --stop "$version" "$cluster"
done

APT_ONLY_PART4=(
  -o Dir::Etc::sourcelist=/dev/null
  -o Dir::Etc::sourceparts=/etc/apt/sources.list.d.part4
  -o APT::Get::List-Cleanup=0
)
((${#chapter_packages[@]} == 0)) || sudo DEBIAN_FRONTEND=noninteractive apt-get "${APT_ONLY_PART4[@]}" purge -y "${chapter_packages[@]}"

sudo userdel postgres 2>/dev/null || true
sudo groupdel postgres 2>/dev/null || true
sudo groupdel ssl-cert 2>/dev/null || true
sudo chown root:root /etc/ssl/private
sudo chmod 0700 /etc/ssl/private

sudo rm -rf /etc/apt/sources.list.d.part4
sudo rm -f /etc/apt/keyrings/infra-study-archive-keyring.asc
rm -f /tmp/infra-study-archive-keyring.asc /tmp/apt-update-db01.log
test ! -e /etc/apt/sources.list.d.part4
test ! -e /etc/apt/keyrings/infra-study-archive-keyring.asc

mapfile -t package_diff < <(dpkg-query -W -f='${binary:Package}\n' | LC_ALL=C sort | comm -13 /tmp/part4-packages-before.tsv -)
if ((${#package_diff[@]})); then
  printf '章開始時より増えた package: %s\n' "${package_diff[@]}" >&2
  exit 1
else
  echo '章開始時より増えた package は 0 件'
fi
rm -f /tmp/part4-packages-before.tsv
test ! -e /tmp/part4-packages-before.tsv

if systemctl is-active --quiet postgresql; then
  echo "PostgreSQL service 稼働中"
else
  echo "PostgreSQL service は非稼働"
fi
if sudo ss -lntp | grep -q ':5432 '; then
  echo "5432 番の待受あり"
else
  echo "5432 番の待受なし"
fi
if sudo test -e /var/lib/postgresql -o -e /etc/postgresql; then
  echo "データディレクトリが残っています"
else
  echo "データディレクトリなし"
fi
getent passwd postgres || echo "postgres user なし"
getent group postgres || echo "postgres group なし"
getent group ssl-cert || echo "ssl-cert group なし"
stat -c '/etc/ssl/private mode=%a gid=%g' /etc/ssl/private
)
rc=$?
((rc == 0)) || echo 'SSH 接続は維持したまま処理を中止しました' >&2
分類検査すること合格条件
DBpg_database の transit_lab0 行
package / service章開始時との差分と PostgreSQL service増えた package が 0 件で、service が稼働していない
socketPostgreSQL の 5432 番待受がない
データcluster（1 つのデータディレクトリで管理される DB のまとまり）のデータと設定（/var/lib/postgresql・/etc/postgresql）どちらも残っていない
期待される出力
0
章開始時より増えた package は 0 件
PostgreSQL service は非稼働
5432 番の待受なし

4 分類の外を manifest 差分で確認する

4 分類を自分で列挙して検査したあと、その外側に残る変更を、構築前後の sorted manifest で確認します。差分は 4 分類を置き換えるものではなく、列挙から漏れた残留候補を見つける安全網です。全差分は /var/tmp/part4-chapter-manifest.diff に一時保存し、「残留候補」と「更新日時だけ変わった通常ファイル」に分けて全件を画面に表示してから削除します。途中で切りません。表示に失敗したときは、構築前・章末の manifest と差分ファイルを消さずに残し、理由を表示します。表示された理由を確かめるまで、後片付けへ進まないでください。

次の除外には、それぞれ理由があります。

  • /var/lib/apt/ は apt が索引を更新する場所で、章の作業とは無関係に必ず変わります。
  • /var/lib/dpkg/ は package 操作で更新される管理情報で、package 差分として別に判定します。
  • /var/lib/systemd/ は service の状態管理で更新される場所で、service・unit の分類として別に判定します。
  • /var/log/ は操作の証跡そのもので、コマンドを実行するだけでも変わります。
  • /var/cache/ は再生成できる一時的な cache で、章の構築物とは分けて扱います。
  • .bash_history は操作履歴として更新されるため、章が配置したファイルとは分けて扱います。
  • /etc/passwd-・group-・shadow-・gshadow- は shadow-utils が保持する前世代の自動バックアップです。本体は検査対象に残します。
  • /var/lib/fwupd/ と /etc/fwupd/ は fwupd が生成・管理する機器メタデータと設定です。
  • /var/lib/ucf/ は ucf が生成する設定管理台帳です。
  • /var/lib/landscape/ は landscape が生成するシステム情報 cache です。
  • /etc/ld.so.cache は ldconfig が生成する共有ライブラリ cache です。
  • /var/lib/snapd/state.json・/var/lib/snapd/maintenance.json と /var/lib/logrotate/status は、snapd と logrotate が章と無関係に定期的に書き換える状態ファイルです。親ディレクトリは除外しません。

/var/lib/swcatalog/ は除外しません。既存 source を変更せず、章専用 source だけを指定して package を操作した結果まで検査するためです。

bash · manifest 差分
sudo find /etc /var/lib /srv /opt /usr/local "$HOME" \
  -not -path '/var/lib/apt/*' \
  -not -path '/var/lib/dpkg/*' \
  -not -path '/var/lib/systemd/*' \
  -not -path '/var/log/*' \
  -not -path '/var/cache/*' \
  -not -name '.bash_history' \
  -not -path '/etc/passwd-' -not -path '/etc/group-' \
  -not -path '/etc/shadow-' -not -path '/etc/gshadow-' \
  -not -path '/var/lib/fwupd' -not -path '/var/lib/fwupd/*' \
  -not -path '/etc/fwupd' -not -path '/etc/fwupd/*' \
  -not -path '/var/lib/ucf' -not -path '/var/lib/ucf/*' \
  -not -path '/var/lib/landscape' -not -path '/var/lib/landscape/*' \
  -not -path '/etc/ld.so.cache' \
  -not -path '/var/lib/ubuntu-release-upgrader/release-upgrade-available' \
  -not -path '/var/lib/snapd/state.json' \
  -not -path '/var/lib/snapd/maintenance.json' \
  -not -path '/var/lib/logrotate/status' \
  -printf '%p\t%y\t%m\t%U\t%G\t%s\t%T@\n' 2>/dev/null \
  | LC_ALL=C sort \
  | sudo tee /tmp/part4-manifest-after.tsv >/dev/null
sudo diff -u /tmp/part4-manifest-before.tsv /tmp/part4-manifest-after.tsv > /var/tmp/part4-chapter-manifest.diff
diff_rc=$?
if [ "$diff_rc" -le 1 ]; then
  if awk 'BEGIN { FS = "\t"; c = "LC_ALL=C sort" }
      /^\+\+\+|^---|^@@/ { next }
      /^[+-]/ {
        if (NF != 7) { printf "manifest の列数が 7 ではありません: %s\n", $0 > "/dev/stderr"; bad = 1; exit 2 }
        t++; s = substr($1, 1, 1); p = substr($1, 2)
        k = $2 " " $3 " " $4 " " $5 " " ($2 == "d" ? "-" : $6)
        if (s == "-") { if (p in b) { dup = p; exit 2 } b[p] = k; bm[p] = $7; bt[p] = $2 }
        else          { if (p in a) { dup = p; exit 2 } a[p] = k; am[p] = $7 }
      }
      END {
        if (bad) exit 2
        if (dup != "") { printf "同じパスが重複しています: %s\n", dup > "/dev/stderr"; exit 2 }
        print "== 残留候補（0 件で合格）=="
        n = 0
        for (p in b) {
          if (!(p in a)) { print "消えた   " p | c; n++ }
          else if (a[p] != b[p]) { print "変わった " p "  (" b[p] " -> " a[p] ")" | c; n++ }
        }
        for (p in a) if (!(p in b)) { print "増えた   " p | c; n++ }
        fflush(); close(c)
        print "残留候補: " n " 件"
        print "== 更新日時だけ変わった通常ファイル（1 件ずつ理由を説明できること）=="
        m = 0; d = 0
        for (p in b) if ((p in a) && a[p] == b[p] && am[p] != bm[p]) { if (bt[p] == "d") d++; else { print p | c; m++ } }
        fflush(); close(c)
        print "更新日時だけ変わった通常ファイル: " m " 件 ／ ディレクトリ: " d " 件"
        print "差分行総数: " (t + 0)
      }' /var/tmp/part4-chapter-manifest.diff; then
    sudo rm -f /tmp/part4-manifest-before.tsv /tmp/part4-manifest-after.tsv
  else
    echo "差分の表示に失敗しました。構築前・章末の manifest と /var/tmp/part4-chapter-manifest.diff は残してあります。表示された理由を確認してください" >&2
  fi
else
  echo "差分を採れませんでした（diff の終了コード $diff_rc）。構築前 manifest /tmp/part4-manifest-before.tsv があるか確認してください" >&2
fi
bash · 差分証跡の後片付け
sudo rm -f /var/tmp/part4-chapter-manifest.diff
test ! -e /var/tmp/part4-chapter-manifest.diff

表示は 2 つの節に分かれます。合格の条件は、1 つ目の「残留候補」が 0 件であることです。残留候補は、構築前になかったパスが増えた、あったパスが消えた、種別・権限・所有者・サイズが変わった、のいずれかです（ディレクトリのサイズは比べません）。

2 つ目の「更新日時だけ変わった通常ファイル」は、中身の属性は同じで更新日時だけが違うファイルです。ここに出る行は 1 件ずつ、次のどちらかで説明できなければ完了にしません。

  • OS の自動処理（package 索引の更新、通知用の記録など）が書いたもの。
  • 章の操作の跡で、中身は戻っているもの。

どちらでも説明できない行が 1 件でもあれば、それは自分が挙げた 4 分類の外側にある残留です。何の分類にあたるかを考えて「5 番目の分類」を自分の後片付け手順へ足し、解消後に再確認してください。

期待される出力
（形の例です。M・K・T には実行したときの件数が入ります。更新日時だけ変わった通常ファイルの行と件数は、台・章によって変わります。合格の条件は「残留候補: 0 件」です）
== 残留候補（0 件で合格）==
残留候補: 0 件
== 更新日時だけ変わった通常ファイル（1 件ずつ理由を説明できること）==
（該当するパスが 1 行ずつ並びます）
更新日時だけ変わった通常ファイル: M 件 ／ ディレクトリ: K 件
差分行総数: T

この演習環境は、セッションが終わると基盤側の手順でも初期状態へ戻ります。ただし、それを当てにして終えるのではなく、自分が作ったものを自分で消し、DB・package / service・socket・データの 4 分類と manifest 差分で消えたことを確認するところまでが演習です。

つまずきポイント

ハマり #1 · 未 commit のまま接続を切ったら変更が消えた

transaction 中の UPDATE では残高が変わって見えたのに、再接続すると元の値へ戻っています。

確認順序

  1. psql の prompt が transaction 中を示していたか確認する。
  2. COMMIT を実行した記録があるか確認する。
  3. 再接続後に同じ card_id を検索する。
  • 原因: 接続終了時点で transaction が未確定だったため、PostgreSQL が自動的に rollback しました。
  • 対処: 変更を残す処理では、関連する SQL がすべて成功した後に明示的に COMMIT します。途中失敗なら ROLLBACK し、どちらで終えたかを曖昧にしません。
ハマり #2 · event の INSERT が一意制約（同じ値を 2 度入れさせない決まり）で拒否された

同じ event ID を再度 INSERT すると、重複を示すエラーになります。

確認順序

  1. SELECT で同じ event_id がすでに存在するか確認する。
  2. \d tap_events で主キーまたは一意制約を確認する。
  3. 直前の transaction が確定済みか確認する。
  • 原因: 同じ event を二重に記録しない制約が働いています。
  • 対処: 別 event なら新しい ID を使います。同じ要求の再送なら、既存行を成功結果として扱うか拒否するかをアプリ側で決めます。制約を削除して通してはいけません。
ハマり #3 · 待機中の接続が解放されない

片方の psql が長時間待ち続ける場合があります。

確認順序

  1. 先に開いた psql の prompt が transaction 中か確認する。
  2. pg_stat_activity で待機中の接続を確認する。
  3. 先行 transaction を COMMIT または ROLLBACK で明示的に終了する。
  • 原因: 先行 transaction が行ロックを保持したままです。
  • 対処: 接続を無理に増やさず、先行 transaction の処理結果を確認して明示的に終了します。

AI に相談

AIPostgreSQL の transaction と行ロックの詰まりを質問できます
この章の制約・transaction・行ロック・確定状態を前提に答えます

修了確認

問 1 · transaction の境界を説明する

残高の UPDATE は成功しましたが、event の INSERT が一意制約で拒否されました。両方を同じ transaction で実行していた場合、残高を中途半端に残さないために何を確認し、どう終了しますか。

問 2 · 行ロックの待ちを切り分ける

右側の SELECT ... FOR UPDATE が返りません。PostgreSQL 停止や通信断と決めつける前に、どの順序で確認しますか。

問 3 · 確定済みと未確定を区別する

UPDATE の結果が画面に表示されました。これだけで残高が確定したと言えるでしょうか。合格に必要な観測を答えてください。

問 4 · 2 種類の rollback を区別する

SQL の ROLLBACK と演習環境の VM rollback は、何を戻す操作ですか。

段階ヒント（問 1〜問 4）

問 1 のヒントと答え

ヒント 1 段 · 軽く方向だけ

一意制約違反が起きた瞬間、その処理全体が確定前か確定後かに注目してください。

ヒント 2 段 · 確認するコマンド

同じ接続で transaction が失敗状態のままかを確認し、ROLLBACK を実行します。その後 SELECT で既存の event ID と残高を確認します。

答えを見る

transaction が失敗状態になっていることを確認し、ROLLBACK で全体を取り消します。残高更新と event 登録を同じ transaction に置くことで、片方だけを確定させません。再実行前に既存の event ID と残高を SELECT で確認します。

問 2 のヒントと答え

ヒント 1 段 · 軽く方向だけ

PostgreSQL の停止や通信断を疑う前に、同じ行を今誰が保持しているかに注目してください。

ヒント 2 段 · 確認する順序

同じ接続で簡単な問い合わせができるか、service と socket の状態を確認します。次に pg_stat_activity で左側の transaction が開いたままかを確認し、左側を COMMIT または ROLLBACK します。

答えを見る

まず同じ接続で単純な問い合わせができるか、service と socket が生きているかを確認します。次に左側で同じ行をロックした transaction が開いたままかを確認します。左側を COMMIT または ROLLBACK し、右側が返るなら行ロック待ちです。

問 3 のヒントと答え

ヒント 1 段 · 軽く方向だけ

画面に表示された結果が、どの接続から見えているかに注目してください。

ヒント 2 段 · 比べる観測点

別の接続を開いて SELECT で残高を確認します。COMMIT の前後、または接続を切断した後で値がどう変わるかを比べます。

答えを見る

言えません。COMMIT 前の変更は未確定です。別接続から確定済みの残高を確認し、COMMIT 後にだけ新しい値が見えること、または切断後に未確定の変更が残らないことを観測します。

問 4 のヒントと答え

ヒント 1 段 · 軽く方向だけ

SQL の ROLLBACK と VM rollback が、それぞれどの範囲を戻すかに注目してください。

ヒント 2 段 · 比べる状態

\d で schema を確認します。SQL の ROLLBACK の前後、および VM rollback の前後で、package・設定・schema・確定済みデータの状態がどこまで戻るかを比べます。

答えを見る

SQL の ROLLBACK は現在の未確定 transaction を取り消します。VM rollback は package、設定、schema、確定済みデータを含む VM 全体を基準状態へ戻します。目的と影響範囲が異なるため、同じ操作として扱いません。

入口とデータ層をつなぐ

Chapter 1 で入口を作り、本章で残高を守るデータ層を作りました。しかし、まだ HTTP の要求は DB の transaction へ届きません。次章では教材アプリを間に置き、ひとつの event ID を入口・アプリ・DB の記録から追えるようにします。

Chapter 3: Web、アプリ、DB をつないで、タップを処理する

  • 教材アプリを導入して systemd service として動かす
  • アプリから PostgreSQL へ接続する
  • 同じ event ID を入口・アプリ・DB で照合する