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:
- 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.
- 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.
- 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.
- 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.
- 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.
- 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: