There are many open source RDBMS. But the commonest of all are MySQL and PostgreSQL both of which are rich in programming APIs and administration tools. While both are quite competitive, there are many reasons a lot of businesses will choose PostgreSQL over its contemporary. These are full compliance with the standards of ANSI SQL, compatibility with a wide variety of indexing models, asynchronous commit, and compatibility with synchronous and asynchronous replication.
Generally, there are steps involved in MySQL to PostgreSQL database migration:
- Table definitions are extracted with corresponding indexes and constraints. The definitions are extracted in form of SQL CREATE-statements from MySQL database source.
- Before loading into the target database, the statements are converted into destination format.
- Using an intermediate storage such as CSV files, export data from MySQL database.
- The exported data is transformed to the format of the destination and loaded into Postgres database.
- Views, triggers, and stored procedures should be extracted from MySQL database. However, extraction format should be SQL statements and source code.
- Before loading into the PostgreSQL database, both extraction formats from the previous step should be converted into destination format.
- Lastly, ensure that proper conversion has been made to the database objects.
Manual MySQL to PostgreSQL database migration is a procedure which requires loads of efforts. More than this, human factor has a tendency to expose data to high risk of corruption or loss. Therefore, the conversion process should be automated with a special software.
Fortunately, some Intelligent Converters has developed such automation tools. Since 2001, this software vendor has catered to database migration, synchronization, and conversion solutions. Most average modern systems cannot boast of high performance of about 10,000 records per second. But this is not so with the MySQL to PostgreSQL converter. This is because of it:
- Makes no use of middleware libraries or components for direct reading or writing of data.
- Supports all MySQL to PostgreSQL versions, plus cloud solutions.
- Automats and schedules database conversion by providing command line version.
- Merges and synchronizes MySQL database with existing PostgreSQL database.
Furthermore, data can be filtered via SELECT queries when there is need to migrate selected records. This option allows the selection of particular record and columns or the transformation of data before it is converted from MySQL to PostgreSQL.
A dialogue window called ‘custom column mapping’ is another feature that helps to change column type in the resulting database. Properties of the column of a table such as a type, name, NULL-attribute, and default value can be changed as well. However, certain columns can be excluded from conversion.
If direct connection to the destination database server is not available or to get more control over MySQL to PostgreSQL migration, the data can be exported into SQL script when the target PostgreSQL prevents remote connection. The database administrator performs basic steps in this approach including:
- Export of database into ‘SQL-statement’ files.
- Creation of tables containing indexes and constraints.
- Filling the tables with data.
- Import of the script file to PostgreSQL server with standard tools such as phpPgAdmin or psql.
Visit the official vendor’s site to learn more about MySQL to PostgreSQL converter.
