Skip to main content

How to Connect to a PostgreSQL Database from a Batch Script

PostgreSQL provides native command-line tools built for automation, psql for executing SQL and pg_dump for creating backups. Administering PostgreSQL from Windows Batch scripts reduces dependencies and enables repeatable maintenance tasks (health checks, migrations, reporting extracts, etc.).

In this guide, we will demonstrate how to connect to a local or remote PostgreSQL database using a Batch script.

The Strategy: The psql.exe Client​

  1. Identify the PostgreSQL bin directory (e.g., C:\Program Files\PostgreSQL\16\bin).
  2. Define the connection details (Host, Port, Database, User).
  3. Provide authentication securely (prefer PGPASSFILE/pgpass.conf; or use PGPASSWORD as a scoped environment variable).
  4. Execute SQL via -c, or run a .sql file via -f.

Method 1: Run an Inline SQL Statement with PGPASSWORD (Scoped)​

PostgreSQL discourages putting passwords directly on the command line. A common Batch pattern is setting PGPASSWORD only for the lifetime of the script (via setlocal) and then calling psql.

@echo off
setlocal

:: Define the PostgreSQL executable
set "psqlExe=C:\Program Files\PostgreSQL\16\bin\psql.exe"

:: Define connection details
set "pgHost=127.0.0.1"
set "pgPort=5432"
set "pgUser=postgres"
set "pgDatabase=inventory_db"

:: Validate psql.exe exists
if not exist "%psqlExe%" (
    echo [ERROR] psql.exe not found at "%psqlExe%".
    pause
    exit /b 1
)

:: Prompt for password (interactive usage)
set /p "PGPASSWORD=Enter PostgreSQL password: "
if "%PGPASSWORD%"=="" (
    echo [ERROR] Password cannot be empty.
    pause
    exit /b 1
)

echo.
echo Connecting to PostgreSQL at %pgHost%:%pgPort% ...
echo Executing sample query...
echo.

set "query=SELECT COUNT(*) AS current_items FROM products;"

:: -X                 = do not read startup files (.psqlrc) for consistent automation behavior
:: -v ON_ERROR_STOP=1 = exit non-zero if the SQL fails (otherwise many SQL errors still return 0)
:: -q                 = quiet
"%psqlExe%" -X -q -v ON_ERROR_STOP=1 -h "%pgHost%" -p "%pgPort%" -U "%pgUser%" -d "%pgDatabase%" -c "%query%"

if %errorlevel% equ 0 (
    echo.
    echo ==========================================
    echo EXECUTION SUCCESSFUL
    echo ==========================================
) else (
    echo.
    echo [ERROR] psql returned error code: %errorlevel%.
    pause
    exit /b %errorlevel%
)

pause
endlocal
warning

PGPASSWORD is safer than placing a password directly in the command line, but it is not fully secret: other processes running as the same user may be able to read environment variables. For unattended automation, prefer a restricted pgpass.conf (see tip below).

tip

For non-interactive automation, use a password file instead of prompting:

  • Default location: %APPDATA%\postgresql\pgpass.conf
  • Or set a custom file: set "PGPASSFILE=C:\Secure\pgpass.conf"

File format (one line per connection): hostname:port:database:username:password

Lock down the file with NTFS permissions (only the service account should read it).

Method 2: Output Query Results to a CSV Report​

For reports meant for Excel/BI tools, psql can emit real CSV. On modern PostgreSQL versions, psql supports --csv.

@echo off
setlocal

set "psqlExe=C:\Program Files\PostgreSQL\16\bin\psql.exe"
set "pgHost=10.0.1.201"
set "pgPort=5432"
set "pgUser=report_reader"
set "pgDatabase=finance_db"

set "outputCsv=C:\Reports\PaidInvoices.csv"
set "query=SELECT id, invoice_number, total FROM invoices WHERE status='PAID' ORDER BY id;"

:: Validate psql.exe exists
if not exist "%psqlExe%" (
    echo [ERROR] psql.exe not found at "%psqlExe%".
    pause
    exit /b 1
)

:: Ensure output directory exists
for %%A in ("%outputCsv%") do (
    if not exist "%%~dpA" mkdir "%%~dpA"
)

:: Prompt for password (interactive usage)
set /p "PGPASSWORD=Enter PostgreSQL password: "
if "%PGPASSWORD%"=="" (
    echo [ERROR] Password cannot be empty.
    pause
    exit /b 1
)

