How to convert postgresql to mysql database

Source: Internet
Author: User
Tags unix domain socket

Recently, the company asked a website to be revised and download the website programs and databases to programmers. The program file is very simple. Just download it by creating a compressed package. When I looked at the configuration file to view the database, I was dumpfounded. The database type was postgresql, and I had never heard of the Data Type before, let alone the operation. No way, you have to stick your head down. Baidu times to see how to export postgreSQL.

Export the database structure and

# Su-postgres

# Pg_dump-U export setbtz-f/var/lib/pgsql/etbtz. SQL

I thought exporting data would be all right. After a few days, the programmer came back and said that it was to be deployed locally. Let me help you convert the database type to mysql.

Use the Database Connection Tool Navicat Premium to remotely connect to the server. After the configuration is added, the server rejects the connection. You must allow remote connection on the postgresQL server.

How to configurePostgreSQLRemote database connection

After the PostgreSQL database is installed, only local access connections are accepted by default. If you want to access the PostgreSQL database server on another host, you need to configure it accordingly. To remotely connect to a PostgreSQL database, you only need to modify pg_hba.conf and postgresql in the data directory. conf, in which pg_cmd.conf is used to configure access permissions to the database, postgresql. the conf file is used to configure the corresponding parameters of the PostgreSQL database server. The following describes the configuration steps:

1. Modify the pg_cmd.conf file and configure the user's access permissions:

# Type database user CIDR-ADDRESS METHOD

# "Local" is for Unix domain socket connections only
Local all trust
# IPv4 local connections:
Host all trust
Host all md5
# IPv6 local connections:
Host all: 1/128 trust

The content marked in red is newly added, which allows all hosts on the network segment to access the database with all valid database usernames and provides encrypted password verification. In our environment, we need to use the postgres user on host to access the PostgreSQL database on

2. Modify the postgresql. conf file to change the listening mode of the database server to listening for connection requests from all hosts.

Go to # listen_addresses = 'localhost '. After PostgreSQL is installed, it only accepts connection requests from localhost on the local machine by default. By modifying the modified content to listen_addresses = '*', the database server can listen for connection requests from any host:

Listen_addresses = '*' # what IP address (es) to listen on;
# Comma-separated list of addresses;
# Defaults to 'localhost', '*' = all
# (Change requires restart)

After modification, save and exit, and then restart the database to access the PostgreSQL database on a remote machine.

Restart Database: service postgresql restart

Try the local connection again. At this time, the connection is available.

PostgresqlMysql Tool

I collected some articles on postgresql to mysql on the Internet. Most of them said that I first exported the SQL script from postgresql, and then manually changed some of the statements in the SQL script to the mysql format, I thought that if I was a big data company, I would also manually change the data to mysql?

So we found a conversion tool DBConvert for MySQL & PostgreSQL between databases, which is very convenient to convert between databases. Of course, we all know that convenient software is free of charge, after installing the software, you can try it out for a period of time to switch the database time.

Step 1: Try it out

650) this. width = 650; "title =" 1.jpg" src = ""/>

Step 2: select the data source and connect to the database of the corresponding data source. Here is postgresql. Select the database to be converted and click next after the test connection is successful.

650) this. width = 650; "style =" width: 794px; height: 551px "title =" 2.png" border = "0" hspace = "0" src = "" width = "794" height = "551"/>


650) this. width = 650; "title =" 2.png" src = "" width = "794" height = "551"/>

Step 3: Fill in the connection and target database of the target database. My target database is mysql.

650) this. width = 650; "title =" 3.jpg" src = ""/>

Step 4: Click Next to view the corresponding table and table structure. Click Next to execute.
650) this. width = 650; "title =" 4.jpg" src = ""/>‍

‍As long as this field is not filled in correctly, the entire conversion process is very smooth and I have to admire the strong technology of foreign friends.

Attachment download address: mysql-to-postgresql-pro.php? DB = 3

This article is from "Just do it !" Blog, please be sure to keep this source

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: 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.