90H Tech: PostgreSQL9.2で同期レプリケーション

次のような環境を構築してみようと思います。

各サーバーの構成
名前	役割
pgpool	pgpool2で負荷分散と障害時のフェイルオーバー
pgsql1	PostgreSQL9.2で同期レプリケーションのプライマリ
pgsql2	PostgreSQL9.2で同期レプリケーションのセカンダリ

OSはすべてubuntu 12.04 LTS Serverを使います。

[PostgreSQLのセットアップ]

まず、各サーバーにPostgreSQLをソースからビルドしてセットアップします。
ビルドに必要なパッケージをインストールします。

sudo apt-get install build_essential libreadline6-dev zlib1g-dev

PostgreSQLユーザーとディレクトリを作成します。

sudo adduser postgres
sudo mkdir /usr/local/pgsql
sudo chown postgres:postgres /usr/local/pgsql

PostgreSQLのソースをダウンロードします。

cd /usr/local/src
sudo wget http://ftp.postgresql.org/pub/source/v9.2.1/postgresql-9.2.1.tar.gz
sudo tar zxvf postgresql-9.2.1.tar.gz
sudo chown -R postgres:postgres postgresql-9.2.1

PostgreSQLのソースをビルドします。

su - postgres
cd /usr/local/source/postgresql-9.2.1
./configure
make
make install

postgresユーザーの.profileに

# User specific environment and startup programs
PGHOME=/usr/local/pgsql
PGDATA=$PGHOME/data
PGLIB=$PGHOME/lib
PATH=$PATH:$HOME/bin:$PGHOME/bin
export PGHOME PGDATA PGLIB PATH

を追加して

source ~/.profile

で、環境変数に反映します。
PostgreSQLの初期化を行います。

initdb

sudoの権限のあるユーザーに戻って、PostgreSQLの自動起動の設定を行います。

sudo vi /etc/ld.so.conf

で、ファイルの末尾に /usr/local/pgsql/lib を追加して、

sudo ldconfig -v

を実行します。
PostgreSQL起動スクリプトをコピーします。

sudo cp /usr/local/src/postgresql-9.2.1/contrib/start-scripts/linux /etc/init.d/postgresql
sudo chmod 755 /etc/init.d/postgresql
sudo update-rc.d postgresql start 90 2 3 4 5 . stop 10 0 1 6 .

PostgreSQLを起動してみます。

sudo /etc/init.d/postgresql start

問題なく起動できたら、データベースのユーザーのpostgresにパスワードを設定します。

su - postgres
psql -c "alter user postgres with password 'postgresのパスワード';"

[下準備]

pgpoolから、pgsql1,pgsql2のコマンドを、パスワードなしのsshで実行できるようにします。
これはpgsql1かpgsql2で障害が発生した時、その対応処理をpgpoolから実行させるためです。
まず、pgpoolで

su - postgres
mkdir .ssh
chmod 700 .ssh
ssh-keygen -t dsa

で、パスワードなしの鍵を作成し、作成された公開鍵をpgsql1とpgsql2にコピーします。

scp .ssh/id_dsa.pub postgres@pgsql1:~/
scp .ssh/id_dsa.pub postgres@pgsql2:~/

次に、pgsql1とpgsql2の両方で、

su - postgres
mkdir .ssh
chmod 700 .ssh
cd .ssh
cat ~/id_dsa.pub >> authorized_keys
chmod 600 authorized_keys
rm ~/id_dsa.pub

として、公開鍵を追加します。
試しに、pgpoolからpgsql1とpgsql2のコマンドを実行してみて

ssh postgres@pgsql1 "/usr/local/pgsql/bin/psql -l"
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 template0 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

ssh postgres@pgsql2 "/usr/local/pgsql/bin/psql -l"
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 template0 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

と表示されれば成功です。

[PostgreSQLの同期レプリケーションの設定]

pgsql1をプライマリ、pgsql2をセカンダリという構成で同期レプリケーションするように設定します。

まず、プライマリのpgsql1の設定を変更します。

su - postgres
cd /usr/local/pgsql/data
vi postgresql.conf

postgresql.confに以下の設定を追加します。

