使用py-mysql2pgsql 简单实现mysql数据库迁移到pgsql
参考：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 installpostgresql10-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 ownerto gitlab;
alter database gitlabhq_production ownerto gitlab;
echo "export PATH=/usr/pgsql-10/bin/:$PATH" >> /etc/profile
source /etc/profile
3、安装py-mysql2pgsql
pip install py-mysql2pgsql
4、编写导出文件的yml，如下：
# 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
# 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
页:
[1]