PostgreSQLの導入と接続 | Ubuntu Server 22.04

01 / PostgreSQL 18を導入する

Ubuntu Server 22.04の同じマシン上で動くアプリ用に、PostgreSQL 18を用意します。物理・仮想マシン共通です。MariaDBとは独立した選択肢で、既存のWordPressやLaravelの接続先を自動的に変更する手順ではありません。アプリが対応するDBを選んでください。

操作場所はサーバーの一般管理者の端末です。sudo、復旧コンソール、更新・バックアップの方針を先に確認します。本手順はPostgreSQLが未導入のサーバーを対象とします。既存のクラスタや5432番の利用があれば、削除・初期化せず構成を調べてください。

dpkg -l 'postgresql*'
sudo ss -lntp 'sport = :5432'
ls /etc/postgresql 2>/dev/null

Ubuntu 22.04標準の14系は上流保守期限が2026年11月12日に迫っているため、ここではPostgreSQLプロジェクトのPGDGリポジトリから18系を指定して導入します。18系の上流保守期限は2030年11月14日です。OS提供元の更新条件とは区別してください。以下はamd64/arm64等、配布元が対応するアーキテクチャ向けです。追加配布元の署名検証を維持し、メジャー版を自動的に乗り換える無指定のpostgresqlパッケージは使いません。

sudo apt-get update
sudo apt-get install ca-certificates curl gnupg
sudo test ! -e /usr/share/keyrings/postgresql-pgdg.gpg
sudo test ! -e /etc/apt/sources.list.d/pgdg.list
WORKDIR="$(mktemp -d)"
curl --fail --show-error --location https://www.postgresql.org/media/keys/ACCC4CF8.asc -o "$WORKDIR/pgdg.asc"
gpg --show-keys --with-fingerprint "$WORKDIR/pgdg.asc"

上のtestが失敗した場合は既存設定を確認して中断します。鍵の指紋を公式Ubuntu向け案内のB97B 0AFC AA1A 47F0 44F2 44A0 7FCC 7D46 ACCC 4CF8と照合してから進みます。一致しない場合は登録しません。

gpg --batch --dearmor -o "$WORKDIR/pgdg.gpg" "$WORKDIR/pgdg.asc"
sudo install -m 644 "$WORKDIR/pgdg.gpg" /usr/share/keyrings/postgresql-pgdg.gpg
printf '%s\n' 'deb [signed-by=/usr/share/keyrings/postgresql-pgdg.gpg] https://apt.postgresql.org/pub/repos/apt jammy-pgdg main' | sudo tee /etc/apt/sources.list.d/pgdg.list >/dev/null
sudo apt-get update
apt-cache policy postgresql-18
sudo apt-get -s install postgresql-18 postgresql-client-18
sudo apt-get --no-remove install postgresql-18 postgresql-client-18
pg_lsclusters
sudo systemctl enable --now postgresql@18-main
systemctl is-active postgresql@18-main
systemctl is-enabled postgresql@18-main

シミュレーションで削除・置換・既存サービスへの影響を確認してから導入します。PGDG追加により共通ライブラリー等の更新候補も変わり得るため、運用中の依存パッケージがあれば事前検証してください。

18 mainが5432番でonline、サービスがactive・enabledであることを確認します。postgresql.serviceは集約用なので、その状態だけでDBの起動を判断しません。失敗時はsudo journalctl -u postgresql@18-main -b --no-pagerと/var/log/postgresql/を確認します。

02 / ローカル待受と認証を設定する

設定の場所と退避先を確認します。バックアップはWeb公開領域・Gitの外に保管します。以後は新規の18/mainクラスタの例です。

sudo -u postgres psql -X -c 'SHOW config_file' -c 'SHOW hba_file'
BACKUP="$(sudo mktemp -d /root/server-guide-pg.XXXXXX)" &&
sudo cp -a /etc/postgresql/18/main "$BACKUP/"
printf '保存先: %s\n' "$BACKUP"
sudo test ! -e /etc/postgresql/18/main/conf.d/server-guide.conf &&
sudo tee /etc/postgresql/18/main/conf.d/server-guide.conf >/dev/null <<'EOF'
listen_addresses = 'localhost'
password_encryption = 'scram-sha-256'
EOF

