Jay Taylor's notes
back to listing indexImport MySQL dump to PostgreSQL database
[web search]
I tried several ways through internet and scripts to use MySQL dump "xxx.sql" and ORACLE dump "xxx.dump" to postgresql database. Nothing worked for me. Kindly anyone suggest me to do this. I want to import xxx.dump from ORACLE and xxx.sql from MySQL to postgresql database.
|
|||||||||
|
|||||||||
Don't expect that to work without editing. Maybe a lot of editing. mysqldump has a compatibility argument, You'll get more useful help here if you include the complete command you used to create the dump, along with any error messages you got instead of saying just "Nothing worked for me."
|
|||||||||||||||
|
This question is a little old but a few days ago I was dealing with this situation and found pgloader.io. This is by far the easiest way of doing it, you need to install it, and then run a simple lisp script (script.lisp) with the following 3 lines:
And after that your postgresql DB will have all of the information that you had in your MySQL SB. On a side note, make sure you compile pgloader since at the time of this post, the installer has a bug. (version 3.2.0)
|
|||||||||||||||||||||||||||
|
The fastest (and most complete) way I found was to use Kettle. This will also generate the needed tables, convert the indexes and everything else. The The steps:
|
|||
You could potentially export to CSV from MySQL and then import CSV into PostgreSQL.
|
|||
For those Googlers who are in 2015+. I've tried all the solutions described at this article by Alexandru Cotioras (which is full of despair). Of all the solutions mentioned there only one worked for me. — lanyrd/mysql-postgresql-converter @ github.com (Python) But this alone won't do. When you'll be importing your new converted dump file:
So you'll have to fix those types manually as per this table. In short it is:
You can use
|
||||
It is not possible to import an Oracle (binary) dump to PostgreSQL. If the MySQL dump is in plain SQL format, you will need to edit the file to make the syntax correct for PostgreSQL (e.g. remove the non-standard backtick quoting, remove the engine definition for the CREATE TABLE statements adjust the data types and a lot of other things)
|
|||
I have this bash script to migrate the data, it doesn't create the tables because they are created in migration scripts, so I need only to convert the data. I use a list of the tables to not import data from the
You will get a lot of warnings you can safely ignore like this:
|
|||
Here is a simple program to create and load all tables in a mysql database (honey) to postgresql. Type conversion from mysql is coarse-grained but easily refined. You will have to recreate the indexes manually:
|
||||
As with most database migrations, there isn't really a cut and dried solution. These are some ideas to keep in mind when doing a migration:
Keep these in mind. The best way is probably to write a conversion utility. Have a happy conversion!
|
|||
Use your xxx.sql file to set up a MySQL database and make use of FromMysqlToPostrgreSQL. Very easy to use, short configuration and works like a charm. It imports your database with the set primary keys, foreign keys and indices on the tables. You can even import data alone if you set appropriate flag in the config file.
See here too: PG Wiki Page
|
|||
I could copy tables from MySQL to Postgres using DBCopy Plugin for SQuirreL SQL Client. This was not from a dump, but between live databases.
|
|||
I had to do this recently to a lot of large .sql files approximately 7 GB in size. Even VIM had troubling editing those. Your best bet is to import the .sql into MySql and then export it as a csv which can be then imported to Postgres. But, the MySQL export as a csv is horrendously slow as it runs the select * from yourtable query. If you have a large database/table I would suggest using some other method. One way is to write a script that reads the sql inserts line by line and uses string manipulation to reformat it to "Postgres-compliant" insert statements and then execute these statements in Postgres
|
|||
Your Answer
asked |
5 years ago |
viewed |
26608 times |
active |
Technology | Life / Arts | Culture / Recreation | Science | Other | ||||||
---|---|---|---|---|---|---|---|---|---|---|