PostgreSQL の特徴 Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 2

PostgreSQL とは 1996 年 ~ インターネットを利用したボランティア開発体制中心となる開発者は 30 人程度バージョンは 6.0 から http://www.postgresql.org/ ( 本家 ) http://www.postgresql.jp/ ( 日本 PostgreSQL ユーザ会 ) Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 3

PostgreSQL の生い立ち Ingres アメリカカルフォルニア大学 (UCB) で開発された最も初期の RDBMS CA-Ingress II Postgres UCB で研究目的で開発ユーザ増加バージョン 4.2 で打ち切り Illustra postgres95 Informix ソースコードの見直しと改良 PostgreSQL Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 4

バージョン番号規則 8. 2. 6 メジャーバージョンマイナーバージョンメジャーバージョンは仕様の追加変更がある移行には付属コマンドでのバックアップリストアが必要マイナーバージョンは主にバグ修正 2008 年 1 月現在の最新バージョンは 8.2.6, 8.1.11, 8.0.15, 7.4.19 Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 5

PostgreSQL の主な特徴豊富なプラットフォームに対応 Linux を含むほとんどの UNIX 系システム Windows Mac OS X 本格的な DBMS トランザクションオプティマイザマルチバイトサブクエリー外部キー手続き言語トリガートランザクションログテーブルスペース PITR テーブル継承 Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 7

PostgreSQL の主な特徴 Windows 版はインストーラが用意されている商用データベースと比べてコンパクトでも機能は勝らずとも劣らず性能もそこそこ ( 数千万件でも問題なし ) 無料で利用できる個人マシンなどに簡単にインストールできるのでリレーショナルデータベースの学習にも最適! Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 8

クライアント / サーバ構成ネットワーク対応フロントエンド ( クライアント ) バックエンド ( サーバ ) クライアント / サーバの OS の違いを吸収軽量クライアントデータベースエンジンの変更に影響されにくいクライアント A クライアント B クライアント C PostgreSQL Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 9

マルチバイト対応文字エンコーディングデータベースごとに指定クライアントごとに通信するエンコーディングを指定可能自動的に変換することで EUC_JP のデータベースに SJIS クライアントで通信したり UNICODE のデータベースに EUC_JP で通信することができる日本語では以下の組み合わせが可能バックエンド ( データベース ) フロントエンド ( クライアント ) EUC_JP UNICODE (UTF-8) EUC_JP SJIS UNICODE EUC_JP SJIS UNICODE Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 11

PostgreSQL のソースコード本家もしくは JPUG ホームページからダウンロードサイズ目安 tar.bz2 アーカイブ 12MB アーカイブを展開して 130MB コンパイル前プログラム本体オンラインマニュアルライブラリ + ヘッダファイル 13MB 9MB 9MB コンパイル後 Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 14

コンパイルとインストール./configure make make install 最近の Linux ディストリビューションであれば特別な手順はなくインストールできるソースコードからインストールする場合は RPM パッケージと競合しないように注意./configure 時にインストール先などを指定できる./configure --help でヘルプ表示 Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 15

インストールされるコマンド createdb データベース作成 pg_config インストール情報 dropdb データベース抹消 pg_ctl PostgreSQL の起動 createuser ユーザ登録 pg_dump データベースのバックアップ dropuser createlang ユーザ抹消プログラミング言語追加 pg_dumpall pg_restore データベース全体のバックアップデータベース復旧 droplang プログラミング言語削除 psql SQL インタプリタ initdb データベースクラスタ初期化 vacuumdb データベースのガベージコレクション Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 19

データベースクラスタ PostgreSQL のデータが全て格納されるディレクトリ initdb コマンドで最初の 1 回だけ実行データベースクラスタデータベース A システムデータベースがデフォルトで作成される環境変数 $PGDATA で指定一般的に /usr/local/pgsql/data template0 template1 postgres テーブルテーブルテーブルデータベース B テーブル Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 20

起動停止 pg_ctl コマンド起動 pg_ctl -D $PGDATA -w start 再起動 pg_ctl -D $PGDATA restart 停止 pg_ctl -D $PGDATA -m f stop root では実行できない -w wait の略処理が完了したらプロンプトを戻す停止の時はデフォルトで有効になっている -m { s f i } mode の略 smart 全てのクライアントが切断するまで待つ ( デフォルト ) fast クライアントが切断するまで待たない ) immediate クリーンアップ処理なしで全サーバプロセスを中断 Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 21

