Migrating from MySQL to PostgreSQL Using pgloader

Pgloader is a migration tool that facilitates the transfer of data from MySQL, SQLite and MS SQL Server to PostgreSQL. It is free and needs the following prerequisites:

• To gain access to servers that implement a firewall, you need to use a non-root user with sudo privileges.
• The MySQL root user authenticates using a password and permits encrypted connections.
• PostgreSQL server must be the client machine of MySQL.

As soon as you fulfill all these requirements, Pgloader can be installed from the Ubuntu APT repositories using the apt command. However, in order to use the useSSL option in v3.5.1 and later, it is necessary to install from the source code that can be obtained from the GitHub repository.

After the installation of Pgloader succeeds, the database migration procedure containing the following steps may be started:

• Generate PostgreSQL role and database. PostgreSQL database users can authenticate through the identification protocol/ident (which is the default) or password. Although the default option provides more security, it causes issues with external connections.

  If a role authenticates using the same method and name as the Linux user profile that run the tool, Pgloader can upload data into a PostgreSQL database using that role. This whitepaper explores how to create a PostgreSQL role with password authentication and generate a target database for loading the source data.

• Create new MySQL user for database migration and handle certificates. This user should be granted access to the target. Include the ca.pem and client-cert.pem files in the trusted certificate store of Ubuntu OS to enable SSL for Pgloader. Remember to modify the names of these files to incorporate the .crt file extension.
• Replace incompatible values for MySQL datetime types. Unlike MySQL, PostgreSQL does not accept 0000-00-00 00:00:00 datetime values. Those values must be replaced by 1970-01-01 00:00:00, so that PostgreSQL could accept it.
• Resolve MySQL table and index names that are too long PostgreSQL. MySQL allows longer names for tables and indexes than PostgreSQL, so pgLoader have to trancate them to shorter forms. It may lead to duplicate name conflicts when the first 63 characters of multiple object names are the same.
• Resolve duplicated index and constraint names. MySQL requires index and constraint names are unique within the table. In PostgreSQL index names must be unique within the schema.
• Migrate data from MySQL to PostgreSQL.

  $ pgloader mysql://mysql_username:password@mysql_host/source_database_name? 
  useSSL=true postgresql:// postgresql://postgresql_role_name:password@postgres_host/target_database_name
  ?option_1=value&option_n=value
  

Have questions? Contact us