既存のpostgresql.confがconf.dを読み込むことを確認します。認証ファイル/etc/postgresql/18/main/pg_hba.confをsudoeditで開き、次のルールを既存ルールより前に追加します。管理用のpeer認証を残し、後で作るapp_roleはapp_dbにだけ接続できる順序にします。

local   all      postgres                          peer
local   app_db   app_role                          scram-sha-256
local   all      app_role                          reject
host    app_db   app_role   127.0.0.1/32            scram-sha-256
host    all      app_role   127.0.0.1/32            reject
host    app_db   app_role   ::1/128                 scram-sha-256
host    all      app_role   ::1/128                 reject

HBAは最初に一致したルールを使います。広い許可の後ろへ追加しても制限になりません。既存の他ユーザー用ルールは削除せず、trustや外部ネットワークの許可を追加しません。

sudo -u postgres psql -X -c "SELECT line_number,error FROM pg_hba_file_rules WHERE error IS NOT NULL;"
sudo systemctl restart postgresql@18-main
sudo -u postgres psql -X -c 'SHOW listen_addresses' -c 'SHOW password_encryption'
sudo ss -lntp 'sport = :5432'
pg_isready -h 127.0.0.1 -p 5432

構文エラーが0行、設定がlocalhostとscram-sha-256、待受が127.0.0.1と、IPv6が有効なら::1だけであることを確認します。UFWやクラウドFWに5432番の許可は追加しません。pg_isreadyは応答確認であり、アプリの認証成功は次の操作で確認します。

再起動や接続に失敗したら復旧コンソールでログを確認します。この手順で新規作成した設定だけを退避し、HBAを戻す例は次のとおりです。BACKUPを控えた保存先へ設定し直します。権限設定を戻す間はアプリの運用を止め、復旧後に待受と認証を再確認してください。データディレクトリーやクラスタは削除しません。

sudo cp -a "$BACKUP/main/pg_hba.conf" /etc/postgresql/18/main/pg_hba.conf
sudo mv /etc/postgresql/18/main/conf.d/server-guide.conf "$BACKUP/server-guide.conf.failed"
sudo systemctl restart postgresql@18-main

03 / アプリ専用のDBと権限を作る

例ではDB名をapp_db、ロール名をapp_roleとします。変更する場合はHBAと後続コマンドもそろえます。同名オブジェクトがあれば、用途を調べて中断してください。アプリに管理用のpostgresロールを渡しません。

sudo -u postgres env PSQL_HISTORY=/dev/null psql -X

psql内で次を実行します。\passwordの非表示プロンプトで、パスワード管理ソフト等が生成した固有の強いパスワードを2回入力し、安全に保存してください。SQL文字列やシェル引数へ秘密を書きません。

CREATE ROLE app_role LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION;
\password app_role
CREATE DATABASE app_db OWNER postgres;
REVOKE ALL ON DATABASE app_db FROM PUBLIC;
GRANT CONNECT ON DATABASE app_db TO app_role;
\connect app_db
GRANT USAGE, CREATE ON SCHEMA public TO app_role;
\quit

DBの所有者は管理用ロールのままにし、アプリはそのDB内でテーブル等を作成・操作します。これはアプリ自身がマイグレーションする小規模構成です。運用用ロールからDDL権限を分離する設計や他DBの既存権限の変更は扱いません。

04 / 接続・操作・拒否を確認する

一般管理者のシェルからTCPで接続し、-Wのプロンプトでパスワードを入力します。PGPASSWORDや接続URLにパスワードを埋め込む必要はありません。

psql -X -h 127.0.0.1 -p 5432 -U app_role -d app_db -W

