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

by | Sep 15, 2022 | IT Tips | 0 comments

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

?
1
2
3
4
# 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

?
1
2
3
"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

?
1
2
3
4
5
6
7
8
9
10
11
12
-- 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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
<?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

?
1
2
3
4
5
6
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`;

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.