Migrating from MS-SQL Server to PostgreSQL Database

First, we need a SQL Server with the “AdventureWorks2014” database. Secondly, we will need a Windows/Linux system with PostgreSQL 10/11/12/13 version installed. The database we will transfer is as shown in the picture below. Now, for a quick and easy migration, we will use the project ready from the link “https://github.com/isapir/Migrate2Postgres”. We download the project to our computer.

After downloading the project, we put the “migrate.bat” file in “examples > bin” and the “MsSqlAWT2Postgres.conf” file in “examples → conf” into the home directory of the same project.

Now we will edit the “Conf” file. When we open the “conf” file with a text editor, a ready-made template for “AdventureWorks” will appear. We will only be entering the necessary information here. You can see the screen below for the places to be fixed in the “conf” file.

{
    /**
     * Migration project name
     */
     name : "AdventureWorks"  //**project name**
    /**
     * Template that will be used for DDL and DML settings. The settings from this file
     * will be merged with the template's settings.  If a template is not used
     * then all of the settings must be in this config file.
     */
    ,template : "ms-sql-server"
    ,information_schema : {
        /**
         * This value is required for information_schema.query in the ms-sql-server template
         */
         database_name : "AdventureWorks"  //**Name of the Database you will transfer**
    }
    /**
     * The names of the connections that will be used as source (copy from) and target (copy to).
     * The keys of source and target must appear in the connections segment of the config.
     */
    ,source : "mssql"
    ,target : "postgres"
    /**
     * Defines the available database connections.  Each connection must
     * have a connectionString key.  Other keys are optional and will be
     * added to the Properties object when connecting to the database.
     */
    ,connections : {
         mssql : {
              connectionString : "jdbc:sqlserver://localhost:1433"
             ,user             : "%connections.mssql.user%" //**username of the database**
             ,password         : "%connections.mssql.password%" //**database user password**
             ,databaseName     : "AdventureWorks" //**database name**
        }
        ,postgres : {
              connectionString : "jdbc:postgresql://localhost:5432/adventure_works"
             ,user             : "postgres"//**username of the database**
             ,password         : "" //**database user password**
        }
    }
    /**
     * Mappings for schema names from source to target, e.g. HumanResources -> hr
     */
    ,schema_mapping : {
         dbo            : "public"
        ,HumanResources : "hr"
    }
    /**
     * Mappings for table names from source to target, e.g. HumanResources -> hr
     * runs before table_transform
     */
    ,table_mapping : {
         ProductModelProductDescriptionCulture : "ProductModelProdDescCulture"
    }
    /**
     * Mappings for column names in case the source db has keyword names like "group"
     * or "primary", or has a space that we want to remove.
     * runs before column_transform
     */
    ,column_mapping : {
         "Database Version" : "db_version"
        ,group              : "group_name"
        ,primary            : "is_primary"
    }
    /**
     * Specify whether to transform the table and/or column names.  Available transforms are:
     *   lower_case          - transforms MyTableName to mytablename
     *   upper_case          - transforms MyTableName to MYTABLENAME
     *   camel_to_snake_case - transforms MyTableName to my_table_name
     */
    ,table_transform  : "camel_to_snake_case"
    ,column_transform : "camel_to_snake_case"
}

Then we open our file on the command line (cmd).

migrate.bat ddl

And we enter the code. We are running our migration file.

It has created a sql file in the specified path using the “config” file written as specified in the field we marked with red. You need to open the resulting file with a text editor and run it in PostgreSQL. With this process, you create the “DDL” structure of the source database on the PostgreSQL side. After running the DDL Script, you can see that the “AdventureWorks” database is created on the PostgreSQL side without any errors.

Finally, we need to transfer the “DML” contents that exist in SQL Server, and it will be sufficient to run the “migrate.bat” file as follows.

migrate.bat dml

When you check after the transfers are complete, you will see that your existing data in SQL Server will be transferred to PostgreSQL.