How to Restore PostgreSQL Databases (Using PowerShell)

This guide provides the steps to restore your CloudM PostgreSQL databases from a previous backup.

This procedure uses the Restore_BackupDatabases.ps1 PowerShell script. All steps must be performed on the machine where your PostgreSQL service is installed.

CRITICAL WARNING: This Process is Destructive

Running this restore script will DROP (delete) and REPLACE any existing databases that share a name with a database in the backup folder.

All current data in those databases will be permanently lost.

Only proceed if you are certain you want to overwrite the current data with the data from your backup files.

Prerequisites: Before You Begin

Before starting the restore, you must prepare the server and the required files.

1. Stop CloudM Services:
   • First, log in to the CloudM application and verify that no migrations are currently running.
   • On the server, open the Windows Services console (run services.msc).
   • Locate and Stop the CloudMigratorService. This is essential to release any active connections to the databases.
2. Locate Backup Files:
   • Ensure you have the folder containing the database backup files (e.g., .backup files) you intend to restore.
   • Example: C:\Backup_Databases
3. Obtain the Restore Script:
   • Download the Restore_BackupDatabases.ps1 script. (available at the bottom of this article).
   • Save the script to a known location on the server.

Restore Procedure

Follow these steps to run the PowerShell script and perform the restore.

1. Run PowerShell as Administrator:
   • Click the Start Menu.
   • Type "PowerShell ISE".
   • Right-click Windows PowerShell ISE and select Run as administrator.
2. Open the Script (if using ISE):
   • In PowerShell ISE, go to File > Open.
   • Navigate to and open the Restore_BackupDatabases.ps1 script you saved earlier.
3. Run the Script:
   • Click the Green "Play" button (Run Script) in the toolbar, or press F5.
   • If you are not using ISE, navigate to the script's directory and run it (e.g., .\Restore_BackupDatabases.ps1).
4. Respond to the Prompts:
   • The script will stop and ask for four pieces of information in the console.
   • PostgreSQL superuser login:
     • Enter the username for a PostgreSQL user with the SUPERUSER role.
     • The default user is postgres (recommended).
   • Password for [user]:
     • Enter the password for the superuser you specified.
   • Backup folder path:
     • Enter the full path to the backup folder containing your .backup files.
     • Example: C:\Backup_Databases
   • Path to pg_restore.exe:
     • Enter the full path to the pg_restore.exe utility, which is part of your PostgreSQL installation.
     • Default path: C:\Program Files\PostgreSQL\17\bin\pg_restore.exe
5. Monitor the Restore:
   • The script will run and restore all databases found in the backup folder. As noted, it will drop existing databases to replace them.
   • Wait for the script to complete.

Final Step: Restart and Verify

After the script has finished, you must restart the CloudM service and verify the restore.

1. Start CloudM Service:
   • Return to the Windows Services console (services.msc).
   • Locate and Start the CloudMigratorService.
2. Verify Functionality:
   • Log in to the CloudM application.
   • Check your dashboard, configurations, and migration history to ensure the restored data is present and the application is working as expected.