Zenn
🐘

PostgreSQLを初めて使う方へ｜導入後にやるべき設定とWindowsでの基本メンテナンス

に公開
PostgreSQL
Windows
tech

✅ 目次

  1. はじめに
  2. インストール直後に確認すべきこと
  3. 注意事項（サービス名とPATH設定）
  4. データベースのバックアップ
  5. データベース・ユーザ作成
  6. データベースのリストア
  7. 他の端末から接続するための設定（pg_hba.conf）
  8. 暗号化方式の確認と変更（必要に応じて）
  9. Windowsでの基本設定（ファイアウォール・サービス）
  10. UPS連動で安全停止を実現する
  11. データディレクトリを外付けHDDに移動する場合
  12. WALをSSDに分離して性能を向上する
  13. 定期バックアップを自動化（7世代ローテーション）
  14. まとめと次のステップ

✅ 1. はじめに

OracleやSQL Server、MySQLを使っていた方が、PostgreSQLをインストールしたあとに
「何を設定すればよいのか？」で迷うことがあります。

そこで、PostgreSQL導入後にやっておくべき基本設定と、Windows環境での運用に必要なメンテナンスをまとめました。

✅ 2. インストール直後に確認すべきこと

  • サービスが正常に登録されているか
  • PostgreSQLのバージョンを把握
  • binディレクトリへのPATH設定
  • 初期ユーザ（postgres）のパスワード管理

✅ 3. 注意事項（サービス名とPATH設定）

  • PostgreSQLはWindowsで「サービス」として管理されます。

  • デフォルトのサービス名：

    postgresql-x64-17

    → 17 はバージョン番号です。環境に応じて置き換えてください。

  • binディレクトリ（コマンド実行に必要）

    C:\Program Files\PostgreSQL\17\bin

  • PATHに含まれているか確認するにはコマンドプロンプトで下記のコマンドを実行します。

    echo %PATH%

✅ 4. データベースのバックアップ

  • PostgreSQLではpg_dumpを使用します。
  • 下記コマンド実行は、PostgreSQLのbinディレクトリをPATHに追加しておくと便利です。
pg_dump -U postgres -h <ホスト名> -p <ポート番号> -F c -b -v -f <出力ファイル>.dump <データベース名>

コマンドについて詳しくは 日本PostgreSQLユーザ会のキュメント を参照してください。

✅ 5. データベース・ユーザ作成

他DBと異なり、PostgreSQLは**ロール（ROLE）**でユーザと権限を統合管理します。

CREATE ROLE "ユーザ名" SUPERUSER LOGIN PASSWORD 'パスワード';
CREATE DATABASE "DB名" TEMPLATE template1;

注意：例では、SUPERUSERにて作成したロールに管理者権限を割り当てています。
本番環境では、適切な権限を割り当てしてください。

✅ 6. データベースのリストア

  • pg_restoreで実施します。
  • 下記コマンド実行は、PostgreSQLのbinディレクトリをPATHに追加しておくと便利です。
pg_restore -U postgres -h <ホスト名> -p <ポート番号> -d <データベース名> -v <バックアップファイル>.dump

コマンドについて詳しくは 日本PostgreSQLユーザ会のキュメント を参照してください。

✅ 7. 他の端末から接続するための設定（pg_hba.conf）

PostgreSQLはpg_hba.confで接続制御を行います。
他のDBと違い、これを設定しないと外部接続できません。

host    all    all    XXX.XXX.XXX.XXX/XXX    scram-sha-256
  • ADDRESS：許可するIP（例：192.168.1.0/24）
  • METHOD：
    • trust（パスワードなし）
    • md5（暗号化パスワード）
    • scram-sha-256（推奨）

注意：md5やscram-sha-256を選んだ場合、暗号化設定と一致していないと接続できません
→ 暗号化方式の確認と変更を参照。

✅ 8. 暗号化方式の確認と変更（必要に応じて）

PostgreSQL 17ではscram-sha-256がデフォルトです。
ツール互換のためmd5に変更することもあります。

# postgresql.conf
password_encryption = md5

再起動後、パスワードを再設定：

ALTER ROLE postgres WITH PASSWORD 'XXXX';

✅ 9. Windowsでの基本設定（ファイアウォール・サービス）

  • ポート5432(デフォルトポートの場合)を許可
    netsh advfirewall firewall add rule name="PostgreSQL" dir=in action=allow protocol=TCP localport=5432

✅ 10. UPS連動で安全停止を実現する

停電時にDBを安全に停止するには、UPSツールからバッチを呼び出します。
バッチ例：

@echo off
@set PGBIN="C:\Program Files\PostgreSQL\17\bin"
@set PGDATA="D:\PostgreSQL\data"
@set PGLOG="D:\pg\logs\pg_shutdown.log"

@echo [%DATE% %TIME%] Shutdown start >> %PGLOG%
%PGBIN%\pg_ctl.exe stop -D %PGDATA% -m fast >> %PGLOG% 2>&1
@echo [%DATE% %TIME%] Shutdown end >> %PGLOG%

