Mysql迁移至PostGreSQL方案

PostGreSQL相比较于Mysql具有极大的优势，具体参见PostGreSQL与MySQL比较（http://bbs.chinaunix.net/thread-1688208-1-1.html）。

然而，有很多项目已经采用了MySQL数据库，如何进行转换，下面提供了具体步骤。

1、  DUMP

将MYSQL中数据DUMP下来保存为mysql.sql，保存在与mysql2pgsql.pl（附件一）同样的文件夹中。

2、  PERL环境安装

Mysql2pgsql.pl是用perl语言编写的转换工具的源码，需要对应的编译程序编译运行，我用的是Strawberry Perl (64-bit)。需在环境变量中添加进PATH，路径一直包含到bin。

3、  运行脚本，获得转换后的pg.sql，命令perl mysql2pgsql.pl mysql.sql pg.sql

4、  导入数据库

a)         安装好postgresql后，在对应的database中导入转换后的内容即可。由于mysql和postgresql的命令格式区别还是比较大，所以转换后有可能会需要手动改动一些命令。

b)         导入文件语句：psql -d 数据库名 -h localhost -p 5432 -U postgres -f 文件路径；或者右键粘贴运行。

5、  Postgresql Jdbc测试

导入postgresql jdbc的jar包，可以连接数据库。

源码： 

package com.intel.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class TestDB {


 public static void main(String[] args) {
 
 try {
 Class.forName("org.postgresql.Driver").newInstance();
 String url = "jdbc:postgresql://localhost:5432/postgres";
 Connection conn = DriverManager.getConnection(url, "postgres", "hello123456$");
 Statement st = conn.createStatement();
 String sql = "select name from student where id='3'";
 ResultSet rs = st.executeQuery(sql);


 while(rs.next())
 {
 System.out.print(rs.getString(1));
 }
 rs.close();
 st.close();
 conn.close();
 } catch (InstantiationException e) {
 e.printStackTrace();
 } catch (IllegalAccessException e) {
 e.printStackTrace();
 } catch (ClassNotFoundException e) {
 e.printStackTrace();
 } catch (SQLException e) {
 e.printStackTrace();
 }
 
 }


}

附件一：

#!/usr/bin/perl -w
# mysql2pgsql
# MySQL to PostgreSQL dump file converter
#
# For usage: perl mysql2pgsql.perl --help
#
# ddl statments are changed but none or only minimal real data
# formatting are done.  
# data consistency is up to the DBA.
# 
# (c) 2004-2007 Jose M Duarte and Joseph Speigle ... gborg
# 
# (c) 2000-2004 Maxim Rudensky  <fonin@omnistaronline.com>
# (c) 2000 Valentine Danilchuk  <valdan@ziet.zhitomir.ua>
# All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions
# are met:
# 1. Redistributions of source code must retain the above copyright
#    notice, this list of conditions and the following disclaimer.
# 2. Redistributions in binary form must reproduce the above copyright
#    notice, this list of conditions and the following disclaimer in the
#    documentation and/or other materials provided with the distribution.
# 3. All advertising materials mentioning features or use of this software
#    must display the following acknowledgement:
# This product includes software developed by the Max Rudensky
# and its contributors.
# 4. Neither the name of the author nor the names of its contributors
#    may be used to endorse or promote products derived from this software
#    without specific prior written permission.
# THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS'' AND
# ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
# ARE DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE
# FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
# DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
# OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
# HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
# LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
# OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
# SUCH DAMAGE.

use Getopt::Long;

use POSIX;

use strict;
use warnings;


# main sections
# -------------
# 1 variable declarations
# 2 subroutines
# 3 get commandline options and specify help statement
# 4 loop through file and process
# 5. print_plpgsql function prototype

#################################################################
#  1.  variable declarations
#################################################################
# command line options
my( $ENC_IN, $ENC_OUT, $PRESERVE_CASE, $HELP, $DEBUG, $SCHEMA, $LOWERCASE, $CHAR2VARCHAR, $NODROP, $SEP_FILE, $opt_debug, $opt_help, $opt_schema, $opt_preserve_case, $opt_char2varchar, $opt_nodrop, $opt_sepfile, $opt_enc_in, $opt_enc_out );
# variables for constructing pre-create-table entities
my $pre_create_sql='';	# comments, 'enum' constraints preceding create table statement
my $auto_increment_seq= '';	# so we can easily substitute it if we need a default value
my $create_sql='';	# all the datatypes in the create table section 
my $post_create_sql='';   # create indexes, foreign keys, table comments
my $function_create_sql = '';  # for the set (function,trigger) and CURRENT_TIMESTAMP ( function,trigger )
#  constraints 
my ($type, $column_valuesStr, @column_values, $value );
my %constraints=(); #  holds values constraints used to emulate mysql datatypes (e.g. year, set)
# datatype conversion variables
my ( $index,$seq);
my ( $column_name, $col, $quoted_column);
my ( @year_holder, $year, $constraint_table_name);
my $table="";   # table_name for create sql statements
my $table_no_quotes="";   # table_name for create sql statements
my $sl = '^\s+\w+\s+';  # matches the column name
my $tables_first_timestamp_column= 1;  #  decision to print warnings about default_timestamp not being in postgres
my $mysql_numeric_datatypes = "TINYINT|SMALLINT|MEDIUMINT|INT|INTEGER|BIGINT|REAL|DOUBLE|FLOAT|DECIMAL|NUMERIC";
my $mysql_datetime_datatypes = "|DATE|TIME|TIMESTAMP|DATETIME|YEAR";
my $mysql_text_datatypes = "CHAR|VARCHAR|BINARY|VARBINARY|TINYBLOB|BLOB|MEDIUMBLOB|LONGBLOB|TINYTEXT|TEXT|MEDIUMTEXT|LONGTEXT|ENUM|SET";
my $mysql_datatypesStr =  $mysql_numeric_datatypes . "|". $mysql_datetime_datatypes . "|". $mysql_text_datatypes ;
# handling INSERT INTO statements
my $rowRe = qr{
    \(                  # opening parens
        (               #  (start capture)
            (?:         #  (start group)
            '           # string start
                [^'\\]*     # up to string-end or backslash (escape)
                (?:     #  (start group)
                \\.     # gobble escaped character
                [^'\\]*     # up to string-end of backslash
                )*      #  (end group, repeat zer