Varnish 2.1のサブルーチンの流れ

こちらの続き

blog.kasei-san.com

VarnishのSaaSであるfastlyでは、VCLがVarnish2.1準拠 そのため、Varnish2.1での処理の流れを改めて把握して、図に書いた

Varnish 2.1のサブルーチンの流れ

エラーやrestatなど、正常処理以外の流れは端折っているので注意

ポイント

• 6.0 よりだいぶシンプル
• return する値も結構異なっている
• vcl_hash は、req.hash+= で hash のキーを定義する

参考

👇 v2.1 のサブルーチンの解説

varnish-cache.org

VarnishとVCLおぼえがき

Varnish とは

• 正式名 Varnish HTTP Cache
• cache機能を持つリバースプロキシ
• 基本的にはcacheサーバとして使われる
• VCL - Varnish Configuration Language という独自言語で設定を行う
• 最新バージョンは6.0.2 (2018/11/12 Release)

VCLについて

• 手続き型言語
• これだけでIP制限やbasic認証をかけられたりと意外に器用
• 特定の処理ごとに、VCLに定義されたサブルーチンが呼びだされる(後述)

Varnishのcacheについて

• cacheは hash と呼ばれる領域に格納される
• すべてオンメモリなので高速(らしい
• デフォルトでは、hashのキーはリクエストのURLとドメイン/IPアドレス
  • それとオリジンサーバの Varyヘッダ (後述) の組み合わせをキーにしてcacheが行われる
• 後述する vcl_recv でcacheの可否。vcl_hash でhashのキーの定義ができる

Varyヘッダについて

• キャッシュサーバに対して、どのようにcacheを分けるかを指示するための情報
• オリジンサーバからのレスポンスヘッダに格納されている
• 例えば Vary: Accept-Encoding ならば、Varnish側のhashキーが同一でも Accept-Encoding が異なるRequestは別の情報としてcacheされる

VCLでよく使う関数

• include: 別のvclファイルをincludeする
• set: 変数に値を格納する
• unset: 変数の値を削除する
• call: サブルーチンの呼び出し

VCLでよく使われる変数

サブルーチンによって、使えたり使えなかったりするので注意

• client : クライアントの情報
• server : サーバの情報
• req : クライアントからのリクエスト
• bereq : サーバに実際送るリクエスト
• beresp: サーバから帰ってきたレスポンスヘッダ
• resp: クライアントに返すレスポンスヘッダ

Varnishの処理の流れ

• Requestを受け取った時や、hashのキーを決定するときなど、それぞれのタイミングで サブルーチン が呼ばれる
• その サブルーチン を定義することで、独自の設定をVarnishに加えることができる
• サブルーチンの流れは、こちらの公式ドキュメントを参照

ざっくりした流れ

vcl_recv

• Requestを受けると最初に呼ばれるサブルーチン
  • 主に、hash , pass , pipe のいずれかを return の引数に入れる
  • hash : cacheする場合に選択。次はhashのキーを確定する vcl_cache が呼びだされる
  • pass : cacheしない場合に選択。次はpassした場合に呼び出される、 vcl_pass が呼び出される
  • pipe: 処理をパイプ(キャッシュサーバは何もせず、クライアントとサーバの通信をパイプするだけ)する場合に選択。次はpipeした場合に呼び出される、 vcl_pipe が呼び出される

vcl_pipe

• vcl_recv が return(pipe) したら来る
• あんまりいじることはない。長い処理の場合、タイムアウト値を変更したりする

vcl_pass

• vcl_recv が return(pass) したら来る
• passの場合だけリクエストヘッダをいじったりしたいときにはここに追記する
• return(fetch) すれば、オリジンサーバに問い合わせをする直前のサブルーチン vcl_backend_fetch が呼び出される

vcl_hash

• vcl_recv が return(hash) したら来る
• hashのキーを定義する
• Varnishでは、cacheされたデータは hash に格納される
• vcl_hash では、そのhashを取り出すためのキーを定義する
• 関数 hash_data に値を追加していって、その値の組み合わせがキーになる
• 最後に return (lookup) する
• この後は、キーにhitするcacheがあれ vcl_hit 。無ければ、vcl_miss が呼び出される

参考

この場合、リクエストURLと、ホスト(もしくはipアドレス)の組み合わせがhashのキーとなっている

sub vcl_hash {
    hash_data(req.url);
    if (req.http.host) {
        hash_data(req.http.host);
    } else {
        hash_data(server.ip);
    }
    return (lookup);
}

ここに、webサービスの特性に応じてhash_dataを設定していく

• スマホとPCでcacheを別にするとか
• ログインユーザ毎に異なるcacheを表示するとか

vcl_hit

• cacheがヒットした場合に呼び出される
• return(deliver) すれば、vcl_deliver (cacheをクライアントにわたす直前のサブルーチン) が呼び出される
• return(miss) すれば、vcl_miss (cacheがヒットしなかった場合のサブルーチン) が呼び出される

vcl_miss

• cacheがヒットしなかった場合に呼び出される
• return(fetch) すれば、オリジンサーバに問い合わせをする直前のサブルーチン vcl_backend_fetch が呼び出される

vcl_backend_fetch

• オリジンサーバに問い合わせる直前のサブルーチン
• Requestを加工したりするのはここ
• return(fetch) すれば、オリジンサーバに問い合わせた後、vcl_backend_response が呼び出される

vcl_backend_response

• オリジンサーバに問い合わせた後のサブルーチン
• Responseを加工したりするのはここ
• return(deliver) すれば、vcl_deliver (cacheをクライアントにわたす直前のサブルーチン) が呼び出される

vcl_deliver

• クライアントに配信する直前のサブルーチン
• vcl_hit か vcl_backend_response の後に呼び出される
• return(deliver) すると、クライアントにcacheが渡される

参考

👇 弊社blog記事。最初に読むと概要をつかみやすい

techblog.lclco.com

👇 v3.0.1 時代に作られたわかりやすい同人誌

blog.xcir.net

👇 公式ドキュメントのサブルーチンの流れの解説

varnish-cache.org

👇 公式ドキュメントのサブルーチンの解説

varnish-cache.org

PostgreSQL9.3で同期レプリケーションしてみる

これの続き

blog.kasei-san.com

やったこと

1. EC2環境を2台構築
2. マスタの環境構築
3. スタンバイの環境構築
4. マスタでレプリケーションステータスを確認
5. マスタが更新されるとスタンバイが更新されることを確認

EC2環境を2台構築

• web UI から Amazon linux の t2.small を作成
• セキュリティグループで、SSHと、PostgreSQLを許可

環境のIP

• マスタ: 172.31.59.13
• スタンバイ: 172.31.57.96

マスタの環境構築

概要

1. PostgreSQLのインストール
2. DB、テーブルを作成して、動作確認用のデータを挿入
3. 他のサーバからアクセスするため、postgres ユーザにパスワードを設定
4. 同期レプリケーションのための設定を postgresql.conf に設定
5. WALアーカイブディレクトリを作成
6. 他サーバから postgres ユーザでアクセスするために、pg_hba.conf を設定
7. PostgreSQLサーバ再起動

PostgreSQLのインストール

$ wget https://download.postgresql.org/pub/repos/yum/9.3/redhat/rhel-6-x86_64/pgdg-ami201503-93-9.3-3.noarch.rpm
$ sudo rpm -ivh pgdg-ami201503-93-9.3-3.noarch.rpm
$ sudo yum -y install postgresql93-server.x86_64
$ sudo chkconfig postgresql-9.3 on
$ sudo service postgresql-9.3 initdb -E UTF-8 --no-locale
$ sudo service postgresql-9.3 start

DB、テーブルを作成して、動作確認用のデータを挿入

$ sudo su - postgres
$ createdb mydb
$ psql mydb

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- 最低気温
    temp_hi         int,           -- 最高気温
    prcp            real,          -- 降水量
    date            date
);
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

