ユーザ用ツール

サイト用ツール


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

差分

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

この比較画面へのリンク

両方とも前のリビジョン前のリビジョン
次のリビジョン
前のリビジョン
次のリビジョン両方とも次のリビジョン
it技術:データベース:postgresql:運用 [2019/07/29 00:37] – [WALアーカイブの削除] yajuadminit技術:データベース: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^説明^ ^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 で設定する。
行 323: 行 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ファイルが分かる。 pg_controldata コマンドで最終チェックポイントのREDO WALファイルが分かる。
  
行 465: 行 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 をするようにしてみてください。
行 507: 行 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/運用.txt · 最終更新: 2022/06/06 11:17 by yajuadmin