listen_addresses = '*'
wal_level = hot_standby
archive_mode = on
archive_command= '/bin/cp %p /usr/local/pgsql/data/pg_archive/%f'
max_wal_senders = 3
wal_keep_segments = 5
synchronous_standby_names = 'pgsql2'

postgresql.confをコピーして、同期レプリケーション用(postgresql.conf.sync)と非同期レプリケーション用(postgresql.conf.async)を作成します(これはフェイルオーバーした時に使用します)。

cp postgresql.conf postgresql.conf.sync
cp postgresql.conf postgresql.conf.async

非同期レプリケーション用のpostgresql.conf.asyncのsynchronous_standby_namesをコメントアウトして無効にします(行を削除してもいいです)。

#synchronous_standby_names = 'pgsql2'

次に、pgsql1にpgsql2からアクセスできるようにpg_hba.confを編集します。

vi pg_hba.conf

pg_hba.confに以下の内容を追加します。

host    replication     postgres        192.168.0.0/24        trust

※IPアドレスはサーバーのセグメントに合わせます。
続けて、アーカイブログのディレクトリを作成します。

mkdir -p pg_archive
chmod 700 pg_archive

sudoの権限のあるユーザーで、PostgreSQLを再起動します。

sudo /etc/init.d/posgresql restart

次にセカンダリのpgsql2の設定を変更します。
もし、PostgreSQLが起動しているなら停止します。

sudo /etc/init.d/postgresql stop

su - postgres
cd /usr/local/pgsql
rm -rf data
pg_basebackup -h pgsql1 -p 5432 -D /usr/local/pgsql/data --xlog --progress --verbose
cd data
vi postgresql.conf

postgresql.confに以下の設定を追加します。

#synchronous_standby_names = ''    #をつけてコメントにするか行を削除する
hot_standby = on

recovery.confを作成します。

cp ../share/recovery.conf.sample recovery.conf
vi recovery.conf

recovery.confに以下の設定を追加します。

standby_mode = on
primary_conninfo = 'host=pgsql1 port=5432 application_name=pgsql2'

sudoの権限のあるユーザーで、PostgreSQLを起動します。

sudo /etc/init.d/posgresql start

同期レプリケーションできているか確認してみます。
pgsql1で次のコマンドを実行して確認します。

su - postgres
psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
 application_name |   state   | sync_priority | sync_state
------------------+-----------+---------------+------------
 pgsql2           | streaming |             1 | sync
(1 rows)

pgsql2のsync_stateがsyncになっていれば、同期レプリケーションで動作しています。
サンプルのデータベースを作成してみます。

createuser -P test1
createdb test1 -O test1 -E UTF8 -T template0
psql test1 -U test1 -c "create table table1 (id integer not null primary key, value text);"

pgsql2でデータベースとテーブルが作成されているか確認します。

su - postgres
psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 template0 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test1     | test1    | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
(4 rows)

psql test1 -U test1 -c "\dt;"
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | table1 | table | postgres
(1 row)

psql -c "select pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 t
(1 row)

データベースtest1とテーブルtable1がpgsql2にも作成されていればOKです。
また、pg_is_in_recovery()の結果が"t"(true)になっていれば、セカンダリとして動作していることが確認できます。

[pgpool-IIの設定]

まず、pgpoolサーバーにpgpool-IIをソースからビルドしてセットアップします。
pgpool-IIのディレクトリを作成します。

sudo mkdir /usr/local/pgpool2
sudo chown postgres:postgres /usr/local/pgpool2
cd /usr/local/src
sudo wget http://www.pgpool.net/download.php?f=pgpool-II-3.2.1.tar.gz
sudo tar zxvf pgpool-II-3.2.1.tar.gz
sudo chown -R postgres:postgres pgpool-II-3.2.1

PostgreSQLのソースをビルドします。

su - postgres
cd /usr/local/source/pgpool-II-3.2.1
./configure --prefix=/usr/local/pgpool2 -with-pgsql=/usr/local/pgsql
make
make install

pgpool-IIの設定を変更します。

cd /usr/local/pgpool2/etc
cp pcp.conf.sample pcp.conf
vi pcp.conf

pcp.confの末尾に、postgresユーザーのパスワードをMD5で暗号化したものを記述します。パスワードのMD5変換したものは、

