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

Written by James McDonald

September 15, 2022

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.

You May Also Like…

Network speed test host to host

On Ubuntu / Debian apt-get install iperf3 On Windows download it from https://iperf.fr/iperf-download.php#windows Make...

Clear HSTS Settings in CHrome

Open chrome://net-internals/#hsts enter the domain in the query field and click Query to confirm it has HSTS settings...