Skip to main content

Migrate data from PostgreSQL to KakaoCloud MySQL

This guide explains how to migrate data from PostgreSQL to KakaoCloud MySQL.

Basic information
  • Estimated Time: 30 minutes
  • Recommended OS: MacOS, Ubuntu
  • Prerequisites

About scenario​

In this scenario, we’ll walk through how to migrate data from PostgreSQL to KakaoCloud MySQL. Key steps include:

  • Setting up the migration environment: Configure security groups and create a bastion host to prepare for data migration.
  • Creating a MySQL database: Provision a managed MySQL instance in KakaoCloud console.
  • Transforming PostgreSQL data: Generate a PostgreSQL dump using pg_dump and convert it to MySQL format using the pg2mysql converter tool.
  • Importing and verifying MySQL data: Import the transformed data into the MySQL instance and confirm data integrity.

Scenario Architecture

info

pg2mysql Converter Tool is an open-source software based on PHP. For more details, please refer to the official webpage.

Before you start​

1. Network environment setup​

Before setting up data migration and replication on KakaoCloud, configure the network environment by creating a VPC and public subnet based on the following values:

  1. Go to KakaoCloud console > Networking > VPC.

  2. Select the [+ Create VPC] button and configure the VPC and subnet as shown below:

    CategoryItemSetting/Input Value
    VPC InformationVPC Nametutorial
    VPC IP CIDR Block10.0.0.0/16
    Availability ZoneNumber of Availability Zones1
    First AZkr-central-2-a
    Subnet SettingsNumber of Public Subnets per AZ1
    Public Subnet CIDR Block10.0.0.0/20

  3. Verify the topology displayed at the bottom and select [Create] if it is correct.

    • The status of the subnet will change in the following order: Pending Create > Pending Update > Active. Ensure the status is Active before proceeding.

2. Configure security group​

Configuring security groups is essential for securing data transfer and allowing required traffic.

Security Group: tutorial-bastion-sg

  1. Go to KakaoCloud console > VPC > Security Group and create a security group based on the table below:

    NameDescription (optional)
    tutorial-bastion-sgSecurity policy for the bastion host

  2. Select [+ Add] to set the following inbound rules, and then select [Apply].

    Inbound PolicyItemSetting
    bastion inbound policy 1ProtocolTCP
    Source IP{Your Public IP}/32
    Port Number5432
    bastion inbound policy 2ProtocolTCP
    Source IP{Your Public IP}/32
    Port Number22

3. Set up bastion host​

The bastion host serves as a gateway for securely accessing cloud instances remotely for data migration and MySQL access.

  1. Go to KakaoCloud console > Beyond Compute Service > Virtual Machine.

  2. Refer to the table below to create a VM instance that will serve as the bastion host.

    CategoryItemSetting/Input ValueNotes
    Basic InfoNametutorial-migration
    Count1
    ImageUbuntu 20.04
    Instance Typem2a.large
    VolumeRoot Volume20 GB
    Key Pair{USER_KEYPAIR}Ensure the key pair is securely stored.
    NetworkVPCtutorial
    Subnetmain (10.0.0.0/20)
    Security Grouptutorial-bastion-sg

4. Create MySQL database instance​

Create a MySQL instance to serve as the target for data migration. KakaoCloud’s MySQL is a fully managed database service that provides secure access within a logically isolated network.

  1. Go to KakaoCloud console > Data Store > MySQL.

  2. Under the Instance Group tab, select [Create Instance Group] and configure the instance group based on the following table:

    CategoryItemSetting/Input ValueNotes
    Basic SettingsInstance Group Nametutorial-mysql
    DescriptionOptional
    MySQL SettingsEngine VersionUse specified version
    MySQL User Nameadmin
    MySQL Passwordroot1234
    Instance AvailabilitySingle (Primary Instance)
    Instance Typem2a.large
    StorageStorage Type/Size100
    Log StorageLog Storage Type/Size100
    NetworkVPCtutorial
    Subnetmain (10.0.0.0/20)
    Automatic BackupAutomatic Backup OptionDisabled

