PostgreSQL12導入手順

概要

• 背景
• 前提条件
• インストール
• 接続
• その他接続設定の変更
• データの準備
• データ操作に関する設定
• バックアップ設定
• リカバリ設定
• レプリケーション設定
• ログ設定
• 監視

背景

• ポスグレに初めて触れることになったので、事前学習として動かした際のメモ。
• 基本部分の動作確認が目的。

前提条件

SW

• Ubuntu18.04LTS
• PostgreSQL 12

作業ディレクトリ

• $PGDATA=/var/lib/postgres/12/data

ユーザ

• 初期のpostgresに、管理者権限を付与

インストール

パッケージリストとパッケージを最新に

apt-get -y update && apt-get -y upgrade

関連ツールのインストール

apt-get install -y vim \
procps iputils-ping net-tools \
wget gnupg lsb-release 

postgresqlの最新バージョンをリポジトリに追加＆インストール

echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" > /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - 
apt-get -y update && apt-get -y upgrade
apt-get -y install tzdata postgresql-12
gpasswd -a postgres sudo

パス含む環境変数の設定

export PATH=${PATH}:/usr/lib/postgresql/12/bin
export PGDATA=/var/lib/postgresql/12/data
export POSTGRES_PASSWORD=postgres

データベースクラスタの初期化

• 以降は、postgresユーザで操作する

su postgres
rm -rf /var/lib/postgresql/12/main
initdb -no-locale

接続設定

事前準備

• ipの確認

  ifconfig
  

接続の許可

• listen_addresses = '*'で外部ホストからの接続を許可。

cd ${PGDATA}
#   バックアップ
cp ./postgresql.conf ./postgresql.conf_org
#   postgresql.confの変更
sed s/"#listen_addresses = 'localhost'"/"listen_addresses = '*'"/ -i ./postgresql.conf && \
sed s/"#post = 5432"/"post = 5432"/ -i ./postgresql.conf

認証設定

• 接続するホスト、及びその認証方式を指定。

  中身のデータベースやユーザ、IP等はPJに応じて変更する

  • 設定する際は、IPの範囲が「小⇨大」になるように設定する。（有効なものが２つ以上ある場合、上が優先されるため）

• 主な認証方式

  認証方式	説明
  trust	無条件で接続を許可
  md5	ユーザ、パスワードを認証して接続許可

cd ${PGDATA}
#   バックアップ
cp ./pg_hba.conf ./pg_hba.conf_org
#   pg_hba.confの変更
hba_parameter=""
hba_parameter="${hba_parameter}# PostgreSQL Client Authentication Configuration File \n" && \
hba_parameter="${hba_parameter}# =================================================== \n" && \
hba_parameter="${hba_parameter}local all all              trust \n" && \
hba_parameter="${hba_parameter}host  all all 172.20.0.0/24  trust \n" && \
hba_parameter="${hba_parameter}host  all all 172.20.0.0/16  md5"
echo ${hba_parameter}> ./pg_hba.conf

起動

pg_ctl -D $PGDATA -l logfile start

起動確認

#   プロセスの確認
ps faxw | grep postgres
#   SQLの実行確認
psql -U postgres -c "SELECT 1"

外部からの接続確認

• pg_hba.confで認証設定したホストから接続を確認する。

#   SQL実行の確認
psql -h [IPアドレス] -U postgres -c "SELECT 1"

---

その他接続設定の変更（必要なら）

---

接続数

• 検討事項
  • 同時接続するクライアント数、スタンバイ、バックアップ実行箇所
  • 同時接続数と消費メモリ・I/Oのトレードオフ

max_connections = 100   #   デフォルト
superuser_reserved_connections = 3  #    デフォルト(高負荷がかかっている時にも管理者は繋げるようにしとく)

データの準備

データベースの作成

• オプションはこちら
  • デフォルトでは、template1から複製される。

psql -U postgres -d postgres

--  データベース一覧の確認
\l
--  データベース作成
CREATE DATABASE test_db;
--  データベース変更
\c test_db

テーブルの作成

--  サンプル
CREATE TABLE test_tbl (
    id integer, 
    name varchar(20), 
    PRIMARY KEY (id),
    CONSTRAINT id_check CHECK (id > 0)
);

データの挿入

• 初期データ作成スクリプト（とりあえず10万行作成）

#   import_init_data.sh
#!/bin/bash

echo "start 'insert init data'"
start_time=`date +%s`

# initialize
id=0
name='tanaka'
insert_values=""
insert_file=${PWD}/init_data.csv
record_num=100000
write_timing=`expr ${record_num} / 100`

# create import file
echo "id, name" > ${insert_file}
for i in `seq 1 ${record_num}`
do
  id=`expr ${id} + 1`
  insert_values="${insert_values}${id}, '${name}${id}'\n"
  if [ `expr ${id} % ${write_timing}` = 0 ]; then
    echo -e -n ${insert_values} >> ${insert_file}
    echo "${id} records are created."
    insert_values=""
  fi
