it技術:データベース:postgresql:運用
差分
このページの2つのバージョン間の差分を表示します。
次のリビジョン | 前のリビジョン次のリビジョン両方とも次のリビジョン | ||
it技術:データベース:postgresql:運用 [2019/04/17 15:15] – [checkpoint_timeout] yajuadmin | it技術:データベース:postgresql:運用 [2020/01/24 17:08] – [VACUUM実行確認] yajuadmin | ||
---|---|---|---|
行 4: | 行 4: | ||
^Windows版の未対応ツール^^ | ^Windows版の未対応ツール^^ | ||
^ツール名^内容^ | ^ツール名^内容^ | ||
- | |pg\_rman|バックアップ/ | + | |pg_rman|バックアップ/ |
- | |pg\_statsinfo|PostgreSQLのサーバ運用状況を定期的に取得する| | + | |pg_statsinfo|PostgreSQLのサーバ運用状況を定期的に取得する| |
- | |pg\_mons|zabbixを使ったサーバ運用状況の監視| | + | |pg_mons|zabbixを使ったサーバ運用状況の監視| |
- | [[https:// | + | * [[https:// |
+ | * [[https:// | ||
===== チューニング ===== | ===== チューニング ===== | ||
代表的なチューニング項目を記す。\\ | 代表的なチューニング項目を記す。\\ | ||
行 109: | 行 110: | ||
* [[https:// | * [[https:// | ||
- | ===== ログ設定 ===== | + | |
- | * [[https://www.ashisuto.co.jp/db_blog/article/20151117_logging_parameter.html|障害発生に備えて設定すべき3つのログ関連パラメーター]] | + | ==== wal_keep_segments |
+ | バックアップ後のpg_logに下記のエラーが発生していました。 | ||
+ | < | ||
+ | 2019-08-06 02:18:35 JST ERROR: | ||
+ | 要求された wal セグメント * はすでに削除されています。 | ||
+ | </ | ||
+ | |||
+ | 対処として、wal_keep_segmentsをゼロ以外の値に設定する。\\ | ||
+ | これは、ストリームの危険性が追いつかなくなるのを防ぐためです。\\ | ||
+ | ただしpg_basebackupを使用する場合は、 – checkpoint | ||
+ | https:// | ||
+ | |||
+ | < | ||
+ | # | ||
+ | ↓ | ||
+ | wal_keep_segments | ||
+ | |||
+ | backup.bat の見直し | ||
+ | pg_basebackup -U postgres -D " | ||
+ | ↓ | ||
+ | pg_basebackup -U postgres -D " | ||
+ | </ | ||
+ | ===== データベース初期化 ===== | ||
+ | ==== 全てのデータベース初期化 ==== | ||
+ | コマンドプロンプトから実行する。initdb.exeはPostgreSQLのBINフォルダにある。 | ||
+ | |||
+ | < | ||
+ | >initdb --locale=C --encoding=UTF-8 -D " | ||
+ | </ | ||
+ | |||
+ | * [[https://blogs.yahoo.co.jp/yukigaku0313/21115869.html|PostgreSQL インストールのトラブルシューティング]] | ||
+ | * [[http:// | ||
+ | |||
+ | === Permission denied === | ||
+ | initdb: ディレクトリ " | ||
+ | |||
+ | Userに変更にチェックを追加 することで回避した。\\ | ||
+ | 他にもrunasコマンドを使用する(データベースクラスタの作成過程でpostgresプロセスを起動しますが、このプロセスは管理者権限では実行できないため)\\ | ||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ | ==== 特定のデータベース初期化 ==== | ||
+ | スーパーユーザー(例 postgres)にてデータベースを削除する。\\ | ||
+ | [[https:// | ||
+ | |||
+ | < | ||
+ | drop database test; | ||
+ | </ | ||
===== バックアップとリストア ===== | ===== バックアップとリストア ===== | ||
* [[https:// | * [[https:// | ||
行 182: | 行 230: | ||
* [[https:// | * [[https:// | ||
- | pg_dumpでオンライン・バックアップを行う際の手順 | + | pg_basebackupでオンライン・バックアップを行う際の手順 |
- pg_start_backup()を実行 | - pg_start_backup()を実行 | ||
行 195: | 行 243: | ||
<code bash コマンドプロンプト> | <code bash コマンドプロンプト> | ||
- | > pg_basebackup -U postgres -D " | + | > pg_basebackup -U postgres -D " |
</ | </ | ||
+ | |||
+ | ^Option^説明^ | ||
+ | |-D|ベースバックアップ出力先パスを指定。出力先は空でなければならない。 \\ ディレクトリが存在しなければ作ってくれる。| | ||
+ | |-F t|出力する形式。tはtarで出力する。圧縮するためにはtarの指定が必要| | ||
+ | |-x|ベースバックアップ処理中、データベースが更新された際もファイルシステム上のファイルは更新しないようにする。出力中はメモリ上で頑張る。 \\ (そうすることで、出力ファイルが変な状態になることを防ぐ)| | ||
+ | |-z|gzipで圧縮した状態にする| | ||
+ | |--checkpoint=fast|fast を指定すると、バックアップ開始時のチェックポイント処理は高速になりますが、集中した I/O のために動作中のアプリケーションへの性能の影響が大きくなります。 spread ではチェックポイントはゆっくり実行されるためアプリケーションへの影響は小さいですが、バックアップに時間がかかります。| | ||
+ | |||
-Dオプションでバックアップ保存先を指定する。サイズが大きい場合はtar-gz圧縮する。だいたい5分以内で完了する。 | -Dオプションでバックアップ保存先を指定する。サイズが大きい場合はtar-gz圧縮する。だいたい5分以内で完了する。 | ||
行 225: | 行 281: | ||
* [[https:// | * [[https:// | ||
* [[https:// | * [[https:// | ||
+ | * [[https:// | ||
==== 設定 ==== | ==== 設定 ==== | ||
postgresql.conf で設定する。 | postgresql.conf で設定する。 | ||
行 231: | 行 288: | ||
wal_level = replica | wal_level = replica | ||
archive_mode = on # PostgreSQLによるWALアーカイブ処理を有効にする | archive_mode = on # PostgreSQLによるWALアーカイブ処理を有効にする | ||
- | archive_command = 'copy " | + | archive_command = 'copy " |
</ | </ | ||
※PostgreSQL9.6を使用する場合、archiveとhot_standbyが新しい設定値「replica」に統合\\ | ※PostgreSQL9.6を使用する場合、archiveとhot_standbyが新しい設定値「replica」に統合\\ | ||
- | ※archive_commandのフォルダは先に作成しておく。 | + | ※archive_commandのフォルダは先に作成しておく。\\ |
+ | ※archive_commandで「" | ||
* [[http:// | * [[http:// | ||
行 260: | 行 318: | ||
[[https:// | [[https:// | ||
- | ==== WALアーカイブからのリストア ==== | + | === archive_timeout === |
- | [[http:// | + | archive_timeoutはトランザクションがほとんど発生しない「なぎ」のとき、WALにたまった内容がいつまで経ってもアーカイブされないことを防ぐ。デフォルトでは0(機能無効) |
+ | |||
+ | archive_timeout = 60 とした場合、16MB*60回/ | ||
+ | [[http:// | ||
+ | ==== WALアーカイブの削除 ==== | ||
+ | pg_archivecleanupコマンドで削除する。 | ||
+ | |||
+ | pg_basebackup を実行すると、WALアーカイブのフォルダに拡張子backupが作成される。\\ | ||
+ | これにより指定した拡張子backupより前の不要なWALアーカイブが削除される。 | ||
+ | |||
+ | < | ||
+ | pg_archivecleanup " | ||
+ | </ | ||
+ | |||
+ | [[https:// | ||
+ | |||
+ | === archive_cleanup_command === | ||
+ | recovery.conf設定ファイルにarchive_cleanup_commandを指定するとpg_archivecleanupコマンドの実行後に不要なWALアーカイブを自動で削除してくれる。\\ | ||
+ | [[https:// | ||
+ | |||
+ | <code bat recovery.conf> | ||
+ | restore_command = 'copy " | ||
+ | archive_cleanup_command = ' | ||
+ | </ | ||
+ | |||
+ | しかし、recovery.confはリカバリをする時に必要なので不要なWALアーカイブを削除するためだけに使うわけにはいかない。\\ | ||
+ | よって、不要なWALアーカイブの削除は手動で行うこととする。\\ | ||
+ | ※postgreSQLサービスを再起動すればリカバリが実行され、recovery.confはrecovery.doneになります。 | ||
+ | ==== WALアーカイブからのリカバリ ==== | ||
+ | リストアとリカバリとは区別しないで使うときもあるが、今回は分けました。 | ||
+ | |||
+ | * リストアはバックアップデータを、バックアップを取ったときと同じ状態に物理的に復元すること | ||
+ | * リカバリはリストアしたデータに何かの処理をして最新の状態や正常な状態に復旧させること | ||
+ | |||
+ | === PITRの仕組み | ||
+ | PITR(Point In Time Recovery)は、WALレコード適用によるリカバリが前提となっている。 | ||
+ | | ||
+ | * [[http:// | ||
+ | |||
+ | **WALレコードの適用までの流れ**\\ | ||
+ | リカバリを開始してWALレコードを適用するまでの流れは次のようになっている。 | ||
+ | |||
+ | - pg_controlファイルを読み込む | ||
+ | - recovery.confを読み込む | ||
+ | - backup_labelを読み込む | ||
+ | - pg_controlファイルを更新し、backup_labelを削除する | ||
+ | - 必要なWALを繰り返し適用する | ||
+ | |||
+ | ※backup_labelファイルから適用を開始すべきWALの位置を取得できなかった場合は、pg_controlファイルの情報を元にリカバリを開始します。 | ||
+ | |||
+ | pg_controlファイルは、バイナリファイルなので通常のエディタでは内容を確認できません。そのために pg_controldata コマンドが用意されています。\\ | ||
+ | pg_controldata コマンドで最終チェックポイントのREDO WALファイルが分かる。 | ||
+ | |||
+ | < | ||
+ | pg_controldata -D C: | ||
+ | |||
+ | pg_controlバージョン番号: | ||
+ | カタログバージョン番号: | ||
+ | データベースシステム識別子: | ||
+ | データベースクラスタの状態: | ||
+ | pg_control最終更新: | ||
+ | 最終チェックポイント位置: | ||
+ | 前回のチェックポイント位置: | ||
+ | 最終チェックポイントのREDO位置: | ||
+ | 最終チェックポイントのREDO WALファイル: | ||
+ | 最終チェックポイントの時系列ID: | ||
+ | 最終チェックポイントのPrevTimeLineID: | ||
+ | 最終チェックポイントのfull_page_writes オン | ||
+ | 最終チェックポイントのNextXID: | ||
+ | 最終チェックポイントのNextOID: | ||
+ | 最終チェックポイントのNextMultiXactId: | ||
+ | 最終チェックポイントのNextMultiOffset: | ||
+ | 最終チェックポイントのoldestXID: | ||
+ | 最終チェックポイントのoldestXIDのDB: | ||
+ | 最終チェックポイントのoldestActiveXID: | ||
+ | 最終チェックポイントのoldestMultiXid: | ||
+ | 最終チェックポイントのoldestMulti' | ||
+ | 最終チェックポイントのoldestCommitTsXid: | ||
+ | 最終チェックポイントのnewestCommitTsXid: | ||
+ | 最終チェックポイント時刻: | ||
+ | ログを取らないリレーション向けの偽のLSNカウンタ: | ||
+ | 最小リカバリ終了位置: | ||
+ | 最小リカバリ終了位置のタイムライン: | ||
+ | バックアップ開始位置: | ||
+ | バックアップ終了位置: | ||
+ | 必要なバックアップ最終レコード: | ||
+ | wal_level の設定: | ||
+ | wal_log_hints の設定: | ||
+ | max_connections の設定: | ||
+ | max_worker_processes の設定: | ||
+ | max_prepared_xacts の設定: | ||
+ | max_locks_per_xact の設定: | ||
+ | track_commit_timestamp の設定: | ||
+ | 最大データアラインメント | ||
+ | データベースのブロックサイズ: | ||
+ | ラージリレーションのセグメント当たりのブロック数: | ||
+ | WALブロックのサイズ: | ||
+ | WALセグメント当たりのバイト数: | ||
+ | 識別子の最大長: | ||
+ | インデックス内の最大列数: | ||
+ | TOASTチャンクの最大サイズ: | ||
+ | ラージオブジェクトチャンクのサイズ: | ||
+ | 日付/ | ||
+ | Float4 引数の渡し方: | ||
+ | Float8 | ||
+ | データベージチェックサムのバージョン: | ||
+ | </ | ||
===== VACUUM処理 ===== | ===== VACUUM処理 ===== | ||
PostgreSQLは、追記型アーキテクチャ(MVCC: | PostgreSQLは、追記型アーキテクチャ(MVCC: | ||
行 344: | 行 509: | ||
</ | </ | ||
+ | === 最後にVACUUM/ | ||
+ | <code sql> | ||
+ | SELECT relname, n_live_tup, n_dead_tup, last_vacuum, | ||
+ | FROM pg_stat_all_tables | ||
+ | WHERE schemaname = ' | ||
+ | ORDER BY relname | ||
+ | </ | ||
==== VACUUM手動実行判断 ==== | ==== VACUUM手動実行判断 ==== | ||
デッドタプルが多く、有効なレコードに対するデッドタプルの割合の多いテーブルに VACUUM をするようにしてみてください。 | デッドタプルが多く、有効なレコードに対するデッドタプルの割合の多いテーブルに VACUUM をするようにしてみてください。 |
it技術/データベース/postgresql/運用.txt · 最終更新: 2024/04/24 11:07 by yajuadmin