UPSツールの設定例（OMRON PowerAttendant）では、このバッチをタスクに登録。

✅ 11. データディレクトリを外付けHDDに移動する場合

  • サービス停止 → データコピー → サービス再登録
  • コマンド例：
pg_ctl unregister -N postgresql-x64-17
xcopy "C:\Program Files\PostgreSQL\17\data" "D:\PostgreSQL\data" /E /H /C /I
pg_ctl register -N postgresql-x64-17 -D "D:\PostgreSQL\data"

注意：データディレクトリを移動した後、Windows起動時にHDDが認識できず、
PostgreSQLが起動しないことがあります。

対策として**サービス起動モードを「自動（遅延開始）」**に設定します。

✅ 12. WALをSSDに分離して性能を向上する

  • データディレクトリを外付けHDDを使用する場合、WAL（pg_wal）はI/Oが多いため、内蔵SSDに置くと高速化につながります
  • 手順：
move "D:\PostgreSQL\data\pg_wal" "C:\PostgreSQL\pg_wal"
mklink /D "D:\PostgreSQL\data\pg_wal" "C:\PostgreSQL\pg_wal"

注意：例では、cドライブ下にmoveコマンドでWALを移動しているため、アクセス権限の問題で移動できないことがあります。
失敗した場合は、手作業でフォルダ移動をするなどします。

✅ 13. 定期バックアップを自動化（7世代ローテーション）

手動バックアップに加え、1週間分のローテーションを自動で実施するバッチファイルを設定すると便利です。

バッチスクリプト例（backup_pg.bat） 

rem @echo off
REM =========================================
REM PostgreSQL Weekly Backup Script (No Compression)
REM バックアップを曜日ごとにローテーションで保存
REM カスタム形式（.dump）、7世代保持
REM =========================================

REM ▼PostgreSQLのbinディレクトリ
set PG_BIN="C:\Program Files\PostgreSQL\17\bin"

REM ▼バックアップ保存先
set BACKUP_DIR="D:\PostgreSQL\Backup"

REM ▼ログ保存先
set LOG_DIR="D:\pg\logs"

REM ▼接続設定（環境に合わせて変更してください）
set DB_NAME=YourDatabaseName
set DB_USER=YourUserName
set DB_PASSWORD=YourPassword
set PG_HOST=localhost
set PG_PORT=5432

REM ▼ログファイル
set LOG_FILE=%LOG_DIR%\pg_backup.log

REM ▼pg_dumpにパスワードを渡す
set PGPASSWORD=%DB_PASSWORD%

REM ▼曜日を取得（0～6）
for /f %%a in ('wmic path win32_localtime get dayofweek /value ^| find "="') do set %%a

REM ▼バックアップファイル名を曜日で決定
set BACKUP_FILE=%BACKUP_DIR%Backup_%dayofweek%.dump

REM ▼日時取得
set CURRENT_DATE=%DATE%
set CURRENT_TIME=%TIME%

echo [%CURRENT_DATE% %CURRENT_TIME%] Backup Start... >> %LOG_FILE%

if not exist "%LOG_DIR%" mkdir "%LOG_DIR%"
if not exist "%BACKUP_DIR%" mkdir "%BACKUP_DIR%"

REM ▼バックアップ実行
%PG_BIN%\pg_dump -U %DB_USER% -h %PG_HOST% -p %PG_PORT% -F c -b -v -f %BACKUP_FILE% %DB_NAME% >> %LOG_FILE% 2>&1

echo [%CURRENT_DATE% %CURRENT_TIME%] Backup Completed. >> %LOG_FILE%

補足 set PGPASSWORD=YourPassword はそのバッチスクリプト（またはコマンドプロンプト）内でのみ有効です。
スクリプト終了後は自動的に無効になるため、他のプロセスやユーザーから参照されることはありません。

タスクスケジューラで自動化

  1. タスクの作成
    • タスクスケジューラで「新しいタスク」を作成
  2. トリガー
    • 毎日深夜（例：午前2時）
  3. 操作
    • 上記バッチファイルを実行
  4. オプション
    • 「ユーザーがログオンしているかどうかにかかわらず実行」
    • 「最上位の特権で実行」

**タスクスケジューラ作成コマンド例

管理者権限のコマンドプロンプトで実行：

schtasks /Create ^
 /TN "PostgreSQL Daily Backup" ^
 /TR "C:\path\to\pg_backup_weekly.bat" ^
 /SC DAILY ^
 /ST 02:00 ^
 /RL HIGHEST ^
 /RU SYSTEM ^
 /F

✅ 14. まとめと次のステップ

  • PostgreSQLは初期設定が重要
  • 他端末接続はpg_hba.conf、暗号化方式に注意
  • WAL分離で性能最適化
  • バックアップは自動化＋世代管理で安心

Discussion