在 Linux (Kubuntu) 上从 MySQL 迁移到 PostgreSQL

Question

很久以前，在一个很远很远的系统上...

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

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

我想使用R编程语言使用SQL select语句来分析数据； PostgreSQL 有 PL/R，但 MySQL 没有（据我所知）。

新希望

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

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 apt-get安装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 code>
7. \password postgres
8. sudo -u postgres createdb Climate
9. pgadmin3

使用 pgadmin3 配置数据库并创建模式。

这一事件在一个名为 bash 的远程 shell 中继续，两个数据库都在运行，并且安装了一组带有相当不寻常徽标的工具：SQL 仙女.

1. perl Makefile.PL
2. sudo make install
3. sudo apt-get install perl-doc （奇怪的是，它不叫perldoc >)
4. perldoc SQL::Translator::Manual

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

1. sqlt -f DBI --dsn dbi:mysql:气候 --db-user 用户 --db-password 密码 -t PostgreSQL > Climate-pg-ddl.sql
2. 编辑 climate-pg-ddl.sql 并将标识符转换为小写，然后插入模式引用（使用 VIM）：
   • :%s/"\([A-Z_]*\)"/\L\1/g
   • :%s/表/表气候./g
   • ：%s/关于/关于气候。/g
3. mysqldump --skip-add-locks --complete-insert --no-create-db --no-create-info --quick --result-file="climate-my.sql" --databases Climate --skip-comments -u root -p

将 MySQL 中的表和列重命名为小写可能是值得的：

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

数据库反击

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

1. pgadmin3（切换到它）
2. 单击执行任意 SQL 查询图标
3. 打开 climate-pg-ddl.sql
4. 搜索 TABLE“ 替换为 TABLE Climate.”（插入架构名称 climate）
5. 在“< /code> 替换为 on Climate." （插入模式名称 climate）
6. 按 F5 执行

这会导致：

Query returned successfully with no result in 122 ms.

Replies of the Jedi

At这一点我很困惑。

• 我应该从这里去哪里（步骤是什么）将 climate-my.sql 转换为 climate-pg.sql 以便他们可以针对 PostgreSQL 执行？
• 如何确保正确复制索引（以保持引用完整性；目前没有限制来简化转换）？
• 如何确保在 PostgreSQL 中添加新行将从插入的最后一行的索引开始枚举（并且不与序列中的现有主键冲突）？
• 将数据从 MySQL 转换为 PostgreSQL 插入时，如何确保模式名称能够通过？

资源

需要相当多的信息才能达到此目的：

• 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/

谢谢！

Answer 1

我通常为此类迁移做两件事：

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

然后进行转换，并用您最熟悉的任何语言编写一个程序来完成以下任务：

• 从 MySQL 数据库读取数据。
• 对要存储在 PostgreSQL 数据库中的数据执行任何必要的转换。
• 将现在转换的数据保存在 PostgreSQL 数据库中。

重新设计 PostgreSQL 的表以利用其功能。

如果您只是使用 sed 脚本将 SQL 转储从一种格式转换为另一种格式，那么您只需执行以下操作：我们正在做的是将 MySQL 数据库放入 PostgreSQL 服务器中。您可以这样做，而且这样做仍然会有一些好处，但如果您要迁移，请完全迁移。

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

Answer 2

将 mysqldump 文件转换为 PostgreSQL 友好的格式

按如下方式转换数据（不要使用 mysql2pgsql.perl):

1. 转义引号。

   sed "s/\\\'/\'\'/g" Climate-my.sql | sed "s/\\\r/\r/g" | sed sed "s/\\\n/\n/g" > escaped-my.sql

2. 将 USE "climate"; 替换为搜索路径并注释注释：

   sed "s/USE \"climate\";/SET search_path TO Climate;/g" escaped-my.sql | sed "s/^\/\*/--/" > climate-pg.sql

3. 连接到数据库。

   sudo su - postgres
psql Climate

4. 设置编码（mysqldump忽略其编码参数），然后执行脚本。

   \编码 iso-8859-1
\i Climate-pg.sql

这一系列步骤可能不适用于具有多种混合类型的复杂数据库。但是，它适用于整数、varchar 和浮点型。

索引、主键和序列

由于 mysqldump 在生成 INSERT 语句时包含主键，因此它们将胜过表的自动序列。经检查，所有表的序列仍为 1。

导入后设置序列

使用 ALTER SEQUENCE 命令将它们设置为所需的任何值。

架构前缀

无需为表添加架构名称前缀。使用：

SET search_path TO climate;

Answer 3

如果您已经转换了架构，那么迁移数据将是简单的部分：

• 从 PostgreSQL 转储架构（您说过您已将架构转换为 postgres，所以我们现在将转储它，因为我们将删除并重新创建目标数据库，将其清理）：

  pg_dump 数据库名称> /tmp/dbname-schema.sql
  

• 将模式拆分为2 部分 — /tmp/dbname-schema-1.sql 包含创建表语句，/tmp/dbname-schema-2.sql — 其余部分。 PostgreSQL 需要在导入外键、触发器等之前导入数据，但在导入表定义之后。

• 仅使用架构的 1 部分重新创建数据库：

  删除数据库dbname
  创建数据库 dbname
  \i /tmp/dbname-schema-1.sql
  -- 现在我们的表没有数据、触发器、外键等。
  

• 导入数据：

  <前>(
  echo '开始交易';
  mysqldump --skip-quote-names 数据库名 | grep ^插入；
  回显'提交'
  )| psql 数据库名
  -- 现在我们有包含数据的表，但没有触发器、外键等。
  

  MySQL 5.1.3 中添加了 --skip-quote-names 选项，因此如果您有旧版本，请暂时在 /tmp/mysql （configure --prefix=/tmp/mysql && make install 应该做）并使用 /tmp/mysql/bin/mysqldump。

• 导入架构的其余部分：

  psql 数据库名称
  开始交易
  \i /tmp/dbname-schema-2.sql
  犯罪
  ——我们完成了
  

Answer 4

查看 etlalchemy。它允许您用 4 行 Python 代码从 MySQL 迁移到 PostgreSQL，或者在几个其他数据库之间迁移。您可以在此处了解更多相关信息。

安装：pip install etlalchemy

运行：

from etlalchemy import ETLAlchemySource, ETLAlchemyTarget
# Migrate from MySQL to PostgreSQL
src = ETLAlchemySource("mysql://user:passwd@hostname/dbname")
tgt = ETLAlchemyTarget("postgresql://user:passwd@hostname/dbname",
                          drop_database=True)
tgt.addSource(src)
tgt.migrate()