カテゴリー: PostgreSQL

投稿日:

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

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/*

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

# 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/
投稿日:

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/
投稿日:

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でクラスタ全体をバックアップする

実際に試した環境

・ Ubuntu 20.04
・ PostgreSQL 12

バックアップする。

# pg_dumpall -U postgres -f /mnt/backup/test.sql

「Peer authentication failed for user “postgres”」が発生する場合、下記ファイルを編集してpeer認証を無効(サービス再起動が必要)にする。

#local    all    postgres            peer
local    all    postgres            md5

もしcronなどで利用するためにパスワード認証をスキップしたい場合は、実行ユーザー(ここではroot)のホームディレクトリに下記を作成する。

*:*:*:postgres:ぱすわーど

バックアップからリストアする。

# su postgres
$ psql -f /mnt/backup/test.sql

補足

圧縮しながらバックアップする。

# pg_dumpall -U postgres | gzip -c /mnt/backup/test.sql.gz

パスワードをつける場合はzipを使用するぐらいしか思いつかない。

# cd /mnt/backup/
# pg_dumpall -U postgres -f test.sql
# zip --encrypt --password ぱすわーど test.sql.zip test.sql
投稿日:

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サービスを開始し、ログで正常動作を確認する。