PostgreSQL Backup Restore Script

A simple automation script for Windows that streamlines PostgreSQL database backup restoration. This PowerShell script eliminates manual commands and provides an interactive, user-friendly way to restore PostgreSQL backups with automatic version detection, connection verification, and post-restore validation.

Perfect for developers and DBAs who work with multiple PostgreSQL versions and need a quick, reliable way to restore database backups without memorizing complex pg_restore or psql commands.

🚀 Features

• Automatic Version Detection - Scans and lists all installed PostgreSQL versions on your system
• Interactive Version Selection - Choose which PostgreSQL version to use for restoration
• Connection Verification - Tests database connection and verifies version matches selected instance
• Port Mismatch Detection - Alerts if you're connecting to wrong PostgreSQL instance on different port
• Smart Database Handling - Automatically creates database if it doesn't exist, or restores to existing one
• Drag & Drop Support - Supports file paths with or without quotes (drag and drop files directly)
• Multiple Backup Formats - Handles both .sql (plain SQL) and .backup (custom/tar/directory) formats automatically
• Post-Restore Validation - Lists all restored tables after successful restoration
• Color-Coded Output - Easy-to-read success/error/info messages
• Secure Password Input - Password masking for security

📋 Prerequisites

• Windows operating system
• PostgreSQL installed (supports multiple versions: 9.5, 15, etc.)
• PowerShell 5.1 or higher
• PostgreSQL backup files (.sql or .backup formats)
• Appropriate database user credentials

💡 Why Use This Script?

Instead of manually typing commands like:

pg_restore -h localhost -p 5432 -U postgres -d mydb -v mybackup.backup

This script automates the entire process:

• ✅ No need to remember complex commands
• ✅ Automatically detects all PostgreSQL versions
• ✅ Prevents restoring to wrong version/port
• ✅ Creates database if it doesn't exist
• ✅ Shows what tables were restored
• ✅ Works with both .sql and .backup files

🔧 Installation

1. Clone this repository or download the script:

git clone https://github.com/yourusername/postgresql-restore-script.git

2. Navigate to the script directory:

cd postgresql-restore-script

3. Set execution policy (if needed):

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

📖 Usage

Basic Usage

1. Run the script:

.\restore_pg_backup.ps1

2. Follow the interactive prompts:
   • Select PostgreSQL version from detected installations
   • Enter database connection details (host, port, user, password)
   • Provide backup file path (supports .sql and .backup files)
   • You can drag and drop the backup file directly into PowerShell
   • Specify target database name
   • Confirm creation/restoration

Example Session

================================================
  PostgreSQL Backup Restore Script
================================================

[INFO] Searching for PostgreSQL installations...
[SUCCESS] Found 2 PostgreSQL installation(s):

  [1] PostgreSQL 9.5 - C:\Program Files\PostgreSQL\9.5\bin\psql.exe
  [2] PostgreSQL 15 - C:\Program Files\PostgreSQL\15\bin\psql.exe

Select PostgreSQL version [1-2]: 2
[SUCCESS] Selected PostgreSQL 15

Enter database host [localhost]: localhost
Enter database port [5432]: 5432
Enter database user: postgres
Enter database password: ****

[INFO] Testing connection to PostgreSQL server...
[SUCCESS] Connected to PostgreSQL server version: 15
[SUCCESS] Version verification passed!

Enter path to backup file (or drag and drop the file here): C:\backups\mydb.backup
[SUCCESS] Backup file found: C:\backups\mydb.backup

Enter target database name: restored_db
[INFO] Checking if database 'restored_db' exists...
[INFO] Database 'restored_db' does not exist
Create new database and restore? [Y/n]: y
[SUCCESS] Database 'restored_db' created successfully

[INFO] Starting restore process...
========================================
[SUCCESS] Backup restored successfully to database 'restored_db'

[INFO] Querying restored tables...
[SUCCESS] Found 3 table(s) in database 'restored_db':

  - customers
  - products
  - orders

[SUCCESS] Script completed successfully!

Press any key to exit...

🧪 Testing

A test backup file (test_backup.sql) is included with sample data:

• customers table (5 records)
• products table (8 records)
• orders table (10 records)

Use this file to test the script:

.\restore_pg_backup.ps1
# When prompted, use: test_backup.sql
# Database name: test_restore_db

The script works with:

• .sql files - Plain SQL dump files (created with pg_dump)
• .backup files - Custom format backups (created with pg_dump -Fc)
• tar/directory formats - Other PostgreSQL backup formats

⚠️ Common Issues & Solutions

Issue: "No PostgreSQL installations found"

Solution: Ensure PostgreSQL is installed in standard locations:

• C:\Program Files\PostgreSQL\*\bin\
• C:\PostgreSQL\*\bin\
• Or add PostgreSQL to your system PATH

Issue: "VERSION MISMATCH DETECTED"

Solution: This means you selected one PostgreSQL version but connected to another on a different port.

• PostgreSQL 9.5 might be on port 5433
• PostgreSQL 15 might be on port 5432
• Either select the correct version or use the correct port

Issue: "Backup file not found"

Solution:

• Don't use quotes when typing the path manually
• When dragging and dropping, quotes are automatically handled
• Ensure the file path is correct and file exists

Issue: Connection Failed

Solution: Verify:

• PostgreSQL service is running (services.msc)
• Host and port are correct
• Username and password are correct
• User has appropriate permissions

🔐 Security Notes

• Passwords are masked during input
• PGPASSWORD environment variable is used temporarily and cleared after use
• Never hardcode passwords in the script
• Ensure backup files contain no sensitive credentials

👤 Author

Kakamyrat Komekov - @komekovv

🙏 Acknowledgments

• PostgreSQL community for excellent documentation
• PowerShell community for scripting best practices

---

Note: Always test backup restoration on a non-production database first to ensure compatibility and data integrity.