How to convert mysql database dump to postgresql?

Question

My file dump.sql for MySQL

MySQL dump 10.13  Distrib 5.7.20, for Win64 (x86_64)

in file set encoding cp1251
Error in the normal recovery psql databasename < /path/to/dump/dump.sql:

ERROR:  invalid byte sequence for encoding "UTF8": 0xd3 0xc0

I need to convert it so that it can be restored in postgresql.
Are there any plugins? or ways?

The error is pretty clear. You tried to read the script file as UTF8 when you say you saved it as CP1251. Save it as UTF8. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 30, 2018 at 13:01
Dumps aren't backups, they are SQL scripts. You aren't recovering anything, you are executing a script. If you use the wrong codepage you may end up with garbled text. You can still run into trouble if the script contains syntax that isn't valid for Postgres. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 30, 2018 at 13:03

Laurenz Albe · Accepted Answer · 2018-01-30 13:52:59Z

3

You can tell psql what the encoding of the SQL script is:

PGCLIENTENCODING=WIN1251 psql -d databasename -f /path/to/dump/dump.sql

answered Jan 30, 2018 at 13:52

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

evans Over a year ago

thanks. but now psql:/home/path/to/dump/dump.sql:7988: ERROR: syntax error at or near "`"

Laurenz Albe Over a year ago

I cannot possibly fix all the syntax differences between MySQL and PostgreSQL in comments. But it seems like your encoding problem is solved, right? Did you try mysqldump --compatible=postgresql?

Collectives™ on Stack Overflow

How to convert mysql database dump to postgresql?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related