本ページは広告が含まれています。気になる広告をクリック頂けますと、サーバ運営費になります(^^
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
こちらも初期リリースから５年がサポート期間です。
PostgreSQLのパッケージ管理は優れているのでapt-getで入るpostgresqlはサポート期間はDebian10のサポート期限2024年までと期待を寄せたいところですが、パッケージの説明を見ると、
オブジェクトリレーショナル SQL データベース (サポート版)
パッケージ: postgresql (11+200+deb10u5)
本メタパッケージはサポートされる最新版の PostgreSQL データベースサーバに常 に依存しています。
と書いてあるので、やっぱり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-timesyncdapt調整
今回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 contribapt-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周辺の拡張モジュールや便利ツール
設定ファイルの場所
/etc/postgresql/11/mainpostgresql.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*#’ ０字以上の空白文字の後のコメントアウト行を排除
egrep -vE ‘^\s*$’ ０字以上の空白行を排除
# 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' froPostgreSQLをリスタートして設定を読み込ませる
# /etc/init.d/postgresql restart
Restarting postgresql (via systemctl): postgresql.service.クライアントへのpgAdminのインストール
私が管理しているパソコンはWindowsです。ここはGUIで管理した方が楽なので、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 md5postgresql再起動
# /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．プライマリ側にレプリケーション用ユーザ作成
PgAdminから、ログイン／グループロール でユーザを作成する
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 md53.プライマリ側postgres.confの設定
どれだけの情報がWALに書かれるか
wal_level = replicaトランザクションのコミットがクライアントに「成功」の報告を返す前に、WALレコードがディスク上に書き込まれるまで待つかどうかの指定（トランザクションのコミットはローカルディスクへの吐き出しのみを待機）
synchronous_commit = localarchive_modeが有効な場合、archive_commandを設定することにより、完了したWALセグメントはアーカイブ格納領域に送信されます。
archive_mode = off同時接続を受ける接続最大値
max_wal_senders = 3pg_walに保持され続けるセグメントの最小値
wal_keep_segments = 16サーバが使用できるレプリケーションスロット(26.2.6参照)の最大数
max_replication_slots = 3同期レプリケーションをサポート可能なスタンバイサーバのリスト
synchronous_standby_names = '*'リカバリの最中に接続し、そして問い合わせを実行できるか否か
hot_standby = onホットスタンバイがスタンバイサーバ上で現在処理を行っている問い合わせについて、プライマリーまたは上位サーバにフィードバックを送るか否か
hot_standby_feedback = offここで、一度Postgresに設定を読み込ませる
# /etc/init.d/postgresql reload3.プライマリサーバにてスロット名の作成
SELECT * FROM pg_create_physical_replication_slot('db_test');※スロットを削除したい時はこちら
SELECT * FROM pg_drop_replication_slot('db_test')※登録してあるスロット名を表示したい場合
SELECT slot_name, active FROM pg_replication_slots;
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'Postgres15でも postgresql.auto.confが出来てた
Debian で、Postgres15で試していますが、上記pg_basebackupを行ったら、mainの中に上記と同じようなファイルの位置づけで postgresql.auto.conf ができてました。書いてある内容は同じです。
接続設定の情報、また、指定があればpg_basebackupが使用しているレプリケーションスロットの情報を記録するので、ストリーミングレプリケーションは後で同じ設定を使用します。
–write-recovery-conf
オプションを付けた事で自動的に生成されたようです。
スタンバイサーバのPostgrfeSQLをスタートさせる
root@postgres-test:/var/lib/postgresql/11/main# /etc/init.d/postgresql start
[ ok ] Starting postgresql (via systemctl): postgresql.service.postgres15でも無事にレプリケーションが始まりました
※以下の「スタンバイが起動しない-権限の問題」を解消後、Postgres15で稼働始めました。
2023-05-16 15:59:43.078 JST [198637] LOG: PostgreSQL 15.3 (Debian 15.3-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit を起動しています
2023-05-16 15:59:43.080 JST [198637] LOG: IPv4アドレス"0.0.0.0"、ポート57481で待ち受けています
2023-05-16 15:59:43.080 JST [198637] LOG: IPv6アドレス"::"、ポート5432で待ち受けています
2023-05-16 15:59:43.081 JST [198637] LOG: Unixソケット"/var/run/postgresql/.s.PGSQL.57481"で待ち受けています
2023-05-16 15:59:43.088 JST [198640] LOG: データベースシステムは中断されました: 2023-05-16 14:50:30 JST まで動作していたことは確認できます
2023-05-16 15:59:43.177 JST [198640] LOG: スタンバイモードに入ります
2023-05-16 15:59:43.183 JST [198640] LOG: REDOをA/EE000028から開始します
2023-05-16 15:59:43.184 JST [198640] LOG: A/EE000138 でリカバリの一貫性が確保されました
2023-05-16 15:59:43.184 JST [198637] LOG: データベースシステムはリードオンリー接続の受け付け準備ができました
2023-05-16 15:59:43.203 JST [198641] LOG: プライマリのタイムライン1の A/EF000000からでWALストリーミングを始めます特にスロットの指定はなくても大丈夫っぽい
スタンバイが起動しない-権限の問題
起動しないログ 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になってる！
メイン側に書き込みしてみる
メインに書き込んだデータはすぐにreplica へ反映されていました。
2023-09-16 08:48:29.686 JST [560939] LOG: リスタートポイント開始: time
2023-09-16 08:48:29.895 JST [560939] LOG: リスタートポイント完了: 3個のバッファを出力 (0.0%); 0個のWALファイルを追加、0個を削除、0個を再利用; 書き出し=0.202秒, 同期=0.003秒, 全体=0.210秒; 同期したファイル=3, 最長=0.002秒, 平均=0.001秒; 距離=6 kB, 予測=14746 kB
2023-09-16 08:48:29.895 JST [560939] LOG: リカバリ再開ポイントは22/1E0019D8です
2023-09-16 08:48:29.895 JST [560939] 詳細: 最後に完了したトランザクションはログ時刻 2023-09-16 08:43:37.033121+09 のものですスタンバイ側に書き込みしてみる
スタンバイ側に書き込みすると
ERRORが出て書き込みできません。
2023-09-16 08:55:19.282 JST [565831] postgres@jp_bak1 ERROR: リードオンリーのトランザクションでは INSERT を実行できません
2023-09-16 08:55:19.282 JST [565831] postgres@jp_bak1 文: INSERT INTO dplus.tl_message (
id, userid, noticeid, status, statusdescription) VALUES (
'testtest2'::text, 'test'::character varying, 'testtesttesttest'::character varying, '93410'::integer, 'O.K.'::text)
returning id,userid;あくまで main → replica への一方通行でデータは書かれます。
バージョンの不整合
2023-10-26 17:04:12.970 JST [87863] postgres@template1 ヒント: このデータベース内でデフォルトの照合順序を使用している
全てのオブジェクトを再構築して、ALTER DATABASE template1 REFRESH COLLATION VERSIONを実行するか、正しいバージョンのライ
ブラリを用いてPostgreSQLをビルドしてください。プライマリもセカンダリも、
apt-get update
apt-upgrade
して、すべて最新にしたつもりが、なぜか、このようなメッセージが。
PostgreSQL は、データベースの照合順序 (collation)を計算するために libc または ICU を使用します。データベースを作成した時点での libc または ICU のバージョンと、現在のバージョンが異なる場合、インデックスが破損する危険性があるためこのような警告が表示されます。
https://zenn.dev/ciffelia/articles/postgres-collation-version-mismatch
私のケースでは、OS のアップデートで libc のバージョンが v2.31 から v2.36 に変わったためこのような警告が表示されていたようです。
プライマリはDebian 11.8
スタンバイはDebian 12.2
でOSバージョンの違いによりこのような事がおこってしまうようです。