他のサーバからアクセスするため、postgres ユーザにパスワードを設定

$ sudo su - postgres
$ psql

alter role postgres with password 'password';

本当はもっと強固なパスワードにすべき

同期レプリケーションのための設定を postgresql.conf に設定

デフォルトの設定との差分は以下の通り

$ diff /var/lib/pgsql/9.3/data/postgresql.conf.bak /var/lib/pgsql/9.3/data/postgresql.conf
59c59
< #listen_addresses = 'localhost'               # what IP address(es) to listen on;
---
> listen_addresses = '*'                # what IP address(es) to listen on;
159c159
< #wal_level = minimal                  # minimal, archive, or hot_standby
---
> wal_level = hot_standby                 # minimal, archive, or hot_standby
188c188
< #archive_mode = off           # allows archiving to be done
---
> archive_mode = on             # allows archiving to be done
190c190,191
< #archive_command = ''         # command to use to archive a logfile segment
---
> archive_command = 'test ! -f /usr/local/pgsql/backup/%f && cp %p /usr/local/pgsql/backup/%f'
>                               # command to use to archive a logfile segment
206c207
< #max_wal_senders = 0          # max number of walsender processes
---
> max_wal_senders = 3           # max number of walsender processes
215c216
< #synchronous_standby_names = ''       # standby servers that provide sync rep
---
> synchronous_standby_names = 'slave'   # standby servers that provide sync rep

それぞれの設定値の説明

• listen_addresses : 外部からのアクセスを許可する設定(* ですべてのIPからのアクセスを許可)
• wal_level : WALの保存レベルの設定
• archive_mode : on にすると、WALアーカイブプロセスが起動するようになる
• archive_command : WALアーカイブ時に実行されるコマンド
• max_wal_senders : WALを外部に送信するプロセスの最大数
  • 公式ドキュメント によると、想定より少し多めに設定した方が良いらしい
