Migrate data from Postgres to MySQL

PostgreSQL Migration en mysql to pg 1

In this blog, we will discuss how to Migrate data from Postgres to MySQL. There are many reasons to migrate from one database to another which includes performance issues, stability, reliability, High Availability issues, costs for the license (dual license, GPL), etc.

Migrate data from Postgres to MySQL

1. Background

For a certain project of the company, the original production environment has always used PostgreSQL RDS, but later for some more advanced function extensions, it was replaced by MySQL RDS. Therefore, the database needs to be migrated.

Technology stack:

  • Node.js (Express)
  • React
  • PostgreSQL

The specific version is not introduced

2. Structure migration

Our Example is relatively simple, with only tables. There is no views, functions, stored procedures, triggers, etc. So there is no need to think about the structure too much. You can even migrate complex database.

Below Example generate sample row which I will be using to migrate Postgres to MySQL.

const { Sequelize, Model, DataTypes } = require('sequelize');
const sequelize = new Sequelize('postgres','my_user','my_pass', {
  dialect: 'postgres'
})


class User extends Model {}
User.init({
  username: DataTypes.STRING,
  birthday: DataTypes.DATE
}, { sequelize, modelName: 'user' });

(async () => {
  await sequelize.sync();
  const jane = await User.create({
    username: 'Durgeshkashyap',
    birthday: new Date(1996, 9, 30)
  });
  console.log(jane.toJSON());
})();
migrate data from postgres to mysql
Migrate data from Postgres to MySQL

3. Data migration

After the structure of the database is migrated, the next step is to migrate the data.

Our library is relatively simple and only involves table data.

STEP 1, Backup PostgreSQL

Usually, when we backup the Postgres database, we will add the -Fc parameter to indicate compression. But because this time we are migrating to a different database product, we can only export the plain text file of the SQL statement.

implement:

pg_dump --data-only --inserts --column-inserts -h <host_name> -U <user_name> -d <database_name> > ./XXXX_prod.sql

Parameter explanation:

  • --data-only: Only migrate data, not structure
  • --inserts: Generate a plain text file of SQL statement
  • --column-inserts: The generated INSERT statement will bring a list (that is, clearly specify the specific column name)

Result: The generated XXXX_prod.sql files.

STEP 2. Modify the XXXX_prod.sql file (generated in the previous step)#

(1) Remove schema

Remove public. from Query.

For example:

ACTUAL:

INSERT INTO public.users (id, username, birthday, "createdAt", "updatedAt") VALUES (2, 'Durgeshkashyap', '1996-10-30 00:00:00+00', '2021-09-07 10:33:58.631+00', '2021-09-07 10:33:58.631+00');

NEW QUERY:

INSERT INTO users (id, username, birthday, "createdAt", "updatedAt") VALUES (2, 'Durgeshkashyap', '1996-10-30 00:00:00+00', '2021-09-07 10:33:58.631+00', '2021-09-07 10:33:58.631+00');

Reason: PostgreSQL has a schema, but MySQL does not.

(2) Modify the format of the reference system identifier

Method: Change the reference involving table name + field name from the original “” package to “ package.

ACTUAL:

INSERT INTO users (id, username, birthday, "createdAt", "updatedAt") VALUES (2, 'Durgeshkashyap', '1996-10-30 00:00:00+00', '2021-09-07 10:33:58.631+00', '2021-09-07 10:33:58.631+00');

NEW QUERY:

INSERT INTO users (id, username, birthday, `createdAt`, `updatedAt`) VALUES (2, 'Durgeshkashyap', '1996-10-30 00:00:00+00', '2021-09-07 10:33:58.631+00', '2021-09-07 10:33:58.631+00')

Reason: As explained in the following extension:

The difference between some common wordings of PostgreSQL and MySQL

  • MySQL uses nonstandard ‘#’ to begin a comment line; PostgreSQL doesn’t. Instead, use ‘–‘ (double dash), as this is the ANSI standard, and both databases understand it.
  • MySQL uses ‘ or ” to quote values . This is not the ANSI standard for databases. PostgreSQL uses only single quotes.
  • Double quotes are used to quote system identifiers; field names, table names, etc. (i.e. WHERE “last name” = ‘Smith’).
  • MySQL uses ` (accent mark or backtick) to quote system identifiers, which is decidedly non-standard.

For more differences, please refer to: https://wiki.postgresql.org/wiki/Things_to_find_out_about_when_moving_from_MySQL_to_PostgreSQL

Summary: PostgreSQL is more compatible ANSI Standard and cross-platform.

Here is a recommended tool for converting pg to mysql sql from an online web: http://www.lightbox.ca/pg2mysql.php (You can copy and paste the dump of postgres and you will get dump in for MySQL. This tool has limited functions, and the modification of the reference system identifier can still be used, of course, you can also modify it manually) .

(3) Time zone conversion

The difference between PostgreSQL and MySQL about date data type and time zone

① Regarding the DateTime data type, the corresponding relationship between the two:

DescriptionPostgreSQLMySQL
Without time zonetimestampDateTime
With time zonetimestamptzTimestamp


Question: Is it better to use the one with the time zone or the one without the time zone?

Answer: It is recommended to bring a time zone.

Benefits of Time Zone:

  • Save you the trouble of considering different time zones. As long as the time zone of the database (or the time zone of the operating system) changes, the time with the time zone will be automatically adjusted to the value under this time zone .
  • For internationalization and future scalability considerations

​ ​② Who does the default time zone depend on?

PostgreSQL default depends on the database settings:

-- View time zone
show timezone;   -- PRC

-- Set time zone
select * from pg_timezone_names;   -- View the list of time zones to choose from
set timezone='UTC';  
migrate data from postgres to mysql

MySQL default depends on the time zone setting of the operating system:

-- View time zone

show variables like '%time_zone%';
migrate data from postgres to mysql

​③ If it is a data type with time zone, the difference between saving in, out, and display:

  • The PostgreSQL: 2021-09-07 17:00:00.22+05(intimate will take the time zone information: +05)
  • MySQL: 2021-09-07 17:00:00.22(There is no time zone information, if you want to know the time zone of this time? You need to check the current time zone of mysql)

The above expansion introduces the difference, so what are we going to do?

Because we used Sequelize, his DATE type, PostgreSQL is with time zone timestamptz type (the form of 2021-09-07 17:00:00.22+05), while MySQL is the time zone without DateTime type (the form of 2021-09-07 17:00:00.22), so we INSERT time, we should Strings +05 remove, so:

Practice: 

For example: ‘2021-09-07 15:02:10.616+05’ -> ‘2021-09-07 15:02:10.616’

Note: Remember to make sure that the time zone of MySQL is +05 when sql is executed.

Q: Why does Sequelize not correspond to the Timestamp type with the time zone in MySQL?

Answer: I did not find relevant explanations online. My own guess is that Sequelize considers that the Timestamp type has a 2038 problem, and the DateTime type has a wider range of values, which is the best choice, and it is used by many people (when I used MySQL before, I was used to DateTime)

(4) Statements that do not need to be commented

  • Note the sql statement of the latest value of the update sequence at the end of the file (because mysql does not have a separate sequence concept for postgres), such as:SELECT pg_catalog.setval('public."PocketShopPower_id_seq"', 6640, true);

Step 3. Execute XXXX_prod.sql statement on MySQL(Migrate data from Postgres to MySQL is Completed)

You can use Navicat or the command line. slightly.

More plans on Migrate data from Postgres to MySQL

1. GUI tools

  1. Navicat [Premium] (Pro-test is invalid, tools -> Data Synchronization can be used, but an error is reported, the SQL statement generated by check, I found that it did not help me to do the conversion!)
  2. MySQL Workbench (I haven’t tried it, see: https://mysqlworkbench.org/2012/11/how-to-migrate-postgresql-databases-to-mysql-using-the-mysql-workbench-migration-wizard/

2. Other

pg2mysql: https://github.com/pivotal-cf/pg2mysql

Hope You like our Migrate data from Postgres to MySQL Blog. Please subscribe to our blog for upcoming blogs.

Read More : AWS DynamoDB Tutorial | 2021, Best OS for Programming

Leave a comment

Your email address will not be published.


*