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

by James McDonaldSep 15, 2022IT Tips0 comments

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:devpass@10.197.3.140:4050/sourcedb
     INTO     postgres://dev:devpass@10.197.3.140: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

1 2	`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

1 2	`USE mysql;` `SELECT` `*` `FROM` `time_zone_name`;

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

Get a list of Timezones out of Postgres

Get a list of Timezones out of MySQL

0 Comments

Submit a Comment Cancel reply