PostgreSQL徹底入門 第4版 インストールから機能・仕組み、アプリ作り、管理・運用まで
PostgreSQL徹底入門 第4版 インストールから機能・仕組み、アプリ作り、管理・運用まで
翔泳社
著者：近藤雄太、正野裕大、坂井潔、鳥越淳、笠原辰仁
監修：石井達夫
はじめに
改訂にあたって
謝辞
目次
Chapter 1 PostgreSQLについて知ろう
1.1 データベースとは
1.1.1 データベースの役割
1.1.2 データモデル
1.2 PostgreSQLの歴史
1.2.1 POSTGRES
1.2.2 Postgres95
1.2.3 PostgreSQL
1.3 WindowsユーザーのためのPostgreSQL
1.4 PostgreSQL公式Webサイトの歩き方
Chapter 2 インストール（Windows編／Linux編）
2.1 インストールの準備
・ハードディスクの空き容量
・メモリの容量
2.2 Windowsへのインストール
2.2.1 動作環境
2.2.2 one click installerのダウンロード
2.2.3 PostgreSQLのインストール
・1.インストーラの起動
・2.インストールディレクトリの選択
・3.インストールするコンポーネントの選択
・4.データディレクトリの選択
・5.パスワードの指定
・6.ポート番号の指定
・7.ロケールの指定
・8.インストール情報の確認
・9.インストールの開始
・10.インストールの完了
2.2.4 スタックビルダによるソフトウェアのインストール
・1.スタックビルダの起動
・2.PostgreSQLの選択
・3.ソフトウェアの選択
・4.ダウンロードの開始
・5.インストールの開始
・6.インストールの完了
2.2.5 パスの設定
2.2.6 アンインストール
2.3 LinuxへのYumによるインストール
2.3.1 Yumリポジトリの設定
2.3.2 PostgreSQLのインストール
2.3.3 環境設定
2.3.4 アンインストール
2.4 PostgreSQLの起動
2.4.1 データベースクラスタの作成
2.4.2 サーバの設定
・$PGDATA/postgresql.conf
・$PGDATA/pg_hba.conf
2.4.3 サーバの起動／停止
2.5 サーバーの自動起動
2.5.1 Windowsでの自動起動
・サービスへの登録
・サービスへの登録解除
・サービスの起動／停止
2.6 PostgreSQL操作入門
2.6.1 ユーザーの作成
2.6.2 データベースの作成
2.6.3 データベースへのログイン
Chapter 3 WindowsでPostgreSQLを使う～かんたんpgAdminマニュアル
3.1 pgAdmin 4
3.2 サーバーの追加
3.3 データベースの構築
3.4 データの編集
3.4.1 テーブルの作成
・テーブル名の入力
・列の作成
・制約の追加
3.4.2 データの編集
3.4.3 制約違反を試す
3.4.4 データの削除
3.4,5 データの検索
3.5 管理コマンドの実行
3.5.1 メンテナンス
3.5.2 テーブルスペースの管理
・permission deniedエラーで失敗する場合
3.5.3 ログインロールとグループロールの管理
・ログインロールの作成
・グループロールの作成
3.5.4 バックアップ／リストア
・バックアップ
・リストア
Chapter 4 SQL入門
4.1 RDBMSを操作するための言語─SQL
4.2 psql
4.2.1 psqlの起動
4.2.2 データベースへの接続
4.2.3 psqlのバックスラッシュコマンド
・SQL文の終わり
・大文字と小文字の区別
・エラーの表示
4.3 DDL（CREATE/DROP）
4.3.1 スキーマの作成（CREATE SCHEMA）
4.3.2 スキーマの削除（DROP SCHEMA）
4.3.3 テーブルの作成（CREATE TABLE）
4.3.4 テーブルの削除（DROP TABLE）
4.3.5 シーケンスの作成（CREATE SEQUENCE）
4.3.6 シーケンスの削除（DROP SEQUENCE）
4.4 DML（INSERT/SELECT/UPDATE/DELETE）
4.4.1 挿入（INSERT）
4.4.2 検索（SELECT）
4.4.3 更新（UPDATE）
4.4.4 削除（DELETE）
4.5 DML（SELECTのオプション）
4.5.1 SELECT…AS
4.5.2 LIMIT…OFFSET
4.5.3 GROUP BY/HAVING
4.5.4 JOIN
4.5.5 副問い合わせ（サブクエリ）
4.6 DDL（インデックスと制約）
4.6.1 インデックとは
・インデックスの効果
・インデックス使用時の注意点
4.6.2 インデックスの作成（CREATE INDEX）
4.6.3 インデックスの削除（DROP INDEX）
4.6.4 制約とは
4.6.5 NOT NULL制約
4.6.6 ユニーク（UNIQUE）制約
4.6.7 主キー（PRIMARY KEY）制約
4.6.8 チェック（CHECK）制約
4.6.9 デフォルト（DEFAULT）制約
4.6.10 外部キー（FOREIGN KEY）制約
4.7 トランザクション
4.7.1 トランザクションの開始（BEGIN）
4.7.2 トランザクションのコミット（COMMIT）
4.7.3 トランザクションのロールバック（ROLLBACK/ABORT）
4.7.4 セーブポイント（SAVEPOINT）
4.7.5 ロック
・SELECT…FOR UPDATE
・LOCK TABLE
4.8 パラレルクエリ
4.8.1 パラレルクエリが実行される条件と設定
・クエリの処理条件
・クエリ対象のテーブルやインデックスのサイズ
・パラレルクエリを使うための設定
4.8.2 パラレルクエリの効果
4.9 その他のSQLコマンド
4.9.1 COPY
4.9.2 TRUNCATE
Chapter 5 PHPでPostgreSQLを使う～PHPアプリケーションの作成（1）
5.1 開発環境のセットアップ
5.1.1 PostgreSQLとPHPのインストール
5.1.2 php.iniの設定
5.1.3 環境変数の設定
5.1.4 phpinfoファイルの作成
5.1.5 ビルトインウェブサーバの起動
5.2 データベースプログラムを書いてみよう
5.2.1 データベースとテーブルの作成
5.2.2 データベースへの接続
5.2.3 テーブルの内容の表示
5.2.4 テーブルへのデータ挿入
5.2.5 テーブルのデータの変更／削除
5.2.6 データベースプログラムの要点
5.2.7 まとめ
Chapter 6 PHPでPostgreSQLを使う～PHPアプリケーションの作成（2）
6.1 SNSアプリケーションを作ってみよう
6.1.1 「かんたんSNS」の機能
6.1.2 「かんたんSNS」の大まかな流れ
6.1.3 テーブルの設計
・CHECK制約に正規表現を使う
・lower関数を用いて大文字小文字を無視したUNIQUE制約
・REFERENCE・・・ON DELETE CASCADE、ON DELETE SET NULL
・comments.parent_comment_id
6.4.1 ドキュメントルートの変更とWebサーバーの起動
6.2 ユーザー登録とログイン処理
6.2.1 共通関数・クラス
・functions.php
・Csrf.php
・common.php
6.2.2 ユーザ登録
・入力項目チェック
・エラー処理
・POST時の処理
6.2.3 ログイン処理
6.3 タイムラインの表示
・COUNT関数
・JOIN句
・IN演算子
・OFFSETとLIMIT
・ORDER BY句
・「次へ」「前へ」のpageの算出
6.4 投稿の書き込み処理／削除処理
・チェック処理（4～17行目）
・投稿処理（20～29行目）
・共通CSSファイルの作成
・header.htmlのインクルード
6.5 ログアウト処理
6.6 フォロー／アンフォロー機能
6.7 コメント機能
6.7.1 投稿の削除
6.7.2 コメントの登録
・WITH問い合わせ
・再帰クエリ
6.8 退会処理
・トランザクション処理
6.9 まとめ
Chapter 7 PostgreSQLの仕組みを理解する
7.1 PostgreSQLのプロセス
7.1.1 フロントエンドとバックエンド
7.1.2 バックエンドプロセス
・postmaster－最初に起動するプロセス
・postgres－接続ごとに起動するプロセス
・logger－ログをファイルに保存するプロセス
・chekpointer－チェックポイントを実現するプロセス
・background writer－共有メモリバッファ上のデータをディスクに書き込むプロセス
・walwriter－データの変更内容をWALファイルに書きこむプロセス
・autovacuum launcher/autovacuum woker－定期的なメンテナンスを実行するプロセス
・stats collector－稼働統計情報を収集するプロセス
・logical replication launcher－ロジカルレプリケーションのワーカを起動するプロセス
・walsender/walreceiver－マスター／スタンバイサーバー間でWALの送受信を行うプロセス
・parallel worker－パラレルクエリを処理するプロセス
7.1.3 フロントエンドプロセス
・psql
・COPY文と\copyコマンド
7.1.4 フロントエンドとバックエンドのやり取り
・フロントエンドプロトコル／バックエンドプロトコル
・libpqの機能
7.1.5 メモリ構造
・共有メモリバッファ
・WALバッファ
・ワークメモリ
・メンテナンスワークメモリ
7.2 PostgreSQLのデータベースファイル
7.2.1 データベースファイルの格納場所
7.2.2 データの一覧
・baseディレクトリ
・globalディレクトリ
・pg_tblspcディレクトリ
・pg_walディレクトリ
Chapter 8 PostgreSQLをきちんと使う
8.1 日本語の扱い
8.1.1 ロケール
8.1.2 文字コード
・データベースエンコーディングの指定方法
・クライアントエンコーディングの指定方法
8.1.3 EUC_JPとSJIS
・PHPの場合
・Javaの場合
・格納されている文字コードを確認する
8.2 チェックサム
8.3 PostgreSQLの起動と停止
8.3.1 PostgreSQLの起動
8.3.2 PostgreSQLの停止
8.4 設定──postgresql.conf
8.4.1 設定変更の方法
・postgresql.confの編集
・ALTER SYSTEMコマンド
・SETコマンド
8.4.2 設定反映のタイミング
Chapter 9 PostgreSQLをセキュアに使う
9.1 ネットワークからのアクセス制御
9.1.1 listen_addresses－接続の制御
9.1.2 pg_hba.conf－クライアント認証
・trust－認証を行わずに接続を許可する
・reject－認証を行わずに接続を拒否する
・md5－パスワード認証を行う
・scram-sha-256－堅牢なパスワード認証を行う
9.2 ユーザーによるアクセス制御
9.3 データベースオブジェクトへのアクセス制御
9.3.1 所有者とスーパーユーザー
9.3.2 権限の付与／剥奪
・オブジェクトへの権限の付与／剥奪
・スキーマへの権限の付与／剥奪
9.3.3 サーチパスの保護
9.3.4 権限の確認
9.3.5 UPDATE/DELETE権限とSELECT権限
9.3.6 列の参照の許可
9.3.7 行の参照の許可
9.3.8 特定の操作の許可
9.4 通信の暗号化
Chapter 10 PostgreSQLの動作状況を把握する
10.1 ログの監視
10.1.1 ログ出力先の設定
10.1.2 ログレベルとログ出力設定
・ログレベル
・ログレベルに応じたログ出力設定
10.1.3 ログ出力内容の設定
10.1.4 有用なログ関連の設定
・遅いSQL文（スロークエリ）をログに出力する
・特定のユーザーが実行したSQL文をログに出力する
・処理を実施したアプリケーション名を出力する
・チェックポイントの情報を出力する
・ロック獲得待ちの情報を出力する
・一時ファイルの情報を出力する
10.1.5 ログローテーションの設定
10.2 PostgreSQLから得られる情報
10.2.1 稼働統計情報
・稼働統計情報の注意点
10.2.2 重要な稼働統計情報
・pg_stat_database
・pg_stat_user_tables
・pg_stat_user_indexes
・pg_stat_activity
10.3 OSの情報
Chapter 11 PostgreSQLをメンテナンスする
11.1 VACUUM
11.1.1 追記型アーキテクチャ
11.1.2 VACUUMによる不要領域の回収
11.1.3 自動VACUUM
・VACUUM実行の判断
11.1.4 手動VACUUM
・自動VACUUMのログ
・pg_stat_all_tablesビュー
・pg_stat_progress_vacuumビュー
11.2 統計情報の解析
11.2.1 ANALYZEコマンド
11.3 インデックス
11.3.1 インデックスの断片化
11.3.2 REINDEXコマンド
11.4 クラスタ化
11.4.1 CLUSTERコマンド
11.5 テーブル／インデックスの肥大化対策
11.5.1 VACUUM FULLコマンド
11.6 実行計画
11.6.1 EXPLAINコマンド
・ANALYZEオプション
・BUFFERSオプション
11.6.2 実行計画の確認ポイントと対処
11.7 PostgreSQLのバージョンアップ
11.7.1 PostgreSQLのバージョニングルール
11.7.2 マイナーバージョンアップ
11.7.3 メジャーバージョンアップ
・論理バックアップを利用する方法
・pg_upgradeを利用する方法
Chapter 12 PostgreSQLのバックアップとリストア
12.1 論理バックアップと物理バックアップ
12.2 論理バックアップ
12.2.1 pg_dump
・pg_dumpの使い方
・バックアップファイルの形式
・バックアップ対象
・パラレルバックアップ
12.2.2 pg_restore
・特定のオブジェクトのみを取り出す方法
12.2.3 pg_dumpall
12.3 オフライン物理バックアップ
12.4 オンライン物理バックアップ
12.4.1 WAL
12.4.2 オンライン物理バックアップの設定
12.4.3 オンライン物理バックアップの手順
・pg_start_backup()の実行
・物理バックアップの取得
・pg_stop_backup()の実行
12.4.4 オンライン物理バックアップのリストア
・物理バックアップをdataディレクトリに戻す
・最新のWALログをdataディレクトリに戻す
・recovery.confファイルを設定する
・PostgreSQLを起動する
12.4.5 pg_basebackup
Chapter 13 レプリケーションを使う
13.1 レプリケーションとは
13.1.1 PostgreSQLのレプリケーション
・ストリーミングレプリケーションの特長
13.1.2 ストリーミングレプリケーションのアーキテクチャ
13.1.3 ロジカルレプリケーション
13.2 ストリーミングレプリケーション環境の構築
13.2.1 プライマリ1台、スタンバイ1台のレプリケーション構成
・プライマリの設定
・スタンバイの設定
13.2.2 プライマリ1台、スタンバイ2台のレプリケーション構成
・パラメータ
13.3 さまざまなレプリケーションの機能
13.3.1 同期／非同期レプリケーション
・同期と非同期の違い
・同期レプリケーション環境の構築
・スタンバイの応答タイミング
・スタンバイ故障の影響
13.3.2 マルチ同期レプリケーション
・クォーラムコミット
13.3.3 レプリケーションの遅延への対応
・アーカイブWAL
・wal_keep_segments
・レプリケーションスロット
13.3.4 カスケードレプリケーション
13.4 レプリケーションの運用
13.4.1 レプリケーション状況の確認
・レプリケーション実施状況
・同期レプリケーションの実施状況
・遅延状況
13.4.2 フェイルオーバーとフェイルバック
・フェイルオーバー
・フェイルバック（旧プライマリの再組み込み）
13.4.3 レプリケーションの衝突
・レプリケーション衝突の対策
索引
監修者・執筆者紹介
COLUMN
インストーラが自動生成したアカウントの管理について
サービスが残ってしまった場合について
データベースクラスタのバージョンアップ
クエリツール
大文字・小文字の区別と二重引用符
スキーマの有効利用
列名を指定しないINSERT
関数
UPDATEやDELETEコマンドの取り消し
インデックスの種類
文字エンコーディングに注意
SQLインジェクション
htmlspecialcharts関数とrawurlencode関数－HTMLにPHPの変数の中身を埋め込む
SQLの処理
ログの意味