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

Source: Internet
Author: User
Tags postgresql

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.