echo.
echo Extracting PostgreSQL data into "%outputCsv%"...
echo.

:: --csv       = CSV output
:: -o          = write output directly to file (more reliable than cmd > redirection for some cases)
:: -P footer=off -P pager=off = remove "(N rows)" footer and disable paging for automation
:: -v ON_ERROR_STOP=1 = fail the script if the query fails
"%psqlExe%" -X -q -v ON_ERROR_STOP=1 ^
  -h "%pgHost%" -p "%pgPort%" -U "%pgUser%" -d "%pgDatabase%" ^
  --csv -P "footer=off" -P "pager=off" ^
  -c "%query%" -o "%outputCsv%"

if %errorlevel% equ 0 (
    echo.
    echo ==========================================
    echo EXTRACTION SUCCESSFUL
    echo Output: %outputCsv%
    echo ==========================================
) else (
    echo.
    echo [ERROR] psql returned error code: %errorlevel%.
    pause
    exit /b %errorlevel%
)

pause
endlocal
tip

If you need server-side CSV generation (written by PostgreSQL on the server’s filesystem), use COPY ... TO 'path'. If you need client-side output (written on the machine running the script), use \copy ... TO 'path' (a psql meta-command).

Method 3: Execute a Multi-Line .sql File (Migrations / Batch SQL)​

For migrations, schema changes, or long scripts, execute a .sql file with -f.

@echo off
setlocal

set "psqlExe=C:\Program Files\PostgreSQL\16\bin\psql.exe"
set "pgHost=localhost"
set "pgPort=5432"
set "pgUser=postgres"
set "pgDatabase=inventory_db"
set "sqlFile=C:\Migrations\V2_Schema_Update.sql"

:: Validate psql.exe exists
if not exist "%psqlExe%" (
    echo [ERROR] psql.exe not found at "%psqlExe%".
    pause
    exit /b 1
)

:: Validate .sql file exists
if not exist "%sqlFile%" (
    echo [ERROR] SQL file not found at "%sqlFile%".
    pause
    exit /b 1
)

:: Prompt for password (interactive usage)
set /p "PGPASSWORD=Enter PostgreSQL password: "
if "%PGPASSWORD%"=="" (
    echo [ERROR] Password cannot be empty.
    pause
    exit /b 1
)

echo.
echo Executing PostgreSQL script: "%sqlFile%"...
echo.

:: -v ON_ERROR_STOP=1 makes psql stop and return non-zero if any statement fails
"%psqlExe%" -X -q -v ON_ERROR_STOP=1 -h "%pgHost%" -p "%pgPort%" -U "%pgUser%" -d "%pgDatabase%" -f "%sqlFile%"

if %errorlevel% equ 0 (
    echo.
    echo ==========================================
    echo SCRIPT EXECUTION SUCCESSFUL
    echo ==========================================
) else (
    echo.
    echo [ERROR] psql returned error code: %errorlevel%.
    pause
    exit /b %errorlevel%
)

pause
endlocal

Why Connect to PostgreSQL from Batch?​

  1. Backup Automation: Scheduling pg_dump jobs with Task Scheduler for consistent disaster recovery.
  2. ETL Orchestration: Running imports/exports and staging transformations as part of a nightly pipeline.
  3. Local Dev Reset: Recreating and reseeding a local database quickly when developers switch branches.

Important Considerations​

  1. Password Security: Avoid hard-coding passwords in .bat files. Even PGPASSWORD is still a plain-text secret in your script or console session. Prefer pgpass.conf / PGPASSFILE with tight NTFS permissions for unattended jobs.
  2. Host Access (pg_hba.conf): If you see no pg_hba.conf entry for host, the server is refusing your connection. On Windows, pg_hba.conf is in the PostgreSQL data directory (commonly C:\Program Files\PostgreSQL\<version>\data\pg_hba.conf, but it can vary by installation). Update rules and reload/restart PostgreSQL.
  3. Reliable Exit Codes: Use -v ON_ERROR_STOP=1 for automation. Without it, some SQL errors will print messages but still return a success exit code, which breaks %errorlevel% checks.

Conclusion​

Connecting to PostgreSQL from a Batch script enables repeatable administrative operations: running queries, generating reports, and executing migration bundles as part of scheduled maintenance. With psql.exe and careful credential handling, simple .bat files can safely integrate PostgreSQL tasks into Windows automation workflows.

Table of Contents