mysql2pgsql
Differences
This shows you the differences between two versions of the page.
| Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
| mysql2pgsql [2006/07/23 18:13] – slonopotamus | mysql2pgsql [2019/06/12 16:08] (current) – external edit 127.0.0.1 | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | [[start|На главную]] | ||
| + | ====== Конвертация данных из MySQL в PostgreSQL ====== | ||
| + | Сильно ковырятый скрипт из проекта [[http:// | ||
| + | ===== Фичи ===== | ||
| + | * Конвертация FOREIGN KEY' | ||
| + | * Конвертация индексов | ||
| + | * Конвертация BLOB' | ||
| + | * Вставка данных в таблицу единой транзакцией | ||
| + | ===== Известные недостатки ===== | ||
| + | * Поддержка только одного авто-инкрементного поля на таблицу | ||
| + | * Довольно медленная вставка в PostgreSQL. В идеале INSERT' | ||
| + | ===== Использование ===== | ||
| + | Скрипт принимает в STDIN дамп из MySQL' | ||
| + | |||
| + | ==== Команда для экспорта из MySQL' | ||
| + | < | ||
| + | mysqldump --user=USER_NAME --extended-insert=0 --add-drop-table=FALSE --add-locks=FALSE --hex-blob --password=PASSWORD DATABASE_NAME > pg_dump.sql | ||
| + | </ | ||
| + | Команда для импорта в postgres: | ||
| + | < | ||
| + | cat pg_dump.sql | perl mysql2pgsql.pl | psql -U USER_NAME -W DATABASE_NAME | ||
| + | </ | ||
| + | В принципе можно запустить прямо пайпом из mysql' | ||
| + | < | ||
| + | mysqldump --user=USER_NAME --extended-insert=0 --add-drop-table=FALSE --add-locks=FALSE --hex-blob --password=PASSWORD DATABASE_NAME | perl mysql2pgsql.pl | psql -U USER_NAME -W DATABASE_NAME | ||
| + | </ | ||
| + | <code perl> | ||
| + | # | ||
| + | use Data:: | ||
| + | use Getopt:: | ||
| + | # the next two work with SET datatype | ||
| + | use List:: | ||
| + | use List:: | ||
| + | |||
| + | use strict; | ||
| + | |||
| + | # command arguments | ||
| + | my( $DEBUG, $HELP, $SCHEMA, $opt_debug, $opt_help, $opt_schema); | ||
| + | # general values | ||
| + | my ($out, $size); | ||
| + | # variables for constructing pre-create-table entities | ||
| + | my $create_sql=''; | ||
| + | my %constraints=(); | ||
| + | my ( $index, | ||
| + | my $table=''; | ||
| + | my $pre_create_sql=''; | ||
| + | my $foreign_key_create_sql = ''; | ||
| + | my $index_create_sql=''; | ||
| + | my $transaction = 0; | ||
| + | my $mysql_datatypesStr = " | ||
| + | my $table_start_time = time(); | ||
| + | |||
| + | # create separate table to reference and to hold mysql' | ||
| + | # values. | ||
| + | # definition | ||
| + | # and create a reference to that table in the create table statement | ||
| + | sub convert_enum_and_set_to_constraint_table { | ||
| + | my ($d, $type, @tmp, $column_valuesStr, | ||
| + | if ($DEBUG) { | ||
| + | $d = Data:: | ||
| + | print $d-> | ||
| + | } | ||
| + | foreach $column_name (keys %constraints) { | ||
| + | $type=$constraints{$column_name}{' | ||
| + | $column_valuesStr = $constraints{$column_name}{' | ||
| + | if ($DEBUG) { | ||
| + | $d = Data:: | ||
| + | print $d-> | ||
| + | } | ||
| + | $constraint_table_name = ${table} . " | ||
| + | $max_length=0; | ||
| + | @column_values = split /,/, $column_valuesStr; | ||
| + | if ($DEBUG) { | ||
| + | print STDERR | ||
| + | } | ||
| + | # do constraints for a set | ||
| + | # yes, i feel sorry for whoever needs to change their acceptable mysql SET values down the road | ||
| + | # yes, this is | ||
| + | if ($type eq " | ||
| + | my $ps = powerset(@column_values); | ||
| + | while (my $power_set = pop(@$ps)) { | ||
| + | my $perm = new List:: | ||
| + | while (my @set = $perm-> | ||
| + | # print "One order is @set.\n"; | ||
| + | $setStr | ||
| + | $setStr =~ s/','/,/ | ||
| + | push(@tmp, $setStr); | ||
| + | } | ||
| + | } | ||
| + | @column_values = @tmp; | ||
| + | } | ||
| + | |||
| + | # do constraints for an enum | ||
| + | # find max length of values in enumeration | ||
| + | foreach (@column_values) { | ||
| + | if ($max_length < length($_)) { $max_length = length($_); } | ||
| + | } | ||
| + | $pre_create_sql .= qq~create table $constraint_table_name | ||
| + | foreach $value (@column_values) { | ||
| + | if ($DEBUG) { | ||
| + | print STDERR "---- column_value = " . $value . " | ||
| + | } | ||
| + | if ($value =~ m/'/) { | ||
| + | $pre_create_sql .= qq~insert into $constraint_table_name | ||
| + | } else { | ||
| + | $pre_create_sql .= qq~insert into $constraint_table_name values (' | ||
| + | } | ||
| + | } | ||
| + | } | ||
| + | # now append to $pre_create_sql the " create table " clause which references $constraint_table_name | ||
| + | } # END sub convert_enum_and_set_to_constraint_table | ||
| + | |||
| + | # print create table and reset create table vars | ||
| + | # when moving from each " | ||
| + | sub print_table_def() { | ||
| + | my ( @create_idx_commandsArr, | ||
| + | & | ||
| + | print $pre_create_sql; | ||
| + | print $create_sql; | ||
| + | $create_sql=""; | ||
| + | $pre_create_sql=''; | ||
| + | # empty %constraints for next " create table" statement | ||
| + | %constraints=(); | ||
| + | } | ||
| + | |||
| + | sub toOctets($) { | ||
| + | $_ =~ / | ||
| + | return ",'" | ||
| + | } | ||
| + | |||
| + | sub hex2dec($) { | ||
| + | my $srcString = shift; | ||
| + | my $dstString = ''; | ||
| + | my $strLen = length $srcString; | ||
| + | if (($strLen % 2) != 0) { | ||
| + | $srcString = ' | ||
| + | $strLen++; | ||
| + | } | ||
| + | for (my $i = 0; $i < $strLen; $i+=2) { | ||
| + | my $currentOctet = substr($srcString, | ||
| + | $dstString .= ' | ||
| + | } | ||
| + | | ||
| + | } | ||
| + | |||
| + | |||
| + | my %pg_keywords = ( | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | ); | ||
| + | |||
| + | |||
| + | GetOptions(" | ||
| + | |||
| + | $DEBUG = $opt_debug || 0; | ||
| + | $HELP = $opt_help || 0; | ||
| + | |||
| + | binmode STDIN; | ||
| + | binmode STDOUT; | ||
| + | |||
| + | my $start = time(); | ||
| + | |||
| + | print " | ||
| + | print "-- Generated from mysql2pgsql.perl\n"; | ||
| + | print "-- http:// | ||
| + | print "-- (c) 2004, PostgreSQL Inc.\n"; | ||
| + | print " | ||
| + | print " | ||
| + | print "-- errors are generated for drop tables if they do not exist\n"; | ||
| + | print "-- please see http:// | ||
| + | |||
| + | if ($SCHEMA ) { | ||
| + | print "set search_path='" | ||
| + | } | ||
| + | |||
| + | while(<> | ||
| + | s/ | ||
| + | # comments or empty lines | ||
| + | if (/^#/ || /^$/ || /^--/) { | ||
| + | print $_; | ||
| + | next; | ||
| + | } | ||
| + | if (/ | ||
| + | print "\\c ". $1; | ||
| + | next; | ||
| + | } | ||
| + | |||
| + | if ($create_sql ne "" | ||
| + | if (/ | ||
| + | s/,$//g; | ||
| + | $foreign_key_create_sql .= "alter table $table add $_; | ||
| + | next; | ||
| + | } | ||
| + | if (/\);/i) { # end of create table squence | ||
| + | $create_sql =~ s/,$//g; # strip last , inside create table | ||
| + | $create_sql .= $_; | ||
| + | & | ||
| + | print (" | ||
| + | $transaction = 1; | ||
| + | next; | ||
| + | } elsif (/\).*;/i) { # end of create table sequence | ||
| + | s/ | ||
| + | s/DEFAULT CHARSET=[^; | ||
| + | s/ | ||
| + | s/PARTITION BY LINEAR KEY \(.*\) PARTITIONS \d+//i; | ||
| + | s/ | ||
| + | $create_sql =~ s/,$//g; # strip last , inside create table | ||
| + | $create_sql .= $_; | ||
| + | & | ||
| + | print (" | ||
| + | $transaction = 1; | ||
| + | next; | ||
| + | } | ||
| + | # A SET is a string object that can have zero or more values, each of which must be chosen from a list of allowed values specified when the table is created. SET column values that consist of multiple set members are specified with members separated by commas (‘,’). A consequence of this is that SET member values should not themselves contain commas. | ||
| + | |||
| + | #For example, a column specified as SET(' | ||
| + | |||
| + | #'' | ||
| + | #' | ||
| + | #' | ||
| + | #' | ||
| + | if (/ | ||
| + | $column_name=$1; | ||
| + | $constraints{$column_name}{' | ||
| + | $constraints{$column_name}{' | ||
| + | $_ = qq~ $1 varchar CONSTRAINT ${table}_${column_name}_constraint REFERENCES ${table}_${column_name}_constraint_table (${column_name}) $3\n~; | ||
| + | } elsif (/ | ||
| + | $column_name=$1; | ||
| + | $constraints{$column_name}{' | ||
| + | $constraints{$column_name}{' | ||
| + | $_ = qq~ $1 varchar CONSTRAINT ${table}_${column_name}_constraint REFERENCES ${table}_${column_name}_constraint_table (${column_name}) $3\n~; | ||
| + | } elsif (/ | ||
| + | $column_name=$1; | ||
| + | my @year_holder = (); | ||
| + | my $year; | ||
| + | for (1901 .. 2155) { | ||
| + | $year = "' | ||
| + | unless ($year =~ /2155/) { $year .= ','; | ||
| + | push( @year_holder, | ||
| + | } | ||
| + | $constraints{$column_name}{' | ||
| + | $constraints{$column_name}{' | ||
| + | $_ = qq~ $1 varchar CONSTRAINT ${table}_${column_name}_constraint REFERENCES ${table}_${column_name}_constraint_table (${column_name}) $2\n~; | ||
| + | } elsif (/ | ||
| + | $column_name=$1; | ||
| + | my @year_holder = (); | ||
| + | my $year; | ||
| + | for (1970 .. 2069) { | ||
| + | $year = "' | ||
| + | if ($year =~ /2069/) { next; } | ||
| + | push( @year_holder, | ||
| + | } | ||
| + | push( @year_holder, | ||
| + | $constraints{$column_name}{' | ||
| + | $constraints{$column_name}{' | ||
| + | $_ = qq~ $1 varchar CONSTRAINT ${table}_${column_name}_constraint REFERENCES ${table}_${column_name}_constraint_table (${column_name}) $2\n~; | ||
| + | } elsif (/ | ||
| + | $seq = qq~${table}_seq~; | ||
| + | # Note: Before PostgreSQL 8.1, the arguments of the sequence functions were of type text, not regclass, and the above-described conversion from a text string to an OID value would happen at run time during each call. For backwards compatibility, | ||
| + | s/ | ||
| + | $pre_create_sql.=" | ||
| + | $index_create_sql .= " | ||
| + | $create_sql.=$_; | ||
| + | next; | ||
| + | |||
| + | # int type conversion | ||
| + | } elsif (/ | ||
| + | $size=$1; | ||
| + | $size =~ tr [A-Z] [a-z]; | ||
| + | if ($size eq " | ||
| + | $out = " | ||
| + | } elsif ($size eq " | ||
| + | $out = " | ||
| + | } else { | ||
| + | $out = " | ||
| + | } | ||
| + | s/ | ||
| + | } | ||
| + | |||
| + | # nuke int unsigned | ||
| + | s/ | ||
| + | |||
| + | # blob -> text | ||
| + | # mysql docs: A BLOB is a binary large object that can hold a variable amount of data. | ||
| + | s/ | ||
| + | # tinytext/ | ||
| + | s/ | ||
| + | s/ | ||
| + | s/ | ||
| + | |||
| + | # char -> varchar | ||
| + | # PostgreSQL would otherwise pad with spaces as opposed | ||
| + | # to MySQL! Your user interface may depend on this! | ||
| + | s/ | ||
| + | |||
| + | # nuke date representation (not supported in PostgreSQL) | ||
| + | if (m/ | ||
| + | s/datetime NOT NULL default ' | ||
| + | s/datetime default ' | ||
| + | s/datetime /timestamp without time zone /i; | ||
| + | s/date default ' | ||
| + | s/time default ' | ||
| + | |||
| + | # change not null datetime field to null valid ones | ||
| + | # (to support remapping of "zero time" to null | ||
| + | s/datetime not null/ | ||
| + | } | ||
| + | s/' | ||
| + | |||
| + | # nuke size of timestamp | ||
| + | s/ | ||
| + | |||
| + | # double -> float8 | ||
| + | s/ | ||
| + | |||
| + | # add unique to definition of type (MySQL separates this) | ||
| + | if (/unique \w+ \((\w+)\)/ | ||
| + | $create_sql.=~s/ | ||
| + | next; | ||
| + | } | ||
| + | #There are three types of indexes (keys) in MySQL (plus fulltext), | ||
| + | |||
| + | # FIXED (?) : unique for multipe columns (col1,col2) are unsupported! | ||
| + | # unique constraint w/ multipe columns (col1,col2) in Pg 7.4 | ||
| + | |||
| + | if ( !/ | ||
| + | if ( / | ||
| + | # the tsvector datatype is made for these types of things | ||
| + | # example mysql file: | ||
| + | # `commenttext` text NOT NULL, | ||
| + | # `deleted` enum(' | ||
| + | # `onhold` tinyint(1) NOT NULL default ' | ||
| + | # PRIMARY KEY (`commentid`), | ||
| + | # FULLTEXT KEY `commenttext` (`commenttext`) | ||
| + | # what is tsvector datatype? | ||
| + | # http:// | ||
| + | next; | ||
| + | } elsif ( /^\s+unique key\s+/i ) { | ||
| + | s/unique key .* (\(.*\))/ | ||
| + | $create_sql .= $_; | ||
| + | next; | ||
| + | } elsif ( / | ||
| + | chomp($_); | ||
| + | s/,$//; | ||
| + | $index_create_sql .= "ALTER TABLE $table ADD constraint " | ||
| + | next; | ||
| + | } elsif (/\bkey\b/i ) { | ||
| + | #regular key: allows null=YES, allows duplicates=YES | ||
| + | # MYSQL: | ||
| + | # example: | ||
| + | m/key (.*?) (\(.*\))/ | ||
| + | # * MySQL: ALTER TABLE {$table} ADD KEY $column ($column) | ||
| + | # * PostgreSQL: CREATE INDEX {$table}_$column_idx ON {$table}($column) // Please note the _idx " | ||
| + | # PRIMARY KEY (`postid`), | ||
| + | # KEY `ownerid` (`ownerid`) | ||
| + | # create an index for everything which has a key listed for it. | ||
| + | $index_create_sql.=" | ||
| + | # just create index do not add to create table statement | ||
| + | next; | ||
| + | } | ||
| + | |||
| + | } elsif (/ | ||
| + | $index = qq~${table}_${1}_idx~; | ||
| + | $index_create_sql.=" | ||
| + | $create_sql.=$_; | ||
| + | } | ||
| + | # TODO: create a trigger for this | ||
| + | s/on update CURRENT_TIMESTAMP//; | ||
| + | |||
| + | # quote column names | ||
| + | if ( / | ||
| + | s/ | ||
| + | } | ||
| + | $create_sql.=$_; | ||
| + | } # END of if ($create_sql ne "" | ||
| + | else { # not inside create table | ||
| + | s/, | ||
| + | s/, | ||
| + | #---- fix data in inserted data: (from MS world) | ||
| + | # FIX: disabled for now | ||
| + | if (00 && /insert into/i) { | ||
| + | s!\x96!-!g; | ||
| + | s!\x93!" | ||
| + | s!\x94!" | ||
| + | s!\x85!... !g; # \ldots | ||
| + | s!\x92!`!g; | ||
| + | } | ||
| + | |||
| + | # fix dates ' | ||
| + | s/' | ||
| + | s/' | ||
| + | s/' | ||
| + | # s/ | ||
| + | |||
| + | # protect ; in inserts | ||
| + | #while (/ | ||
| + | # | ||
| + | #} | ||
| + | |||
| + | # DROP TABLE IF EXISTS `english_english`; | ||
| + | if (/ | ||
| + | next; | ||
| + | s/ | ||
| + | # remove "if exists" | ||
| + | # to drop a table that is referenced by a view or a foreign-key constraint of another table, CASCADE must be specified. (CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely.) | ||
| + | s/ | ||
| + | } | ||
| + | |||
| + | |||
| + | if (/^create table/i) { | ||
| + | if ($transaction) { | ||
| + | print " | ||
| + | print STDERR "Table $table: " | ||
| + | $table_start_time = time(); | ||
| + | $transaction = 0; | ||
| + | } | ||
| + | # CREATE TABLE `english_english` ( | ||
| + | s/ | ||
| + | $create_sql = $_; | ||
| + | /create table (.+?) /i; | ||
| + | $table=$1; | ||
| + | } else { | ||
| + | print $_; | ||
| + | } | ||
| + | } # end of if inside create_table | ||
| + | } # END while(<> | ||
| + | |||
| + | if ($transaction) { | ||
| + | print " | ||
| + | $transaction = 0; | ||
| + | } | ||
| + | |||
| + | print $index_create_sql; | ||
| + | |||
| + | print $foreign_key_create_sql; | ||
| + | |||
| + | print STDERR "Total time: " | ||
| + | </ | ||