done

# create sql
echo "begin importing."
sql="COPY test_tbl FROM '${insert_file}' with csv header;"

# insert
psql -d test_db -U postgres -c "$sql"
end_time=`date +%s`

# message
SS=`expr ${end_time} - ${start_time}`
HH=`expr ${SS} / 3600`
SS=`expr ${SS} % 3600`
MM=`expr ${SS} / 60`
SS=`expr ${SS} % 60`
echo "elapsed time: ${HH}:${MM}:${SS}"
rm ${insert_file}
echo 'finish!'

確認

psql -d test_db -U postgres -c "SELECT COUNT(*) FROM test_tbl;"

データ操作に関する設定（必要なら）

メモリ設定

共有バッファ

• shared_buffersは、物理メモリの25%程度が推奨されている。

#   postgresql.conf
shared_buffers=1２８MB    #   デフォルト

作業メモリ

• ソートやJOINの実行時消費メモリに応じて、必要に応じて大きくする
  • EXPLAIN ANALYZEで確認して、外部ディスク利用をしていたら拡大が必要。

#   postgresql.conf
work_mem=4MB    #   デフォルト

問い合わせ計画

プランナコスト定数

• seq_page_cost

  • シーケンシャルスキャンのコスト係数。

    これは１のまま固定し、他をseq_page_costの相対値として調整する。

• random_page_cost

  • ランダムアクセスのコスト係数。基本は４。
    • SSDの場合は、１にする。

• effective_cache_size

  • コスト計算する際の指標として使用される。物理メモリの50%程度が望ましい。
    • 実際に50%使われるわけではない。

#   postgresql.conf
seq_page_cost = 1.0 #   デフォルト（固定する）
random_page_cost = 4.0  #   デフォルト
effective_cache_size=4GB    #   デフォルト

バックアップ設定

WAL保持設定

• wal_level

  • WALに残す情報レベル。基本はreplica

• wal_buffers

  • メモリ上に保持するWALサイズ上限。-1は、shared_buffersの1/32。

    多数のクライアントによる同時コミットが発生する場合は、数字変更してもいいかも

• wal_max_size

  • 保持するWAL物理ファイル合計の上限。
    • 上限を超えると再利用（古いWALファイルから上書き）される

• wal_min_size

  • WALファイルの上書きを防ぐ閾値。
    • max_wal_sizeと同じにしておけば問題ない

#   postgresql.conf
wal_level=replica   #   デフォルト
wal_buffers=-1  #   デフォルト
max_wal_size=1GB    #   デフォルト
min_wal_size=1GB

WALアーカイブ設定

• archive_mode

  • アーカイブモードの設定。基本はスタンバイ（レプリケーションする場合）かプライマリでon。
    • アーカイブからリストアする際のログも残したい場合は、always

• archive_command

  • 16MBになったWALファイルをアーカイブする際に実行するコマンド。

    %pでWAL物理ファイルの相対パス、%fでWAL物理ファイル名を表す。

    • 実行できない場合、WALファイルが溜まり続け、アーカイブ前WALの上書きによるWAL消失が発生する可能性がある。

  • 実環境では、別ディスクにWALアーカイブを保存すべき

• (archive_timeout)

  • アーカイブを強制実施する時間間隔。基本は0。
    • 少量の更新が続く場合、なかなかWALアーカイブが実施されないことがある。そのケースが存在する場合は、時間を指定する。

#   postgresql.conf
archive_mode = on       #   デフォルトはoff
archive_command =  'test ! -f [アーカイブディレクトリ]/%f && cp %p [アーカイブディレクトリ]/%f'

認証設定

• バックアップ時は、replication権限で実行される。
  • allでは実行されないので注意。

#   pg_hba.confへ追加
local replication postgres              trust   #   localでバックアップ実行する場合
host replication postgres  [IPアドレス/サブネットビット数] trust   #   localでバックアップ実行する場合

バックアップ実行

• -h：ホスト指定

• -D：出力ディレクトリ指定

• -f：フォーマット。tでtar形式

• -z：gzipによる圧縮形式。

• -P：プログレス表示

• -R：standby.signal（スタンバイ起動時に必要なファイル）作成

  • 実行時点のディスク＋WAL情報をバックアップできる

    実環境では、別ディスクにWALアーカイブを保存すべき

pg_basebackup -h [ホスト名] -D [バックアップディレクトリ] -F t -z -P [-R]

リカバリ設定

WALリカバリ設定

• restore_command

  • リカバリ前に、アーカイブファイル取得するために実行するコマンド。

    archive_commandで指定したディレクトリからコピーする。

#   postgresql.conf
restore_command = 'cp [アーカイブディレクトリ]/%f %p'

リカバリ実行

• basebackup＋WALアーカイブファイルが必要
  • 厳密にリカバリするなら、障害直前のWALファイルも（16MBいっぱいにならないとWALアーアイブが実行されない
    • 対策としては、以下が挙げられる
      • archive_timeoutで短い時間で定期的にWALアーカイブを作る
      • スタンバイを立てて、障害が起きたらスタンバイ側のWALをリカバリに使用する