/usr/local/pgpool2/bin/pg_md5 postgresのパスワード

で取得できます。これを

# USERID:MD5PASSWD
postgres:MD5変換されたパスワード

というように追加します。
次に、pgpool.confを設定します。

cp pgpool.conf.sample-stream pgpool.conf
vi pgpool.conf

pgpool.confに以下の設定を追加します。

listen_addresses = '*'
port = 9999
pid_file_name = '/usr/local/pgpool2/pgpool.pid'

failover_command = '/usr/local/pgpool2/etc/failover.sh %d "%h" %p %D %m %M "%H" %P %r %R'

# pgsql1
backend_hostname0 = 'pgsql1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/usr/local/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
# pgsql2
backend_hostname1 = 'pgsql2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/usr/local/pgsql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER&#8216;

sr_check_user = 'postgres'
sr_check_password = 'postgresのパスワード'
health_check_user = 'postgres'
health_check_password = 'postgresのパスワード'
recovery_user = 'postgres'
recovery_password = 'postgresのパスワード'

フェイルオーバー時のスクリプトを作成します。

vi failover.sh

failover.shに以下のようにスクリプトを記述します。

#!/bin/sh

failed_node_id=$1
failed_host_name=$2
failed_port=$3
failed_db_cluster=$4
new_master_id=$5
old_master_id=$6
new_master_host_name=$7
old_primary_node_id=$8
new_master_port=$9
new_master_db_cluster=$10
logfile=/usr/local/pgpool2/log/failover.log

echo "------------------------------------------------------------------" >> $logfile
date >> $logfile
echo "failed_node_id=$failed_node_id" >> $logfile
echo "failed_host_name=$failed_host_name" >> $logfile
echo "failed_port=$failed_port" >> $logfile
echo "failed_db_cluster=$failed_db_cluster" >> $logfile
echo "new_master_id=$new_master_id" >> $logfile
echo "old_master_id=$old_master_id" >> $logfile
echo "new_master_host_name=$new_master_host_name" >> $logfile
echo "old_primary_node_id=$old_primary_node_id" >> $logfile
echo "new_master_port=$new_master_port" >> $logfile
echo "new_master_db_cluster=$new_master_db_cluster" >> $logfile

if [ $new_master_id -eq -1 ]
then
    echo "unknown new_master_id=$new_master_id" >> $logfile
    exit 0
fi

if [ -z $new_master_host_name ]
then
    echo "unknown new_master_host_name=$new_master_host_name" >> $logfile
    exit 0
fi

if [ $failed_node_id = $old_primary_node_id ] # primary failed
then
    echo "secondary promote" >> $logfile
    /usr/bin/ssh postgres@$new_master_host_name "/usr/local/pgsql/bin/pg_ctl -D $new_master_db_cluster promote" >> $logfile 2>&1
else # secondary failed
    echo "primary async mode" >> $logfile
    /usr/bin/ssh postgres@$new_master_host_name "/bin/cp /usr/local/pgsql/data/postgresql.conf.async /usr/local/pgsql/data/postgresql.conf" >> $logfile 2>&1
    /usr/bin/ssh postgres@$new_master_host_name "/usr/local/pgsql/bin/pg_ctl reload -D $new_master_db_cluster" >> $logfile 2>&1
fi

ログを入れるディレクトリを作成します。

cd ..
mkdir log

pgsql1とpgsql2のpg_hba.confを編集して、pgpoolからアクセスできるようにします。
psql1とpsql2で、

su - postgres
cd /usr/local/pgsql/data
vi pg_hba.conf

pg_hba.confに以下の設定を追加します。

host    all             all             pgpoolのIPアドレス/32      password

pgsql1とpgsqlのPostgreSQLのパラメータを再読み込みします。
sudoの権限のあるユーザーで

sudo /etc/init.d/postgresql reload

pgpoolを起動します。
起動のためのスクリプトをpostgresqlの起動スクリプトを真似して次のように作ってみました。

#! /bin/sh

# Installation prefix
prefix=/usr/local/pgpool2

# Who to run the postmaster as, usually "postgres".  (NOT "root")
PGUSER=postgres

# Where to keep a log file
PGLOG="$prefix/log/serverlog"

