Perform a MySQL to Postgres Migration with PGLoader docker container and command-file

Datetimes in mysql are stored…

Login

Blog History

Datetimes in mysql are stored as below with a Timezone. These dates as diplayed are stored in Australia/Sydney timezone

# bb_date	production_date	id
2024-03-16	2022-09-15 14:26:36	248078
2024-03-16	2022-09-15 14:29:51	248079
2024-03-16	2022-09-15 14:34:30	248080

When they arrive in Postgres after being imported with pgLoader they are being stored in UTC

"2024-03-16"	"2022-09-15 04:26:36+00"	248078
"2024-03-16"	"2022-09-15 04:29:51+00"	248079
"2024-03-16"	"2022-09-15 04:34:30+00"	248080

To make the offset correct when they reash postgres you need to set the timezone in the load file for pgloader as follows

Modify the user:password database server IP or hostname, port and DB names

-- file name is named pgload.load
LOAD DATABASE
     FROM     mysql://dev:[email protected]:4050/sourcedb
     INTO     postgres://dev:[email protected]:4080/targetdb
           set timezone to 'Australia/Sydney'
 WITH include drop, create tables

ALTER SCHEMA 'sourcedb' RENAME TO 'public'

BEFORE LOAD DO
 $$ DROP SCHEMA public CASCADE; $$
;

I am using a docker container to run pgloader

Run this using by mounting the pgload.load command file into the container and calling pgloader

docker run --rm -it -v "$(pwd)/pgload.load:/pgload.load" dimitri/pgloader:latest \
    pgloader /pgload.load

The pgloader migration doesn't include the AUTO_INCREMENT attribute from MySQL so you need to add a sequence and default value for the id columns

This is an example of achieving it with a CakePHP 4 migration. You can fish the SQL out of this code to run the commands manually in postgres

<?php

declare(strict_types=1);

use Cake\Datasource\ConnectionManager;
use Migrations\AbstractMigration;

class AddSequencesToId extends AbstractMigration
{
    /**
     * Change Method.
     *
     * More information on this method is available here:
     * https://book.cakephp.org/phinx/0/en/migrations.html#the-change-method
     * @return void
     */
    public function change()
    {
        $db = ConnectionManager::get('default');

        // Create a schema collection.
        $collection = $db->getSchemaCollection();

        // Get the table names
        $tables = $collection->listTables();

        // Get a single table (instance of Schema\TableSchema)
        // $tableSchema = $collection->describe('posts');
        foreach ($tables as $tableName) {
            $table = $this->table($tableName);

            if (in_array($tableName, ['phinxlog'])) {
                continue;
            }
            $maxId = $this->fetchRow('SELECT MAX(id) as max_id FROM ' .  $tableName);

            $next = $maxId['max_id'] + 1;
            if ($table->hasColumn('id')) {
                $this->execute("CREATE SEQUENCE IF NOT EXISTS {$tableName}_id_seq START {$next};");
                $this->execute("ALTER TABLE {$tableName} ALTER COLUMN id SET DEFAULT nextval('{$tableName}_id_seq');");
            }
        };
    }
}

Get a list of Timezones out of Postgres

SELECT
    name,
    abbrev,
    utc_offset,
    is_dst
FROM pg_timezone_names;

Get a list of Timezones out of MySQL

USE mysql;
SELECT * FROM `time_zone_name`;

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

The reCAPTCHA verification period has expired. Please reload the page.