Postgres 勉強メモ

  • 構造1-ファイル群-
<PGDATA>
-PG_VERSION …PostgreSQLのバージョンを示したファイル
-base …各データベースのデータ本体用のディレクトリ
-global …データベースクラスタ共通用のディレクトリ
-pg_clog …コミットログ格納用のディレクトリ
-pg_hba.conf …HostBase認証用の設定ファイル
-pg_ident.conf …ident認証用の設定ファイル
-pg_log …サーバログファイル格納用ディレクトリ
-pg_multixact …マルチトランザクション管理用ディレクトリ
-pg_notify …Listen/Notifyコマンド用ディレクトリ
-pg_serial …シリアル型の管理用ディレクトリ
-pg_stat_tmp …統計情報の一時ファイル用ディレクトリ
-pg_subtrans …サブトランザクション用ディレクトリ
-pg_tblspc …テーブルスペースに関するデータ
-pg_twophase …2相コミット利用時のデータ
-pg_xlog …トランザクションログ
-postgresql.conf …PostgreSQLの設定ファイル
-postmaster.pid …現在のpostmasterのプロセスID等を記述したファイル
-postmaster.opts …PostgreSQLの起動オプションを示したファイル

構造2 -メモリ群 -

- shared_buffers(共有メモリ)
   -> データの読込み、書込みに使用されるメモリ領域。
- wal_buffers(WALバッファ)
   -> WALログの書込みに使用されるメモリ領域。
- work_memory(作業領域)
   -> ソート処理、ハッシュ作成処理、結合に使われるメモリ領域
で、実行プロセス毎に確保される。
- maintenance_work_memory(メンテナンス領域)
   -> VACUUMやインデックス作成に使われるメモリ領域で、実
行プロセス毎に確保される。

構造3 -プロセス-

- postmaster(リスナプロセス)
    クライアントからの接続を受付け、認証処理を行う。
    起動時にサーバプロセス以外の各プロセスを生成する(fork)。
    認証されたクライアントに対して、postgresプロセスを生成し(fork)、
処理を引き渡す。
- postgres(サーバプロセス)
    個々のクライアントの要求を処理するプロセス。
- writer(バックグラウンドライタ)
    共有バッファのデータをテーブルファイル、インデックスファイルに書
込む。
- wal_writer(WALライタ)
     WALバッファのデータをトランザクションログファイルに書込む。
- archiver(WALアーカイバ)
     WALログファイルをPITRのためにアーカイバに移動する。
- checkpointer(チェックポインタ)
     全てのダーティページをデータファイルに書出す。

各種設定方法

  • postgresql.conf -> Postgresqlのパラメータ設定
  • pg_hba.conf -> クライアント認証関連の設定

接続と認証

  • listen_address -> クライアントからの接続を監視するIPアドレスを指定
  • port -> サーバーが監視するTCPポート

クライアント接続デフォルト

パフォーマンスチューニング関連の設定

  • shared_buffers -> 共有メモリの使用するメモリ量
  • work_mem -> 内部そーと、ハッシュテーブル操作で使用するメモリ量 *maintenance_work_mem -> VACUUM等の保守操作で使用するメモリ量
  • bgwriter_delay -> バックグラウンドライタの動作周期

運用管理

運用管理にはデータベースの追加・削除・変更、 データベースの各種情報取得、テーブル単位の権 限の付与・取消など色々あるが、PostgreSQL独特であるVACUUM(+ANALYZE)について注視する。 ####なぜvacuumが必要か? 　　-> PostgreSQLのデータ管理が追記型だか ら、不要領域を回収する必要があるため。

f:id:snsn19910803:20161125151817p:plain

f:id:snsn19910803:20161125151823p:plain

ファイル追記型にはメリットもあるが、何もしな いとファイルサイズが肥大化し続けてしまうため、 不要領域のメンテナンスが必要 ⇒運用管理としてVACUUMが必要

f:id:snsn19910803:20161125151924p:plain

f:id:snsn19910803:20161125151955p:plain

pg_dumpによるバックアップ

###バックアップコマンド
 *pg_dumpコマンド
      - データベースを指定してバックアップする。
 *pg_dumpall
      -  全てのデータベース、データベースクラスタ全体をバック
アップする。

###リストアコマンド
*テキスト形式の場合
      - psqlコマンド
*バイナリ形式の場合
      - pg_restoreコマンド

他にも osコマンドとPITRによるバックアップが可能なので今度勉強する

実行計画の取得

* SQL文を実際に実行せずに、推測で取得する方法
     - SQL文の前に、「EXPLAIN」を付与

* SQL文を実際に実行して、実行結果の情報も併せて取得する方法
    - SQL文の前に、「EXPLAIN ANALYSE」を付与
    - INSERT、UPDATE、DELETE等を実行すると実データに影響を与えるため注意

f:id:snsn19910803:20161125152623p:plainf:id:snsn19910803:20161125152627p:plain