CRITICAL: Required Before Upgrading to Pyramid 2025.11 or Later

Required Timeline

1. Complete this migration. -- Taking the initial backup of the Pyramid repository database is critical. 
2. Verify everything is working properly. 
3. Proceed with the Pyramid 2025.11 or later upgrade.

---

Overview

This guide walks you through migrating your Pyramid Postgres server from PostgreSQL 9.6 to PostgreSQL 18. You can follow the manual process step-by-step or use the provided PowerShell script for automation(see the end of this article).

Prerequisites

• Administrative access to your Pyramid server(s).
• PostgreSQL 18 installer (download from postgresql.org).
• A database backup location with at least 2x the current database size in free disk space.
• All Pyramid services will be offline during this process.
• Note your Pyramid installation directory (default: C:\Program Files\Pyramid, but it may differ).
• PostgreSQL 18 must be installed before running this migration.

---

Manual Migration Process

Step 1: Install PostgreSQL 18

1. Download PostgreSQL 18 from postgresql.org.
2. Run the installer.
3. Accept the default settings:
   • Installation directory: C:\Program Files\PostgreSQL\18
   • Port: 5432
   • Username: postgres
   • Password: Set a secure password for the postgres superuser.
4. Skip Stack Builder at the end of the installation.
5. Complete the installation.

Step 2: Configure PostgreSQL 18 Host-Based Authentication

After installing PostgreSQL 18, you must configure which servers can connect to it. This is controlled by the pg_hba.conf file.

1. Open the following file with a text editor such as Notepad:
   C:\Program Files\PostgreSQL\18\data\pg_hba.conf
2. Find the sections labeled IPv4 local connections and IPv6 local connections.
3. Edit the below lines to allow your Pyramid servers to connect.

Below are two example configurations. Choose the configuration appropriate for your security requirements and work with your IT team to determine what is appropriate for your environment.

Example 1: Allow All Servers

Simplest configuration, but least secure:

# IPv4 local connections:
host    all    all    127.0.0.1/32    scram-sha-256
host    all    all    0.0.0.0/0       scram-sha-256

# IPv6 local connections:
host    all    all    ::1/128         scram-sha-256
host    all    all    ::/0            scram-sha-256

Example 2: Allow a Specific Subnet

More restrictive and secure:  --- change the ip's to your subnet ip's

# IPv4 local connections:
host    all    all    127.0.0.1/32      scram-sha-256
host    all    all    192.168.1.0/24    scram-sha-256

# IPv6 local connections:
host    all    all    ::1/128           scram-sha-256
host    all    all    fe80::/10         scram-sha-256

4. Save the file.

5. Increase max_connections. The default of 100 is too low for Pyramid's service pools. Open C:\Program Files\PostgreSQL\18\data\postgresql.conf in a text editor, find the CONNECTIONS AND AUTHENTICATION section, and set:

   max_connections = 1000

6. Restart the PostgreSQL 18 service:
   • Open services.msc.
   • Locate the PostgreSQL 18 service. The name may be PostgreSQL18, postgres-x64-18, or similar.
   • Right-click the service and select Restart.
   • Wait approximately 10–15 seconds for it to restart fully.

Verification

If you have secondary servers, test the connection from one of them:

psql -h [PostgreSQL18_IP] -U postgres -d postgres -p 5432

When prompted, enter the postgres superuser password. If the connection succeeds, authentication is configured correctly.

Step 3: Locate Your Pyramid Installation Directory

Your Pyramid installation may be in a non-default location. To locate it:

1. Open services.msc.
2. Right-click any Pyramid service and select Properties.
3. On the General tab, note the path shown for the service executable.

Common locations include:

• C:\Program Files\Pyramid
• C:\Pyramid
• D:\Applications\Pyramid

Remember this path—you will need it throughout this guide.

Step 4: Retrieve Database Credentials from config.ini

Extract your database connection details so that you can connect to the existing PostgreSQL 9.6 instance.

1. Navigate to your Pyramid installation directory.
2. Open config.ini with a text editor.
3. Find the [data] section.
4. Locate the relevant database values.

For example:

[data]
password=
type=postgres
port=12130
username=pyramid
database=pyramidb3e0d8e8
dbhost=EC2AMAZ-2TTQ7TS
dblocation=0
dbssl=0

Extract the following values:

• Username: pyramid
• Database Name: the value from database=
• Port: the value from port=
• Server/Host: the value from dbhost=

Construct the Password

The password stored in config.ini is encrypted, but the actual password Pyramid uses is constructed from the database name. 

• Take the database name and extract everything after the word pyramid.
• Password format: pyramid![everything_after_pyramid]

Example:

• Database name: pyramidb3e0d8e8
• Everything after pyramid: b3e0d8e8
• Password: pyramid!b3e0d8e8

Verify the PostgreSQL 9.6 Connection

1. Open pgAdmin 4.
2. Create a new server connection:
   • Right-click Servers → Create → Server.
   • Name: Pyramid_9.6_Backup
   • Open the Connection tab.
   • Host name/address: the value from dbhost=
   • Port: the value from port=
   • Username: pyramid
   • Password: the password constructed above.
   • Save password: Yes
