在Linux(Kubuntu)上从MySQL迁移到PostgreSQL [英] Migrate from MySQL to PostgreSQL on Linux (Kubuntu)

查看：49
本文介绍了在Linux(Kubuntu)上从MySQL迁移到PostgreSQL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

尝试将数据库从MySQL迁移到PostgreSQL.我阅读的所有文档都非常详细地介绍了如何迁移结构.我发现有关迁移数据的文档很少.该架构有13个表(已成功迁移)和9 GB数据.

Trying to migrate a database from MySQL to PostgreSQL. All the documentation I have read covers, in great detail, how to migrate the structure. I have found very little documentation on migrating the data. The schema has 13 tables (which have been migrated successfully) and 9 GB of data.

MySQL版本:5.1.x
PostgreSQL版本:8.4.x

MySQL version: 5.1.x
PostgreSQL version: 8.4.x

我想使用R编程语言通过SQL select语句分析数据； PostgreSQL有PL/R，但MySQL没有(据我所知).

I want to use the R programming language to analyze the data using SQL select statements; PostgreSQL has PL/R, but MySQL has nothing (as far as I can tell).

创建数据库位置(/var空间不足；也不喜欢到处都有PostgreSQL版本号-升级会破坏脚本！):

Create the database location (/var has insufficient space; also dislike having the PostgreSQL version number everywhere -- upgrading would break scripts!):

  1. sudo mkdir -p /home/postgres/main
  2. sudo cp -Rp /var/lib/postgresql/8.4/main /home/postgres
  3. sudo chown -R postgres.postgres /home/postgres
  4. sudo chmod -R 700 /home/postgres
  5. sudo usermod -d /home/postgres/ postgres
  1. sudo mkdir -p /home/postgres/main
  2. sudo cp -Rp /var/lib/postgresql/8.4/main /home/postgres
  3. sudo chown -R postgres.postgres /home/postgres
  4. sudo chmod -R 700 /home/postgres
  5. sudo usermod -d /home/postgres/ postgres

这里一切都很好.接下来，使用以下安装说明重新启动服务器并配置数据库:

All good to here. Next, restart the server and configure the database using these installation instructions:

  1. sudo apt-get install postgresql pgadmin3
  2. sudo /etc/init.d/postgresql-8.4 stop
  3. sudo vi /etc/postgresql/8.4/main/postgresql.conf
  4. 将data_directory更改为/home/postgres/main
  5. sudo /etc/init.d/postgresql-8.4 start
  6. sudo -u postgres psql postgres
  7. \password postgres
  8. sudo -u postgres createdb climate
  9. pgadmin3
  1. sudo apt-get install postgresql pgadmin3
  2. sudo /etc/init.d/postgresql-8.4 stop
  3. sudo vi /etc/postgresql/8.4/main/postgresql.conf
  4. Change data_directory to /home/postgres/main
  5. sudo /etc/init.d/postgresql-8.4 start
  6. sudo -u postgres psql postgres
  7. \password postgres
  8. sudo -u postgres createdb climate
  9. pgadmin3

使用pgadmin3配置数据库并创建架构.

Use pgadmin3 to configure the database and create a schema.

此情节在名为bash的远程外壳中继续进行，两个数据库都在运行，并且安装了一组带有非常不寻常徽标的工具:

The episode continues in a remote shell known as bash, with both databases running, and the installation of a set of tools with a rather unusual logo: SQL Fairy.

  1. perl Makefile.PL
  2. sudo make install
  3. sudo apt-get install perl-doc(奇怪的是，它不称为perldoc)
  4. perldoc SQL::Translator::Manual
  1. perl Makefile.PL
  2. sudo make install
  3. sudo apt-get install perl-doc (strangely, it is not called perldoc)
  4. perldoc SQL::Translator::Manual

提取PostgreSQL友好的DDL和所有MySQL数据:

Extract a PostgreSQL-friendly DDL and all the MySQL data:

  1. sqlt -f DBI --dsn dbi:mysql:climate --db-user user --db-password password -t PostgreSQL > climate-pg-ddl.sql
  2. 编辑climate-pg-ddl.sql并将标识符转换为小写，然后插入模式引用(使用VIM):
    • :%s/"\([A-Z_]*\)"/\L\1/g
    • :%s/ TABLE / TABLE climate./g
    • :%s/ on / on climate./g
  1. sqlt -f DBI --dsn dbi:mysql:climate --db-user user --db-password password -t PostgreSQL > climate-pg-ddl.sql
  2. Edit climate-pg-ddl.sql and convert the identifiers to lowercase, and insert the schema reference (using VIM):
    • :%s/"\([A-Z_]*\)"/\L\1/g
    • :%s/ TABLE / TABLE climate./g
    • :%s/ on / on climate./g

