Starting with IDS versions 2.24+, we've begun using PostgreSQL 17. For users utilizing prior versions, instructions for backing up and upgrading are below.

Backing up your current database:

Step 1: Perform a Database Backup:

Option A: Logical Backup: 

1. Open the Command Prompt or PowerShell as an Administrator. 

2. Run the following command to back up all databases: 

•  pg_dumpall -U postgres > C:\backup\backup.sql 

3. Replace C:\backup\backup.sql with your preferred backup path. 

Option B: Physical Backup (recommended): 

1. Stop the PostgreSQL 11 service:

• either run the command net stop postgresql-x64-11, or stop the service in Windows Services 

2. Copy the entire data directory to a safe location.

Step 2: Install PostgreSQL 17:

1. Download the PostgreSQL 17 installer from the official PostgreSQL website or use this link.

2. Run the installer and follow the installation wizard:

• Choose a new installation directory (e.g., C:\Program Files\PostgreSQL\17).
• Do not overwrite the existing PostgreSQL 11 installation.

3. Initialize a new PostgreSQL 17 instance when prompted during installation.

Step 3: Perform the Upgrade

Option 1: Using pg_upgrade

1. Open Command Prompt or PowerShell as Administrator. 

2. Navigate to the bin directory of PostgreSQL 17: 

• cd "C:\Program Files\PostgreSQL\17\bin" 

3. Run pg_upgrade with the appropriate paths: 

• pg_upgrade.exe ^ 
        --old-datadir "C:\Program Files\PostgreSQL\11\data" ^ 
        --new-datadir "C:\Program Files\PostgreSQL\17\data" ^ 
        --old-bindir "C:\Program Files\PostgreSQL\11\bin" ^ 
        --new-bindir "C:\Program Files\PostgreSQL\17\bin" ^ 
        --username postgres 

4. If the upgrade completes successfully: 

• Run the analyze_new_cluster.bat script generated by pg_upgrade to optimize performance. 
• Run the delete_old_cluster.bat script to remove old PostgreSQL data (optional).

Option 2: Using Logical Dump and Restore

1. Dump the Data: 

• Open the Command Prompt and run: 
   pg_dumpall -U postgres > C:\backup\backup.sql 
   

2. Restore the Data: 

• Start the PostgreSQL 17 service (if not running): 

  net start postgresql-x64-17

• Restore the backup: 

  psql -U postgres -f C:\backup\backup.sql

Step 4: Update Configuration:

1. Review the postgresql.conf and pg_hba.conf files in the PostgreSQL 17 data directory: 

• Ensure settings such as listen_addresses, port, and authentication methods match your requirements. 

2. Test the connection to PostgreSQL 17 using psql or a client tool like pgAdmin.

Step 5: Verify the Upgrade:

1. Check that all databases, extensions, and schemas are accessible. 

2. Test your application to ensure compatibility with PostgreSQL 17.

Step 6: Cleanup (Optional):

1. Remove PostgreSQL 11 (if no longer needed): 

• Uninstall via Add or Remove Programs in the Control Panel. 

2. Delete the old data directory after confirming the upgrade was successful.