PostgreSQL – 最北端で暮らすSEのknow-how

2021年7月13日2021年7月13日

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

CentOS7.9を使用。

2台構成

host：sv1　ip：192.168.0.100　略称：プライマリ
host：sv2　ip：192.168.0.101　略称：セカンダリ

プライマリとセカンダリの共通設定

インストールする。

# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# yum install postgresql10-server postgresql10-devel postgresql10-contrib

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

# firewall-cmd --add-service=postgresql --permanent
# firewall-cmd --reload

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

# postgresql-10-setup initdb

サービスを有効化する。

# systemctl enable postgresql-10

プライマリの設定

サービスを起動する。

# systemctl start postgresql-10

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

# su postgres
$ psql
=# CREATE USER replication_user REPLICATION PASSWORD 'password';
=# \q

外部からの通信を許可。最下部に下記を追記する。

host    replication    replication_user    192.168.0.0/24    md5

postgresql.confを編集して下記8行を変更する。
※synchronous_commitはシステム要件によって変更した方が良い

listen_addresses = '*'
wal_level = hot_standby
fsync = on
synchronous_commit = off
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程度

サービスを再起動する。

# systemctl restart postgresql-10

セカンダリの設定

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

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

先程作成したレプリケーション用ユーザーを使用してプライマリからデータをコピーする。
※-Rオプションはrecovery.confを自動作成する指定
※-Pオプションは進捗を表示する指定

# su postgres
$ pg_basebackup -R -h 192.168.0.100 -p 5432 -U replication_user -D /var/lib/pgsql/10/data/ -P

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

ll /var/lib/pgsql/10/data/

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

hot_standby = on

postgresqlサービスを起動する。

# systemctl start postgresql-10

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

SELECT * FROM pg_stat_replication;

フェイルオーバーする際はセカンダリで下記コマンドを実行する。

# su postgres
$ pg_ctl promote -D /var/lib/pgsql/10/data/

2019年9月19日2019年10月4日

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

2017年7月4日2021年7月11日

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

OS規定のversion:10を使用。

2台構成

host：sv1　ip：192.168.0.100　略称：プライマリ
host：sv2　ip：192.168.0.101　略称：セカンダリ

プライマリとセカンダリの共通設定

インストールする。

# dnf install postgresql-server postgresql-devel postgresql-contrib

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

# firewall-cmd --add-service=postgresql --permanent
# firewall-cmd --reload

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

# postgresql-setup initdb

サービスを有効化する。

# systemctl enable postgresql

プライマリの設定

サービスを起動する。

# systemctl start postgresql

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

# su postgres
$ psql
=# CREATE USER replication_user REPLICATION PASSWORD 'password';
=# \q

外部からの通信を許可。最下部に下記を追記する。

host    replication    replication_user    192.168.0.0/24    md5

postgresql.confを編集して下記8行を変更する。
※synchronous_commitはシステム要件によって変更した方が良い

listen_addresses = '*'
wal_level = hot_standby
fsync = on
synchronous_commit = off
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程度

サービスを再起動する。

# systemctl restart postgresql

セカンダリの設定

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

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

# su postgres
$ pg_basebackup -R -h 192.168.0.100 -p 5432 -U replication_user -D /var/lib/pgsql/data/ -P

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

ll /var/lib/pgsql/data/

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

hot_standby = on

postgresqlサービスを起動する。

# systemctl start postgresql

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

SELECT * FROM pg_stat_replication;

フェイルオーバーする際はセカンダリで下記コマンドを実行する。

# su postgres
$ pg_ctl promote -D /var/lib/pgsql/data/

2013年11月21日2021年6月24日

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

2台構成

host：sv1　ip：192.168.0.100　略称：プライマリ
host：sv2　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の下記8行を編集する。
※synchronous_commitはシステム要件によって変更した方が良い

listen_addresses = '*'
wal_level = hot_standby
fsync = on
synchronous_commit = off
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

インストールフォルダ内の「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=sv2'
recovery_target_timeline = 'latest'
trigger_file = 'C:\\\pgsql\\failover-trigger'
recovery_end_command = ''

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