代码之家 › 专栏 › 技术社区 › Dave Jarvis James Eichele

在Linux上从mysql迁移到postgresql(kubuntu)

database-migration postgresql mysql

Dave Jarvis James Eichele · 技术社区 · 15 年前

很久以前,在一个遥远的系统上…

尝试将数据库从mysql迁移到postgresql。我读过的所有文档都非常详细地介绍了如何迁移结构。我几乎没有找到关于迁移数据的文档。该架构有13个表(已成功迁移)和9 GB的数据。

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

我想用r编程语言用sql select语句来分析数据;postgresql有pl/r,但是mysql没有(据我所知)。

新希望

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

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

一切都很好。接下来,重新启动服务器并使用这些配置数据库 installation instructions :

sudo apt-get install postgresql pgadmin3
sudo /etc/init.d/postgresql-8.4 stop
sudo vi /etc/postgresql/8.4/main/postgresql.conf
变化 data_directory 到 /home/postgres/main
sudo /etc/init.d/postgresql-8.4 start
sudo -u postgres psql postgres
\password postgres
sudo -u postgres createdb climate
pgadmin3

使用 PGADMN3 配置数据库并创建架构。

这一集在一个叫做 bash ,两个数据库都在运行,并且安装了一组带有相当不寻常徽标的工具: SQL Fairy .

perl Makefile.PL
sudo make install
sudo apt-get install perl-doc (奇怪的是,它没有被称为 perldoc )
perldoc SQL::Translator::Manual

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

sqlt -f DBI --dsn dbi:mysql:climate --db-user user --db-password password -t PostgreSQL > climate-pg-ddl.sql
编辑 climate-pg-ddl.sql 并将标识符转换为小写,然后插入模式引用(使用vim):
- :%s/"\([A-Z_]*\)"/\L\1/g
- :%s/ TABLE / TABLE climate./g
- :%s/ on / on climate./g
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中的表和列重命名为小写就可以了:

select concat( 'RENAME TABLE climate.', TABLE_NAME, ' to climate.', lower(TABLE_NAME), ';' ) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='climate';
执行上一步中的命令。
可能有一种方法可以对列执行同样的操作;我手动更改它们,因为这比找出如何编写查询要快。

数据库反击

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

PGADMN3 (切换到它)
单击 执行任意SQL查询 偶像
正常开放 气候-pg-ddl.sql
寻找 TABLE " 替换为 TABLE climate." (插入架构名称 climate )
寻找 on " 替换为 on climate." (插入架构名称 气候 )
出版社 F5 执行

这将导致:

Query returned successfully with no result in 122 ms.

绝地武士的回答

在这一点上,我被难住了。

我从这到哪( 步骤是什么 )转换 climate-my.sql 到 climate-pg.sql 以便对PostgreSQL执行?
如何确保索引被正确复制(为了保持引用完整性;我目前没有减轻转换的约束)?
如何确保在PostgreSQL中添加新行将从插入的最后一行的索引开始枚举(并且不会与序列中现有的主键冲突)?
在将数据从mysql转换为postgresql插入时,如何确保模式名是通过的?

资源

要做到这一点,需要一点信息:

谢谢您!

4 回复 | 直到 9 年前

msanford 10 年前

对于这种迁移,我通常要做两件事:

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

然后进行转换,用您最熟悉的语言编写一个程序,完成以下任务:

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

为PostgreSQL重新设计表以利用其特性。

如果你只是做一些像 sed 脚本将SQL转储从一种格式转换为下一种格式,您所要做的就是将MySQL数据库放入PostgreSQL服务器。您可以这样做,这样做仍然会有一些好处,但是如果您要迁移,请完全迁移。

这将需要更多的前期时间,但我还没有遇到一个不值得的情况。

Dave Jarvis James Eichele 15 年前

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

转换数据如下(不要使用 mysql2pgsql.perl ):

转义引号。

sed "s/\\\'/\'\'/g" climate-my.sql | sed "s/\\\r/\r/g" | sed "s/\\\n/\n/g" > escaped-my.sql
替换 USE "climate"; 使用搜索路径和注释:

sed "s/USE \"climate\";/SET search_path TO climate;/g" escaped-my.sql | sed "s/^\/\*/--/" > climate-pg.sql
连接到数据库。

sudo su - postgres
psql climate
设置编码(mysqldump忽略其编码参数),然后执行脚本。

\encoding iso-8859-1
\i climate-pg.sql

这一系列步骤可能不适用于具有许多混合类型的复杂数据库。但是,它适用于 integer S varchar S和 float S.

索引、主键和序列

自从 mysqldump 生成时包含主键 INSERT 语句,它们将胜过表的自动序列。检查后,所有表的顺序保持为1。

导入后设置序列

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

模式前缀

不需要为表添加架构名称前缀。用途:

SET search_path TO climate;

Tometzky 15 年前

如果您已经转换了模式,那么迁移数据将是最简单的部分:

从PostgreSQL中转储模式(您说过您已经将模式转换为Postgres,因此我们现在将转储它,因为我们将删除并重新创建目标数据库,以便对其进行清理):
```
pg_dump dbname > /tmp/dbname-schema.sql
```
将架构拆分为两部分 /tmp/dbname-schema-1.sql 包含create table语句, /tmp/dbname-schema-2.sql 剩下的。PostgreSQL需要在导入外键、触发器等之前导入数据,但在导入表定义之后。

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

drop database dbname
create database dbname
\i /tmp/dbname-schema-1.sql
-- now we have tables without data, triggers, foreign keys etc.

导入数据:
```
(
   echo 'start transaction';
   mysqldump --skip-quote-names dbname | grep ^INSERT;
   echo 'commit'
) | psql dbname
-- now we have tables with data, but without triggers, foreign keys etc.
```
一 --skip-quote-names 选项是在mysql 5.1.3中添加的,因此如果您有较旧的版本,则在中临时安装较新的mysql /tmp/mysql ( configure --prefix=/tmp/mysql && make install 应该做)和使用 /tmp/mysql/bin/mysqldump .

导入架构的其余部分:

psql dbname
start transaction
\i /tmp/dbname-schema-2.sql
commit
-- we're done

depperm 9 年前

退房 etlalchemy . 它允许您从 MySQL 到 波斯特雷斯尔 或者在其他几个数据库之间,在4行python中。你可以多读一些 here .

安装: 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()