Skip to main content
Version: 2.0.0 (latest)

Migrating from other tools

pgModeler works with PostgreSQL only. If your database lives in another system, the path into pgModeler has three steps: convert the database to PostgreSQL with a migration tool, check the result, and import it into pgModeler through reverse engineering. From there, the model is yours: you can reorganize it, document it and keep the database and the model in sync with the diff.

note

The tools below are independent projects and are not part of pgModeler. The commands are only an outline: check each project's documentation for the options of the version you install.

Step 1: convert the database to PostgreSQL

Create an empty database on a PostgreSQL server (a test server is a good idea) and use one of the tools below to fill it.

pgloader​

pgloader loads data into PostgreSQL from several sources and, for the databases it understands, also migrates the schema (tables, indexes, foreign keys), converting types by a set of default rules that can be adjusted in a command file. It reads, among others, MySQL/MariaDB, SQLite, SQL Server and CSV files. In its simplest form a migration is a single command with the source and the target:

pgloader mysql://user:password@mysql-host/source_db \
         postgresql://user:password@pg-host/target_db
pgloader sqlite:///path/to/source.db postgresql://user@pg-host/target_db

At the end pgloader prints a report with the tables loaded and the errors found. See the pgloader documentation for the rules of type conversion and for the command file syntax.

ora2pg​

ora2pg is the usual choice for Oracle (it also reads MySQL). It is driven by a configuration file (ora2pg.conf) and exports the schema and the data in separate steps, which gives you the SQL scripts to review before running them on PostgreSQL. Besides tables and indexes, it can also convert views, sequences, triggers, functions and procedures (the PL/SQL code is translated to PL/pgSQL, but not always completely). It can also report how hard the migration will be before you start it:

ora2pg -c ora2pg.conf -t SHOW_REPORT --estimate_cost
ora2pg -c ora2pg.conf -t TABLE -o tables.sql
ora2pg -c ora2pg.conf -t COPY -o data.sql

Run the generated scripts on the target database with psql. Consult the ora2pg documentation for the available export types.

Step 2: check the result in PostgreSQL

Neither tool replaces a review. Before importing into pgModeler, look at the converted database and check at least:

  • Data types. Types without an exact equivalent become the closest one (TINYINT, ENUM, NUMBER, DATETIME...). Check whether the choice makes sense for your data.
  • Auto-increment columns. They usually become sequences or identity columns; confirm that the sequences were positioned after the largest existing value.
  • Names. Identifiers in upper case or with spaces may have been kept between quotes, which forces you to quote them in every query. Decide whether to rename them.
  • Code. Stored procedures, triggers and views in the dialect of the origin system often need manual work, and some are not converted.
  • Constraints and indexes. Compare the counts of tables, indexes and foreign keys with the origin.

Fixing these in the PostgreSQL database now is simpler than fixing them later in the model.

Step 3: import into pgModeler

note

The database import (reverse engineering) is a pgModeler Plus feature. See Community and Plus editions.

Open pgModeler, create a connection to the converted database and run the import with Ctrl + Shift + I, as described in Import a database. pgModeler reads the system catalogs and builds the model with all the tables, relationships (inferred from the foreign keys), constraints and other objects it finds.

After the import:

  1. Arrange the tables on the canvas. The auto-arrange options in Advanced tips and tricks give you a first layout to refine by hand.
  2. Validate and save the model.
  3. From now on, keep the model as the source of truth and use the diff to apply changes to the database.