简单地将MySQL中的表和列重命名为小写可能是值得的:

It might be worthwhile to simply rename the tables and columns in MySQL to lowercase:

  1. select concat( 'RENAME TABLE climate.', TABLE_NAME, ' to climate.', lower(TABLE_NAME), ';' ) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='climate';
  2. 执行上一步中的命令.
  3. 可能有一种方法可以对列执行相同的操作；我手动更改了它们，因为它比弄清楚如何编写查询要快.

数据库反击

在PostgreSQL中重新创建结构，如下所示:

The Database Strikes Back

Recreate the structure in PostgreSQL as follows:

  1. pgadmin3(切换到它)
  2. 点击执行任意SQL查询图标
  3. 打开climate-pg-ddl.sql
  4. 搜索TABLE "替换为TABLE climate."(插入架构名称climate)
  5. 搜索on "替换为on climate."(插入架构名称climate)
  6. 按F5执行
  1. pgadmin3 (switch to it)
  2. Click the Execute arbitrary SQL queries icon
  3. Open climate-pg-ddl.sql
  4. Search for TABLE " replace with TABLE climate." (insert the schema name climate)
  5. Search for on " replace with on climate." (insert the schema name climate)
  6. Press F5 to execute

结果是:

Query returned successfully with no result in 122 ms.

绝地的回信

这时我很沮丧.

Replies of the Jedi

At this point I am stumped.

  • 我从哪里去(什么步骤)将climate-my.sql转换为climate-pg.sql，以便可以在PostgreSQL上执行它们?
  • 如何确保正确复制索引(以保持引用完整性；目前我没有约束来简化过渡)?
  • 如何确保在PostgreSQL中添加新行将从插入的最后一行的索引开始枚举(并且不与序列中现有的主键冲突)?
  • 在将数据从MySQL转换为PostgreSQL插入时，如何确保通过模式名输入?
  • Where do I go from here (what are the steps) to convert climate-my.sql to climate-pg.sql so that they can be executed against PostgreSQL?
  • How to I make sure the indexes are copied over correctly (to maintain referential integrity; I don't have constraints at the moment to ease the transition)?
  • How do I ensure that adding new rows in PostgreSQL will start enumerating from the index of the last row inserted (and not conflict with an existing primary key from the sequence)?
  • How do you ensure the schema name comes through when transforming the data from MySQL to PostgreSQL inserts?

要达到这一目标，需要大量信息:

A fair bit of information was needed to get this far:
  • https://help.ubuntu.com/community/PostgreSQL
  • http://articles.sitepoint.com/article/site-mysql-postgresql-1
  • http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL
  • http://pgfoundry.org/frs/shownotes.php?release_id=810
  • http://sqlfairy.sourceforge.net/

谢谢！

推荐答案

我通常为这种迁移做两件事:

What I usually do for such migrations is two-fold:

  • 从MySQL提取整个数据库定义，并使其适应PostgreSQL语法.
  • 遍历数据库定义并对其进行转换，以利用MySQL中不存在的PostgreSQL中的功能.

然后进行转换，并以您最喜欢的语言编写一个程序，以完成以下任务:

Then do the conversion, and write a program in whatever language you are most comfortable with that accomplishes the following:

  • 从MySQL数据库读取数据.
  • 对要存储在PostgreSQL数据库中的数据进行必要的转换.
  • 将现在转换后的数据保存在PostgreSQL数据库中.

为PostgreSQL重新设计表以利用其功能.

如果只是使用sed脚本将SQL转储从一种格式转换为另一种格式，则您要做的就是将MySQL数据库放入PostgreSQL服务器中.您可以这样做，这样做仍然会带来一些好处，但是如果要进行迁移，请完全迁移.

If you just do something like use a sed script to convert the SQL dump from one format to the next, all you are doing is putting a MySQL database in a PostgreSQL server. You can do that, and there will still be some benefit from doing so, but if you're going to migrate, migrate fully.

这将花费更多的前期时间，但是我还没有遇到不值得的情况.

It will involve a little bit more up-front time spent, but I have yet to come across a situation where it isn't worth it.

这篇关于在Linux(Kubuntu)上从MySQL迁移到PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