Database Migration to PostgreSQL: How to Do It Like a Winner

The primary challenge or issue in database migration often lies not with the data itself but with the code and logic governing its operations (such as procedures, functions, etc.). While data conversion can often be managed, rewriting code is often much more complex. 

So, database migration to PostgreSQL is like moving into a new home. All your precious stuff moves with you to the new house. 

It’s not a walk in the park, but you know it’s exciting to do new things when you get there. 

Likewise, migrating your precious data from SQL Server, Oracle, SQLite, MariaDB, or MySQL to PostgreSQL poses challenges but it’s well worth it. The popularity of PostgreSQL these days is not without merit, as you will see later. 

Here’s what’s in store for you in this comprehensive article: 

• Why Migrate to PostgreSQL 
• Preparing for Migration to PostgreSQL 
• Migration Process with Examples 
• MySQL Migration to PostgreSQL 
• Oracle Migration to PostgreSQL 
• SQL Server Migration to PostgreSQL 
• SQLite Migration to PostgreSQL 
• MariaDB Migration to PostgreSQL 

Before we dive into the nitty-gritty of database migration, let’s discuss why you would want to migrate there in the first place. 

Why Migrate to PostgreSQL 

According to the StackOverflow Developer Survey in 2019, 2020, 2021, and 2022, PostgreSQL is the #2 most popular database. But in 2023, PostgreSQL took the top spot for professional developers. The following reasons make PostgreSQL a bigger deal: 

1. Open Source and Cost-Efficiency: 

• PostgreSQL is open source. So, it’s free to use, modify, and distribute. If you’re avoiding licensing costs or vendor lock-in, PostgreSQL is appealing. 

2. Feature-Rich and Standards-Compliant: 

• PostgreSQL has advanced features. It supports different index types, JSONB for document storage, table inheritance, and custom data types. That’s why it’s not just a relational database. It’s object-relational. And speaking of SQL standards, it’s also highly compliant. 

3. Scalability and Performance: 

• PostgreSQL has robust support for vertical and horizontal scaling and offers performance-tuning features. You can use these power-ups if your company experiences high data growth and performance demands. 

4. Community Support and Extensibility: 

• PostgreSQL has a large community. So, resources to support your needs are plentiful. PostgreSQL is also highly extensible. Though it already has a lot of data types, it allows you to create your own. Functions can also be coded in C if that’s your cup of tea. You can also extend PostgreSQL functionality using extensions—and many more extensibility features. 

5. Flexibility: 

• PostgreSQL’s flexible architecture supports a wide range of use cases. From OLTP (Online Transaction Processing) to OLAP (Online Analytical Processing) to NoSQL-like document storage – you name it. 

6. Compatibility with Multiple Platforms: 

• PostgreSQL is cross-platform. PostgreSQL can operate on a local server under the control of any operating system or in the cloud, including popular platforms like AWS, Azure, Google Cloud, Heroku, and others. 

Sounds exciting? 

It sure does! 

Preparing for Migration to PostgreSQL 

Data migration is not a copy-paste operation or a simple copying of files. If that were so, you wouldn’t be reading this article because there’s no need for it. 

Databases are not created equal, so you need to know your source database well and the rules in PostgreSQL. That way, your data will fit just right, and no data loss will happen. And your application will blend in like the migration never happened. Also, your users won’t feel it’s slower, but just the same or faster. 

Typically, migration works this way: 

• Create the target database’s structure in PostgreSQL. This includes all the tables, stored procedures, functions, etc. If you will only convert the source database structure, you can start by creating a script of the source database. Then, edit that script for use in PostgreSQL. GUI tools like dbForge Studio, SQL Server Management Studio, and others can create the initial script for you in a few clicks. 
• Modify your app to adapt to the new PostgreSQL database. You need a new library or package to connect and use PostgreSQL from your app. 
• Using a migration tool, you extract the data from the source database, transform some data, if necessary, and load it to your new PostgreSQL database. 
• Validate the migrated data and test your app with the new PostgreSQL database. 