psql内で自分のDBとロールを確認し、検証用の新しいテーブルを作ってCRUDを確認します。同名テーブルがあれば作り直さず別名を選びます。

SELECT current_database(), current_user;
CREATE TABLE guide_check (id integer PRIMARY KEY, note text NOT NULL);
INSERT INTO guide_check VALUES (1, 'ready');
UPDATE guide_check SET note = 'verified' WHERE id = 1;
SELECT * FROM guide_check;
INSERT INTO guide_check VALUES (2, 'remove');
DELETE FROM guide_check WHERE id = 2;
SELECT * FROM pg_authid;
CREATE DATABASE guide_denied;
\quit

app_db・app_roleとテーブル操作の成功を確認します。pg_authidの参照と別DBの作成は権限不足で拒否されるのが正常です。管理用ロールへ変更して拒否試験を通さないでください。

psql -X -h 127.0.0.1 -p 5432 -U app_role -d postgres -W
psql -X -h 127.0.0.1 -p 5432 -U app_role -d app_db -W

最初のコマンドはHBAで拒否されます。次は一度だけ誤ったパスワードを入力し、認証で拒否されることを確認します。その後、正しいパスワードでapp_dbへ接続できることも再確認します。接続できない原因は、待受・経路・HBA・パスワードを順に調べ、外部公開やtrustへ切り替えません。

05 / バックアップと復元を確認する

DBのダンプは秘密データを含み得ます。rootだけがアクセスできる保存先を作り、Web・Gitから分離します。BACKUPはこの端末で控えた実際の退避先へ置き換えてください。

パイプの両側のエラーを見落とさないよう、Bashでset -o pipefailを有効にして実行し、終了コード0とダンプの読み取りを確認します。復元先は実在しない専用名を選びます。次はapp_restore_checkが未使用であることを確認した場合の例です。各コマンドが成功したことを確認してから次へ進み、createdbが失敗した場合は復元も削除も実行しません。

set -o pipefail
sudo -u postgres pg_dump -Fc app_db | sudo sh -c 'umask 077; cat > "$1/app_db.dump"' sh "$BACKUP"
sudo pg_restore --list "$BACKUP/app_db.dump" >/dev/null
sudo -u postgres createdb app_restore_check
sudo cat "$BACKUP/app_db.dump" | sudo -u postgres pg_restore --exit-on-error --no-owner --no-privileges -d app_restore_check
sudo -u postgres psql -X -d app_restore_check -c 'TABLE guide_check;'
sudo -u postgres dropdb app_restore_check

1 / verifiedが復元できたことを確認してから、この操作で新規作成した検証用DBだけを削除します。本番DBは上書きしません。アプリDBの不要になった検証テーブルもapp_roleで接続してDROP TABLE guide_check;により削除します。ロール、HBA、アプリのファイル、暗号化鍵、オフサイト保管と全損時の復旧はDBダンプだけでは復元できないため、別途備えます。

Ubuntu Server 22.04.5の実VMでPostgreSQL 18.6を導入し、18/mainの起動・自動起動、localhost待受、SCRAM認証、app_roleのテーブル作成・読取・更新・削除を確認しました。別DBの作成とpg_authidの参照は終了コード1、別DBへの接続・誤ったパスワード・パスワード未指定は終了コード2で拒否されました。非公開のカスタム形式ダンプから使い捨てのDBへ復元し、1 / verifiedを読み取ってから検証用DBを削除しました。外部ネットワークからの接続、IPv6クライアント認証、全損復旧・本番移行は未検証です。

PostgreSQLのバージョン・ローカル接続とテーブル操作を確認する実VM画面 — Ubuntu Server 22.04.5の検証用VMで撮影した実画面。

上流の保守期間、HBA認証、SQLダンプと復元を確認し、18系のマイナー更新を継続します。追加したPGDGがUbuntu標準の自動更新対象に自動で含まれるとは限りません。更新元・保留パッケージを確認し、バックアップと停止計画を用意して適用します。メジャー更新はバックアップと移行検証を伴う別作業です。