mysql数据库迁移 OceanBase mysql数据库迁移到pg

公司的有个项目，原先用的是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，如下：

[root@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

[root@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下验证下数据是否正常

本文转自 lirulei90 51CTO博客，原文链接：http://blog.51cto.com/lee90/1975363，如需转载请自行联系原作者