Skip to content

PostgreSQL Automatic Backup Operations

This document explains how to automatically back up PostgreSQL databases using the databaseBackup.ps1 PowerShell script.

System Overview

The system consists of the main backup operation: These operations are executed by PowerShell scripts (.ps1) triggered via .bat files.

Important Note: The databaseBackup.bat file is configured to be automatically run using the Windows Task Scheduler. These .bat files execute the corresponding PowerShell scripts with the ExecutionPolicy Bypass parameter.

1. Midday Backup (databaseBackup.ps1)

This script backs up to a network drive (File Server).

Trigger: databaseBackup.bat

Process Steps:

  1. Server Information:
  2. Host: {database_ip_address}
  3. User: {database_user}
  4. Database Listing:
  5. The script connects to the server.
  6. Automatically queries all databases except the base system databases, which should be excluded from backup.
  7. Backup Path:
  8. Uses the {database_backup_folder_path} folder on the network.
  9. Backup Process:
  10. Creates a new folder in this folder with the format Linux_YYYYMMdd_HHmmss.
  11. Runs the pg_dump command for each database found.
  12. Saves each database as database_name.sql within this timestamped folder.

Restoring PostgreSQL Backups

This document explains how to restore PostgreSQL backups in .sql format to the server. Two methods are provided: manual loading using DBeaver or automatic restoration using RestoreAllDatabases.bat.

Method 1: Manual Restoration with DBeaver

The most reliable way to load .sql (plain text) files created with pg_dump into DBeaver is with the "Execute Script" tool.

Step 1: Creating an Empty Database

Before loading the backup, you need an empty database to import the data into.

  1. Connect to the {database_ip_address} server in DBeaver.

  2. Right-click "Databases" in the left panel.

  3. Select "Create New Database".

  4. Enter the name of the database you will be backing up (e.g., "testdb_restore") and click "OK."

Step 2: Execute the SQL Script

  1. Right-click on the new, empty database you created (e.g., "testdb_restore").

  2. From the menu that opens, select "Tools" -> "Execute Script". (This may also be called "Run Script" in some versions.)

  3. A wizard window will open.

  4. In the "Script file(s)" section, use the "Select File(s)" button to select the .sql backup file on your computer (e.g., C:\Backups\testdb.sql).

  5. You don't need to change any settings.

  6. Click the "Start" button.

DBeaver will run all the commands in the .sql file sequentially, creating the tables and loading the data into your new database.

Method 2: Automatic Restore (RestoreAllDatabases.bat)

The RestoreAllDatabases.bat script is designed to restore all .sql files in a specified folder to the {database_ip_address} server.

Prerequisites:

  • The .sql files to be restored must be available.

  • The name of the .sql file must be the same as the name of the database to be created (e.g., musteridb.sql will create a database named musteridb).

Steps:

  1. Prepare Backups:
  2. Copy all .sql files you want to restore (e.g., the files you extracted from the evening backup) into the C:\SERENDERYEDEK folder.
  3. Note: The script is configured to use this folder. If you want to change the path, you will need to edit the .bat file.
  4. Run the Script:
  5. Double-click the RestoreAllDatabases.bat file to run it.

What Does the Script Do? (Technical Procedure)

  1. Starts a loop for each .sql file in the C:\SERENDERYEDEK folder.
  2. Extracts the database name from the file name (e.g., testdb.sql -> testdb).

  3. It attempts to create a new database on the server with this name using the psql command (CREATE DATABASE "testdb").

  4. If the database already exists, the CREATE DATABASE command will throw an error, but the script suppresses this error message with 2>nul and continues the restore process.

  5. Finally, it loads the relevant .sql file into that database using the psql command.

Sample PowerShell Script

[Console]::OutputEncoding = [System.Text.Encoding]::UTF8

$pgHost = "{database_ip_address}"
$pgPort = "5432"
$pgUser = "{database_user}"
$pgPassword = 'your_password'
$env:PGPASSWORD = $pgPassword
$date = Get-Date -Format "yyyyMMdd_HHmmss"
$backupParentPath = "{database_backup_folder_path}"
$backupFolderPath = Join-Path $backupParentPath "Linux_$date"
$folderPathToDelete = "C:\Backup\ServerBackup"

# Database names - automatically retrieved from the PostgreSQL server
Write-Host "Retrieving database list from the PostgreSQL server..."
$psqlCommand = "psql -h $pgHost -p $pgPort -U $pgUser -d postgres -t -A -c `"SELECT datname FROM pg_database WHERE NOT datistemplate AND datname NOT IN ('postgres','sonarqube', 'wikidb') AND datname NOT LIKE 'template%';`""
$databases = Invoke-Expression $psqlCommand | Where-Object { $_ -ne '' }
Write-Host "A total of $($databases.Count) databases were found."

# Creating a folder
New-Item -ItemType Directory -Force -Path $backupFolderPath

# Backing up each database
foreach ($dbname in $databases) {
$outputFile = Join-Path $backupFolderPath "$dbname.sql"
$dumpCommand = "pg_dump -h $pgHost -p $pgPort -U $pgUser -d $dbname -E UTF8 --encoding=UTF8 --no-privileges --no-owner | Out-File -Encoding UTF8 -FilePath `"$outputFile`""
Write-Host "Backing up: $outputFile"
Invoke-Expression $dumpCommand
Write-Host "Backup completed: $outputFile"
}

Write-Host "All backups were taken and saved to the '$backupFolderPath' folder."

if (Test-Path $folderPathToDelete) {
Remove-Item -Path $folderPathToDelete\* -Recurse -Force
Write-Host "Folder contents successfully deleted: $folderPathToDelete"
} else {
Write-Host "Folder to delete not found: $folderPathToDelete"
}

# Retrieve backup files from folder
$backupFiles = Get-ChildItem -Path $backupParentPath -File | Where-Object { $_.Extension -eq ".sql" }
$backupFilesSorted = $backupFiles | Sort-Object LastWriteTime -Descending
$newestFiles = $backupFilesSorted | Select-Object -First 10

foreach ($file in $backupFilesSorted) {
if (-not ($newestFiles -contains $file)) {
Remove-Item -Path $file.FullName -Force
Write-Host "Old backup file deleted: $($file.FullName)"
}
}

$rarFileName = "$backupParentPath\Linux_$date.rar"
Compress-Archive -Path $backupFolderPath -DestinationPath $rarFileName
Write-Host "Backup folder compressed: $rarFileName"

Sample BAT File

@echo off

:: Script name
set "scriptName=databaseBackup.ps1"

:: Get the directory where the current BAT file is located
set "batDir=%~dp0"

:: Script file Create the path

set "scriptPath=%batDir%%scriptName%"

:: Check if the file exists and run it with PowerShell
if exist "%scriptPath%" (
echo Script found: %scriptPath%
echo Executing script...
PowerShell.exe -NoProfile -ExecutionPolicy Bypass -File "%scriptPath%"
) else (
echo Script not found: %scriptName%
)

:: Waiting process
pause