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: " | ||
+ | </ |
mysql2pgsql.txt · Last modified: 2019/06/12 16:08 by 127.0.0.1