How to Back Up PostgreSQL Databases (Using PowerShell)

This guide provides the steps to create a complete backup of all non-system PostgreSQL databases used by CloudM.

This procedure requires using the Backup-AllPGDatabases.ps1 PowerShell script available at the bottom of this article. All steps must be performed on the machine where your PostgreSQL service is installed.

Prerequisites: Before You Begin

Before starting the backup, you must prepare the server and obtain the necessary script.

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.
2. Create a Backup Folder:
   • Create a new, empty folder on your server to store the database backups.
   • Example: C:\Backup_Databases
3. Obtain the Backup Script:
   • Download the Backup-AllPGDatabases.ps1 script. (available at the bottom of this article).
   • Save the script to a known location on the server (e.g., C:\Backup_Databases or your desktop).

Backup Procedure

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

1. Run PowerShell as Administrator:

   • Click the Start Menu.
   • Type "PowerShell ISE".
   • Right-click Windows PowerShell ISE and select Run as administrator.

   Note: You can also use the standard "Windows PowerShell" terminal, but it must be run as an administrator.

2. Open the Script (if using ISE):
   • In PowerShell ISE, go to File > Open.
   • Navigate to and open the Backup-AllPGDatabases.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 (e.g., cd C:\Backup_Databases) and run it (e.g., .\Backup-AllPGDatabases.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 you created in the prerequisites.
     • Example: C:\Backup_Databases
   • Path to pg_dump.exe:
     • Enter the full path to the pg_dump.exe utility, which is part of your PostgreSQL installation.
     • Default path: C:\Program Files\PostgreSQL\17\bin\pg_dump.exe
5. Monitor and Verify:
   • The script will run and back up all non-system databases.
   • Once the script is finished, check your backup folder (e.g., C:\Backup_Databases) to confirm that the .backup files have been created successfully.

Final Step: Restart Service

After you have verified that your backup files are present, you must restart the CloudM service.

1. Return to the Windows Services console (services.msc).
2. Locate and Start the CloudMigratorService.