使用py-mysql2pgsql 简单实现mysql数据库迁移到pgsql

erfsfd

                      参考：https://pypi.python.org/pypi/py-mysql2pgsql


公司的有个项目，原先用的是MySQL数据库，现在要改成postgres。 于是搜了下，找到个py-mysql2pgsql工具。下面是笔记：

假设我们要把本机的mysql里面的gitlab_ci_production、gitlabhq_production 这2个库导入到本机的pgsql中（本地地址:192.168.2.100）。

1、安装pgsql10
yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-6-x86_64/pgdg-centos10-10-1.noarch.rpm

yum install postgresql10 postgresql10-server
yum install  postgresql10-devel

service postgresql-10 initdb
chkconfig postgresql-10 on
service postgresql-10 start

修改绑定地址
vim /var/lib/pgsql/10/data/postgresql.conf
listen_addresses = '*'

添加客户端IP放行(生产环境不建议放行0.0.0.0/0 范围太大了)
vim pg_hba.conf  
host    all    all   0.0.0.0/0    md5

/etc/init.d/postgresql-10 restart


2、在pgsql里面创建用户及相关的库：
su - postgres
psql
create user gitlab with password '123456' ;
create database gitlab_ci_production;
create database gitlabhq_production;
alter database gitlab_ci_production owner  to gitlab;
alter database gitlabhq_production owner  to gitlab;




echo "export PATH=/usr/pgsql-10/bin/:$PATH" >> /etc/profile
source /etc/profile




3、安装py-mysql2pgsql
pip install py-mysql2pgsql

4、编写导出文件的yml，如下：
[iyunv@localhost ~]# cat convert.yml  内容如下：
# if a socket is specified we will use that
# if tcp is chosen you can use compression
mysql:
hostname: localhost
port: 3306
socket: /tmp/mysql.sock
username: root
password: 123456
database: gitlab_ci_production
compress: false
destination:
# if file is given, output goes to file, else postgres
file:
postgres:
  hostname: 192.168.2.100
  port: 5432
  username: gitlab
  password: 123456
  database: gitlab_ci_production


[iyunv@localhost ~]# cat convert2.yml   内容如下：
# if a socket is specified we will use that
# if tcp is chosen you can use compression
mysql:
hostname: localhost
port: 3306
socket: /tmp/mysql.sock
username: root
password: 123456
database: gitlabhq_production
compress: false
destination:
# if file is given, output goes to file, else postgres
file:
postgres:
  hostname: 192.168.2.100
  port: 5432
  username: gitlab
  password: 123456
  database: gitlabhq_production




5、执行导入数据的命令：
py-mysql2pgsql -v -f convert.yml
py-mysql2pgsql -v -f convert2.yml

6、稍后去pgsql下验证下数据是否正常

具体参考：https://pypi.python.org/pypi/py-mysql2pgsql
                  

看雪

路过帮顶！！！

genricky

谢谢楼主分享