• synchronous_standby_names : 同期スタンバイの名前(あとでスタンバイ側でも定義する)

WALアーカイブディレクトリを作成

$ sudo mkdir -p /usr/local/pgsql/backup/
$ sudo chown postgres:postgres /usr/local/pgsql/backup

他サーバから postgres ユーザでアクセスするために、pg_hba.conf を設定

• 接続方法/ユーザ/DB毎の認証方法を定義するファイル
• HBAとは、host-based authentication: ホストベース認証の略
• フォーマットは、 接続方法\t接続するDB\tuser\tIPアドレス\t認証方法

詳しくは公式ドキュメント → pg_hba.confファイル

デフォルトの設定との差分は以下の通り

$ diff /var/lib/pgsql/9.3/data/pg_hba.conf /var/lib/pgsql/9.3/data/pg_hba.conf.bak
90,91d89
< host    replication     postgres        172.16.0.0/12            md5
< host    all     postgres        172.16.0.0/12            md5

レプリケーション用の設定(1行目)と、普通にDBにアクセスする設定(2行目)も追加

• 接続するDB の replication はレプリケーション用の特別な設定
• 認証方法 md5 はパスワード認証

PostgreSQLサーバ再起動

$ sudo service postgresql-9.3 restart

スタンバイ環境構築

概要

1. PostgreSQLのインストール
2. マスタのベースバックアップを取得
3. recovery.conf にスタンバイサーバの設定を追記
4. postgresql.conf にホットスタンバイの設定を追記
5. PostgreSQLサーバを起動

PostgreSQLのインストール

$ wget https://download.postgresql.org/pub/repos/yum/9.3/redhat/rhel-6-x86_64/pgdg-ami201503-93-9.3-3.noarch.rpm
$ sudo rpm -ivh pgdg-ami201503-93-9.3-3.noarch.rpm
$ sudo yum -y install postgresql93-server.x86_64
$ sudo chkconfig postgresql-9.3 on

マスタのベースバックアップを取得

直接 $PG_DATAを上書き

$ sudo su - postgres
$ rm -rf $PGDATA
$ pg_basebackup -h 172.31.59.13 -D $PGDATA -x -P -U postgres -R

• パスワードを聞かれるので、先程設定したパスワードを入力する
• -R オプションで、後述の recovery.conf を自動生成してくれる

recovery.conf にスタンバイサーバの設定を追記

スタンバイサーバの設定は、 recovery.conf に書く

なんで recovery.conf に書くかというと、どうやら、PostgreSQLのレプリケーションとは、マスタデータから来たデータを元にDBをリカバリし続けるという方法のため、リカバリの設定を書く所に書くようになったっぽい

$ sudo su - postgres
$ vim /var/lib/pgsql/9.3/data/recovery.conf

standby_mode = 'on'
primary_conninfo = 'user=postgres host=172.31.59.13 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres application_name=slave'

• primary_conninfo はDBに接続する時の接続文字列を定義
  • pg_basebackup で自動生成された内容に加えて、同期レプリケーションのため、master の postgresql.conf で定義した application_name=slave を追記
• primary_conninfo について、詳しくは → データベース接続制御関数#接続文字列

postgresql.conf にホットスタンバイの設定を追記

hot_standby = on だけ追記

• これにより、スタンバイでも読み込みだけ可能になる

PostgreSQLサーバを起動

$ sudo service postgresql-9.3 start

レプリケーションステータスを確認

マスタ側で実行

$ sudo su - postgres
$ psql

postgres-# \x
Expanded display is on.

postgres=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 356
usesysid         | 10
usename          | postgres
application_name | slave
client_addr      | 172.31.57.96
client_hostname  | 
client_port      | 34172
backend_start    | 2019-01-01 09:40:25.070405+00
state            | streaming
sent_location    | 0/30002A8
write_location   | 0/30002A8
flush_location   | 0/30002A8
replay_location  | 0/30002A8
sync_priority    | 1
sync_state       | sync

• 172.31.57.96 と、同期 sync_state: sync のレプリケーションができていることが確認できた

マスタが更新されるとスタンバイが更新されることを確認

スタンバイでテーブルをSELECT

$ sudo su - postgres
$ psql mydb

mydb=# SELECT * FROM weather;
     city      | temp_lo | temp_hi | prcp |    date    
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
(1 row)

マスタでテーブルを更新

$ sudo su - postgres
$ psql mydb

mydb=# INSERT INTO weather VALUES ('Tokyo', 46, 50, 0.25, '1994-11-27');
INSERT 0 1

スタンバイでテーブルをSELECT