3. Click Save.
4. Expand the connection and verify that you can see your databases.

This confirms that your credentials are correct before proceeding with the backup.

Step 5: Verify PostgreSQL 18 is Installed

1. Verify that C:\Program Files\PostgreSQL\18\bin exists.
2. Open services.msc.
3. Verify that the PostgreSQL 18 service exists.
4. Verify that you completed Step 2 and configured pg_hba.conf.

Step 6: Check Available Disk Space

1. Right-click the backup destination drive and select Properties.
2. Verify that free space is at least 2x the current database size.

To estimate the database size in pgAdmin 4:

• Right-click the Pyramid database.
• Select Properties → Statistics.
• Note the Size value.

Step 7: Stop Pyramid Services

1. Open services.msc.
2. Stop every service starting with Pyramid, except PyramidPostgreSQL.
3. Confirm that PyramidPostgreSQL is still Running.
4. Verify that all other Pyramid services are stopped before proceeding.

Step 8: Back Up config.ini

1. Navigate to your Pyramid installation directory.
2. Copy config.ini to a safe location.
3. Name the copy something such as config.ini.backup_[date].
4. Keep this file so that the original configuration can be restored if necessary.

Step 9: Create the Pyramid User in PostgreSQL 18

1. Open pgAdmin 4 installed with PostgreSQL 18.
2. Connect using the postgres superuser credentials.
3. Right-click Login/Group Roles → Create → Login/Group Role.
4. Configure the role:
   • Name: pyramid
   • Password: Use the password constructed in Step 4.
   • Under Definition, ensure Can login? is enabled.
   • Under Privileges, enable Can create databases? and Superuser?.
5. Save and close.

Step 10: Back Up the Current PostgreSQL 9.6 Database

1. Open pgAdmin 4 and connect to the existing PostgreSQL 9.6 instance.
2. Expand Databases and locate the Pyramid database identified in Step 4.
3. Right-click the database and select Backup.
4. Configure the backup:
   • Format: Custom or TAR
   • Filename: pyramid_backup_[date].backup
   • Compression: Medium
   • Path: A backup location with sufficient free space
5. Click Backup.
6. Wait for completion and monitor the Messages tab.

Verify the Backup

• Confirm that the .backup file was created.
• Confirm that its file size is reasonable compared with the source database size.

Step 11: Disable the Old PostgreSQL Service

1. Open services.msc.
2. Locate PyramidPostgreSQL. On some installations it may appear as PostgreSQL96 or postgresql-x64-9.6.
3. Right-click → Properties and set Startup type to Disabled.
4. Click Stop.
5. Confirm that the service is stopped and its startup type is Disabled.

Step 12: Restore the Database to PostgreSQL 18

1. Open pgAdmin 4 and connect to PostgreSQL 18.
2. Create a new database:
   • Right-click Databases → Create → Database.
   • Name: Use the exact database name identified in Step 4.
   • Owner: pyramid
   • Click Save.
3. Right-click the new database and select Restore.
4. Select the backup file created in Step 10.
5. Click Restore and monitor for errors.
6. Verify that the restore completes successfully.

Verify the Restore

• Right-click the restored database and select Properties → Statistics.
• Verify that its size is similar to the original database.
• Expand the database and verify that the expected tables exist.

Step 13: Update Pyramid Configuration on the Primary Server

1. Navigate to your Pyramid installation directory.
2. Open config.ini.
3. Locate the [data] section.
4. Change only the port:

   port=5432

5. Save the file.

Step 14: Update Additional Servers

If you have multiple Pyramid servers in your deployment:

1. On each additional Pyramid server:
   • Connect to the server.
   • Locate its Pyramid installation directory.
   • Back up config.ini.
   • Open config.ini.
   • Locate the [data] section.
   • Change only the port to 5432.
   • Do not change the username, password, database name, or dbhost.
   • Save the file.
2. Stop all Pyramid services on each secondary server, except PyramidPostgreSQL.
3. Restart the required Pyramid services on each secondary server.

Step 15: Restart Services on the Primary Server

1. Open services.msc.
2. Start all Pyramid services except PyramidPostgreSQL.
   • Right-click each service and select Start.
   • Wait approximately 3–5 seconds between each service start.
3. Wait approximately 60–90 seconds for all services to fully initialize.

Step 16: Update the Data Source Connection in Pyramid Admin Hub

1. Access Pyramid Admin Hub. Allow 1–2 minutes for services to fully start.
2. Navigate to Admin → Data → Data Source.
3. Locate and select Local PG.
4. In the Connection section, change the port from 12130 to 5432.
5. Click Test to verify connectivity.
6. If the test succeeds, click Apply.
7. Restart the remaining Pyramid services if prompted.

Step 18: Upgrade to Pyramid 2025.11 or Later

The PostgreSQL 9.6 to PostgreSQL 18 database migration is a prerequisite for upgrading to Pyramid 2025.11 or later.

Automate the process

Instead of doing all the above steps manually, you can use the attached Power shell script "Migrate-PyramidPostgres.ps1.zip" . To use the Power shell script attached to this article, first follow all steps 1-2.

Then download the attached Power shell script , unzip it and run as Administrator and it will do all the manually steps for you excluding steps 1-2.