PostgreSQL Setup and DB Migration
Last updated
Last updated
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).
Install
When installing PostgreSQL, ALWAYS use sql for the superuser:
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.
Database migration:
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
Change the Postgre Adress and Port if they are any different from the default values
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
Open pgAdmin and check if the database structure was generated.
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
If the migrated database has errors, you can delete the database as follows:
Create a new server profile in TimeLine Server, as follows:
Updated the new database (server Profile) with V17 Postgre SQLs only