# It's often a good idea to protect the postmaster from being killed by the
# OOM killer (which will tend to preferentially kill the postmaster because
# of the way it accounts for shared memory).  Setting the OOM_SCORE_ADJ value
# to -1000 will disable OOM kill altogether.  If you enable this, you probably
# want to compile PostgreSQL with "-DLINUX_OOM_SCORE_ADJ=0", so that
# individual backends can still be killed by the OOM killer.
#OOM_SCORE_ADJ=-1000
# Older Linux kernels may not have /proc/self/oom_score_adj, but instead
# /proc/self/oom_adj, which works similarly except the disable value is -17.
# For such a system, enable this and compile with "-DLINUX_OOM_ADJ=0".
#OOM_ADJ=-17

## STOP EDITING HERE

# The path that is to be used for the script
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# What to use to start up the pgpool.
DAEMON="$prefix/bin/pgpool"

# What to use to shut down the pgpool
PGPOOL="$prefix/bin/pgpool"

set &#8211;e

# Only start if we can find the pgpool.
test -x $DAEMON ||
{
        echo "$DAEMON not found"
        if [ "$1" = "stop" ]
        then exit 0
        else exit 5
        fi
}

# Parse command line parameters.
case $1 in
  start)
        echo -n "Starting pgpool-II: "
        test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" > /proc/self/oom_score_adj
        test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj
        su - $PGUSER -c "$DAEMON &" >>$PGLOG 2>&1
        echo "ok"
        ;;
  stop)
        echo -n "Stopping pgpool-II: "
        su - $PGUSER -c "$PGPOOL -m fast stop"
        echo "ok"
        ;;
  restart)
        echo -n "Restarting pgpool-II: "
        su - $PGUSER -c "$PGPOOL -m fast stop"
        test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" > /proc/self/oom_score_adj
        test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj
        su - $PGUSER -c "$DAEMON &" >>$PGLOG 2>&1
        echo "ok"
        ;;
 reload)
        echo -n "Reload pgpool-II: "
        su - $PGUSER -c "$PGPOOL reload"
        echo "ok"
        ;;
  *)
        # Print help
        echo "Usage: $0 {start|stop|reload|restart}" 1>&2
        exit 1
        ;;
esac

exit 0

これを /etc/init.d/pgpool に保存します。
※完全に理解できていない箇所もあるので、もし真似してやった方がいて何か問題が起きても自己責任でお願いします(^^;
ちなみにubuntuのpgpool2のパッケージをインストールした時に生成される /etc/init.d/pgpool2 は以下のようになっていました。

#! /bin/sh

### BEGIN INIT INFO
# Provides:          pgpool2
# Required-Start:    $remote_fs $syslog
# Required-Stop:     $remote_fs $syslog
# Should-Start:      postgresql
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: start pgpool-II
# Description: pgpool-II is a connection pool server and replication
#              proxy for PostgreSQL.
### END INIT INFO


PATH=/sbin:/bin:/usr/sbin:/usr/bin
DAEMON=/usr/sbin/pgpool
PIDFILE=/var/run/postgresql/pgpool.pid

test -x $DAEMON || exit 5

# Include pgpool defaults if available
if [ -f /etc/default/pgpool2 ] ; then
 . /etc/default/pgpool2
fi

OPTS=""
if [ x"$PGPOOL_LOG_DEBUG" = x"yes" ]; then
 OPTS="$OPTS -d"
fi

. /lib/lsb/init-functions


is_running() {
 pidofproc -p $PIDFILE $DAEMON >/dev/null
}


d_start() {
 if is_running; then
  :
 else
  su -c "$DAEMON -n $OPTS 2>&1 </dev/null | logger -t pgpool -p ${PGPOOL_SYSLOG_FACILITY:-local0}.info >/dev/null 2>&1 &" - postgres
 fi
}


d_stop() {
 killproc -p $PIDFILE $DAEMON -INT
 status=$?
 [ $status -eq 0 ] || [ $status -eq 3 ]
 return $?
}


case "$1" in
    start)
 log_daemon_msg "Starting pgpool-II" pgpool
 d_start
 log_end_msg $?
 ;;
    stop)
 log_daemon_msg "Stopping pgpool-II" pgpool
 d_stop
 log_end_msg $?
 ;;
    status)
 is_running
 status=$?
 if [ $status -eq 0 ]; then
  log_success_msg "pgpool-II is running."
 else
  log_failure_msg "pgpool-II is not running."
 fi
 exit $status
 ;;
    restart|force-reload)
 log_daemon_msg "Restarting pgpool-II" pgpool
 d_stop && sleep 1 && d_start
 log_end_msg $?
 ;;
    try-restart)
 if $0 status >/dev/null; then
  $0 restart
 else
  exit 0
 fi
 ;;
    reload)
 exit 3
 ;;
    *)
 log_failure_msg "Usage: $0 {start|stop|status|restart|try-restart|reload|force-reload}"
 exit 2
 ;;
