Debian10 にPostgreSQLをインストールし、ストリーミングレプリケーション設定してみる

Debian10サポート期間

サーバを更新するなど考えると、いつまでサポート期限なのかが気になります。OSのアップデート、リレーショナルデータベースのアップデートなどはかなり気を遣う作業なので、できれば避けたいです。

Debian10 リリース 2019/7

LTS採用により、今後5年間サポート予定となっており、2024年7月くらいまでがサポート予定です。

postgresql11

apt-get でインストールできるpostgresqlは、11+200+deb10u3 となります。

# apt-cache show postgresql
Package: postgresql
Source: postgresql-common (200+deb10u3)
Version: 11+200+deb10u3
Installed-Size: 64
Maintainer: Debian PostgreSQL Maintainers <team+postgresql@tracker.debian.org>
Architecture: all
Depends: postgresql-11
Suggests: postgresql-doc
Description-en: object-relational SQL database (supported version)
 This metapackage always depends on the currently supported PostgreSQL
 database server version.

PostgreSQL 11 のサポート期限は2023年11月9日となっています。

11 11.18 Yes October 18, 2018 November 9, 2023

こちらも初期リリースから5年がサポート期間です。

PostgreSQLのパッケージ管理は優れているのでapt-getで入るpostgresqlはサポート期間はDebian10のサポート期限2024年までと期待を寄せたいところですが、パッケージの説明を見ると、

オブジェクトリレーショナル SQL データベース (サポート版)
本メタパッケージはサポートされる最新版の PostgreSQL データベースサーバに常 に依存しています。

パッケージ: postgresql (11+200+deb10u5)

と書いてあるので、やっぱりPostgreSQLのバージョンに依存してそうな気がする

Debian10のインストール

この部分についてはたくさんの方が解説しているので、ここではあまり深く掘り下げない。

必要最低限のパッケージのみをインストールしておく。

ホスト名の変更

postgres というホスト名でテストしようかと思ってインストールしましたが、本番環境とごっちゃになると嫌だなと思い、ホスト名を test-postgresに変更しました。

# hostnamectl set-hostname test-postgres
# hostname
test-postgres

ホスト名を表示させると、変更されているのが分かります。

systemd-timesyncdにてntp同期

インターネット標準時刻と同期しておく。

標準でsystemd-timesyncdがインストールされている。systemdがデフォルトで時刻合わせ機能を持っているという事。

# timedatectl status
               Local time: 木 2023-01-12 23:48:28 JST
           Universal time: 木 2023-01-12 14:48:28 UTC
                 RTC time: 木 2023-01-12 14:48:28
                Time zone: Asia/Tokyo (JST, +0900)
System clock synchronized: yes
              NTP service: active
          RTC in local TZ: no

設定ファイル

/etc/systemd/timesyncd.conf

ここのntp設定を行う

[Time]
NTP=ntp.nict.jp

リスタート

# systemctl restart systemd-timesyncd

すると、ntp.nict.jpに向けて同期が始まる

 systemctl -l status systemd-timesyncd
● systemd-timesyncd.service - Network Time Synchronization
     Loaded: loaded (/lib/systemd/system/systemd-timesyncd.service; enabled; vendor preset: enabled)
     Active: active (running) since Thu 2023-01-12 23:57:59 JST; 7s ago
       Docs: man:systemd-timesyncd.service(8)
   Main PID: 5023 (systemd-timesyn)
     Status: "Initial synchronization to time server 133.243.238.244:123 (ntp.nict.jp)."
      Tasks: 2 (limit: 4624)
     Memory: 1000.0K
        CPU: 46ms
     CGroup: /system.slice/systemd-timesyncd.service
             mq5023 /lib/systemd/systemd-timesyncd

apt調整

今回CDでインストールしたので、postgresqlをインストールしようとしたら、

メディア変更:
 'Debian GNU/Linux 10.2.0 _Buster_ - Official amd64 DVD Binary-1 20191116-09:57'
とラベルの付いたディスクをドライブ '/media/cdrom/' に入れて [Enter] キーを押してください

となってしまった。

vi /etc/apt/sources.list

deb http://deb.debian.org/debian/ buster main contrib
deb http://deb.debian.org/debian/ buster-updates main contrib
deb-src http://deb.debian.org/debian/ buster-updates main contrib

apt-get update

postgresqlのインストール

# apt-get install postgresql-11 postgresql-contrib-11
パッケージリストを読み込んでいます... 完了
依存関係ツリーを作成しています
状態情報を読み取っています... 完了
注意、'postgresql-contrib-11' の代わりに 'postgresql-11' を選択します
以下の追加パッケージがインストールされます:
  libllvm7 libpq5 libsensors-config libsensors5 libxslt1.1 postgresql-client-11 postgresql-client-common postgresql-common ssl-cert sysstat