#   dataディレクトリ内綺麗に
rm -rf ${PGDATA}/*
#   basebackupの展開
tar xvzf [base.tar.gzのパス]
#   古いWALの削除して、最新を配置
rm -rf ${PGDATA}/pg_wal/*
#   standby.signalの作成（ない場合）
touch ${PGDATA}/standby.signal
#   リカバリ（recovery.confのコマンドでWAL含めてリカバリする）
pg_ctl start

レプリケーション設定

共通設定

• wal_level
  • WALに残す情報レベル。基本はreplica

#   postgresql.conf
wal_level=replica   #   デフォルト

マスタサーバ設定

postgresql.conf

• synchronous_standby_names

  • 同期するスタンバイサーバの指定をする。名前は、スタンバイrecovery.confの「applicaiton_name」
    • 指定したもののうち２つなど、柔軟な指定も可能

• synchronous_commit

  • トランザクションの成功を返すタイミング。基本はon
    • onで、synchronous_standby_namesが空でない場合は、スタンバイのディスク書き込みまで確認する

• max_wal_senders

  • 起動するwal_senderの数。スタンバイの数+1にする（pg_basebackup用の保険）。

• max_replication_slots

  • 保持する最大のレプリケーションスロット（スタンバイへ同期するためのWAL群）のファイル数。

• wal_sender_timeout

  • レプリケーション接続エラーを判定する時間。基本はそのまま。

    短くすると、早期の障害検知等に役立つ。

#   postgresql.conf
synchronous_standby_names = 'standby1, standby2'
synchronous_commit=on   #   デフォルト
max_wal_senders=10  #    デフォルト
max_replication_slots=10    #   デフォルト
wal_sender_timeout=60s  #   デフォルト

pg_hba.conf

#   レプリケーション設定を追加
host replication postgres [スタンバイIPアドレス] trust

スタンバイサーバ設定

• primary_conninfo

  • マスタサーバに接続する文字列を指定する。

    psqlのオプションを参考に、key-value形式で記載する。

• promote_trigger_file

  • 昇格判定に使うファイル名。任意で指定する。

• hot_standby

  • スタンバイモード時の読み込み許可を指定する。基本はon

• wal_receiver_timeout

  • スタンバイが、プライマリとのレプリケーション接続不可を判定する時間。基本はデフォルトの60秒。

#   postgresql.conf
primary_conninfo='host=172.20.0.2 port=5432　application_name＝standby1 username=postgres password=???? connect_timeout=10'
promote_trigger_file='${PGDATA}/trigger.txt'    #   任意
hot_standby=on  #   デフォルト
wal_receiver_timeout=60s    #   デフォルト

レプリケーション確認

#   walreceiverプロセス確認
ps aux
#   スタンバイ状況（プライマリで実行）
psql -c "SELECT application_name, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, sync_priority, sync_state FROM pg_stat_replication;"

昇格（障害発生時のフェイルオーバー対応）

• 「pg_ctl promote」コマンド

  • 手動による昇格。コマンド実行後、すぐにスタンバイを昇格できる。

• 「promote_trigger_file」を作成

  • 自動昇格。定期的なping等でプライマリの生存確認しておけば、障害発生時に自動で昇格できる。

    ただし、トリガーファイルチェックは定期的に行われるため、ファイル作成後に多少の時間差あり（デフォルト60秒）。

#   手動
pg_ctl promote
#   自動
touch [promote_trigger_file]

昇格時Tips

• 下記が合わせて必要になる
  • IP変更（プライマリへの問い合わせを受けるため）
    • 新プライマリ（旧スタンバイ）：旧プライマリIP
    • 新スタンバイ（旧プライマリ復旧or新サーバ）：旧スタンバイIP
  • postgresql.conf変更（レプリケーション処理変更に対応するため）
    • 新プライマリ（旧スタンバイ）：旧プライマリ設定＋レプリケーション対象から旧スタンバイを削除（旧プライマリ復旧準備できたらレプリケーション対象に追加）
    • 新スタンバイ（旧プライマリ復旧or新サーバ）：旧スタンバイ設定
  • pg_hba.conf変更（レプリケーション許可対応）
    • 新プライマリ（旧スタンバイ）：旧スタンバイ以外のレプリケーション許可を追加
    • 新スタンバイ（旧プライマリ復旧or新サーバ）：なし

ログ設定

Postgreのログ出力設定

• log_destination

  • ログの出力先。デフォルトは標準出力。

    システムログと一緒に出力して運用すると良い。

• log_min_error_statement

  • ログ出力するレベル。

    必要に応じて出力レベルを下げる。

• log_lock_waits

  • deadlock_timeoutを超えたロック待ち時間のクエリをデッドロックと判定してログに出力する。

#   postgresql.conf
log_destination='syslog'
log_min_error_statement=error
log_lock_waits=on

参考

参考サイト

• PostgreSQL公式