Migrate from mysql to postgresql

Q&A
1

Is anyone has done a migration from Atropim on mysql to postgresql ? Some tips or tools to help me out ?

I have an Atropim instance running on a VM with a local mysql database on it and I want to migrate to a remote postgresql database.

2

Hello,

  1. Create posgresql database
  2. Switch PIM on posgresql. For this go to data/config.php and change connection data
    'driver'   => 'pdo_pgsql',
    'host'     => 'localhost',
    'port'     => '5432',
    'charset'  => 'utf8',
    'dbname'   => 'DBNAME',
    'user'     => 'USERNAME',
    'password' => 'PASS'
  1. Execute: php index.php sql diff --run from CLI. This command will prepare DB schema.
  2. create php script that will migrate data from mysql to posgresql. Example:
// filename is mysql_to_pgsql.php

<?php
if (substr(php_sapi_name(), 0, 3) != 'cli') {
    echo 'For CLI only.' . PHP_EOL;
    die();
}

chdir(dirname(__FILE__));
set_include_path(dirname(__FILE__));

require_once 'vendor/autoload.php';

$mysqlConn = \Doctrine\DBAL\DriverManager::getConnection([
    'driver'   => 'pdo_mysql',
    'host'     => 'localhost',
    'port'     => '',
    'charset'  => 'utf8mb4',
    'dbname'   => 'MYSQL_DB_NAME',
    'user'     => 'MYSQL_USER',
    'password' => 'MYSQL_PASS'
], new \Doctrine\DBAL\Configuration());

$pgConn = \Doctrine\DBAL\DriverManager::getConnection([
    'driver'   => 'pdo_pgsql',
    'host'     => 'localhost',
    'port'     => '5432',
    'charset'  => 'utf8',
    'dbname'   => 'PG_DB_NAME',
    'user'     => 'PG_USER',
    'password' => 'PG_PASS'
], new \Doctrine\DBAL\Configuration());

if (empty($argv[1])){
    echo 'Unknown table name.' . PHP_EOL;
    die();
}

$table = $argv[1];

$offset = $argv[2] ?? 0;
$limit = 50000;

$count = $offset + 1;

while (true) {
    $records = $mysqlConn->createQueryBuilder()
        ->select('*')
        ->from($table)
        ->setFirstResult($offset)
        ->setMaxResults($limit)
        ->orderBy('id')
        ->fetchAllAssociative();

    if (empty($records)) {
        break;
    }

    $offset = $offset + $limit;

    foreach ($records as $record) {
        $qb = $pgConn->createQueryBuilder()->insert($pgConn->quoteIdentifier($table));
        foreach ($record as $column => $value) {
            $qb->setValue($pgConn->quoteIdentifier($column), ":a_$column");
            $qb->setParameter("a_$column", $value, \Atro\ORM\DB\RDB\Mapper::getParameterType($value));
        }
        try {
            $qb->executeQuery();
        } catch (\Doctrine\DBAL\Exception\ConstraintViolationException $e) {
        }
        echo "{$table}: $count" . PHP_EOL;
        $count++;
    }
}

than just call script: php mysql_to_pgsql.php product
script will move data from ‘product’ table from mysql to pgsql. Do it for all tables.

1 Like
3

Thanks a lot Roman ! I’m gonna try this !