あっつんブログ

  1. Home
  2. ＩＴ関連
  3. PowerShell と PostgreSQL

PowerShell と PostgreSQL

  • PostgreSQL
  • SQL
  • PowerShell
    •

    PowerShell

    今回は、WindowsServer で PowerShell を使い、PostgreSQL に接続して SQL を発行するスクリプトを作成したいと思います。

    その過程で使用した基本的なコマンドの使い方を残しておきたいと思います。

    ファイル構成

    working-directory |-- connection.ps1 |-- powershell_sample.log |-- powershell_postgresql.ps1 |-- sample.sql

    メインは powershell_postgresql.ps1 で、環境情報は connection.ps1 に持たせて、実行するSQLは sample.sql とします。

    ログファイルは、同一フォルダ内に powershell_sample.log というログファイルを生成します。

    処理フローのイメージ図

    powershell-postgres-01

    SQL ファイルを実行するだけのいたってシンプルな作りです。

    connection.ps1 と sample.sql を準備

    connection.ps1

    変数の定義は「 $変数 = "文字列" 」です。以下のように環境情報を定義しています。

    connection.ps1
    #================================================== # configires #================================================== $HOSTNAME = "koizumi-postgresql-01.eu-west-1.rds.amazonaws.com" $PORT = "5432" $DBNAME = "TESTDB" $DBUSER = "USER01" #PASSWORD = C:\Users\xxxxx\AppData\Roaming\postgres\pgpass.conf

    PostgreSQL のパスワードは pgpass.conf の中

    上記の「connection.ps1」の "#PASSWORD" に注目してほしいのですが、コメントアウトになっています。

    今回は、psql コマンドを使って PostgreSQL に接続するのですが、このファイルでパスワードは指定できません。

    psql はデフォルトでパスワードが対話式に聞かれます。

    ただ、対話式のパスワード入力を回避する策も用意されています。以下のファイルにパスワード情報を定義しておく方法です。

    • C:\Users\xxxxx\AppData\Roaming\postgres\pgpass.conf

    バッチ処理でスクリプトを動かす場合などは、このファイルを使うことになります。今回もその方法を取っています。

    pgpass.conf の中身はこんな感じです。

    pgpass.conf
    #hostname:port:database:username:password koizumi-postgresql-01.eu-west-1.rds.amazonaws.com:5432:TESTDB:USER01:xxxxxxxx

    sample.sql

    あくまでサンプルなので適当な SQL を書いておきましょう。

    sample.sql
    select now();

    powershell_postgresql.ps1

    実行順序

    以下のような順序で処理していきます。

    1. 環境ファイル・SQL ファイルのファイル名定義
    2. ログファイル生成
    3. 出力メッセージのフォーマット定義
    4. 環境ファイル・SQL ファイルの存在確認
    5. 環境ファイルの読み込み
    6. psql で sql 実行
    7. 処理結果を受けエラーハンドリング

    1. 環境ファイル・SQL ファイルのファイル名定義

    スクリプトが置いてあるパスは PowerShell の自動変数 $PSScriptRoot で取得することができます。

    powershell_postgresql.ps1
    # env $CONNECTION_ENV = "connection.ps1" $SAMPLE_SQL = "sample.sql" # path $LOG_FILENM = "powershell_sample.log" $LOGFILE = "$PSScriptRoot\$LOG_FILENM"

    2. ログファイルの作成

    ログファイルの作成でよく使うロジックがこれですね。

    powershell_postgresql.ps1
    # make log file if ( -not (Test-Path $LOGFILE)) { New-Item $LOGFILE -ItemType "file" }

    3. 出力メッセージのフォーマット定義

    ログに出すメッセージは同じフォーマットで書き込みたいですよね。

    • 先頭に "yyyy/MM/dd HH:mm:ss" を挿入
    • 通常時の出力形式とエラー時の出力形式を用意

    function と switch を使うとこのようにメッセージのフォーマットを定義することが出来ます。

    powershell_postgresql.ps1
    function MSG_FMT( $type, $msg ) { switch ( $type ) { 1 { [string]$((Get-date -format "yyyy/MM/dd HH:mm:ss")) + " $msg" break } 2 { [string]$((Get-date -format "yyyy/MM/dd HH:mm:ss")) + " ERROR `r`n" + "$msg" break } } }

    4. 環境ファイル・SQL ファイルの存在確認

    function の使い方はこうです。

    powershell_postgresql.ps1
    # start MSG_FMT 1 "start." >> $LOGFILE # confirm files exist if ( -not (Test-Path $PSScriptRoot\$CONNECTION_ENV)) { MSG_FMT 2 "There is no File = [${PSScriptRoot}\${CONNECTION_ENV}]" >> $LOGFILE MSG_FMT 1 "end." >> $LOGFILE exit } if ( -not (Test-Path $PSScriptRoot\$SAMPLE_SQL)) { MSG_FMT 2 "There is no File = [${PSScriptRoot}\${SAMPLE_SQL}]" >> $LOGFILE MSG_FMT 1 "end." >> $LOGFILE exit }

    たとえば、sample.sql が存在しない場合は、以下のようなメッセージが出力されます。

    2020/07/03 17:55:33 ERROR There is no File = [C:\koizumi\PowerShell\work\sample.sql] 2020/07/03 17:55:33 end.

    5. 環境ファイルの読み込み

    以下の方法（ . ※ドット）で .ps1 ファイルの読み込みは可能です。

    powershell_postgresql.ps1
    # read env . $PSScriptRoot\$CONNECTION_ENV MSG_FMT 1 "HOSTNAME = $HOSTNAME" >> $LOGFILE MSG_FMT 1 "PORT = $PORT" >> $LOGFILE MSG_FMT 1 "DBNAME = $DBNAME" >> $LOGFILE MSG_FMT 1 "DBUSER = $DBUSER" >> $LOGFILE

    6. psql で sql 実行

    $error には配列でエラー情報が格納されていきます。

    そのため、$error の内容をリセットしないと、エラーが発生する度に $error の配列が増え続けます。

    以下のスクリプトでは、$error.Count で配列の数をエラーハンドリングで使用しています。つまり、処理の先頭で $error を掃除しておかないと、エラー処理に回され続けてしまうのです。

    powershell_postgresql.ps1
    # $error clear $error.clear() # run sql by psql psql -w -a -h $HOSTNAME -U $DBUSER -p $PORT -d $DBNAME -f $PSScriptRoot\$SAMPLE_SQL 2>&1 >> $LOGFILE

    psql コマンドの注意点①　[ psql -w ]

    psql コマンドは基本的に対話式でパスワードを聞いてくることは前半で説明しました。

    たとえば、バッチ処理の中でパスワードが対話式で聞かれてしまうと、バッチ処理が終わらなくなってしまいます。

    psql をスクリプトに応用する際、環境ファイルの内容が間違っていて、パスワードを対話式で聞かれる状態となったときのことを考慮しておく必要があります。

    psql コマンドの注意点②　[ psql -a 2>&1]

    psql の引数「 -a 」で発行クエリもログに出力されます。また、「2>&1」とすることで、標準エラー出力もログファイルに出力されます。

    ただ、PowerShell の仕様で $error にも標準エラー出力は格納されています。

    7. 処理結果を受けエラーハンドリング

    $error からエラーの件数を取得して、処理を分岐させます。

    powershell_postgresql.ps1
    if ($error.Count -eq 0) { MSG_FMT 1 "success." >> $LOGFILE } else { MSG_FMT 2 "$($error | Out-String)" >> $LOGFILE }

    $error の使い方

    私は以下の使い方だけ把握しておけば、そんなに困らないと思っています。

    $error[0] # $error の最新のみを出す $error # $error[@] と同じ # $error の中身を全部出す $error.clear() # $error の中身をクリアする $error.Count # $error の件数を出す $($error | Out-String) # $error の詳細情報を出す

    powershell_postgresql.ps1

    最後に全文を載せておきます。

    powershell_postgresql.ps1
    #========================================================== # configures #========================================================== # env $CONNECTION_ENV = "connection.ps1" $SAMPLE_SQL = "sample.sql" #========================================================== # log file #========================================================== # path $LOG_FILENM = "powershell_sample.log" $LOGFILE = "$PSScriptRoot\$LOG_FILENM" # make log file if ( -not (Test-Path $LOGFILE)) { New-Item $LOGFILE -ItemType "file" } # message function MSG_FMT( $type, $msg ) { switch ( $type ) { 1 { [string]$((Get-date -format "yyyy/MM/dd HH:mm:ss")) + " $msg" break } 2 { [string]$((Get-date -format "yyyy/MM/dd HH:mm:ss")) + " ERROR `r`n" + "$msg" break } } } #========================================================== # run #========================================================== # start MSG_FMT 1 "start." >> $LOGFILE # confirm files exist if ( -not (Test-Path $PSScriptRoot\$CONNECTION_ENV)) { MSG_FMT 2 "There is no File = [${PSScriptRoot}\${CONNECTION_ENV}]" >> $LOGFILE MSG_FMT 1 "end." >> $LOGFILE exit } if ( -not (Test-Path $PSScriptRoot\$SAMPLE_SQL)) { MSG_FMT 2 "There is no File = [${PSScriptRoot}\${SAMPLE_SQL}]" >> $LOGFILE MSG_FMT 1 "end." >> $LOGFILE exit } # read env . $PSScriptRoot\$CONNECTION_ENV MSG_FMT 1 "HOSTNAME = $HOSTNAME" >> $LOGFILE MSG_FMT 1 "PORT = $PORT" >> $LOGFILE MSG_FMT 1 "DBNAME = $DBNAME" >> $LOGFILE MSG_FMT 1 "DBUSER = $DBUSER" >> $LOGFILE # $error clear $error.clear() # run sql by psql psql -w -a -h $HOSTNAME -U $DBUSER -p $PORT -d $DBNAME -f $PSScriptRoot\$SAMPLE_SQL 2>&1 >> $LOGFILE if ($error.Count -eq 0) { MSG_FMT 1 "success." >> $LOGFILE } else { MSG_FMT 2 "$($error | Out-String)" >> $LOGFILE } MSG_FMT 1 "end." >> $LOGFILE

    以上です。

    Icons made by Flat Icons from www.flaticon.com

  • PostgreSQL
  • SQL
  • PowerShell
    •