ユーザ用ツール

サイト用ツール


it技術:データベース:postgresql:運用


差分

このページの2つのバージョン間の差分を表示します。

この比較画面にリンクする

両方とも前のリビジョン 前のリビジョン
次のリビジョン
前のリビジョン
it技術:データベース:postgresql:運用 [2019/07/28 11:33]
yajuadmin [pg_basebackup]
it技術:データベース:postgresql:運用 [2020/04/29 23:15] (現在)
yajuadmin [全てのデータベース初期化]
行 111: 行 111:
   * [[https://qiita.com/U_ikki/items/89b1eea657e47120e3ee|PostgreSQLのチェックポイント処理のチューニング]]   * [[https://qiita.com/U_ikki/items/89b1eea657e47120e3ee|PostgreSQLのチェックポイント処理のチューニング]]
  
 +==== wal_keep_segments ====
 +バックアップ後のpg_logに下記のエラーが発生していました。
 +<code>
 +2019-08-06 02:18:35 JST ERROR:  requested WAL segment 000000010000005A000000D1 has already been removed
 +要求された wal セグメント * はすでに削除されています。
 +</code>
 +
 +対処として、wal_keep_segmentsをゼロ以外の値に設定する。\\
 +これは、ストリームの危険性が追いつかなくなるのを防ぐためです。\\
 +ただしpg_basebackupを使用する場合は、 – checkpoint = fastも忘れないでください。\\
 +https://codeday.me/jp/qa/20190609/972945.html
 +
 +<code>
 +#wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables
 +
 +wal_keep_segments = 128
 +
 +backup.bat の見直し
 +pg_basebackup -U postgres -D "%bkPath%" -F t -x -z
 +
 +pg_basebackup -U postgres -D "%bkPath%" -F t -x -z --checkpoint=fast
 +</code>
 ===== データベース初期化 ===== ===== データベース初期化 =====
 ==== 全てのデータベース初期化 ==== ==== 全てのデータベース初期化 ====
行 125: 行 147:
 initdb: ディレクトリ "D:\Program Files\PostgreSQL\9.6\data"の権限を変更できませんでした:Permission denied initdb: ディレクトリ "D:\Program Files\PostgreSQL\9.6\data"の権限を変更できませんでした:Permission denied
  
-Userに変更チェックを追加 することで回避した。\\+Userに変更チェックを追加 することで回避した。\\
 他にもrunasコマンドを使用する(データベースクラスタの作成過程でpostgresプロセスを起動しますが、このプロセスは管理者権限では実行できないため)\\ 他にもrunasコマンドを使用する(データベースクラスタの作成過程でpostgresプロセスを起動しますが、このプロセスは管理者権限では実行できないため)\\
   * [[https://blog.tpc.jp/2018/05/postgresql-10-install-error.html|PostgreSQL 10 インストール時の不具合解消方法]]   * [[https://blog.tpc.jp/2018/05/postgresql-10-install-error.html|PostgreSQL 10 インストール時の不具合解消方法]]
行 221: 行 243:
  
 <code bash コマンドプロンプト> <code bash コマンドプロンプト>
-> pg_basebackup -U postgres -D "E:\backup" -F t -x -z+> pg_basebackup -U postgres -D "E:\backup" -F t -x -z --checkpoint=fast
 </code> </code>
  
-^オプション^説明^+^Option^説明^
 |-D|ベースバックアップ出力先パスを指定。出力先は空でなければならない。 \\ ディレクトリが存在しなければ作ってくれる。| |-D|ベースバックアップ出力先パスを指定。出力先は空でなければならない。 \\ ディレクトリが存在しなければ作ってくれる。|
-|-F t|出力する形式。tはtarで出力する。|+|-F t|出力する形式。tはtarで出力する。圧縮するためにはtarの指定が必要|
 |-x|ベースバックアップ処理中、データベースが更新された際もファイルシステム上のファイルは更新しないようにする。出力中はメモリ上で頑張る。 \\ (そうすることで、出力ファイルが変な状態になることを防ぐ)| |-x|ベースバックアップ処理中、データベースが更新された際もファイルシステム上のファイルは更新しないようにする。出力中はメモリ上で頑張る。 \\ (そうすることで、出力ファイルが変な状態になることを防ぐ)|
 |-z|gzipで圧縮した状態にする| |-z|gzipで圧縮した状態にする|
 +|--checkpoint=fast|fast を指定すると、バックアップ開始時のチェックポイント処理は高速になりますが、集中した I/O のために動作中のアプリケーションへの性能の影響が大きくなります。 spread ではチェックポイントはゆっくり実行されるためアプリケーションへの影響は小さいですが、バックアップに時間がかかります。|
  
 -Dオプションでバックアップ保存先を指定する。サイズが大きい場合はtar-gz圧縮する。だいたい5分以内で完了する。 -Dオプションでバックアップ保存先を指定する。サイズが大きい場合はtar-gz圧縮する。だいたい5分以内で完了する。
行 258: 行 281:
   * [[https://qiita.com/bwtakacy/items/65260e29a25b5fbde835|PostgreSQLのWALファイル(トランザクションログ)について]]   * [[https://qiita.com/bwtakacy/items/65260e29a25b5fbde835|PostgreSQLのWALファイル(トランザクションログ)について]]
   * [[https://ja.wikipedia.org/wiki/%E3%83%AD%E3%82%B0%E5%85%88%E8%A1%8C%E6%9B%B8%E3%81%8D%E8%BE%BC%E3%81%BF|ログ先行書き込み - wikipedia]]   * [[https://ja.wikipedia.org/wiki/%E3%83%AD%E3%82%B0%E5%85%88%E8%A1%8C%E6%9B%B8%E3%81%8D%E8%BE%BC%E3%81%BF|ログ先行書き込み - wikipedia]]
 +  * [[https://qiita.com/kimullaa/items/e7bed14aaa680126b81a|PostgreSQL WALログの仕組みとタイミングを理解したい]]
 ==== 設定 ==== ==== 設定 ====
 postgresql.conf で設定する。 postgresql.conf で設定する。
行 294: 行 318:
 [[https://books.google.co.jp/books?id=D3JqBAAAQBAJ&printsec=frontcover&hl=ja&source=gbs_ge_summary_r&cad=0#v=onepage&q&f=false|内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 - 本]] [[https://books.google.co.jp/books?id=D3JqBAAAQBAJ&printsec=frontcover&hl=ja&source=gbs_ge_summary_r&cad=0#v=onepage&q&f=false|内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 - 本]]
  
 +=== archive_timeout ===
 +archive_timeoutはトランザクションがほとんど発生しない「なぎ」のとき、WALにたまった内容がいつまで経ってもアーカイブされないことを防ぐ。デフォルトでは0(機能無効)
 +
 +archive_timeout = 60 とした場合、16MB*60回/時*24時間=23040MB/日≒23GB/日 が発生しうる。
 +[[http://dbnote.web.fc2.com/note_param_pitr_walarchive.html|archive_timeoutの意味]]
 ==== WALアーカイブの削除 ==== ==== WALアーカイブの削除 ====
 pg_archivecleanupコマンドで削除する。 pg_archivecleanupコマンドで削除する。
  
 pg_basebackup を実行すると、WALアーカイブのフォルダに拡張子backupが作成される。\\ pg_basebackup を実行すると、WALアーカイブのフォルダに拡張子backupが作成される。\\
-これにより指定した拡張子backupより前の不要なWALアーカイブ削除される。+これにより指定した拡張子backupより前の不要なWALアーカイブ削除される。
  
 <code> <code>
行 318: 行 347:
 よって、不要なWALアーカイブの削除は手動で行うこととする。\\ よって、不要なWALアーカイブの削除は手動で行うこととする。\\
 ※postgreSQLサービスを再起動すればリカバリが実行され、recovery.confはrecovery.doneになります。 ※postgreSQLサービスを再起動すればリカバリが実行され、recovery.confはrecovery.doneになります。
-==== WALアーカイブからのリストア ==== +==== WALアーカイブからのリカバリ ==== 
-[[http://ossfan.net/setup/postgresql-37.html|PostgreSQL 9.6.3で時刻指定のPITR(Point In Time Recovery)を実行する]]+リストアとリカバリとは区別しないで使うときもあるが、今回は分けました。 
 + 
 +  * リストアはバックアップデータを、バックアップを取ったときと同じ状態に物理的に復元すること 
 +  * リカバリはリストアしたデータに何かの処理をして最新の状態や正常な状態に復旧させること 
 + 
 +=== PITRの仕組み === 
 +PITR(Point In Time Recovery)は、WALレコード適用によるリカバリが前提となっている。 
 +  [[http://ossfan.net/setup/postgresql-37.html|PostgreSQL 9.6.3で時刻指定のPITR(Point In Time Recovery)を実行する]] 
 +  * [[http://blog.poppypop.mydns.jp/2016/03/03/postgresql_onlinebackup_recovery/|PostgreSQLのオンラインバックアップとリカバリ]] 
 + 
 +**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ファイルが分かる。 
 + 
 +<code> 
 +pg_controldata -D C:\PostgreSQL\9.6\data 
 + 
 +pg_controlバージョン番号:                         960 
 +カタログバージョン番号:                           201608131 
 +データベースシステム識別子:                       6599999130305146812 
 +データベースクラスタの状態:                       運用中 
 +pg_control最終更新:                               2019/07/28 20:58:08 
 +最終チェックポイント位置:                         0/B01B140 
 +前回のチェックポイント位置:                       0/B01B060 
 +最終チェックポイントのREDO位置:                   0/B01B108 
 +最終チェックポイントのREDO WALファイル:    00000004000000000000000B 
 +最終チェックポイントの時系列ID:                   4 
 +最終チェックポイントのPrevTimeLineID:   4 
 +最終チェックポイントのfull_page_writes オン 
 +最終チェックポイントのNextXID:                    0:610 
 +最終チェックポイントのNextOID:                    24655 
 +最終チェックポイントのNextMultiXactId: 
 +最終チェックポイントのNextMultiOffset: 
 +最終チェックポイントのoldestXID:      536 
 +最終チェックポイントのoldestXIDのDB: 
 +最終チェックポイントのoldestActiveXID: 610 
 +最終チェックポイントのoldestMultiXid:   1 
 +最終チェックポイントのoldestMulti'sのDB:
 +最終チェックポイントのoldestCommitTsXid:
 +最終チェックポイントのnewestCommitTsXid:
 +最終チェックポイント時刻:                         2019/07/28 20:58:08 
 +ログを取らないリレーション向けの偽のLSNカウンタ:   0/1 
 +最小リカバリ終了位置:                             0/0 
 +最小リカバリ終了位置のタイムライン:   0 
 +バックアップ開始位置:                 0/0 
 +バックアップ終了位置:                 0/0 
 +必要なバックアップ最終レコード:        no 
 +wal_level の設定:                    replica 
 +wal_log_hints の設定:                オフ 
 +max_connections の設定:              100 
 +max_worker_processes の設定:         8 
 +max_prepared_xacts の設定:           0 
 +max_locks_per_xact の設定:           64 
 +track_commit_timestamp の設定:       オフ 
 +最大データアラインメント              8 
 +データベースのブロックサイズ:                     8192 
 +ラージリレーションのセグメント当たりのブロック数: 131072 
 +WALブロックのサイズ:                              8192 
 +WALセグメント当たりのバイト数:                  16777216 
 +識別子の最大長:                                   64 
 +インデックス内の最大列数:             32 
 +TOASTチャンクの最大サイズ:                               1996 
 +ラージオブジェクトチャンクのサイズ:         2048 
 +日付/時刻型の格納方式:                            64ビット整数 
 +Float4 引数の渡し方:                 値渡し 
 +Float8  引数の渡し方:                値渡し 
 +データベージチェックサムのバージョン:           0 
 +</code> 
 ===== VACUUM処理 ===== ===== VACUUM処理 =====
 PostgreSQLは、追記型アーキテクチャ(MVCC:MultiVersion Concurrency Control)を採用している。データの変更があっても元のレコードを物理的に消さずに、新しい行を追加して、元のレコードを無効マークとします。\\ PostgreSQLは、追記型アーキテクチャ(MVCC:MultiVersion Concurrency Control)を採用している。データの変更があっても元のレコードを物理的に消さずに、新しい行を追加して、元のレコードを無効マークとします。\\
行 402: 行 509:
 </code> </code>
  
 +=== 最後にVACUUM/ANALYZEした日時確認 ===
 +<code sql>
 +SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
 +FROM pg_stat_all_tables
 +WHERE schemaname = 'public' -- schemaname or relnameで絞りこむと見やすい
 +ORDER BY relname
 +</code>
 ==== VACUUM手動実行判断 ==== ==== VACUUM手動実行判断 ====
 デッドタプルが多く、有効なレコードに対するデッドタプルの割合の多いテーブルに VACUUM をするようにしてみてください。 デッドタプルが多く、有効なレコードに対するデッドタプルの割合の多いテーブルに VACUUM をするようにしてみてください。
行 444: 行 558:
 ageが15億になる前にVACUUMをかける。かけるとまた10億になる。 ageが15億になる前にVACUUMをかける。かけるとまた10億になる。
 [[http://blog.livedoor.jp/moonfishnet/archives/26220925.html|VACUUMとage関数]] [[http://blog.livedoor.jp/moonfishnet/archives/26220925.html|VACUUMとage関数]]
 +
 +===== インデックスの再構築 =====
 +
 +==== リインデックス ====
 +reindexコマンドを使用して、インデックスの再構築する。\\
 +<code bat>
 +rem PK_REP_RES_QUEをリインデックスする
 +SET PGPASSWORD=wh_kousei
 +psql -U wh_kousei -c "reindex INDEX pk_rep_res_que;"
 +</code>
 +
 +==== テーブルロックなしのリインデックス ====
 +reindexコマンドはテーブルロックがかかってしまうので、運用中のDBに対して使うのは難しい。\\
 +ただ、PostgreSQLでは別名で全く同じインデックスの作成を行うことができる
 +
 +  * [[http://cynipe.hateblo.jp/entry/2012/08/19/173230|PostgreSQLでテーブルロックせずにインデックスを再構築する方法]]
 +  * [[https://tak-w.hatenadiary.org/entry/20111125|index 再構築方法]]
 +  * [[https://tak-w.hatenadiary.org/entry/20111207/1323270854|index 再構築 Primary key]]
 +  * [[http://kashi.way-nifty.com/jalan/2014/04/postgresql-81a7.html|定期的なインデックス再作成を自動化]]
 +
 +<code bat>
 +rem PK_REP_RES_QUEをリインデックスする
 +SET PGPASSWORD=wh_test
 +psql -U wh_test -f reindex_rep_res_que.sql
 +</code>
 +
 +<code sql reindex_rep_res_que.sql>
 +create unique index concurrently pk_rep_res_que_new on rep_res_que(que_id);
 +
 +alter table rep_res_que drop constraint pk_rep_res_que;
 +alter index pk_rep_res_que_new rename to pk_rep_res_que;
 +alter table rep_res_que add primary key using index pk_rep_res_que;
 +</code>
 ===== 確認用SQL ===== ===== 確認用SQL =====
 === テーブルのディスク使用量を取得する === === テーブルのディスク使用量を取得する ===


it技術/データベース/postgresql/運用.1564281207.txt.gz · 最終更新: 2019/07/28 11:33 by yajuadmin