設定ファイル主なパラメータ listen_addresses max_connections silent_mode port shared_buffers max_files_per_process log_destination log_connections redirect_stderr client_encoding 接続を受け付ける IP アドレスを記述 * なら全ての IP インターフェイスで受付空なら UNIX ドメイン接続のみデータベースサーバへの同時接続の最大数 true の場合サーバはバックグラウンドで起動し制御端末は切り離される接続ポート番号デフォルトは 5432 共有メモリバッファをページ数で指定デフォルトは 1000 プロセスあたりのファイルオープン最大数ログの出力先を指定 stderr syslog eventlog クライアントが接続したことをログに取るようにする stderr に出力したエラーを $PGDATA/pg_log 以下のローテーションするログファイルにリダイレクトするクライアント側文字エンコーディングのデフォルトを指定 Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 23

PostgreSQL の設定 PostgreSQL の動作リソースに関しては全て postgresql.conf で設定するインストール直後 ( デフォルトの設定 ) の postgresql.conf は非力なマシンでも動作するように設定されている PostgreSQL の性能を引き出すためには個々のマシンに沿った設定が必要! Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 24

createuser コマンドユーザ作成 PostgreSQL 8.1 からはロールでユーザとグループを管理する SQL の CREATE ROLE 文も利用可能 [postgres]$ createuser taro Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) y Shall the new role be allowed to create more new roles? (y/n) n CREATE ROLE Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 26

データベース作成 createdb コマンド [postgres]$ createdb データベース名 CREATE DATABASE データベースクラスタ ($PGDATA) には複数のデータベースを作成できるデータベースごとに所有者を決められる SQL の CREATE DATABASE 文も利用可能 Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 27

アンインストールデータベース / ユーザ削除アンインストールはデータベースクラスタとインストールディレクトリを削除するだけ [postgres]$ rm -rf $PGDATA [postgres]$ rm -rf /usr/local/pgsql データベース削除 dropdb コマンド DROP DATABASE 文ユーザ削除 dropuser コマンド DROP ROLE 文 Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 28

SQL の実行 psql コマンド psql [ オプション ].. [DB 名 [ ユーザ名 ]] 対話型 SQL インタプリタ ( クライアントアプリ ) ユーザが入力した SQL をデータベースサーバ ( バックエンド ) に接続して送信する SQL の処理結果を受け取りユーザに表示する Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 29

SQL 標準 SQL に準拠しているので通常の SQL は問題なく使える SELECT, UPDATE, INSERT, DELETE CREATE TABLE, JOIN, OUTER JOIN インデックスビュー外部キースキーマサブクエリートリガシーケンスカーソル... 一部方言独自拡張がある Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 31

ログ独自のログ機能で取得ローテーションなどを行える postgresql.conf で設定ログ出力先ログ出力内容 syslog にも出力可能時刻 PID SQL 文ユーザ名データベース名などローテーションサイズ / 時間 redirect_stderr = on ログが $PGDATA/pg_log/ 以下に出力される Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 33

バックアップ / リストア pg_dump コマンドデータベース単位でバックアップを取得 pg_dumpall コマンドデータベースクラスタ全体のバックアップを取得デフォルトは SQL 文で論理バックアップを作成するデータベースを停止する必要がないリストアは psql を使い SQL 文から復元するポイントインタイムリカバリ (PITR) 機能もある Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 34

VACUUM データベースのガベージコレクション PostgreSQL は追記型アーキテクチャ UPDATE tbl SET val = 'BBB' WHERE val = 'AAA'; val='aaa' val='aaa' 不要領域 val='aaa' 再利用可能 UPDATE VACUUM 追記 val='bbb' val='bbb' Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 38

vacuumdb コマンド VACUUM の種類 VACUUM 運用を妨げずに並行して実行可能データベースのサイズは基本的に変わらない再利用されるだけでファイルサイズが小さくなるわけではない VACUUM FULL (vacuumdb -f) 実行中はテーブルを完全にロックしてしまうファイルの再利用可能領域を詰めてサイズを小さくする Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 39

セキュリティホストによる認証 pg_hba.conf 設定ファイルパスワード認証 ident 認証 pam 認証などを IP アドレスユーザ名データベース名ごとに設定できるユーザの権限テーブルビューシーケンスといったオブジェクトに検索更新削除などの権限を設定できる GRANT / REVOKE 文 Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 41

その他の機能テーブルスペースデータベースクラスタとは別のディスクにテーブルを構築できる PL/pgSQL SQL 手続き言語でユーザが関数を定義できる二相コミット分散されたトランザクション間での同期をとることができるテーブル継承親テーブルを継承する子テーブルを複数作成しデータをクラスタ化できる Copyright 2008 SRA OSS, Inc. Japan All rights reserved. 42