At the outset, it looks easy-peasy. But there are key considerations in doing all of these. Consider the following pointers to help you prepare for a smooth migration. 

Compatibility and Schema Differences 

Data Types 

This is your very first consideration. Ignore this part and you will only encounter errors. No migration will take place. The source table and the PostgreSQL counterpart may have the same table and column names. Yet, their data types may be incompatible. 

Examples: 

The SQL Server NVARCHAR data type is not available in PostgreSQL. You can use CHARACTER VARYING or VARCHAR for that purpose. Then, your PostgreSQL database should support the same multibyte character sets in SQL Server. PostgreSQL does not support multiple character sets in one database. So, use the most applicable character set for your use case. 

Another example is the MEDIUMINT data type in MySQL. You can use INTEGER in PostgreSQL for this. 

Finally, the flexible data typing in SQLite. There are only a few data types in it like TEXT, INTEGER, REAL, NUMERIC, and BLOB. But you can store text in INTEGER columns. So, the rules are not strict by default. This can become problematic in PostgreSQL. So, it depends on what kind of data is in a column. Then, decide the closest PostgreSQL data type. For example, if string values are stored in INTEGER columns, decide for a CHARACTER VARYING in PostgreSQL. It may require cleaning the data after the migration. 

Indexes and Constraints 

Aside from column data types, consider indexes and constraints like primary and foreign keys. 

For indexes, you may initially adopt B-Tree indexes in PostgreSQL. Then, modify them later to a different index type, if applicable. If there’s a difference in the indexing capabilities, adopt the closest one in PostgreSQL. You need to test for the performance impact of these indexes. 

Creating primary keys may differ compared to PostgreSQL. MySQL uses AUTO_INCREMENT for integer types. But in PostgreSQL, you can use SERIAL. Meanwhile, you may choose to create the foreign keys after the migration. Or create them from the start but you have to sequence the data migration correctly to avoid foreign key violations. 

Other table constraints need to be there before data migration, like unique constraints. You want a cleaner migration. So, make sure to include all constraints from the source. 

Views, Stored Procedures, Functions, and Triggers 

Depending on the source database, you may need a major rewrite. 

For example, SQL Server stored procedures can return a result set if a SELECT statement is the last statement in the procedure. You need to rewrite this as a function in PostgreSQL and return a query result. 

Stored procedures and functions may be written in another language other than pgSQL. And you can’t support that language for some reason. Currently, supported languages aside from pgSQL are C, Python, Perl, and Tcl. Some extensions add support for Java and .Net languages. 

Another example is indexed views in SQL Server. The equivalent in PostgreSQL is a materialized view. 

And finally, triggers. Some platforms support INSTEAD OF triggers, but others do not. 

There are syntax differences between PostgreSQL and other database platforms for views, procedures, functions, and triggers. Check the documentation of each for the latest syntax and features available. 

Other Considerations 

The above pointers are the most common considerations. Other factors that may affect converting your current database to PostgreSQL are table partitioning, Unicode support, collation, and if the source uses data replication. Check the official documentation for other differences. 

Application Compatibility 

Moving to PostgreSQL will affect the app using the database. So, note the following pointers: 

• SQL Queries: Your applications may use source database-specific syntax or features. Identify these SQL queries that need adjustment to work with PostgreSQL. For example, you changed a SQL Server stored procedure to a PostgreSQL function to return a result set. So, instead of EXEC <stored_procedure>, you use SELECT * FROM <function>. 
• Drivers and Libraries: Ensure your application’s database drivers and libraries are compatible with PostgreSQL. Use a PostgreSQL JDBC, ODBC, or ADO.Net libraries. 
• Connection Details: Your app’s connection string will change. So, update your app’s configuration for this to adapt to the new database. 

User Access and Security 

User access also needs to be in PostgreSQL. Usernames and passwords, including their database object permissions, should be the same in PostgreSQL. You may need to adopt if the source database uses a different security access. For example, your SQL Server database source may use Windows Authentication. Moving to PostgreSQL requires the same. And it needs the SSPI or GSSAPI authentications. To see all supported authentication methods in PostgreSQL go to PostgreSQL official website. 

