Restore MySQL database from postgresql dump file.

Posted on 2004-11-15
Medium Priority
Last Modified: 2008-02-26
I have postgresql database and prefer to use MySQL to work on the same data.
Can I just dump postgresql data using pg_dump and restore in to MySQL?
Since I'm very new to MySQL, and I think, it shouldn't have been that hard, is it?
Any advice would be very appriciated, thank you very much in advance.
Question by:busakorn
  • 5
  • 3
LVL 26

Expert Comment

ID: 12591178

Author Comment

ID: 12592076
Thanks ushastry.
When I used "database workbench" to connect to my server, it said XXX.XXX.XXX.XXX (my machine) is not allowed to connect to this MySQL server.
And that was why I couldn't do anything else, such as convert postgresql dump to mysql file.
Any more idea?
LVL 26

Expert Comment

ID: 12592115


The MySQL privilege system ensures that all users may perform only the operations allowed to them. As a user, when you connect to a MySQL server, your identity is determined by the host from which you connect and the username you specify. The system grants privileges according to your identity and what you want to do.

Can you tell me few things here.. like OS, are you running mysql server on local host or some other server? have you created any user accounts in the mysql server etc..
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.


Author Comment

ID: 12600777
I'm running MySQL on Linux server and I'll try to connect to it on Windows XP client.
I've created one user on MySQL other than connecting to the dababase as root.
Well, when I granted user to the database, I can register the server, now.
Sorry, I connected as root at first, and now I can connect to it as user.
Well, when I tried to use schema migration, in the Select source schema,
there is no postgresql to select, so I just tried on selecting MySQL, then select server and database.
then I also select MySQL as target schema, (I just tried). The error occured, it said
"select command denied to user xxx@xxx.xxx.xxx.xxx for table 'func'"
Is it because I selected the same thing as source and target?
If so, what should I do? There is no postgresql for me to select.

Author Comment

ID: 12600791
BTW, is there any ways to list the tables name in MySQL?
LVL 26

Accepted Solution

Umesh earned 405 total points
ID: 12602946
use this sql command..



Author Comment

ID: 12610896
Thanks, I have them shown now.
But what about converting file? Is there any ways to convert postgresql file to MySQL?

Author Comment

ID: 12641822
I've had it done now, by perl.
Thank you very much, ushastry, for your kind assistance.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month16 days, 11 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question