In daily works, sometimes we may need to convert our database, and migrate the date to new database. In this article, I will introduce a method to convert and migrate database from mysql to postgresql with sql tools.

1. Required Tools

• MySQL
• PostgreSQL
• SQLynx

2. Differences Between MySQL and PostgreSQL

2.1 Data Structure

2.2 Basic Syntax

2.3 Other

3. Table Creation and Data Migration

3.1 DDL Statements for Table Creation and Data Migration

3.1.1 Table Creation with DDL Statements

Refer to the MySQL DDL statements for the source tables and modify them to fit PostgreSQL syntax. Execute the modified DDL statements at the target location to create new tables in PostgreSQL.

Here is a reference for converting MySQL statements to PostgreSQL:

For example:
MySQL Source Table:

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'order id',
    order_date DATETIME NOT NULL COMMENT 'order date',
    customer_id INT NOT NULL COMMENT 'customer id',
    total DECIMAL(10, 2) COMMENT 'total amount',
    status TINYINT(1) COMMENT 'status',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'create time',
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='order table';

Converted to PostgreSQL:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date TIMESTAMP NOT NULL,
    customer_id INT NOT NULL,
    total NUMERIC(10, 2),
    status BOOLEAN,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
);

COMMENT ON TABLE orders IS 'Order Table';
COMMENT ON COLUMN orders.order_id IS 'Order ID';
COMMENT ON COLUMN orders.order_date IS 'Order Date';
COMMENT ON COLUMN orders.customer_id IS 'Customer ID';
COMMENT ON COLUMN orders.total IS 'Total Amount';
COMMENT ON COLUMN orders.status IS 'Status';
COMMENT ON COLUMN orders.created_at IS 'Creation Time';

3.1.2 Data Migration
Use SQLynx for data migration.

1. Right-click the source table name and select Data Migration.

1. In the popup, choose the target table location.

1. Select the data migration mode, such as Stop on Error, Continue on Error, Transaction Execution, and whether to clear existing data in the target table.

1. Confirm and click Submit to execute the migration.

3.2 Create Table by Import

You can export data from MySQL source tables to local files and import them into PostgreSQL with SQLynx.

1. Select the PostgreSQL target location, right-click the table, and choose Create Table by Import.

1. In the popup, select the local source file.

1. In Table Mapping, column names are automatically recognized. The default data type is varchar, but you can modify it to other types such as timestamp, int, etc., as needed.

1. Preview the data to be imported. After confirmation, click Submit to execute the import.

1. Add constraints to the table, such as primary keys, unique keys, foreign keys, etc.

4. Data Validation

Verify the data in the target tables by executing queries to ensure that the import is completed as expected.

5. Considerations

5.1 Data Cleaning
Before adding constraints to existing data tables, check and clean the data.

• Primary Keys: Ensure that all values in the column are unique and non-null. If there are duplicates or nulls, clean the data first.

ALTER TABLE table_name ADD CONSTRAINT table_name_pkey PRIMARY KEY (column_name);

• Unique Keys: Ensure all values in the column are unique. Clean the data if duplicates exist.

ALTER TABLE table_name ADD CONSTRAINT unique_constraint_name UNIQUE (column_name);

• Foreign Keys: Ensure data consistency. Values in foreign key columns must exist in the referenced table.

ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES other_table(other_column);

• Data Cleaning

DELETE FROM table_name WHERE column_name IS NULL;
DELETE FROM table_name t1 USING table_name t2 WHERE t1.id > t2.id AND t1.column_name = t2.column_name;

5.2 Performance Impact

1. Adding a primary key involves a full table scan, which may impact performance, especially for large tables. It is advisable to perform this operation during low load, off-peak hours.
2. In high-load environments, consider adding constraints in batches.