In today’s dynamic corporate climate, data migration between database systems is a deliberate move that coincides with changing organizational demands. This block describes the migration of PostgreSQL to MySQL. This change necessitates meticulous preparation to handle possible issues and guarantee smooth interaction with existing business procedures.<\/span><\/p>\n <\/p>\n Understanding the data type differences between PostgreSQL and MySQL is critical for a successful move. Here are some important distinctions:<\/span><\/p>\n <\/p>\n <\/p>\n Migration Tools from PostgreSQL to MySQL:<\/b><\/p>\n Several solutions can help with the conversion from PostgreSQL to MySQL, preserving data integrity while decreasing manual labor. Here are some popular options:<\/span><\/p>\n <\/p>\n SQLines’ features include data transfer, database schema (DDL) conversion, procedures, and scripting. It supports a large number of PostgreSQL and MySQL versions .<\/span><\/p>\n It may require tweaking for complicated schema configurations. Performance might vary according to data amount.<\/span><\/p>\n Features: A step-by-step wizard guides you through database conversion, and it supports a variety of RDBMS systems, including PostgreSQL .<\/span><\/p>\n Limitations: On big datasets, GUI difficulties and performance bottlenecks might degrade the user experience. Some manual interventions may be necessary.<\/span><\/p>\n <\/p>\n Features: Provides an automated pipeline for data migration that requires little setup. It also allows for continuous data integration between PostgreSQL and MySQL via live sync.<\/span><\/p>\n Limitations: A paid tool requiring license fees. There may be limits for certain sophisticated data types or formats.<\/span><\/p>\n <\/p>\n Features: A script for converting PostgreSQL dump files to MySQL format, including CREATE TABLE, INSERT INTO, CREATE INDEX, and ALTER TABLE commands.<\/span><\/p>\n Limitations include known concerns with complicated schema items such as triggers and stored procedures. Requires manual intervention for certain data type conversions. In this blog we are going to discuss the pg2mysql tool. It’s an open source migration tool.<\/span><\/p><\/blockquote>\n Unzip main.zip file after installation: Creating a dummy database in postgres and creating four tables: employees, departments, project_assignments, products.<\/span><\/p>\n Checking tables:<\/span><\/p>\n Verification:<\/span><\/p>\n <\/p>\n Here I used the pg_dump to take the backup in the SQL format and convert the file to MySQL compatible format using the pg2mysql script. It’s written in perl.<\/span><\/p>\n Execute the SQL file in MySQL to restore the database.<\/span><\/p>\n Note: In mysql schema is called database.<\/span> pg2mysql is a useful tool for basic migrations, however it has a few limitations:<\/span><\/p>\n Credit:Credit goes to:James Grant Lightbox Technolgoies http:\/\/www.lightbox.org\/<\/a>\u00a0, https:\/\/github.com\/ChrisLundquist\/pg2mysql<\/a><\/span><\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" Introduction In today’s dynamic corporate climate, data migration between database systems is a deliberate move that coincides with changing organizational demands. This block describes the migration of PostgreSQL to MySQL. This change necessitates meticulous preparation to handle possible issues and guarantee smooth interaction with existing business procedures. Basic Differences PostgreSQL Advanced Feature Set: PostgreSQL…<\/p>\n","protected":false},"author":1,"featured_media":2762,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24],"tags":[95,103,111,113,178,197,202,250,266,285,319],"class_list":["post-2761","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","tag-cloud-database","tag-data-transfer","tag-database-conversion","tag-database-migration","tag-it-solutions","tag-migration-tools","tag-mysql","tag-pg2mysql","tag-postgresql","tag-postgresql-to-mysql","tag-sql-databases","category-24","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2761","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2761"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2761\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2762"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2761"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2761"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2761"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Basic Differences<\/b><\/h2>\n
PostgreSQL<\/b><\/h3>\n
\n
MySQL<\/b><\/h3>\n
\n
Data Type Differences<\/b><\/h2>\n
\n\n
\n PostgreSQL<\/b><\/td>\n MySQL<\/b><\/td>\n Notes<\/b><\/td>\n<\/tr>\n \n SERIAL<\/span><\/td>\n INT AUTO_INCREMENT<\/span><\/td>\n MySQL uses AUTO_INCREMENT for auto-incrementing fields.<\/span><\/td>\n<\/tr>\n \n BOOLEAN<\/span><\/td>\n TINYINT(1)<\/span><\/td>\n MySQL doesn\u2019t have a BOOLEAN type; uses TINYINT(1) instead.<\/span><\/td>\n<\/tr>\n \n BYTEA<\/span><\/td>\n BLOB \/ BINARY<\/span><\/td>\n MySQL uses BLOB or BINARY for binary data; careful attention needed for conversions.<\/span><\/td>\n<\/tr>\n \n TEXT<\/span><\/td>\n TEXT<\/span><\/td>\n Both systems support TEXT, but handling and performance may vary.<\/span><\/td>\n<\/tr>\n \n JSON<\/span><\/td>\n JSON<\/span><\/td>\n Both support a JSON type but with different manipulation functions.<\/span><\/td>\n<\/tr>\n \n ARRAY<\/span><\/td>\n N\/A<\/span><\/td>\n MySQL lacks native ARRAY type; use JSON or join tables for similar functionality.<\/span><\/td>\n<\/tr>\n \n UUID<\/span><\/td>\n VARCHAR(36)<\/span><\/td>\n MySQL typically stores UUIDs as VARCHAR(36).<\/span><\/td>\n<\/tr>\n \n TIMESTAMP”<\/span><\/td>\n TIMESTAMP<\/span><\/td>\n Both support TIMESTAMP, but default behaviors and timezone handling may differ.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n SQLines:<\/span><\/h3>\n
<\/h3>\n
MySQL Workbench Migration Wizard:<\/span><\/h3>\n
Hevo Data:<\/span><\/h3>\n
pg2mysql:<\/span><\/h3>\n
\n<\/span><\/p>\nData Migration using pg2mysql:<\/span><\/h2>\n
Installing pg2mysql from github repository.<\/span><\/h3>\n
[postgres@mysql-01 ~]$ wget https:\/\/github.com\/dolthub\/pg2mysql\/archive\/refs\/heads\/main.zip\n--2024-12-31 12:07:12-- https:\/\/github.com\/dolthub\/pg2mysql\/archive\/refs\/heads\/main.zip\nResolving github.com (github.com)... 20.207.73.82\nConnecting to github.com (github.com)|20.207.73.82|:443... connected.\nHTTP request sent, awaiting response... 302 Found\nLocation: https:\/\/codeload.github.com\/dolthub\/pg2mysql\/zip\/refs\/heads\/main [following]\n--2024-12-31 12:07:15-- https:\/\/codeload.github.com\/dolthub\/pg2mysql\/zip\/refs\/heads\/main\nResolving codeload.github.com (codeload.github.com)... 20.207.73.88\nConnecting to codeload.github.com (codeload.github.com)|20.207.73.88|:443... connected.\nHTTP request sent, awaiting response... 200 OK\nLength: unspecified [application\/zip]\nSaving to: 'main.zip'\n\nmain.zip [ <=> ] 18.72K --.-KB\/s in 0.04s\n\n2024-12-31 12:07:16 (531 KB\/s) - 'main.zip' saved [19169]\n\n<\/pre>\n
\n<\/span><\/p>\n[postgres@mysql-01 ~]$ unzip main.zip\nArchive: main.zip\ndd956ff207178dc67750568a375f50025cc37b38\n creating: pg2mysql-main\/\n inflating: pg2mysql-main\/LICENSE\n inflating: pg2mysql-main\/README.md\n inflating: pg2mysql-main\/pg2mysql.pl\n creating: pg2mysql-main\/test\/\n inflating: pg2mysql-main\/test\/auto-increment.bats\n inflating: pg2mysql-main\/test\/basic-types.bats\n inflating: pg2mysql-main\/test\/begin-end.bats\n inflating: pg2mysql-main\/test\/cli-args.bats\n inflating: pg2mysql-main\/test\/common.bash\n inflating: pg2mysql-main\/test\/indexes-keys-constraints.bats\n inflating: pg2mysql-main\/test\/unsupported-types.bats\n\n[postgres@mysql-01 ~]$ cd pg2mysql-main\/\n[postgres@mysql-01 pg2mysql-main]$ ll\ntotal 36\n-rw-r--r-- 1 postgres postgres 11358 Oct 9 2023 LICENSE\n-rwxr-xr-x 1 postgres postgres 20249 Oct 9 2023 pg2mysql.pl\n-rw-r--r-- 1 postgres postgres 1778 Oct 9 2023 README.md\ndrwxr-xr-x 2 postgres postgres 186 Oct 9 2023 test\n<\/pre>\n
postgres=# create database dummy;\nCREATE DATABASE\npostgres=# \\c dummy\nYou are now connected to database \"dummy\" as user \"postgres\".\ndummy=#CREATE TABLE employees (\ndummy=# employee_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,\ndummy=# first_name VARCHAR(50) NOT NULL,\ndummy=# last_name VARCHAR(50) NOT NULL,\ndummy=# email VARCHAR(100) UNIQUE NOT NULL,\ndummy=# hire_date DATE NOT NULL,\ndummy=# salary NUMERIC(10, 2) CHECK(salary > 0)\ndummy=#);\nCREATE TABLE\ndummy=#CREATE TABLE departments (\ndummy=# department_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,\ndummy=# department_name VARCHAR(100) NOT NULL\ndummy=#);\nCREATE TABLE\ndummy=#CREATE TABLE project_assignments (\ndummy=# employee_id INT,\ndummy=# project_id INT,\ndummy=# assignment_date DATE NOT NULL,\ndummy=# PRIMARY KEY (employee_id, project_id)\ndummy=#);\nCREATE TABLE\ndummy=#CREATE TABLE products (\ndummy=# product_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,\ndummy=# product_name VARCHAR(100) NOT NULL,\ndummy=# price NUMERIC(10, 2) DEFAULT 0.00,\ndummy=# created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\ndummy=#);\nCREATE TABLE\n\n<\/pre>\n
dummy=# \\dt+\n List of relations\n Schema | Name | Type | Owner | Persistence | Access method | Size | Description\n--------+---------------------+-------+----------+-------------+---------------+------------+-------------\n public | departments | table | postgres | permanent | heap | 8192 bytes |\n public | employees | table | postgres | permanent | heap | 8192 bytes |\n public | products | table | postgres | permanent | heap | 8192 bytes |\n public | project_assignments | table | postgres | permanent | heap | 8192 bytes |\n(4 rows)<\/pre>\n
dummy=# \\d\n List of relations\n Schema | Name | Type | Owner\n--------+---------------------+-------+----------\n public | departments | table | postgres\n public | employees | table | postgres\n public | products | table | postgres\n public | project_assignments | table | postgres\n(4 rows)\n<\/pre>\n
Taking backup of database in postgres & convert to MySQL schema:<\/span><\/h3>\n
[postgres@mysql-01 ~]$ pg_dump -U postgres -p 5432 -d dummy -f \/var\/lib\/pgsql\/download\/dummy.sql\n[postgres@mysql-01 ~]$ \/var\/lib\/pgsql\/pg2mysql-main\/.\/pg2mysql.pl < \/var\/lib\/pgsql\/download\/dummy.sql > \/var\/lib\/pgsql\/download\/dummy1.sql 2>warnings.txt<\/pre>\n
Restoring database in mysql:<\/span><\/h3>\n
[root@mysql-01 pgsql]# mysql -u root -p < \/var\/lib\/pgsql\/download\/dummy1.sql\nEnter password:\n\n<\/pre>\n
Verification Steps:<\/span><\/h3>\n
\n<\/span><\/p>\nmysql> \\u public\nReading table information for completion of table and column names\nYou can turn off this feature to get a quicker startup with -A\n\nDatabase changed\nmysql> show tables;\n+---------------------+\n| Tables_in_public |\n+---------------------+\n| departments |\n| employees |\n| products |\n| project_assignments |\n+---------------------+\n4 rows in set (0.00 sec)\n\nmysql> desc departments;\n+-----------------+--------------+------+-----+---------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+-----------------+--------------+------+-----+---------+-------+\n| department_id | int | NO | PRI | NULL | |\n| department_name | varchar(100) | NO | | NULL | |\n+-----------------+--------------+------+-----+---------+-------+\n2 rows in set (0.07 sec)\n\nmysql> desc employees;\n+---------------+---------------+------+-----+---------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+---------------+---------------+------+-----+---------+-------+\n| employee_id | int | NO | PRI | NULL | |\n| first_name | varchar(50) | NO | | NULL | |\n| last_name | varchar(50) | NO | | NULL | |\n| email | varchar(100) | NO | UNI | NULL | |\n| hire_date | date | NO | | NULL | |\n| salary | decimal(10,2) | YES | | NULL | |\n| department_id | int | YES | MUL | NULL | |\n+---------------+---------------+------+-----+---------+-------+\n7 rows in set (0.00 sec)\n\nmysql> desc products;\n+--------------+---------------+------+-----+-------------------+-------------------+\n| Field | Type | Null | Key | Default | Extra |\n+--------------+---------------+------+-----+-------------------+-------------------+\n| product_id | int | NO | PRI | NULL | |\n| product_name | varchar(100) | NO | | NULL | |\n| price | decimal(10,2) | YES | | 0.00 | |\n| created_at | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |\n+--------------+---------------+------+-----+-------------------+-------------------+\n4 rows in set (0.00 sec)\n\nmysql> desc project_assignments;\n+-----------------+------+------+-----+---------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+-----------------+------+------+-----+---------+-------+\n| employee_id | int | NO | PRI | NULL | |\n| project_id | int | NO | PRI | NULL | |\n| assignment_date | date | NO | | NULL | |\n+-----------------+------+------+-----+---------+-------+\n3 rows in set (0.00 sec)<\/pre>\n
Limitations of pg2mysql<\/span><\/h2>\n
\n