提案パッケージ:
  lm-sensors postgresql-doc-11 libjson-perl openssl-blacklist isag
以下のパッケージが新たにインストールされます:
  libllvm7 libpq5 libsensors-config libsensors5 libxslt1.1 postgresql-11 postgresql-client-11 postgresql-client-common postgresql-common
  ssl-cert sysstat
アップグレード: 0 個、新規インストール: 11 個、削除: 0 個、保留: 38 個。
30.0 MB 中 29.6 MB のアーカイブを取得する必要があります。
この操作後に追加で 117 MB のディスク容量が消費されます。
続行しますか? [Y/n]

postgresql-contribって?

明示的にコンパイルしてインストールしないとインストールされない、PostgreSQL周辺の拡張モジュールや便利ツール

contrib

設定ファイルの場所

/etc/postgresql/11/main

postgresql.confの設定

マニュアル参照

#どのIPからの接続を許可するか * で全て
listen_addresses = '*'

#待ち受けするポート番号
port = 5432

#データベースサーバに同時接続する最大数
max_connections = 100

#データベースサーバが共有メモリバッファのために使用するメモリ量
shared_buffers = 16GB
#単一の問い合わせで利用できるディスクキャッシュの実効容量に関するプランナの条件を設定
effective_cache_size = 48GB
#VACUUM、CREATE INDEX、およびALTER TABLE ADD FOREIGN KEYの様な保守操作で使用されるメモリの最大容量
maintenance_work_mem = 2GB

#チェックポイントの完了目標をチェックポイント間の総時間の割合として指定
checkpoint_completion_target = 0.9
#未だディスクに書き込まれていないWALデータに対して使用される共有メモリ容量
wal_buffers = 16MB
default_statistics_target = 100
#非シーケンシャル的に取り出されるディスクページのコストに対するプランナの推測を設定
random_page_cost = 4
#PostgreSQLが同時実行可能であると想定する同時ディスクI/O操作の数
effective_io_concurrency = 2
#一時ディスクファイルに書き込むようになる前に、問い合わせ操作が使用する基本的な最大のメモリ容量
work_mem = 27962kB
#古いWALファイルは、消去されることなく今後のチェックポイントで使用するために常にリサイクルされます
min_wal_size = 2GB
#自動WALチェックポイントの際にWALが増加する最大サイズ
max_wal_size = 4GB
#システムがサポートするバックグラウンドプロセスの最大数
max_worker_processes = 4
#一つのGatherまたはGather Mergeノードに起動できるワーカー数の最大値
max_parallel_workers_per_gather = 2
#パラレルクエリ操作用にシステムがサポートできる最大のワーカー数
max_parallel_workers = 4

デフォルトだとこんな感じですね

egrep -vE ‘^\s*#’ 0字以上の空白文字の後のコメントアウト行を排除

egrep -vE ‘^\s*$’ 0字以上の空白行を排除 

# egrep -vE '^\s*#' /etc/postgresql/13/main/postgresql.conf | egrep -vE '^\s*$'
data_directory = '/var/lib/postgresql/13/main'          # use data in another directory
hba_file = '/etc/postgresql/13/main/pg_hba.conf'        # host-based authentication file
ident_file = '/etc/postgresql/13/main/pg_ident.conf'    # ident configuration file
external_pid_file = '/var/run/postgresql/13-main.pid'                   # write an extra PID file
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
ssl = on
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
shared_buffers = 128MB                  # min 128kB
dynamic_shared_memory_type = posix      # the default is the first option
max_wal_size = 1GB
min_wal_size = 80MB
log_line_prefix = '%m [%p] %q%u@%d '            # special values:
log_timezone = 'Asia/Tokyo'
cluster_name = '13/main'                        # added to process titles if nonempty
stats_temp_directory = '/var/run/postgresql/13-main.pg_stat_tmp'
datestyle = 'iso, ymd'
timezone = 'Asia/Tokyo'
lc_messages = 'ja_JP.UTF-8'                     # locale for system error message
lc_monetary = 'ja_JP.UTF-8'                     # locale for monetary formatting
lc_numeric = 'ja_JP.UTF-8'                      # locale for number formatting
lc_time = 'ja_JP.UTF-8'                         # locale for time formatting
default_text_search_config = 'pg_catalog.simple'
include_dir = 'conf.d'                  # include files ending in '.conf' fro

PostgreSQLをリスタートして設定を読み込ませる

# /etc/init.d/postgresql restart
Restarting postgresql (via systemctl): postgresql.service.