mydb=# SELECT * FROM weather;
     city      | temp_lo | temp_hi | prcp |    date    
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 Tokyo         |      46 |      50 | 0.25 | 1994-11-27
(2 rows)

カラムが追加された! 👏

スタンバイでテーブルを更新

mydb=# INSERT INTO weather VALUES ('hoge', 46, 50, 0.25, '1994-11-27');
ERROR:  cannot execute INSERT in a read-only transaction

エラーになることを確認

PostgreSQLのバックアップ覚えがき

PostgreSQLのバックアップは3種類

• コールドバックアップ
• ホットバックアップ
• オンラインバックアップとポイントインタイムリカバリ(PITR)

コールドバックアップ

特徴

• 物理バックアップとも言われる
• $PG_DATA のファイルをまるまるコピーする方法

メリット

• 方法がシンプルなので、手順もシンプル
• ホットバックアップより、バックアップもリストアも高速

デメリット

• DBの停止が必要
• 全DBのバックアップしかできない
• バックアップしたところまでしか、リストアできない

手順

rsync を利用する(特にPostgreSQL由来のコマンドは使用しない)

• それぞれ、PostgreSQLを停止してから行うこと

バックアップ

• $BACKUP は、バックアップファイル格納PATH

$ async -av $PGDATA/ $BACKUP/pgdata

リストア

$ rm -rf $PGDATA
$ async -av $BACKUP/pgdata $PGDATA/ 

ホットバックアップ

特徴

• 論理バックアップとも言われる
• 現状のDBをSQLに変換して保存する

メリット

• DBを停止せずにバックアップが可能
• DBやテーブル単位のバックアップが可能
• コマンド1つで実行可能なため手順がシンプル

デメリット

• コールドバックアップより、バックアップもリストアも時間がかかる
• バックアップしたところまでしか、リストアできない

手順

pg_dump コマンドを実行する

• pg_dumpコマンドは以下の形式でdumpが可能
  • プレーンテキスト(SQL)
  • カスタムアーカイブ(圧縮したバイナリ)
  • ディレクトリ(テーブル単位で圧縮したバイナリ)
  • TAR形式(テーブル単位のバイナリ)
• カスタムアーカイブが、サイズが小さいけど、いざとなったらSQLに変換もできるのでオススメ

バックアップ

リモートからもバックアップ可能

• オプション -Fc はカスタムアーカイブ形式の指定

$ pg_dump -Fc ${database_name} -U ${user_name} -h ${host_name} -f $BACKUP/dump.custom

リストア

$ pg_restore -d ${database_name} $BACKUP/dump.custom

オンラインバックアップとポイントインタイムリカバリ(PITR)

特徴

