Simple implementation of MySQL database migration to Pgsql using Py-mysql2pgsql

Reference: Https://pypi.python.org/pypi/py-mysql2pgsql

The company has a project, originally used MySQL database, now to change to Postgres. So I searched and found a py-mysql2pgsql tool. Here are the notes:

Suppose we want to import the 2 libraries of gitlab_ci_production and gitlabhq_production in this machine into the pgsql of this machine (local address:192.168.2.100).

1, installation 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

Modify binding Address

Vim/var/lib/pgsql/10/data/postgresql.conf

listen_addresses = ' * '

Add client IP release (production environment is not recommended release 0.0.0.0/0 range too Large)

Vim pg_hba.conf

Host All 0.0.0.0/0 MD5

/etc/init.d/postgresql-10 restart

2. Create users and related libraries in 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, installation Py-mysql2pgsql

Pip Install Py-mysql2pgsql

4, write the export file Yml, as follows:

[email protected] ~]# cat convert.yml content is as follows:

# If a socket is specified we'll 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

[email protected] ~]# cat convert2.yml content is as follows:

# If a socket is specified we'll 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. Execute the Import data command:

Py-mysql2pgsql-v-F Convert.yml

Py-mysql2pgsql-v-F Convert2.yml

6, later to Pgsql under verify the data is normal

Specific reference: Https://pypi.python.org/pypi/py-mysql2pgsql

Simple implementation of MySQL database migration to Pgsql using Py-mysql2pgsql