クライアントへのpgAdminのインストール

私が管理しているパソコンはWindowsです。ここはGUIで管理した方が楽なので、pgAdmin4をインストールします。

pgAdmin4

設定を日本語に変更 File → Preferences

パスワードを変更するために、まずpostgresqlのpg_hba.confのアクセス許可をTRUSTとしてLAN内から接続可能状態にする。

host    all             all             192.168.0.0/24          trust
# /etc/init.d/postgresql restart
Restarting postgresql (via systemctl): postgresql.service.

pgAdmin4でクライアントからサーバにアクセスする

接続できた

postgresパスワードの設定

postgresqlをインストールしたのはいいけど、postgresにはパスワードが設定されていない。

Linuxユーザにもpostgresユーザは作成されているのでパスワードをつける

# passwd postgres
新しい パスワード:
新しい パスワードを再入力してください:
passwd: パスワードは正しく更新されました

postgresユーザで入りなおす

# su - postgres
postgres@test-postgres:~$

PostgreSQL内のpostgresユーザのパスワード変更する

postgres@test-postgres:~$ psql
psql (13.9 (Debian 13.9-0+deb11u1))
"help"でヘルプを表示します。

postgres=# alter role postgres with password 'passwd';
ALTER ROLE

これで、pg_hba.confをtrust → md5に変更すると、LAN内からのアクセスにパスワードが聞かれるようになる。

host    all             all             192.168.0.0/24          md5

postgresql再起動

# /etc/init.d/postgresql restart
Restarting postgresql (via systemctl): postgresql.service.

LAN内クライアントのpgAdmin4からアクセスしてみるとパスワードを聞かれるようになる。

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

ストリーミングレプリケーションの仕組み

現在ある本番サーバのストリーミングレプリケーション、メインDBがおかしくなった時にすぐに対応できるようにしておきたい

セカンダリサーバを、メインとして稼働させたい時は?

posgres11 では、

/var/lib/postgresql/11/main

にrecovery.confを配置する事で、セカンダリサーバとして実行かのうだった。PostgreSQL12より、recovery.confは廃止され、postgresql.confに統合された。

O.1. recovery.confファイルをpostgresql.confに統合

ストリーミングレプリケーションがうまく動作しているかどうかの確認

/*NO LOAD BALANCE*/select state from pg_stat_replication;

うまくいっていると、streaming という文字列が返ってくる

recovery.confファイルは

$ pg_basebackup -h 192.168.10.168 -p 5432 -U replication_user -D /data/ –wal-method=fetch –checkpoint=fast –write-recovery-conf –progress Password: 65390/65390 kB (100%), 1/1 tablespace

PostgreSQL11で非同期レプリケーションを設定する

セカンダリサーバでpg_basebackupを行う際に オプション–write-recovery-conf をつけると、自動的に生成されるらしい。

ストリーミングレプリケーション構築手順

1.プライマリ側にレプリケーション用ユーザ作成

CREATE ROLE replication_user LOGIN
  ENCRYPTED PASSWORD 'md5**************'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE REPLICATION;
COMMENT ON ROLE replication_user IS 'レプリケーション用ユーザ';

※ここで作成した replication_userのパスワードは、pg_basebackupする時に利用する

2.プライマリ側のpg_hba.conf設定

セカンダリからの接続を許可する。セカンダリのIPが192.168.0.15/32だったらこんな感じ。

host     replication     replication_user   192.168.0.15/32       md5

3.プライマリ側postgres.confの設定

どれだけの情報がWALに書かれるか

wal_level = replica

トランザクションのコミットがクライアントに「成功」の報告を返す前に、WALレコードがディスク上に書き込まれるまで待つかどうかの指定(トランザクションのコミットはローカルディスクへの吐き出しのみを待機)

synchronous_commit = local

archive_modeが有効な場合、archive_commandを設定することにより、完了したWALセグメントはアーカイブ格納領域に送信されます。

archive_mode = off

同時接続を受ける接続最大値

max_wal_senders = 3

pg_walに保持され続けるセグメントの最小値

wal_keep_segments = 16

サーバが使用できるレプリケーションスロット(26.2.6参照)の最大数

max_replication_slots = 3

同期レプリケーションをサポート可能なスタンバイサーバのリスト

synchronous_standby_names = '*'

リカバリの最中に接続し、そして問い合わせを実行できるか否か

hot_standby = on

ホットスタンバイがスタンバイサーバ上で現在処理を行っている問い合わせについて、プライマリーまたは上位サーバにフィードバックを送るか否か

hot_standby_feedback = off

3.プライマリサーバにてスロット名の作成