esac

pgpoolに -n をつけて、デーモンとして走らせないようにしてるっぽいです。
こっちに合わせて書きなおしたほうがいいかもしれません。

pgpoolを自動起動するように設定します。

sudo chmod 755 /etc/init.d/pgpool
sudo update-rc.d pgpool start 91 2 3 4 5 . stop 11 0 1 6 .

pgpoolを実行します。

sudo /etc/init.d/pgpool start

ここまでで、設定は完了です。

[pgsql1を落とすテスト]

プライマリのpgsql1を停止させてみます。

su - postgres
pg_ctl -D /usr/local/pgsql/data -m immediate stop

適当なクライアントからアクセスしてみます。

psql -h pgpool -p 9999 -U test1 test1
psql: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

一旦エラーになるようです。
どうも、pgsql1を落としたあと、次にアクセスがきた時にフェイルオーバー処理が走るみたいです(ちょっと自信はありませんが)。
もう一度アクセスしてみると、

psql -h pgpool -p 9999 -U test1 test1
Password for user test1: test1のパスワード

test1=> select * from table1;
 id | value
----+-------
(0 row)

test1=> insert into table1 (id, value) values (1, 'aaaa');
INSERT 0 1

無事接続でき、更新クエリも処理されています。
もし、pgsql2がプライマリに昇格できていなければ、更新クエリはエラーになるはずなので、問題なくフェイルオーバー処理が実行されているようです。
念のため、pgsql2の状態を確認してみると

psql -c "select pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 f
(1 row)

pg_is_in_recovery()の結果が"f"(false)になっており、プライマリに昇格しています。

[pgsql2を落とすテスト]

※一旦pgsql1とpsql2を元に戻します。

セカンダリのpgsql2を停止させてみます。

su - postgresql
pg_ctl -D /usr/local/pgsql/data -m immediate stop

適当なクライアントからアクセスしてみます。

psql -h pgpool -p 9999 -U test1 test1
psql: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

※一旦エラーになるようです。
もう一度アクセスしてみると、

psql -h pgpool -p 9999 -U test1 test1
Password for user test1: test1のパスワード

test1=> select * from table1;
 id | value
----+-------
(0 row)

test1=> insert into table1 (id, value) values (1, 'aaaa');
INSERT 0 1

無事接続でき、更新クエリも処理されています。
もし、pgsql1が非同期レプリケーションに切り替わっていなければ、更新クエリはエラーになる(タイムアウトする？)はずなので、ちゃんとフェイルオーバー処理が実行されています。
念のため、pgsqlの/usr/local/pgsql/data/recovery.confを確認してみると、

#synchronous_standby_names = 'pgsql2'

のように、synchronous_standby_namesがコメントアウトされて無効になっているはずです。

長ーくなりましたが、これで同期レプリケーションのまとめはここまで。

もし、サーバーの構成で、セカンダリが複数台あったとき、pgpoolに設定したフェイルオーバースクリプトのsecondary failedの部分の処理は不要かと思います。
同期しているセカンダリが落ちたときは sync_state が potential になっているサーバーが、sync になるので問題ないはず。

90H Tech

2012年12月7日金曜日

PostgreSQL9.2で同期レプリケーション

[PostgreSQLのセットアップ]

[下準備]

[PostgreSQLの同期レプリケーションの設定]

[pgpool-IIの設定]

[pgsql1を落とすテスト]

[pgsql2を落とすテスト]

0 コメント :

コメントを投稿

Labels

Blog Archive