Migration steps​

Step 1. Configure instance for migration​

  1. In Virtual Machine > Instance tab, select the pre-created MySQL database instance tutorial-migration and select [Assign Public IP] to assign a public IP.

    • The assigned Public IP can be checked under the Network tab.

  2. Open a terminal in your local environment, navigate to the folder where the key pair file was downloaded (typically, ~/Downloads), and run the following command to SSH into the instance:

    chmod 400 {PRIVATE_KEY}.pem # Grant read permissions
    ssh -i {PRIVATE_KEY}.pem ubuntu@{tutorial-migraton-public-ip}
    ItemDescription
    {private_key}Name of the private key file
    {tutorial-migration-public-ip}This can be found by selecting the tutorial-migration instance from the Virtual Machine > Instance menu, then checking the Network tab

  1. Configure the migration instance using the following commands.

    # Configure Migration Instance
    sudo apt-get update -y

    sudo apt-get install php -y

    wget https://www.easyredmine.com/ER/media/knowledge-base/pg2mysql-1.9.zip

    unzip pg2mysql-1.9.zip

Step 2. Creating a PostgreSQL data dump​

A pg_dump (PostgreSQL Dump) is a utility and command used for backing up or migrating database data.

  1. Run the following command to create a PostgreSQL dump.

    pg_dump -U postgres -h 127.0.0.1 -p 5432 {DATABASE_NAME} -n public --format=p > {PG_DUMP_FILE_NAME}.sql

  2. Convert the PostgreSQL dump to a MySQL dump.

    php pg2mysql_cli.php {PG_DUMP_FILE_NAME}.sql {MYSQL_DUMP_FILE_NAME}.sql innodb

  3. Modify the converted MySQL file as shown below.

    Example: MYSQL DUMP File AS-IS
    # Converted with pg2mysql-1.9
    # Converted on Thu, 25 Jul 2024 05:11:47 +0000
    # Lightbox Technologies Inc. http://www.lightbox.ca

    SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
    SET time_zone="+00:00";

    CREATE TABLE public.users (
       id int(11) NOT NULL,
       name varchar(50),
       email varchar(50)
    ) ENGINE=innodb;

    INSERT INTO `public.users` (`id`, `name`, `email`) VALUES
    ('1','Alice','alice@example.com'),
    ('2','Bob','bob@example.com'),
    ('3','Charlie','charlie@example.com');

    ALTER TABLE public.users
       ADD CONSTRAINT users_pkey PRIMARY KEY (id);

    Example: MYSQL DUMP File TO-BE
    # Converted with pg2mysql-1.9
    # Converted on Thu, 25 Jul 2024 05:11:47 +0000
    # Lightbox Technologies Inc. http://www.lightbox.ca

    SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
    SET time_zone="+00:00";

    -- Create the database
    CREATE database {DATABASE_NAME};

    -- Select the database
    USE {DATABASE_NAME};

    CREATE TABLE users (
       id int(11) NOT NULL,
       name varchar(50),
       email varchar(50)
    ) ENGINE=innodb;

    INSERT INTO users (id, name, email) VALUES
    ('1', 'Alice', 'alice@example.com'),
    ('2', 'Bob', 'bob@example.com'),
    ('3', 'Charlie', 'charlie@example.com');

    ALTER TABLE users
       ADD CONSTRAINT users_pkey PRIMARY KEY (id);

    info

    Since KakaoCloud MySQL does not have a database for data import, CREATE is required.

Step 3. Import data into KakaoCloud MySQL instance​

Import the MySQL dump file into your KakaoCloud MySQL instance.

mysql -u {USER} -h {MYSQL_ENDPOINT} -p {DATABASE_NAME} < {MYSQL_DUMP_FILE_NAME}.sql

Step 4. Verify data consistency​

Verify the data consistency of the imported database in KakaoCloud MySQL.

mysql> use {DATABASE_NAME};
Database changed

mysql> select * from {TABLE_NAME};