Other considerations are security certificates and encryption/decryption methods. 

Tools and Strategies for Migration to PostgreSQL 

• Migration Tools: Choose appropriate tools for migrating data and schema to PostgreSQL. Some database IDEs have Import/Export utilities, such as SQL Server Management Studio and dbForge Studio for PostgreSQL. Alternatively, if you require complex transformations, consider using data integration tools. While PostgreSQL does offer built-in utilities for migrating from various platforms, they may not always suffice, and you may need to explore third-party services or other solutions. 
• Migration Strategy: Make a detailed migration plan, including incremental migration, data backup, and rollback strategies. Consider downtime and business impact during migration. 
• Stakeholder Communication: Make sure to inform everyone using the app and database about the plan. 

Migration to PostgreSQL Examples 

To migrate from the most popular relational databases like MySQL, MariaDB, Oracle, and SQL Server to PostgreSQL, you can leverage dbForge Edge, a multidatabase solution developed by Devart.

However, for now, let’s delve into each migration case individually. 

This is the meat of this article and we will use examples with pictures so you can see what you need to prepare. 

Migrate MySQL to PostgreSQL 

If we’re going to migrate the sakila sample database in MySQL to PostgreSQL, how will it be? 

Preparation 

First, make individual object scripts in MySQL. Then, use it to make the sakila database in PostgreSQL. Edit as needed. Here’s a sample using the film table. Look at the column declarations in MySQL and PostgreSQL below: 

The major items are highlighted in green. Let’s enumerate them below. 

MySQL vs PostgreSQL Syntax Comparisons 

• PostgreSQL has no UNSIGNED integers, including the TINYINT data type. It’s not SQL-standard compliant. 
• Also, check out the primary key constraint. The unsigned SMALLINT with AUTO_INCREMENT becomes SERIAL in PostgreSQL. 
• Notice the replacement of the MySQL ENUM. You need to create a new type in PostgreSQL as an equivalent. 
• The MySQL SET data type also has no PostgreSQL equivalent. A CHARACTER VARYING is the replacement. You can also use TEXT[] or an array of TEXT if you wish. 
• Keywords like ENGINE and ROW_FORMAT are MySQL-specific and have no equivalent in PostgreSQL. 

The above is only one table sample. There are more. Aside from that, there are indexes, constraints, procedures, triggers, views, and functions. 

Data Migration 

Using the Import tool in dbForge Studio for PostgreSQL, we will import 1000 rows from the film table in MySQL into the film table in PostgreSQL. You can access it from the top-level menu (see below) or by right clicking the table in Database Explorer. 

It will launch the Import wizard and we will use ODBC to connect to MySQL. You need a MySQL ODBC driver for this. 

Connect to MySQL Through ODBC 

Then, you need to supply the connection string to MySQL. You will need an ODBC data source name (DSN) configured. 

The above shows a successful connection to MySQL through an ODBC DSN. 

Define the MySQL Source and the PostgreSQL Target 

Then, choose the source and target table. You need to specify the film table in MySQL (source), and the film table in PostgreSQL (target). See below: 

Data Formats, Column Mappings, and Import Mode 

The Data Formats page will help you deal with strange date/time formats, numbers, and boolean values in data. Change the options as appropriate. After that, click Next. 

Then, you need to map the source and target columns, as seen below: 

Then, change the mode as applicable. Use Append if you’re doing it for the first time. Or Repopulate if you will repeat the process. 

Output, Error Handling, and Import 

You can set the Output to a file or directly import the data. Then, click Next. 

You have 3 options for error handling: 

• abort the import the moment an error occurs, 
• prompt to deal with it individually, 
• or simply ignore them. 

Then, you can click Import from here to start the import process. You will know when it’s done. See a sample below: 

You can click Finish to end the Import. Or Import more to import another table. 

To validate the imported data, you can check for the same row count and column values from both databases. 

Automation Using the Command Line 

