目次

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サイトの歩き方

 

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.5.2　Linuxでの自動起動

■　サービスの登録

2.6　PostgreSQL操作入門

2.6.1　ユーザーの作成

2.6.2　データベースの作成

2.6.3　データベースへのログイン

 

3　WindowsでPostgreSQLを使う　～かんたんpgAdminマニュアル

3.1　pgAdmin4

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　バックアップ／リストア

■　バックアップ

■　リストア

 

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

 

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　まとめ

 

6　PHPでPostgreSQLを使う　～PHPアプリケーションの作成（2）

6.1　SNSアプリケーションを作ってみよう

6.1.1　「かんたんSNS」の機能

6.1.2　「かんたんSNS」の大まかな流れ

6.1.3　テーブルの設計

■　CHECK制約に正規表現を使う

■　lower関数を用いて大文字小文字を無視したUNIQUE制約

■　REFERENCES…ON DELETE CASCADE、ON DELETE SET NULL

■　comments.parent_comment_id

6.1.4　ドキュメントルートの変更と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　まとめ

 

7　PostgreSQLの仕組みを理解する

7.1　PostgreSQLのプロセス

7.1.1　フロントエンドとバックエンド

7.1.2　バックエンドプロセス

■　postmaster ― 最初に起動するプロセス

■　postgres ― 接続ごとに起動するプロセス

■　logger ― ログをファイルに保存するプロセス

■　checkpointer ― チェックポイントを実現するプロセス

■　background writer ― 共有メモリバッファ上のデータをディスクに書き込むプロセス

■　walwriter ― データの変更内容をWALファイルに書き込むプロセス

■　autovacuum launcher/autovacuum worker ― 定期的なメンテナンスを実行するプロセス

■　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ディレクトリ

 

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　設定反映のタイミング

 

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　通信の暗号化

 

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.2.3　オブジェクトサイズの確認

10.3　OSの情報

 

11　PostgreSQLをメンテナンスする

11.1　VACUUM

11.1.1　追記型アーキテクチャ

11.1.2　VACUUMによる不要領域の回収

11.1.3　自動VACUUM

■　VACUUM実行の判断

11.1.4　手動VACUUM

11.1.5　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を利用する方法

 

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

 

13　レプリケーションを使う

13.1　レプリケーションとは

13.1.1　PostgreSQLのレプリケーション

■　ストリーミングレプリケーションの特徴

13.1.2　ストリーミングレプリケーションのアーキテクチャ

13.1.3　ロジカルレプリケーション

13.2　ストリーミングレプリケーション環境の構築

13.2.1　プライマリ１台、スタンバイ１台のレプリケーション構成

■　プライマリの設定

■　スタンバイの設定

13.2.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　レプリケーションの衝突

■　レプリケーション衝突の対策