• バックアップデータと、元DBのWALを使用してリカバリを行う
• PITRとは、オンラインバックアップから現在までの任意の時間の状態でリカバリできる仕組み(すごい!

メリット

• オンラインバックアップ開始〜最新状態の任意の時刻の状態に復旧可能

デメリット

• 手順/運用が複雑(サードパーティツールである程度緩和可能)
• WALアーカイブが必要
  • その分CPUリソースも、ストレージも食う
• 元DBのWALアーカイブと、WALが必要
  • WALは必要が無くなると、別の情報に上書きされてしまう
  • そのため、不要になったWALをアーカイブ領域に保存する必要がある → それがWALアーカイブ

手順

ポイント

• ベースバックアップと、マスタにあるWALアーカイブの組み合わせで復旧を行う

手順

(PostgreSQLを停止した状態で実施する)

1. WALアーカイブを保存するように設定
2. ベースバックアップの実行
3. ベースバックアップからの復旧
4. recovery.conf の設定(PITRの設定)

WALアーカイブを保存するように設定

postgresql.conf に以下の設定をする

wal_level = archive
archive_mode = on
archive_command = 'test ! -f /usr/local/pgsql/backup/%f && cp %p /usr/local/pgsql/backup/%f'

• wal_level : WALの保存レベル。以下の3種類がある
  • minial : クラッシュまたは即時停止から回復するのに必要な情報のみ書き出す(デフォルト)
  • replica : WALアーカイブに対応。ストリーミングレプリケーションに必要な情報も追加
    • 9.6の時に、archive と hot_standby という別々の設定が統一された
  • logical : ロジカルレプリケーションをサポートするのに必要な情報も追加
• archive_mode : WALアーカイブを許可する設定。デフォルトOFF
  • on にすると、アーカイブプロセスが立ち上がるようになる
• archive_command : WALを破棄する時に実行されるコマンド
  • %p はバックアップすべきWALのパス。 %f はWALファイル名
  • 自前でコマンドを用意する必要があるんだ...

ベースバックアップの実行

$ pg_basebackup -D $BACKUP -F t -x -z

• -F: ファイルフォーマットの指定。t はtar形式の保存。デフォルトは p でプレーンテキストで保存される
• -z : 出力ファイルをzip圧縮する
• -x : ベースバックアップ作成中、出力ファイルの更新を止める(メモリを食うので注意

ベースバックアップからの復旧

既存のデータからWALだけ避難

$ mv $PGDATA/pg_wal/ $BACKUP_WAL

既存のデータは不要なので移動

$ mv $PGDATA/pg_wal/  ${適当なバックアップ先}

ベースバックアップからの復旧

(tar.gz を解凍してから)

$ cp $BACKUP/pgdata/* $PGDATA

WALを最新のものに変更

(ベースバックアップのWALは古いので消す)

$ rm -rf  $PGDATA/pg_wal/
$ cp $BACKUP_WAL $PGDATA/pg_wal

recovery.conf の設定

restore_command = 'cp /usr/local/pgsql/backup/%f %p'
recovery_target_time = '2018/12/01 00:47:00'

• restore_command : archive_command と逆に、WALアーカイブからcopyするコマンドを設定
• recovery_target_time : PITRしたい場合、その時刻を設定する
  • PITRが不要な場合はこの行は不要

参考

OSC北海道2014_JPUG資料 from Chika SATO

www.slideshare.net

WEB+DB PRESS Vol.108

物理バップアップの概要 | Let's Postgres

blog.poppypop.mydns.jp

pg_basebackup

19.5. ログ先行書き込み（WAL）

PostgreSQLのレプリケーション覚えがき

PostgreSQL のレプリケーション

現在は以下の2種類をサポート

• ストリーミングレプリケーション(9.0〜
• ロジカルレプリケーション(10〜

どちらもWALをスタンバイに渡すことでレプリケーションが行われている

今回はストリーミングレプリケーションについて詳しく掘り下げる

PostgreSQL のレプリケーションの歴史

PostgreSQL9.0(2010年)まで、公式のレプリケーション機能は未サポートだった

• それまではサードパーティツールが氾濫

なお、自動フェイルオーバーやリードレプリカへの振り分けは今も未サポート

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

• PostgreSQL9.0からサポート
• 異なるメジャーバージョン間でのレプリケーションは未サポート
• WALのやり取りは、マスタは wal_sender スタンバイは wal_receiver が行う
• 非同期、同期という2つのレプリケーションモードを選択可能(後述
  • スタンバイ毎にどちらかを選択可能

非同期レプリケーションの特徴

• PostgreSQL9.0からサポート
• commit後、WALの転送完了を待たない
• そのため...
  • その分、同期レプリケーションより性能が良い
  • フェイルオーバー時に commit の内容が失われることがある
  • スタンバイの内容が古いことがある

同期レプリケーション

• PostgreSQL9.1からサポート
• commit後、WALの転送完了を待つ
• そのため...
  • フェイルオーバー時に commit の内容が失われることは無い
  • スタンバイからの応答が届くまで、別トランザクションからの参照がブロックされる
  • その分、非同期レプリケーションより性能が悪い
• スタンバイの内容が古いことがある
  • 転送完了後にスタンバイがWALを読み込むまでは古い。ただし、非同期よりは早い(はず...
• スタンバイが死ぬと、マスタのトランザクションが停止する
  • マスタがスタンバイの同期完了のレスポンスを待ち続けるため
  • そのため、 スタンバイ死亡時にはマスタのトランザクションをすべて非同期に変更する必要がある

ストリーミングレプリケーションのデータ保護レベル

ストリーミングレプリケーションでは、設定ファイルでデフォルトのデータ保護レベルを設定可能

• off(完全非同期): マスタのWALのHDDへの書き込み(flush)を待たない
• local(スレーブ非同期): マスタのWALのHDDへの書き込み(flush)を待つ。スタンバイは待たない
• remote_write(メモリ同期): スタンバイへのWAL のファイルキャッシュへの書き込みを待つ。HDDへの書き込み(flush)は待たない
• on(同期): スタンバイのWALのHDDへの書き込み(flush)を待つ

なお、トランザクション毎にも設定可能(らしい

カスケードレプリケーション

スタンバイにさらにレプリケーションを貼ることで、レプリケーションのチェインが可能

これをカスケードレプリケーションと言う

• マスタ側で複数のスタンバイへの同期をする必要がなくなるので、多少性能がよくなる(らしい

ロジカルレプリケーションの特徴

• PostgreSQL10からサポート
• 異なるメジャーバージョン間でのレプリケーションもサポート
• ストリーミングレプリケーションより柔軟だが堅牢性が少し低いらしい
  • テーブル単位でのレプリケーションや、スタンバイの書き込みも可能
  • AWS DMSのような印象?

TODO

• 自動フェイルオーバーや分散のためのツールを調査
• スタンバイが死んだ時の対応方法も調査(自動フェイルオーバーツールで対応可能?

参考

PostgreSQLレプリケーション徹底紹介 from NTT DATA OSS Professional Services

www.slideshare.net

qiita.com

Amazon linux に PostgreSQL 9.3の環境を構築してみる

諸事情あって、EC2 on PostgreSQL 9.3 を知る必要があるので、ひとまず環境を作成してみる

やったこと

• EC2環境構築
• PostgreSQL 9.3 のインストール
• PostgreSQL 9.3 の起動
• ユーザとDB、tableを作成して、select を実行

EC2環境構築

適当に web UI から Amazon linux の t2.small を作成

• ストレージはデフォルト8GBだと足りなそうなので16GBに

PostgreSQL 9.3 のインストール

手順

それぞれ、EC2環境にsshして実行している

• yumリポジトリの設定ファイルの rpm をinstall
• yum install

yumリポジトリの設定ファイルの rpm をinstall

PostgreSQL は yumリポジトリの設定ファイルをrpmで配布しているので、それを取得する

• PostgreSQLの公式ページ にAmazon linuxの場合これをダウンロードすると書かれているので従う

$ wget https://download.postgresql.org/pub/repos/yum/9.3/redhat/rhel-6-x86_64/pgdg-ami201503-93-9.3-3.noarch.rpm

rpmのインストール

$ sudo rpm -ivh pgdg-ami201503-93-9.3-3.noarch.rpm 

yumリポジトリが追加されたことを確認

$ rpm -ql pgdg-ami201503-93-9.3-3.noarch

/etc/pki/rpm-gpg
/etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-93
/etc/yum.repos.d/pgdg-93-ami201503.repo

yum install

追加された postgresql93 関係のパッケージを確認

$ sudo yum list all  postgresql93*

読み込んだプラグイン:extras_suggestions, langpacks, priorities, update-motd
14 packages excluded due to repository priority protections
利用可能なパッケージ
postgresql93.x86_64                                                               9.3.25-1PGDG.rhel6                                                    pgdg93
postgresql93-contrib.x86_64                                                       9.3.25-1PGDG.rhel6                                                    pgdg93
postgresql93-debuginfo.x86_64                                                     9.3.25-1PGDG.rhel6                                                    pgdg93
postgresql93-devel.x86_64                                                         9.3.25-1PGDG.rhel6                                                    pgdg93
postgresql93-docs.x86_64                                                          9.3.25-1PGDG.rhel6                                                    pgdg93
postgresql93-libs.x86_64                                                          9.3.25-1PGDG.rhel6                                                    pgdg93
postgresql93-odbc.x86_64                                                          10.03.0000-1PGDG.rhel6                                                pgdg93
postgresql93-odbc-debuginfo.x86_64                                                09.03.0400-1PGDG.rhel6                                                pgdg93
postgresql93-plperl.x86_64                                                        9.3.25-1PGDG.rhel6                                                    pgdg93
postgresql93-plpython.x86_64                                                      9.3.25-1PGDG.rhel6                                                    pgdg93
postgresql93-pltcl.x86_64                                                         9.3.25-1PGDG.rhel6                                                    pgdg93
postgresql93-server.x86_64                                                        9.3.25-1PGDG.rhel6                                                    pgdg93
postgresql93-tcl.x86_64                                                           2.4.0-1.rhel6                                                         pgdg93
postgresql93-tcl-debuginfo.x86_64                                                 2.3.1-1.rhel6                                                         pgdg93
postgresql93-test.x86_64                                                          9.3.25-1PGDG.rhel6                                                    pgdg93

yum install postgresql-server すれば、必要なものはすべてインストールされるらしいので、それに従う

• 参考: yumでPostgreSQLをインストールしてみよう | Let's Postgres

$ sudo yum -y install postgresql93-server.x86_64

結果、以下のパッケージがインストールされた

インストール中:
 postgresql93-server                         x86_64                         9.3.25-1PGDG.rhel6                           pgdg93                         4.1 M
依存性関連でのインストールをします:
 postgresql93                                x86_64                         9.3.25-1PGDG.rhel6                           pgdg93                         1.0 M
 postgresql93-libs                           x86_64                         9.3.25-1PGDG.rhel6                           pgdg93                         198 k

PostgreSQL 9.3の起動

PostgreSQLをインストールすると、postgresql userが追加されるので、それを確認

$ id postgres
uid=26(postgres) gid=26(postgres) groups=26(postgres)

serviceをONにする

$ sudo chkconfig postgresql-9.3 on
$ chkconfig --list postgresql-9.3

注記: この出力に含まれるのは SysV サービスのみです。ネイティブな 
      systemd サービスは含まれません。SysV の設定データはネイティブな
        systemd 設定で上書きされる場合があります。
      systemd サービスを一覧表示する場合は 'systemctl list-unit-files' を使用します。
      特定のターゲットで有効になっているサービスを確認する場合は
      'systemctl list-dependencies [target]'を使用します。

postgresql-9.3  0:off   1:off   2:on    3:on    4:on    5:on    6:off

initdb

$ sudo service postgresql-9.3 initdb -E UTF-8 --no-locale

• -E はエンコードの指定。明示的に指定しないと、起動環境のエンコードを引き継ぐので、UTF-8を明示的に指定
• --no-locale はロケールの指定。こちらも明示的にロケールを引き継がないようにしている(ソート順などに影響がでるらしい

起動

$ sudo service postgresql-9.3 start

本当はこの後にアクセス制限を設定する必要があるが割愛

ユーザとDB、tableを作成して、select を実行

ユーザの作成

$ sudo su - postgres
$ createuser ec2-user

DB作成権限を付与

$ psql

postgres=# ALTER ROLE "ec2-user" WITH CREATEDB;
ALTER ROLE

権限が付与されたことを確認

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 ec2-user  | Create DB                                      | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

DBの作成

ec2-user で実行

$ createdb mydb -U ec2-user

作られたことを確認

$ psql -l
                                         データベース一覧
   名前    |  所有者  | エンコーディング |  照合順序   | Ctype(変換演算子) |      アクセス権       
-----------+----------+------------------+-------------+-------------------+-----------------------
 mydb      | ec2-user | UTF8             | en_US.UTF-8 | en_US.UTF-8       | 
 postgres  | postgres | UTF8             | en_US.UTF-8 | en_US.UTF-8       | 
 template0 | postgres | UTF8             | en_US.UTF-8 | en_US.UTF-8       | =c/postgres          +
           |          |                  |             |                   | postgres=CTc/postgres
 template1 | postgres | UTF8             | en_US.UTF-8 | en_US.UTF-8       | =c/postgres          +
           |          |                  |             |                   | postgres=CTc/postgres
(4 行)

テーブルの作成

DBに接続

$ psql mydb

ここのチュートリアルを参考に、テーブルを作成してみる

• https://www.postgresql.jp/document/10/html/tutorial-table.html

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- 最低気温
    temp_hi         int,           -- 最高気温
    prcp            real,          -- 降水量
    date            date
);

作られたことを確認

mydb=> \d
            リレーションの一覧
 スキーマ |  名前   |    型    |  所有者  
----------+---------+----------+----------
 public   | weather | テーブル | ec2-user
(1 行)

行を挿入

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

挿入されたことを確認

mydb=> select * from weather;
     city      | temp_lo | temp_hi | prcp |    date    
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
(1 行)

今日はここまで

その後やりたいこと

• サンプルデータの挿入
• レプリケーション
• バックアップとリカバリ
• DBのバージョンアップ(to 9.6)

WEB+DB PRESS Vol.108 詳解PostgreSQL 読書メモ

自分が知らなかったことや興味があったことだけをメモしているので、全体が知りたい人は WEB+DB PRESS を買って読みましょう

WEB+DB PRESS Vol.108

バージョニングについて

9系までは、x.y.z の x.y がメジャーバージョン(9.6.5 ならば、9.6がメジャーバージョン)

• ちなみに9系の最後のメジャーバージョンは9.6

10系からは、x.y の x がメジャーバージョン(10.1 ならば、10がメジャーバージョン)

2017年からは、2Q末にメジャーバージョンが毎年リリースされている

最新のメジャーバージョンはPostgreSQL11で、12が開発中

なお、各メジャーバージョンのサポート期限は4世代(4年)

初期設定いろいろ

ロケールのデフォルトは環境の言語に依存するので、依存しないように C にするのが一般的

文字コードはデフォルトは sql_ascii なので、UTF-8にしておくのが無難

パスワードと、IPアドレスによるアクセス制限をしっかりつけておく

PostgreSQLの内部構造

PostgreSQLはマルチプロセス(MySQLはマルチスレッド)

いろいろなプロセスが、それぞれの役割をこなしている

• マスタプロセス : 最初に起動して、各プロセスを制御する
• バックエンドプロセス : 接続要求毎に生成される
• ライタ : 共有メモリの内容を定期的にストレージに書き込む
• WALライタ : WAL(後述)をストレージに書き込む
• チェックポインタ : ライタに書き込みのタイミングを通知する

など

PostgreSQLのデータ更新方法

基本的にはメモリ内(共有バッファ)上で、データを更新する

そして、ライタプロセスが、 チェックポイント と呼ばれる特定のタイミングで、メモリの内容をストレージに書き込む

• これは、ストレージへの書き込みには時間がかかるので、高速化のため
• なお、ストレージに書き込まれる前のデータを ダーティーページ という

それとは別に、WALライタが、共有バッファの更新毎に、差分をWALファイルに書き込んでいる

サーバがクラッシュした時には、 チェックポイントとメモリの差分はWALファイルに格納されているので、それを使ってリカバリを行う

メモリ

共有メモリ と プロセスメモリ の2種類がある

共有メモリ

PostgreSQL全体で使用するメモリ

• 共有バッファ : テーブルやインデックスのデータをキャッシュする領域。 総メモリの25%程度が適切な値
• 可視性マップ 、 空き領域マップ : VACUUM処理(後述)が使用
• WALバッファ : 上述のWALをキャッシュする領域

プロセスメモリ

バックエンドプロセス毎に持つメモリ

• 作業メモリ : クエリ実行時に使用するメモリ。
  • 実行されるクエリのデータサイズが大きければ必要量が増える
  • ここが減るとswapが発生するので、 swapを監視すると良い
  • (OSのメモリ - 共有バッファのサイズ)/max_connection を超えない範囲でチューニングしていく必要がある
  • 最初は 32MB 程度にして、様子を見ていくのが良い
• 一時バッファ : 一時テーブル作成時に使用
• メンテナンス用作業メモリ : VACUUM(後述)、インデックス作成、外部キー制約の追加などのメンテナンス用の作業メモリ
  • 小さいとメンテナンス作業時にswapが発生して処理が遅くなる

ファイル

• データファイル : テーブルのデータ。8192バイト毎の複数のファイルの格納されている
• インデックスファイル : インデックスのデータ。同じく、8192バイト毎の複数のファイル
• WALファイル : 16MB固定
• TOASTファイル : 8192バイトを超えるデータの格納先、ただし最大1GBまで
  • そのため、PostgreSQLのTEXT型が持てる最大サイズも1GBまで

追記型アーキテクチャとVACUUM

PostgreSQLのデータの更新は、参照を置き換えるという形

• 遅いが、トランザクションやロックの制御がシンプルになるというメリットがあるらしい

参照を置き換えた結果、使用されなくなったデータファイルは VACUUM という仕組みを使って再利用される

• 共有メモリの 可視性マップ にデータファイルの参照状態が格納されているので、VACUUMはそれを参照する

なお、PostgreSQLでは データを削除してもデータファイルの合計サイズは減らない

• VACUUMは、使用されなくなったデータファイルを再利用可能にするだけ
• データファイルの総量を減らしたい場合は CLUSTER コマンドが適切らしい
• また、 pg_repack というツールもある

他のVACUUMの機能

• 統計情報の更新
• トランザクションID(XID)の再利用
  • PostgreSQLは、テーブルやレコード全てにXIDを割り当てている
  • XIDを使い切る前に、未使用のXIDを再利用可能にしている

バックアップ

• pg_dump
  • スタンダードな論理バックアップ
  • カスタムアーカイブ形式がオススメ(テーブルやスキーマ定義だけ分離できる&いざとなったらSQLに戻せる)
• pg_basebackup
  • DBを停止せずにバックアップを取る方法
  • データファイルのコピーをしながら、コピーしている間に発生したWALもアーカイブする(WALアーカイブモードを有効にする必要あり
  • pg_rman は、上記のバックアップ&リストアを簡単に行えるツール

レプリケーション

ストリーミングレプリケーションと、ロジカルレプリケーションがある

ストリーミングレプリケーション

• 主流。スタンバイが、プライマリのWALの更新を受け取って自身のDBを更新する
• プライマリとスタンバイのマイナーバージョンが異なっても、レプリケーションができる

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

• PostgreSQL10から追加
• 自由度が高い
  • メジャーバージョンが異なってもレプリケーションができる
  • 複数のプライマリを集約したりとかもできる
• その分壊れやすい
• AWS DMSっぽい印象

バージョンアップについて

マイナーバージョンアップ

積極的に対応するべき

PostgreSQLの再起動が必要だが、ストリーミングレプリケーションを使って、スタンバイから更新すれば難しいことはないはず

メジャーバージョンアップ

ストリーミングレプリケーションは使えない

データファイルやWALに互換性がないこともあるため、物理バックアップからの復旧もできない

pg_dump の結果を新バージョンのDBに反映させるのが一番スタンダード

• デメリット: 停止時間が長い。サーバを新規に用意する必要がある
• メリット: 元のサーバが残っているため復旧が容易

現在のサーバで pg_upgrade するほうが、停止時間は短い

• PostgreSQL 8.4 移行はデータファイルのフォーマットが変わっていないので、テーブルやインデックスの再構築が行われない
• デメリットは、バージョンダウンはできない
• EC2を使っている場合、スナップショットから復旧すれば良いので、デメリットは無視できそう

参考書籍

これからはじめる PostgreSQL入門

[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)

参考リンク

記事だけよんでわからなかった部分を調べた時に読んだページ

morizyun.github.io

qiita.com

PostgreSQLのリカバリ超入門(もしくはWAL、CHECKPOINT、オンラインバックアップの仕組み) from Hironobu Suzuki

www.slideshare.net