Export MySQL database and import it to PostgreSQL database

Question

I'm trying to export MySQL database and import it to PostgreSQL database.

I've tried

using this mysql-postgresql-converter python script

I have a PHP script

if (!ini_get('date.timezone')) {
    ini_set('date.timezone', 'America/New_York');
}


//Configuration
$db          = 'local';
$username    = 'root';
$hostname    = '127.0.0.1';
$password    = '';
$date        = date('Y-m-d');
$export_type = 'mysql'; // option : mysql | psql
$file_name   = $date.'-portal';
$file_path   = $file_name;

if($export_type == 'mysql'){
    // MySQL Export
    $cmd = 'mysqldump --compatible=postgresql --default-character-set=utf8 -r '.$db.'.mysql -u root '.$db;
    echo $cmd. PHP_EOL;
    exec($cmd);
}

// Convert
$import = 'python db_converter.py '.$db.'.mysql '.$db.'.psql';
echo $import. PHP_EOL;
exec($import);

//Import to PostSQL
$import = 'psql  -U bheng  -d portal -f '.$db.'.psql';
echo $import. PHP_EOL;
exec($import);

Result

I got 2 files created

1- local.mysql 2- local.psql

As soon as I run

psql  -U bheng  -d portal -f local.psql

I get

psql:local.psql:12: ERROR:  relation "caches" already exists
psql:local.psql:14: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:52: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:54: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:63: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:65: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:78: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:80: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:92: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:94: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:110: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:112: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:116: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:118: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:131: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:133: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:144: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:146: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:158: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:160: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:179: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:181: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:195: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:197: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:247: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:249: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:256: ERROR:  cannot cast type smallint to boolean
LINE 1: ...LT, ALTER COLUMN "sign_enable" TYPE boolean USING CAST("sign...
                                                             ^
psql:local.psql:257: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:258: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:263: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:264: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:265: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:266: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:267: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:268: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:269: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:270: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:271: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:272: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:273: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:274: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:275: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:276: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:277: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:278: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:279: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:280: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:281: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:282: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:283: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:284: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:285: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:286: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:287: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:288: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:289: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:290: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:291: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:292: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:293: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:294: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:local.psql:295: ERROR:  current transaction is aborted, commands ignored until end of transaction block

Any hints / suggestions on this will be much appreciated !

Maybe take a look at stackoverflow.com/questions/5417386/… — Ben M.
– Ben M., Commented Jun 15, 2016 at 16:23
Reconsider using that GitHub script as it mentions: This script was designed for our specific database and column requirements. So there might be some syntax/types in your database not handled in above script leading to Postgre errors. — Parfait
– Parfait, Commented Jun 15, 2016 at 19:18
Consider the long way of export/import csv files or using php/python to connect between both servers and running SQL DML and DDL statements. — Parfait
– Parfait, Commented Jun 15, 2016 at 19:19
Sure, thank-you for your advice. I'll look into those export/import csv files. — code-8
– code-8, Commented Jun 15, 2016 at 19:24

FelipeGTX · Accepted Answer · 2018-06-16 15:47:47Z

0

You can use pgloader.

sudo apt-get install pgloader

Using:

pgloader mysql://user:pass@host/database postgresql://user:pass@host/database

Example:

pgloader mysql://root:[email protected]:3306/mydbinmysql postgresql://postgres:postgres@localhost:5432/mydbexportedonpgsql

Just create the new db to which you want to export to in postgres beforehand. In the example above mydbexportedonpgsql.

answered Jun 16, 2018 at 15:47

FelipeGTX

1111 gold badge1 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Export MySQL database and import it to PostgreSQL database

I've tried

I have a PHP script

Result

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

I've tried

I have a PHP script

Result

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related