Database Migration from PostgreSQL 16.x to PostgreSQL 17.x
Codebeamer 3.2 supports PostgreSQL 17.x databases. If your existing PostgreSQL database version is 16.x, you must migrate to PostgreSQL 17.x.
Ensure that the following prerequisites are met before starting the migration process:
• PostgreSQL 17 is installed along with PostgreSQL 16.
• Both the databases are running on different ports. For example, PostgreSQL 16 is running on port 5433 and PostgreSQL 17 is running on port 5434.
• Both PostgreSQL instances have separate binary paths and data directories.
• A backup of PostgreSQL 16 database is taken.
Migration Steps
1. Create the .pgpassfile. This file is required to avoid password prompts during the migration process.
Consider the following while creating this file:
◦ Ensure that the file does not have any extension.
◦ On Windows, save the file at the following location:
C:\Users\<Your Username>\.pgpass.
◦ Include the host and port details of both the PostgreSQL databases in the following format:
▪ hostname:pg16_port:pg16_database:pg16_username:pg16_password
▪ hostname:pg17_port:pg17_database:pg17_username:pg17_password
For example:
▪ localhost:5433:*:postgres:yourpassword
▪ localhost:5434:*:postgres:yourpassword
2. Drop the adminpack extension only on PostgresSQL 16 database.
This step is required as the adminpack extension which is supported for PostgresSQL 16 is no longer supported for PostgresSQL 17.
To drop the adminpack extension:
1. Connect to PostgreSQL 16 database.
2. Run the following command:
DROP EXTENSION IF EXISTS adminpack;
 |
Do not run this command on other databases.
|
3. Run the pg_upgrade tool.
1. Open command prompt with the Admin permission and run the tool from a separate directory. For example, C:\pgupgrade.
2. Run the following command to add the directory to the PATH environment variable. Ensure that both the databases are stopped before running this command.
C:\pgupgrade> set PGPASSFILE=C:\Users\<username>\.pgpass
3. Run the following command to start migrating the data. Ensure that paths are updated if required.
C:\pgupgrade> "C:\Program Files\PostgreSQL\17\bin\pg_upgrade.exe" -b "C:\Program Files\PostgreSQL\16\bin" -B "C:\Program Files\PostgreSQL\17\bin" -d "C:\Program Files\PostgreSQL\16\data" -D "C:\Program Files\PostgreSQL\17\data" -U postgres -p <Postgres 16 port> -P <Postgres 17 port>
4. Start PostgreSQL 17 server.
Ensure that the PostgreSQL 17 server is running on the configured port before going to the next step.
5. Run the VACUUM ANALYZE command if you want to delete the PostgreSQL 16 database.
C:\pgupgrade> "C:\Program Files\PostgreSQL\17\bin\vacuumdb" -U postgres -p 5434 --all --analyze-in-stages
6. Delete the \.pgpass file after migration to avoid security breach.
7. Update the Codebeamer 3.1 configuration.properties file located at <Server Install Location>\config directory to add details of PostgreSQL 17 database such as host, port, user name, and password.
8. Upgrade Codebeamer server from 3.1 to 3.2.