PostgreSQL データベース最適化・自動バックアップ

１．はじめに

PostgreSQLで、データベース最適化や自動バックアップをしたいと思ったが、
SQLSeverにあるメンテナンスプランのような機能がない為、
独自でバッチファイルを用意する必要があった。

２．実際に作ったバッチ

下のソースを、「.bat」ファイル形式で保存します。

rem --------------------------------------------------
rem 接続パラメータ
rem --------------------------------------------------
set PGPATH="C:\Program Files\PostgreSQL\15\bin\"
set HOSTNAME=localhost
set PORTNUM=5432
set DBNAME=XXX
set USERNAME=XXX
set PGPASSWORD=XXX
set DBBACKUPPATH=C:\BACKUP\

cd /d %PGPATH%

rem --------------------------------------------------
rem VACUUM ANALYZE（不要領域回収、統計情報最新化）
rem --------------------------------------------------
echo %date%%time%

%PGPATH%psql -h %HOSTNAME% -p %PORTNUM% -d %DBNAME% -U %USERNAME% -c "VACUUM ANALYZE;"

rem --------------------------------------------------
rem REINDEX（インデックス再構築）
rem --------------------------------------------------
echo %date%%time%

%PGPATH%psql -h %HOSTNAME% -p %PORTNUM% -d %DBNAME% -U %USERNAME% -c "REINDEX DATABASE CONCURRENTLY %DBNAME%;"

rem --------------------------------------------------
rem データベースバックアップ（7日分保持）
rem --------------------------------------------------
echo %date%%time%

cd /d %DBBACKUPPATH%
%PGPATH%pg_dump -U %USERNAME% -f %DBBACKUPPATH%%DBNAME%_%date:~-10,4%%date:~-5,2%%date:~-2,2%.sql -Fc %DBNAME%

forfiles /p %DBBACKUPPATH% /d -7 /m "*.sql" /c "cmd /c del @file"

echo %date%%time%

接続パラメータ　の下にある、設定箇所を、自分の環境に合わせて変更します。
set PGPATH="C:\Program Files\PostgreSQL\15\bin"
set HOSTNAME=localhost
set PORTNUM=5432
set DBNAME=XXX
set USERNAME=XXX
set PGPASSWORD=XXX
set DBBACKUPPATH=C:\BACKUP\

後は、バッチを実行することで、
・VACUUM ANALYZE（不要領域回収、統計情報最新化）
・REINDEX（インデックス再構築）
・データベースバックアップ（7日分保持）
が、実行されます。

３．まとめ

このバッチを、タスクスケジューラなどに登録して自動起動するようにしておけば、
SQLSeverにあるメンテナンスプランのような感じになります。

即席で作ったので、もう少し、ここは改良した方がいいといったご意見を頂けますと幸いです。