If you do an incremental data migration, you may need to repeat some table Import processes for some time. If this is the case, click Save -> Save Command line to save the import command in a batch file (.bat). See a sample below: 

Then, you can schedule it to run on your preferred schedule using Windows Task Scheduler. This automation should work, not just on MySQL, but on others as well. 

Oracle Migration to PostgreSQL 

For Oracle, we will use a human resources sample database downloaded from GitHub. 

Preparation 

Follow the instructions from GitHub to install the sample database in Oracle. Then, you need to script each object and modify them to conform to PostgreSQL syntax. You can use dbForge Studio for Oracle or any Oracle IDE that does the same. 

Below is an example of the EMPLOYEE table in Oracle and its PostgreSQL counterpart: 

As you can see, there are several differences already. Let’s discuss each of them below: 

• NUMBER and VARCHAR2 data types are Oracle-specific. You can use PostgreSQL INTEGER or DECIMAL depending on the decimal places defined for the NUMBER column. Then, use PostgreSQL CHARACTER VARYING for VARCHAR2. 
• The syntax in Oracle is heavy on the use of the TABLESPACE. PostgreSQL has a concept of TABLESPACE too but it’s different from Oracle. Check their respective documentation as it is out of topic to discuss them here. So, the syntax in PostgreSQL is made simpler without the TABLESPACE. 
• The Oracle syntax also named the NOT NULL constraint. In PostgreSQL, it is simply NOT NULL without a name. 
• The rest of the syntax is almost the same for the two databases. 

Data Migration 

The steps to migrate data using the Import tool from dbForge Studio for PostgreSQL are the same. But you need another ODBC driver for Oracle. Download the one from Oracle and use it here. 

Migrating the data is flawless if the PostgreSQL syntax for table creation is followed. Follow the Import wizard and you can’t go wrong. Check out the Source and Target settings are shown below: 

You can click Import after you verify the column mappings. 

Finally, validate the imported data against the source to see if everything has been migrated. Import another table until all the tables have been migrated to PostgreSQL. 

SQL Server Migration to PostgreSQL 

We will use the AdventureWorks SQL Server sample database. There are many tables here with triggers, indexes, and constraints. Aside from that, there are stored procedures, functions, types, and more. 

Preparation 

You need to create the AdventureWorks database in PostgreSQL. You can script the entire database in SQL Server Management Studio or dbForge Studio for SQL Server. Then, modify it so it conforms to PostgreSQL syntax. 

Below is one example using the Employee table. You will need two other extensions because of the incompatible data types between SQL Server and PostgreSQL. 

There’s a lot to point out here, so let’s enumerate each. 

SQL Server vs. PostgreSQL Syntax Comparisons 

• The NVARCHAR in SQL Server is CHARACTER VARYING (or VARCHAR) in PostgreSQL. 
• Next, the HIERARCHYID in SQL Server becomes LTREE in PostgreSQL. These types are good for organizational structures as they deal with hierarchies. You need to install the “ltree” extension using CREATE EXTENSION to make this work in PostgreSQL. 
• The OrganizationalLevel column is a computed column in SQL Server. The GetLevel function of the HIERARCHYID data type provides the level of the employee in the organizational chart. This is the same as a generated column in PostgreSQL. And the NLEVEL function of the LTREE data type provides the value. 
• Then, the NCHAR in SQL Server is CHARACTER (or CHAR) in PostgreSQL. 
• Next, the dbo.Flag is a user-defined type in AdventureWorks. We simply replaced this with BOOLEAN as the effect is the same without creating a new type in PostgreSQL. 
• The UNIQUEIDENTIFIER in SQL Server is UUID in PostgreSQL. Meanwhile, the NEWID() in SQL Server corresponds to UUID_GENERATE_V4() in PostgreSQL. You need to install the “uuid-ossp” extension for this function. You can use the gen_random_uuid() as an alternative function. 
• Next, the SQL Server DATETIME data type for ModifiedDate column is the same as TIMESTAMP in PostgreSQL. Getting the current date and time in SQL Server uses GETDATE(). Use the CURRENT_TIMESTAMP in PostgreSQL to achieve the same output. 
• Then, default values for BirthDate and HireDate columns used date computations and the SQL Server DATEADD does this. Use the PostgreSQL INTERVAL to get the same output. 
• Finally, replace ON PRIMARY GO with a simple semicolon (;) to end the statements. 

