UbuntuServer18.04にPostgreSQL9.5をインストールする

標準のリポジトリにはないので、公式wikiを参考にインストールする。

リポジトリキーをインポートする。

# curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

リポジトリを追加する。

# echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
# apt update

インストール。

# apt install postgresql-9.5

サービスを起動する。

# systemctl start postgresql@9.5-main

postgresのパスワードを設定する。

# su postgres
$ psql
postgres=# alter user postgres with password 'password';
postgres=# \q

PostgreSQLストリーミングレプリケーション設定(CentOS7)

PostgreSQL9.5を使用した。

今回は2台構成
host:master ip:192.168.0.100 略称:マスタ
host:sub1 ip:192.168.0.101 略称:スレーブ

マスタとスレーブの共通設定

公式サイトを参考にリポジトリを追加する。

# yum -y localinstall https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-3.noarch.rpm

インストール。

# yum -y install postgresql95-server postgresql95-devel postgresql95-contrib

ファイアウォールに例外を追加する。

# firewall-cmd --add-port=5432/tcp --permanent
# firewall-cmd --reload

データベースを初期化してデータベースクラスタを作成する。

# /usr/pgsql-9.5/bin/postgresql95-setup initdb

スレーブの設定

postgresqlサービスが停止した状態でdataを空にする。

# rm -r -f /var/lib/pgsql/9.5/data/*

先程作成したレプリケーション用ユーザーを使用してマスタからデータをコピーする。

# su postgres
$ pg_basebackup -h 192.168.0.100 -p 5432 -U rep_user -D /var/lib/pgsql/9.5/data/ -x -c fast -P

/var/lib/pgsql/9.5/data/内のすべてのファイルの所有者/所有グループがpostgresになっているか確認。

/var/lib/pgsql/9.5/data/postgresql.confを編集して設定を変更する。

hot_standby = on

/var/lib/pgsql/9.5/data/recovery.confを作成する。

standby_mode = 'on'
primary_conninfo = 'host=192.168.0.100 port=5432 user=rep_user password=password application_name=sub1'
recovery_target_timeline = 'latest'
trigger_file = '/home/postgres/trigger'

postgresqlサービスを起動する。

# systemctl start postgresql-9.5

マスタサーバーで下記SQLを実行するとレプリケーションされているか確認できる。

SELECT * FROM pg_stat_replication;

PostgreSQLストリーミングレプリケーション設定(Windows)

今回は2台構成

host:master ip:192.168.0.100 略称:マスタ
host:sub1 ip:192.168.0.101 略称:スレーブ

まずは両ホストにPostgreSQLをインストール。本手順作成時はVer9.2を使用。インストール完了後に両端末で共通の管理者権限を持つアカウントを作成し、サービス実行アカウントをそれに変更する。

マスタでの作業

コマンドプロンプトを起動し、PostgreSQLのインストールフォルダ内、binフォルダに移動。

cd "c:\Program Files\PostgreSQL\9.2\bin"

レプリケーション用ユーザーを作成する。

createuser -P -E -U postgres --replication rep_user

作成するユーザーのパスワードを2回入力し、最後にpostgresのパスワードを入力する。コマンドプロンプトの画面は閉じずに次へ。

続いてレプリケーション用データベースのアクセス権を設定する。インストールフォルダ内の「data」にある「pg_hba.conf」の最後に下記の行を追加。

host replication rep_user 192.168.0.0/24 md5

同じく「postgresql.conf」も編集。下記3行のコメントアウトを解除。

wal_level = minimal
archive_mode = off
max_wal_senders = 0

postgresqlサービスを再起動。OS再起動でもよい。

テスト用のデータを作成するため、コマンドプロンプトを操作。

pgbench -p 5432 -i -U postgres
vacuumdb -a -z -U postgres

再度「postgresql.conf」を編集。下記8行を編集。

listen_addresses = '*'
wal_level = hot_standby
fsync = on
synchronous_commit = on
wal_sync_method = fsync
max_wal_senders = 2  ※マスタ+スレーブの数を入れる
wal_keep_segments = 32  ※8~32が目安らしい
synchronous_standby_names = '*'

※構築終了後に下記設定値がシステム要件に合っているか確認する

max_connections = 300  ※同時接続数
shared_buffers = 1024MB  ※物理メモリの1/4程度
work_mem = 8MB  ※物理メモリの1/500程度だが最大8MB
effective_cache_size = 2048MB  ※物理メモリの1/2程度

postgresqlサービスを再起動。OS再起動でもよい。

スレーブでの作業

postgresqlサービスを停止する。

PostgreSQLのインストールフォルダ内の「data」フォルダ内を空にしておく。

コマンドプロンプトを起動し、PosgreSQLのインストールフォルダ内、binフォルダ内に移動。

cd "c:\Program Files\PostgreSQL\9.2\bin"

以下のコマンドを実行してマスタからベースバックアップ。

pg_basebackup -h 192.168.0.100 -p 5432 -U rep_user -D "c:\Program Files\PostgreSQL\9.2\data" -x -c fast -P

※57800/57800 kB (100%)~と表示されれば成功

インストールフォルダ内の「data」にある「postgresql.conf」を編集。

hot_standby = on

postgresqlサービスを再起動。OS再起動でもよい。

同じ場所に「recovery.conf」を新規作成、内容は下記

standby_mode = 'on'
primary_conninfo = 'host=192.168.0.100 port=5432 user=rep_user password=[pass] application_name=sub1'
recovery_target_timeline = 'latest'
trigger_file = 'C:\\\pgsql\\trigger'
recovery_end_command = ''

postgresqlサービスを開始し、ログで正常動作を確認する。

マスタサーバー障害発生時

トリガーファイルの配置、または手動にてフェイルオーバーさせ、スレーブをマスタに昇格させる。旧マスタサーバーが復旧後は、旧マスタをスレーブにする作業を行う。

スレーブサーバー障害発生時

検知するには以下のクエリをマスタに発行し、レコード数をカウントする。必要であればスレーブのapplication_nameなどを確認しスレーブが通信不可であることを確認する。

SELECT * FROM pg_stat_replication;

スレーブダウン時は、設定ファイルで「synchronous_commit = on」を設定しているため、マスタに対する更新が完了しないが、トランザクション中に以下のパラメータを毎回セットすることで、スレーブとの同期を無効にして臨時運用可能。

SET LOCAL synchronous_commit TO LOCAL;

スレーブが復旧した後は、改めてスレーブ構築手順を実施して元に戻す。