PostgreSQL Setup and DB Migration

Important Considerations for Database Migration to PostgreSQL

When migrating a database to PostgreSQL, please take note of the following:

• Database users will be lost. The only user that will remain is the DBA user.

• The following routines will not be migrated: database triggers, events, procedures, and functions. All db objects (tables, views, functions etc) are now lowercase.

• Views will be migrated (best effort).

1. Install PostgreSQL 17.5

When installing PostgreSQL, ALWAYS use sql for the superuser:

PostgreSQL superuser password: sql

Postgres Service Users and Migration

The migration introduces a secondary admin user, named DBA, to maintain consistency with ASA. Initially, sticking to SQL password is recommended purely for the migration process.

The default postgres database, containing metadata and templates, is essential and must not be deleted.

The users are global to the PostgreSQL service. After the migration, you can still easily change passwords for the users.

You can change the password anytime following these steps:

open PGAdmin -> rightclick the postgres user -> properties -> tab 2 -> password: sql -> save.

1. Database migration:

Use a TimeLine Server patch released after July 25, 2025.

The last Standard SQL for V16 is 9132. We highly recommend updating your V16 database to the latest SQLs using TimeLine Server 16 before continuing.

The migrated DB will have the name of the old database - not the name of the profile in TL Server (consider renaming it to v17 before creating the TL Server 17 profile).

Open TimeLine Server 17, right click on the database you want to migrate (Database provider SAOLEDB.17). Attention, this will create a new database, so the ASA Profile will still be working

Migration of ASA17 Database to PostgreSQL

1. Change the Postgre Adress and Port if they are any different from the default values

Changing PG Adress and Port at DB Migration

Troubleshooting migration issues

• In the root folder of TL Server17, there's a log file called "output.log"

• Consider deleting existing data before each migration, as new migrations append information without resetting it

• Check the log file for foreign key (FK) error messages by searching for "violates foreign key constraint" or by scrolling to the file's end to find similar lines as shown in the screenshot below

• Below each error line in the log, the corresponding failed SQL command is displayed. Alternatively, navigate to the /_PG_Migration subfolder and open reload_pg.sql and locate the line number indicated by the error message (e.g., line 39943 in the screenshot) for further investigation

• 

1. Open pgAdmin and check if the database structure was generated.

Hint: if you don’t have any tables listed, there was a problem with the migration. If this happens, do not hesitate to contact us.

pgAdmin4 Database Structure

1. To check if the database was migrated successfully, verify if the double columns from the ASA17 database are now double precision. Anything else is wrong

ASA17 double data type converted to double precision in PostgreSQL

Incorrect migration of double data type. We had a bug in an old version of the migration tool.

1. If the migrated database has errors, you can delete the database as follows:

Deleting database from pgAdmin

1. Create a new server profile in TimeLine Server, as follows:

TL Server Profile setup

1. Updated the new database (server Profile) with V17 Postgre SQLs only

Updating PostgreSQL Database in TimeLine Server 17