This needs a lot of planning to convert the entire database to PostgreSQL. 

Data Migration 

We will use the Import tool in dbForge Studio for PostgreSQL again to migrate the data. So, the steps are the same as in MySQL. But you need another ODBC DSN to connect to SQL Server. 

Let’s highlight a few important things like the Source and Target tables for the employee table sample. See below: 

As you can see above, you need to click the Custom Query button as your source. This is a must because simply picking the employee table from the list will not work. There’s a needed transformation for the OrganizationNode column (a HIERARCHYID column). SQL Server uses numbers and backslashes to represent HIERARCHYID values. See samples below under the OrganizationNode column. 

You can see from above that nodes /1/, /2/, and /3/ report to the CEO (node null – the highest in the hierarchy). And node /1/1/ (Engineering manager) reports to /1/ (Vice President of Engineering). More numbers and slashes appear as you go down the hierarchy. 

But LTREE in PostgreSQL uses dots to separate the numbers, not backslashes. So, we need to convert it. See the custom query below: 

The above shows that we need to convert the HIERARCHYID values to VARCHAR first. Then, replace the backslashes with dots. Finally, SQL Server HIERARCHYID values also contain backslashes in the first and last characters. Hence, the SUBSTRING function started scanning at the second character. And stops scanning before the last character. See the output of the custom query after the transformation: 

Did you see the dots replace the backslashes? And there are no more dots at the start and end. This will now work flawlessly for data migration. 

And the target table? Simply point it to the employee table in PostgreSQL. Check the column mappings before clicking Import. 

In the end, this is migrating one table. It’s not that simple but it’s doable. I trust the rest of the AdventureWorks database objects are in your capable hands. 

SQLite Migration to PostgreSQL 

There are only a few data types in SQLite: TEXT, INTEGER, REAL, BLOB, and NUMERIC. But the rules are flexible. 

Preparation 

Just like in MySQL and other databases, you need a script of the SQLite database and edit it to conform to PostgreSQL syntax. 

Here’s a sample SQLite table and its counterpart in PostgreSQL. 

The above structure is simple. The SQLite INTEGER should be SERIAL in PostgreSQL. The rest are CHARACTER VARYING. 

Data Migration 

There won’t be much problem importing the suppliers table from SQLite to PostgreSQL. The only potential is the column sizes. In SQLite, you don’t need to add column sizes. That is not the case in PostgreSQL. 

Note that you will need another ODBC driver for SQLite. 

The following are the Source and Target definitions: 

You can go through the entire wizard or just click Import. 

MariaDB Migration to PostgreSQL 

Since MariaDB is a fork of MySQL, the schema preparation and data migration are the same as MySQL. But still consult the latest MariaDB documentation for some variations. 

Conclusion 

That’s it. 

This article gave you the pointers to convert or migrate MySQL, Oracle, SQL Server, SQLite, and MariaDB to PostgreSQL. We also had examples to demonstrate how it is done.  

While the focus was primarily on migration through import utilities, it’s worth mentioning that in some scenarios, migrating data via SQL scripts preparation may be necessary. 

We encourage you to practice this using sample databases to get a taste of database migration to PostgreSQL. If you’re looking for a good Import tool in an IDE, check out dbForge Studio for PostgreSQL (or dbForge Edge), which also offers SQL export functionality, facilitating the transition. 

Happy migrating! 

Tags: convert mysql to postgresql, database migration, migrate from mysql to postgresql, migrate mysql to postgresql, migrate oracle to postgresql, migrate sql server to postgresql, mysql to postgresql migration, postgres, postgresql, sql server to postgresql migration tool, sql to postgresql converter Last modified: October 03, 2025