SELECT * FROM pg_create_physical_replication_slot('db_test');

※スロットを削除したい時はこちら

SELECT * FROM pg_drop_replication_slot('db_test')

4.セカンダリサーバにてベースバックアップ

セカンダリpostgresの停止

# /etc/init.d/postgresql stop

/var/lib/postgresql/11/main を空にしておく。空にしないと下記エラーが出る

pg_basebackup: エラー: ディレクトリ"/var/lib/postgresql/11/main"は存在しますが空ではありません

ディレクトリを空にする

/var/lib/postgresql/11/main# rm -r *

ベースバックアップを実行(例 primaryが192.168.0.15:5432の場合)

/var/lib/postgresql/13/main# pg_basebackup -h 192.168.0.15 -p 5432 -U replication_user -D /var/lib/postgresql/13/main --wal-method=fetch --checkpoint=fast --write-recovery-conf --progress
パスワード:
  747745/70748335 kB (1%), 0/1 テーブル空間

※reprication_user のパスワードが聞かれる

※–write-recovery-conf オプションで自動的にrecovery.conf作成だが、postgres12から、recovery.confはpostgresql.confに統合

pg_basebackup中にレプリケーションの状態がどうなっているかを調べる(メインサーバにてSQL実行)

postgres=# /*NO LOAD BALANCE*/select state from pg_stat_replication;
 state
--------
 backup
(1 行)

backupと表示される

recovery.confが自動的に生成されてる

root@postgres-test:/var/lib/postgresql/11/main# cat recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=replication_user password=************* host=192.168.0.15 port=5432 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any'

スタンバイサーバのPostgrfeSQLをスタートさせる

root@postgres-test:/var/lib/postgresql/11/main# /etc/init.d/postgresql start
[ ok ] Starting postgresql (via systemctl): postgresql.service.

スタンバイが起動しない-権限の問題

起動しないログ postgresql.auto.confファイル自体はあるのに起動しない。オープンできません、許可がありませんなど、権限がない事を示唆している。

2023-01-23 07:19:57.744 GMT [10016] LOG:  存在しない設定ファイル"/var/lib/postgresql/11/main/postgresql.auto.conf"をスキップします
2023-01-23 16:19:57.744 JST [10016] FATAL:  ファイル"/var/lib/postgresql/11/main/PG_VERSION"をオープンできませんでした: 許可がありません
pg_ctl: サーバを起動できませんでした。
ログ出力を確認してください。

フォルダにpostgresユーザの権限がない時に起こる

解決方法

chown -R postgres:postgres /var/lib/postgresql/11/main

スタンバイサーバが起動しない-コンフィグが未完了

max_connectionsの値をカスタマーサーバと合わせないとだめ。特にホッとスタンバイ設定にするなら怒られる。

2023-01-23 16:22:52.736 JST [10053] FATAL:  max_connections = 100 がマスターサーバの設定値(400)より小さいので、ホットスタンバイは利用できません
2023-01-23 16:22:52.737 JST [10052] LOG:  起動プロセス (PID 10053)は終了コード1で終了しました
2023-01-23 16:22:52.738 JST [10052] LOG:  起動プロセスの失敗のため起動を中断しています
2023-01-23 16:22:52.739 JST [10052] LOG:  データベースシステムはシャットダウンしました
pg_ctl: サーバを起動できませんでした。

postgresql.confの設定変更

vi /etc/postgresql/11/main/postgresql.conf
max_connections = 400                   # (change requires restart)

起動したらこんな感じのログになる

2023-01-23 16:28:01.164 JST [10096] LOG:  スタンバイモードに入ります
2023-01-23 16:28:01.167 JST [10096] LOG:  REDOを124/152A25B0から開始します
2023-01-23 16:28:01.174 JST [10096] LOG:  124/1531C088 でリカバリの一貫性が確保されました
2023-01-23 16:28:01.174 JST [10096] LOG:  124/15323740のレコードの後方リンク8001/0が不正です
2023-01-23 16:28:01.175 JST [10095] LOG:  データベースシステムはリードオンリー接続の受け付け準備ができました
2023-01-23 16:28:01.201 JST [10100] LOG:  プライマリのタイムライン1の 124/15000000からでWALストリーミングを始めます
2023-01-23 16:28:01.757 JST [10101] [不明]@[不明] LOG:  開始パケットが不完全です

メインサーバの状態確認

postgres=# /*NO LOAD BALANCE*/select state from pg_stat_replication;
   state
-----------
 streaming
(1 行)

streamingになってる!

構築したストリーミングレプリケーション

カスケード接続によるストリーミングレプリケーション

タイトルとURLをコピーしました