将数据从一个数据库移动到具有不同数据结构的另一个数据库
如何将数据从假设的mysql数据库移动到postgres数据库?
场景:两个类似的应用程序。用户想要从一个应用程序切换到另一个应用程序。 但他在之前使用 mysql 数据库的应用程序中保留了某些数据信息。当他切换应用程序时,他必须将数据从旧应用程序移动到使用 postgres 的新应用程序。
这两个数据库都有不同的结构、表与表的关系、表的数量以及表下的列。
但如何将日期从一个数据库移动到另一个数据库呢?
有什么工具可以做到这一点吗?如果是这样,任何人都可以建议这样的工具..
How to move data from suppose mysql database to postgres database?
Scenario: Two similar application. A user wants to switch from one application to other.
But he had maintained certain data information in his previous appilaction which uses mysql database.When he switch his appliaction he has to move his data from his old application to new application which uses postgres.
Both database are different with different structure and table and table relationships, Number of tables and also columns under them.
But how to move the date from one database to other?
Is there any tool to do this? If so can anyone suggest such tool..
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
它可能不是您所需要的,但是此页面包含一个
并讨论了迁移数据的困难。
这可能不合适,因为它可能只迁移 Drupal 数据库(并且您没有说明涉及哪些应用程序)。
您最好的选择可能是以简单的文本形式导出/备份 MySQL 数据,该文本形式可以被处理为 PostgreSQL 格式并导入。这需要一些脚本编写/编辑。
当然,这就是您需要一个工具来完成这项工作的原因。
It may not be what you require, but this page includes a
and a discussion of the difficulties of migrating the data.
It may be inappropriate because it may only migrate Drupal databases (and you didn't say which applications are involved)
Your best bet may be to export/backup your MySQL data in a simple text form which can be massaged into PostgreSQL format and imported. This would require some scripting/editing.
Which, of course, is why you asked for a tool for the job.
我会这样做:(
仅使用数据库工具)
mysqldump
它有一个选项来生成 postgres 兼容脚本:--兼容=postgres
和许多其他非常有趣的选项。(使用定制的应用程序+ORM(例如:C#,NHibernate)——实施时间更长):
(使用已知的工具):
抱歉,但我不知道是否有一个工具可以做到这一点!
I would do it like this:
(Using only database tools)
SELECT
statements (as new tables are a mix of the old onew I suppose), or may be some stored procedures...mysqldump
and it has an option to generate postgres compatible scripts:--compatible=postgres
and many other very interesting options.(Using a custom made application+ORM (ex: C#, NHibernate) -- more longer to implement):
(Using a known tool):
Sorry, but I have no idea if there is a tool that can do this!
您描述的问题是数据仓库世界中最常见的问题之一,您需要从应用程序数据库(通常是经典的关系数据模型)中获取数据,并在具有不同结构的数据仓库中进行合并和集中(大多数情况下具有维度)数据模型)。这就是 ETL 作业(提取、转换、加载)的作用。
您可以将它们作为 SQL 脚本,手动或自动运行。您需要建立的是转换逻辑,然后将其实现为 SQL 脚本。
有专用的 ETL 工具,但我认为它们不会为您的案例增加太多价值。
The problem you're describing is one of the most common problems in datawarehouse world, where you need to take data from application databases (usually classic relational data model) and consolidate and centralize in a datawarehouse with different structure (most of the time with dimensional data model). That what ETL jobs (Extract Transform Load) do.
You can have them as SQL scripts, run by hand, or automatically. What you need to establish is the transformation logic, then to implement it as SQL scripts.
There are dedicated ETL tools, but I don't think they add too much value in your case.
我还建议使用 ETL 工具来完成此类工作。应用不同的功能将使您的迁移100%成功。我建议商业用户使用 Apatar (apatar.com) 等开源工具,而技术用户则使用 Talend (talend.com)。
I would also advise using ETL tools for this kind of job. Applying different functions will make your migration 100% successful. I recommend using open source tools like Apatar (apatar.com) for business users and Talend (talend.com) for technical users.
针对这些场景有一整套工具。谷歌搜索“ETL”,可能还有您正在处理的数据库供应商名称,您可能会找到比您在合理时间内评估的更多解决方案。
There is a whole industry of tools for these scenarios. Google for "ETL" and probably also the database vendor names you are dealing with and you will likely find more solutions than you can evaluate in a reasonable amount of time.
我建议采用与其他路径相同的路径,但略有不同。使用 ETL 工具来做到这一点。大多数 ETL 工具都可以完成您刚才描述的工作。如果您不想花任何钱购买“ETL”工具,我会将票投给 Talend 开源 ETL 工具。我用过它并且它非常强大。文档和示例可在 http://www.talend.com 中找到。
I would suggest the same path as the others with a slight difference. Use ETL tool to do that. Most of the ETL tools would do the job you just described. If you don't want to spend any money buying an "ETL" tool, my vote goes to Talend Open Source ETL tool. I've used it and it is pretty powerful. Documentation and examples are available in http://www.talend.com.