HP ProliantでDebian12 Postgres15で、ストリーミングレプリケーションするまでまとめ

本ページは広告が含まれています。気になる広告をクリック頂けますと、サーバ運営費になります(^^

環境

ハードウェア

HP Proliant DL20 Gen10

[    0.000000] DMI: HPE ProLiant DL20 Gen10/ProLiant DL20 Gen10, BIOS U43 09/16/2021
[    0.000000] tsc: Detected 3400.000 MHz processor
[    0.000000] tsc: Detected 3399.906 MHz TSC

[    1.027289] smartpqi 0000:04:00.0: added 0:2:0:0 0000000000000000 RAID              HPE      E208i-a SR Gen10

[    1.057253] sd 0:1:0:0: [sda] 1875319472 512-byte logical blocks: (960 GB/894 GiB)

Debian

# cat /etc/debian_version
12.1

Postgres

# apt-cache show postgresql
Package: postgresql
Source: postgresql-common (248)
Version: 15+248
Installed-Size: 15
Maintainer: Debian PostgreSQL Maintainers <team+postgresql@tracker.debian.org>
Architecture: all
Depends: postgresql-15

IPアドレス

マスター　192.168.0.12 ,IPエイリアス 192.168.0.13

ホットスタンバイ 192.168.0.17

UPS

OMRON BN50T

Omron SimpleShutdownSoftware

サーバ管理ツール

iLO5

ハードウェア RAID1

E208i-a smart array card を利用して、RAID1構成にします。

SSDはMicron製のエンタープライズ製品を選定しました

Debianインストール初期設定

初期設定では、NTPを設定したくらいです。ウィンドウマネージャも設定せず、すべてコマンドラインで設定しました。ウィンドウマネージャを利用していると、何かとトラブルが起こったりするので、私は採用してません。クライアントパソコンならまだ入れる意味も出てくると思うのですが、DBサーバ管理に、ウィンドウマネージャ―は必要ないかなぁという感覚です。

NTP設定

編集するファイル

/etc/systemd/timesyncd.conf

[Time]
NTP=ntp.nict.jp

リスタート

systemctl restart systemd-timesyncd

同期できているか確認する

# 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 Sat 2023-04-29 15:23:24 JST; 19s ago
       Docs: man:systemd-timesyncd.service(8)
   Main PID: 4629 (systemd-timesyn)
     Status: "Initial synchronization to time server 61.205.120.130:123 (ntp.nict.jp)."
      Tasks: 2 (limit: 28637)
     Memory: 1004.0K
        CPU: 40ms
     CGroup: /system.slice/systemd-timesyncd.service
             mq4629 /lib/systemd/systemd-timesyncd

 4月 29 15:23:24 testdb systemd[1]: Starting Network Time Synchronization...
 4月 29 15:23:24 testdb systemd[1]: Started Network Time Synchronization.
 4月 29 15:23:24 testdb systemd-timesyncd[4629]: Initial synchronization to time server 61.205.120.130:123 (ntp.nict.jp).

syslog設定

使い慣れたsyslogも入れておきます。

apt-get install rsyslog

UPS設定

UPSで電源供給する事はすぐできますが、停電が起こった時に安全にシャットダウンしたいです。その時に必要なのが、UPS管理ソフトになります。

USB接続しておき、UPSへの電源供給状態によって規定の時間以上停電が起こった時にシャットダウンスクリプトを走らせます。

1台のOMRON UPSとUSB接続する場合

PowerActPro　を利用し、Slaveとして動作させる場合

PowerAct Pro Slave Agent をダウンロード

# wget https://socialsolution.omron.com/jp/ja/products_service/ups/support/download/soft/poweractpro/slave/PAPV460_Slave_Linux9.zip

unzipインストール

# apt-get install unzip

解凍

# unzip PAPV460_Slave_Linux9.zip

rpm → deb 変換ツールインストール

apt-get -y install alien

rpm → deb 変換

/PAPV460_Slave_Linux9/source# alien PowerActPro-SlaveAgent-4.60-20230811.x86_64_Redhat9.rpm
Warning: Skipping conversion of scripts in package PowerActPro-SlaveAgent: postinst postrm preinst prerm
Warning: Use the --scripts parameter to include the scripts.
poweractpro-slaveagent_4.60-20230812_amd64.deb generated

インストール

PAPV460_Slave_Linux9/source# dpkg -i poweractpro-slaveagent_4.60-20230812_amd64.deb

設定

/usr/lib/PowerActPro/SlaveAgent/slaveconfig.sh

1.PA Proを選択

********************************************************************************
   Master Agent
********************************************************************************
   1. PA Pro
   2. SC20G
   3. SC20G2
   4. SC21
   5. SC22

   Select Number ->

冗長化電源は２を選択

********************************************************************************
   Redundant Power Supply Setting
********************************************************************************
   1. Enable
   2. Disable

   Select Number ->

同じセグメントにいるサーバは自動で探してくれるので3 を選択

********************************************************************************
   IP Address of Master Agent
********************************************************************************
   1. End
   2. Add a New IP Manually
   3. Add a New IP Manually From Net Search

   Select Number ->

探してこられたサーバを選択

********************************************************************************
   Select a New IP From Net Search
********************************************************************************
   Net Search is in Process, Please wait ...

     1. 192.168.11.5
     0. Return

   Select Number ->

ポップアップメッセージは表示させたいので1.Enable

********************************************************************************
   Popup Message
********************************************************************************
   1. Enable
   2. Disable

   Select Number ->

シャットダウンに入るまで180秒で設定

********************************************************************************
   Shutdown Start Delay Tiem(Sec.)
********************************************************************************
   * The range of Shutdown start delay time is from 0 Sec to 600 Sec
   * The step between "0 Sec to 600 Sec" is "60 Sec".

   Input Value:

外部コマンドは実行しないので0

********************************************************************************
   Run External Command Time(Sec.)
********************************************************************************
   * The range of Ext cmd run time is from 0 Sec to 600 Sec.
   * The step between "0 Sec to 600 Sec" is "60 Sec".
   * If you use External Command for Virtual Server shutdown, please
     setup External Command needs time for Guest OS shutdown.

   Input Value:

外部実行コマンドはないので、空白のままエンター

********************************************************************************
   Run External Command
********************************************************************************
   Input a executable file:

シャットダウンに必要な時間は120秒で設定

********************************************************************************
   Shutdown Need Time(Sec.)
********************************************************************************
   * The range of Shutdown need time is from 0 Sec to 600 Sec.
   * The step between "0 Sec to 600 Sec" is "60 Sec".

   Input Value: 120

Shutdown Mode は、1.Shutdown

********************************************************************************
   Select OS Shutdown Mode
********************************************************************************
   1. Shutdown
   2. Hibernate

   Select Number ->

バーチャルサーバは利用していない 1.を選択

********************************************************************************
   Select Virtual Server Shutdown Mode
********************************************************************************
   1. Shutdown by Linux System
   2. Shutdown by PowerAct Pro

   Select Number ->

ポップアップメッセージにて正常稼働を確認

#
Broadcast message from root (Tue Jan 16 15:24:57 2024):

Agent Start

Broadcast message from root (Tue Jan 16 15:25:02 2024):

Network Communication starts with master agent

systemdにて自動起動できるように

# vi /etc/systemd/system/omron-ups.service
[Unit]
Description=Simple Shutdown Software

[Service]
Type=forking
ExecStart=/usr/lib/PowerActPro/SlaveAgent/ControlCenter/ControlCenter
KillSignal=SIGUSR1
Restart=on-abort
RestartSec=3

[Install]
WantedBy=multi-user.target

登録・再起動

# systemctl daemon-reload
# systemctl enable omron-ups.service

# systemctl start omron-ups
# /usr/sbin/reboot

PowerAct Pro Master Agentマシンにてコンソール確認

+------------------------------------------------------------------------------+
   [ NetSearch Window Menu ]
+------------------------------------------------------------------------------+
   1. Show NetSearch Information
   2. Monitor PowerAct Pro Master Agent
   0. Exit PowerAct Pro Console

   Select Number -> 1
+------------------------------------------------------------------------------+
   [ NetSearch Window ]
+------------------------------------------------------------------------------+
 ID  Master/Slave      UPS Model   UPS Status   Battery Status   Communication
 1   AD01          BN100T      On Line      Normal           USB1:Normal
     AD02
     TestDB
--------------------------------------------------------------------------------

※TestDB が今回Linux上に設定したPower Act Pro Slave Agent

ステータスの確認

# systemctl status omron-ups.service
● omron-ups.service - Simple Shutdown Software
     Loaded: loaded (/etc/systemd/system/omron-ups.service; enabled; preset: enabled)
     Active: active (running) since Tue 2024-01-16 16:16:37 JST; 2min 28s ago
    Process: 526 ExecStart=/usr/lib/PowerActPro/SlaveAgent/ControlCenter/ControlCenter (code=exited, status=0/SUCCESS)
   Main PID: 534 (ControlCenter)
      Tasks: 2 (limit: 28606)
     Memory: 2.3M
        CPU: 149ms
     CGroup: /system.slice/omron-ups.service
             tq534 /usr/lib/PowerActPro/SlaveAgent/ControlCenter/ControlCenter
             mq582 /usr/lib/PowerActPro/SlaveAgent/Slave/SlaveAgent

 1月 16 16:16:37 TestDB systemd[1]: Starting omron-ups.service - Simple Shutdown Software...
 1月 16 16:16:37 TestDB systemd[1]: Started omron-ups.service - Simple Shutdown Software.

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

postgres.conf 設定ファイル

最後尾に、以下を追記。ファイルの後の方にあるものが有効になる

# Change Default Settings

# From PGTune https://pgtune.leopard.in.ua/
# DB Version: 15
# OS Type: linux
# DB Type: oltp
# Total Memory (RAM): 64 GB
# CPUs num: 4
# Connections num: 400
# Data Storage: ssd

listen_addresses = '*'          # what IP address(es) to listen on;
#PostgreSQLに同時に接続できるクライアントの最大数。デフォルト値100。値を大きくするとメモリー使用量が増え、性能に影響する
max_connections = 400
#データベースサーバが共有メモリバッファのために使用するメモリ量を設定します。 デフォルトは一般的に128メガバイト
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_buffers = 16MB
#デフォルトのまま100
default_statistics_target = 100
#非シーケンシャル的に取り出されるディスクページのコストに対するプランナの推測を設定します。デフォルトは4。小さくすると、システムはなるべくインデックススキャンを使用するようになる。
random_page_cost = 1.1
#設定値を上げるほどより多くのデータを非同期で読み込むようになります。
effective_io_concurrency = 200
#クエリ中の並び替えやハッシュテーブル操作など一部の操作で使用する最大メモリサイズ、一時的にメモリを使用するような一部のクエリで効果が見込める
work_mem = 20971kB
#主共有メモリ領域に対してhuge pageを要求するかどうかを管理、huge pageを使うと、ページテーブルが小さくなり、メモリ管理に使用されるCPU時間が少なくなり、性能が向上
huge_pages = try
#WALファイルの合計サイズの下限
min_wal_size = 2GB
#WALファイルの合計サイズの上限
max_wal_size = 8GB
#システムがサポートするバックグラウンドプロセスの最大数
max_worker_processes = 4
#1つの並行処理に対して起動できるワーカー数の最大値
max_parallel_workers_per_gather = 2
#パラレルクエリ操作用にシステムがサポートできる最大のワーカー数
max_parallel_workers = 4
#単一のユーティリティコマンドで使用されるパラレルワーカーの最大数
max_parallel_maintenance_workers = 2

# For logs
log_destination='stderr'
logging_collector='on'
log_statement='mod'
log_filename='postgresql-%Y-%m-%d.log’
log_line_prefix='[%t]%h %d %p[%l]'
log_rotation_age='1d'
log_rotation_size=800MB
log_min_error_statement=error
log_min_duration_statement=10000
log_directory='/var/local/pgsql/log'

現在の接続数確認方法

postgres=# SHOW max_connections;
 max_connections
-----------------
 400
(1 行)

postgres=# SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend' ;
 count
-------
     1
(1 行)

ここで同時接続数を確認し、最大接続数と比較して値の調整を行う

ユーザパスワードの調整

Linux postgresユーザのパスワード変更

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

PostgresDB内 postgresユーザのパスワード変更

# su - postgres
postgres@MainDB:~$ psql
psql (15.3 (Debian 15.3-0+deb12u1))
"help"でヘルプを表示します。

postgres=# alter role postgres with password 'パスワード文字列';
ALTER ROLE

※ pg_hba.confで、どの端末からもパスワードなしでアクセスできるようにしておき、PgAdminでアクセスしてPostgresDB内のpostgresパスワードを変更する方法もある

例 192.168.11.0/24からパスワード無しでアクセス可能にする

host all all 192.168.11.0/24 trust

DataBaseのデータ復元

データベースのバックアップ方法は、バックアップスクリプトの項を確認

データベースは事前に作成しておく

# su – postgres

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

postgres=# CREATE DATABASE database01;
CREATE DATABASE
postgres-# CREATE DATABASE database02;
CREATE DATABASE
\q →一度抜ける

→ユーザから先に復元するのがコツ
→psql で戻すのと、pg_restoreで戻すのは意味が違うので注意
/usr/lib/postgresql/15/bin/psql template1 < /home/postgres/2023-11-03.user

→Databaseを戻す
/usr/lib/postgresql/15/bin/pg_restore -U postgres -d database01 /home/postgres/2023-11-03.database01

/usr/lib/postgresql/15/bin/pg_restore -U postgres -d database02 /home/postgres/2023-11-03.database02

IPエイリアス

常時稼働しているサーバが、ダウンした時にスタンバイサーバをプライマリに昇格させる際に、IPエイリアスを用いて、実アドレスの変更なしで利用できるようにします。

# The loopback network interface
auto lo
iface lo inet loopback

# The primary network interface
#allow-hotplug eno1
#iface eno1 inet dhcp

allow-hotplug eno1
iface eno1 inet static
        address 192.168.0.12/24
        gateway 192.168.0.254
        # dns-* options are implemented by the resolvconf package, if installed
        dns-nameservers 8.8.8.8
        dns-search techlive.tokyo


post-up ip route add 192.168.20.0/24 via 192.168.0.1 dev eno1

post-up ip addr add 192.168.0.13/24 dev eno1

IPエイリアス反映確認

# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host noprefixroute
       valid_lft forever preferred_lft forever
2: eno1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 5c:ba:2c:5a:5a:78 brd ff:ff:ff:ff:ff:ff
    altname enp2s0f0
    inet 192.168.0.12/24 brd 192.168.0.255 scope global eno1
       valid_lft forever preferred_lft forever
    inet 192.168.0.13/24 scope global secondary eno1
       valid_lft forever preferred_lft forever
    inet6 fe80::5eba:2cff:fe5a:5a78/64 scope link
       valid_lft forever preferred_lft forever
3: eno2: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN group default qlen 1000
    link/ether 5c:ba:2c:5a:5a:79 brd ff:ff:ff:ff:ff:ff
    altname enp2s0f1

ルート反映確認

# ip route show
default via 192.168.0.254 dev eno1 onlink
192.168.0.0/24 dev eno1 proto kernel scope link src 192.168.0.12
192.168.20.0/24 via 192.168.0.1 dev eno1

違うセグメントからアクセスできない

IPエイリアスでサーバを変えても同じアドレスにするという構想なのですが、違うサーバに移行すると、同じセグメントからはアクセスできるのに、違うセグメントからはアクセスできないという事がおきました。

サーバA:　IPアドレス 192.168.0.12,エイリアス192.168.0.13

サーバB:　IPアドレス 192.168.0.17,エイリアス192.168.0.13

サーバBからAに入れ替えてアクセスできなくなり、AからBに入れ替えるとまたPINGが通るという状況で。

C:\Users\hana>ping -t 192.168.0.13

192.168.0.13 に ping を送信しています 32 バイトのデータ:
要求がタイムアウトしました。
要求がタイムアウトしました。
要求がタイムアウトしました。
要求がタイムアウトしました。
192.168.0.13 からの応答: バイト数 =32 時間 =7ms TTL=63　→元に戻した
192.168.0.13 からの応答: バイト数 =32 時間 =2ms TTL=63
192.168.0.13 からの応答: バイト数 =32 時間 =4ms TTL=63
192.168.0.13 からの応答: バイト数 =32 時間 =2ms TTL=63
192.168.0.13 からの応答: バイト数 =32 時間 =2ms TTL=63
192.168.0.13 からの応答: バイト数 =32 時間 =2ms TTL=63

同じセグメントのパソコンからだと即反映されるので、どこかでarpキャッシュを持っているやつがいるなぁと思いセグメントの境に設置してあるCisco L3で確認すると

>show arp
Internet  192.168.0.12            1   5cba.2c5a.aaaa  ARPA   Vlan11110
Internet  192.168.0.13           35   5cba.2c59.bbbb  ARPA   Vlan11110
Internet  192.168.0.17           10   5cba.2c59.bbbb  ARPA   Vlan11110

ここで、clear arp します

>clear arp

違うセグメントpingが通るようになり、arpテーブルを確認してみると

>show arp
Internet  192.168.0.12            0   5cba.2c5a.aaaa  ARPA   Vlan11110
Internet  192.168.0.13            0   5cba.2c5a.aaaa  ARPA   Vlan11110
Internet  192.168.0.17            0   5cba.2c59.bbbb  ARPA   Vlan11110

IPエイリアスでうまくアクセスできない場合、L3やルータなどで、arpキャッシュが残っているかもしれません。

備考　IPエイリアス設定では、元々設定していたMACアドレス同じMACアドレスで登録されます。物理的に同じNICに、２つIPアドレスを設定しているので、当たり前といえば当たり前ですね。

サーバ管理 iLO5

fstab chkdskが走らないようにする

長期間サーバを稼働し、メンテナンスで再起動した際に、自動的にchkdskが走って、1時間ほどサーバが起動しなくなるなどという事態が発生する事があるので、起動時に自動的にchkdskが走らないようにする。

# <file system> <mount point>   <type>  <options>       <dump>  <pass>
# / was on /dev/sda1 during installation
UUID=bsvasd4-faa1-40c3-89c3-6llllsba8d /               ext4    errors=remount-ro 0       0

最後 <pass> を 0とする

※1だと、chkdskが走る

DBのバックアップスクリプト

Databaseを毎日バックアップするスクリプト

毎日このスクリプトを実行するのはCRONに登録する

echo "バックアップ実行"
TDAY=`date +%Y-%m-%d`
/usr/lib/postgresql/15/bin/pg_dumpall -p 5432 -g -U postgres > /var/local/pgsql/backup/$TDAY.group &
/usr/lib/postgresql/15/bin/pg_dump -p 5432 -U postgres  -F c -b -f "/var/local/pgsql/backup/$TDAY.testdb" -l testdb

#実行結果
ls /var/local/pgsql/backup/$TDAY*

#古いバックアップデータを削除
TMDAY1=`date -d '38 days ago' +%Y-%m-%d`
rm  /var/local/pgsql/backup/$TMDAY1*


#古いログファイルを削除
rm  /var/local/pgsql/log/postgresql-$TMDAY1

pg_dumpall オプション

pg_dumpall [connection-option…] [option…]

クラスタの全てのPostgreSQLデータベースを、1つのスクリプトファイルに書き出す（”ダンプする”）ためのユーティリティ

-g グローバルオブジェクト（ロールとテーブル空間）のみをダンプし、データベースのダンプを行いません。

-f 出力を指定したファイルに送ります。これが省略されると標準出力が使用されます。

pg_dump オプション

Postgres データベースをスクリプトファイルに抽出する

pg_dump [connection-option…] [option…] [dbname]

-F c 出力形式を選択します。pg_restoreへの入力に適したカスタム形式アーカイブを出力します。

-b ラージオブジェクトをダンプに含めます。

-f 出力を指定のファイルに送ります。

ログファイル・バッチファイル置き場

ログやバックアップファイルを置く場所（自分で好きな場所を設定して構わない）

/var/local/pgsql

バッチファイルを置く場所

/usr/local/sbin/pg_bat

mkdir /var/local/pgsql
mkdir /var/local/pgsql/backup
mkdir /var/local/pgsql/log

chown -R postgres:postgres /var/local/pgsql
chmod -R 775 /var/local/pgsql

mkdir /usr/local/sbin/pg_bat
chown -R postgres:postgres /usr/local/sbin/pg_bat
chmod -R 775 /usr/local/sbin/pg_bat

pg_restoreする時

pg_dumpall にてログインロールをバックアップ、pg_dump にて、データベースをバックアップした時の注意

※ユーザ／ログインロール　から復元する。でないと、データベースを復元した時に登録されていないユーザのアクセス権は戻らない

ユーザの復元はpsql を利用する。 pg_dumpall はSQLでバックアップファイルが作成される。

Linuxでのコマンドならこんな感じ

#su - postgres
$/usr/lib/postgresql/15/bin/psql -p 5432 template1 < /home/postgres/2023-11-03.group

※Windowsにインストールした Postgresをリストアする時

"C:\Program Files\PostgreSQL\15\bin\psql.exe" -p 5432 -U postgres template1 < C:\Users\hanako\Desktop\2023-11-22.group

データベースを復元する

#su - postgres
$ /usr/lib/postgresql/15/bin/pg_restore -p 5432 -U postgres -d majp /home/postgres/2023-11-03.majp

SSDが故障した時

RAID1でミラーリングしている場合、SSD1本に障害が発生した時の復元手順

プライマリ障害時にスタンバイをプライマリへ

ストリーミングレプリケーションしているサーバを復元する場合

バックアップファイルをスタンバイサーバに残す

sdbに増設したHDDのマウント方法

nfsでホストのハードディスクをマウントする

rsyncでバックアップする