Forum: bugs

Monitor Forum | Start New Thread Start New Thread
RE: what are the commands to convert mysql2pg [ reply ]
By: joe speigle on 2008-07-31 14:47
This tutorial covers how to convert a database of about 40 tables to a postgresql database. I have used my perl mysql-postgresql conversion tool which is on I hope these notes come in handy as a tutorial.

Well, let's get started!

[postgres@www ~]$ pg_ctl --version
pg_ctl (PostgreSQL) 8.0.0beta3
[postgres@www ~]$

I have decided to show how to do it all the way from the beginning! So, let's do an "initdb" with the proper settings. Become the user postgres and find your way to the common directory for the database files and issue the following command:

[postgres@www ~]$ initdb --locale=ko_KR.utf8 --lc-messages=C -E unicode -D `pwd`/mod

In this case, I used "mod" as the directory under which to create the database.

The locale has to be checked, you get strange results if you give it a false value, it probably defaults to something you don't want. I am sure that is a valid locale, because I checked it:

[postgres@www ~]$ locale -a | grep ko

Then, run the postmaster using a different port number, so that you can keep the other postmaster running. (in case the other postmaster connects to another DB which is needed for other web applications on the same server). The command to start the postmaster on a different port looks like this:

[postgres@www ~]$ pg_ctl -D /bup/pgsql/mod -l logfile -o "-p5543 -h/bup/pgsql/mod" start

the -o flag means it is followed by options. the -p5543 gives the daemon a port number. The -h argument gives a data directory which to start from.

That done, let's create another user who has rights on that db. My reference page is the postgres create user command.

[postgres@www mod]$ createuser -d -P -h /bup/pgsql/mod -p portnumber hissecretname
Enter password for new user:
Enter it again:
Shall the new user be allowed to create more new users? (y/n) n
[postgres@www mod]$

The above username/password combination will be used in your client config file (e.g. dbconfig.php, for example). Then, we have to create the database itself. A reference page for the "create database" command in postgres is postgres create database command.

[postgres@www mod]$ createdb -O hissecretname -p portnumber -h /bup/pgsql/mod -E UNICODE -e mod
[postgres@www mod]$

Now that the user and database are in place, it's onto starting the actual conversion!

First, dump and convert the table structure.

The link for the perl script which converts the "create table" type SQL is the mysql2psql file source on

[mysql@www ~]$ mysqldump --no-data --no-create-db [dbname] [ 26 table names snipped ]
[mysql@www ~]$ perl mysql2psql.perl mysql_ddl.sql pg_ddl.sql
[mysql@www ~]$ sudo su - postgres
[postgres@www ~]$ psql -d mod -p5543 -h/bup/pgsql/mod -U hissecretname < pg_ddl.sql

alternatively you could also do this:

[mysql@www ~]$ mysqldump --no-data --no-create-db [dbname] [ 26 table names snipped ]
[mysql@www ~]$ perl mysql2psql.perl mysql_ddl.sql pg_ddl.sql
[mysql@www ~]$ sudo su - postgres
[postgres@www ~]$ psql -d mod -p5543 -h/bup/pgsql/mod -U hissecretname
psql=#\i pg_ddl.sql

The backslash-i command runs the commands in the file.

The above mysqldump command only dumps the "create table" statements.

If you want to dump everything in one fell swoop, you will encounter the following problem.

ERROR: syntax error at or near "english_english" at character 13
LINE 1: LOCK TABLES english_english WRITE;
ERROR: syntax error at or near "," at character 145
LINE 1:',15,'2006-03-18 02:24:20'),(2,'','',...
ERROR: syntax error at or near "," at character 207
LINE 1: ...g of mountain climbing)',13,'2006-03-18 02:24:20'),(8085,'',...
ERROR: syntax error at or near "," at character 320
LINE 1: ...mmation in the synovium ',5,'2006-03-18 02:24:20'),(12989,''...


The errors are caused by mysql doing something like insert into english_english('...','..'),('...','....'),.....

So, you can't simply skip the following, but you're welcome to try and let us know.

The solution is to avoid a dump with INSERT statements. Instead, we have the cool select into outfile and COPY commands.

When your table structure is in place, dumping the actual table data from mysql is the next step.

For more reference, see a file about mysql backuping. That file describes using SELECT INTO OUTFILE by mysql to make a straight dump to the file; i.e., no "insert" statements. There are 'tab-separated fields' and such.

[mysql@www 2pg]$ mysql
mysql > use database1;
mysql > show tables;
(cut and paste tables into the file in the other window, let's call it datadump.sql)
[mysql@www 2pg]$ vi datadump.sql
:%s/\(.*\).*/select * into outfile '\1.txt' FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n' FROM \1;/gc
[mysql@www 2pg]$ mkdir data
[mysql@www 2pg]$ cd data
[mysql@www data]$ mv ../datadump.sql .
[mysql@www data]$ ls
[mysql@www data]$ mysql < datadump.sql
ERROR 1046 (3D000) at line 1: No database selected
[mysql@www data]$ vi ~/.my.cnf
[mysql@www data]$ mysql < datadump.sql
[mysql@www data]$ ls
datadump.sql p_user_todo_german.txt
english_english.txt p_user_todo_japanese.txt
english_korean.txt p_user_todo_korean.txt
french_english.txt p_user_todo_spanish.txt
german_english.txt rss_category.txt
japanese_english.txt rss_info.txt
korean_english.txt spanish_english.txt
p_english.txt user_old_english_english.txt
p_french.txt user_old_english_korean.txt
p_german.txt user_old_french_english.txt
p_japanese.txt user_old_german_english.txt
p_korean.txt user_old_japanese_english.txt
p_spanish.txt user_old_korean_english.txt
p_user_old_english.txt user_old_spanish_english.txt
p_user_old_french.txt user_todo_english_english.txt
p_user_old_german.txt user_todo_english_korean.txt
p_user_old_japanese.txt user_todo_french_english.txt
p_user_old_korean.txt user_todo_german_english.txt
p_user_old_spanish.txt user_todo_japanese_english.txt
p_user_todo_english.txt user_todo_korean_english.txt
p_user_todo_french.txt user_todo_spanish_english.txt
[mysql@www data]$

The individual mysql dump files are then imported into postgres using the "copy" command and a shell script.

The COPY line is like the following:

psql=#COPY user_todo_korean_english FROM '/tmp/data/user_todo_korean_english.txt';

and write a shell script to loop over all of them:

[postgres@www data]$ cat
# run this as the postgres user in the directory
# where the mysql "select into outfile" files are.
# this bash script processes all those *.txt files
# `ls`
# data/table1.txt
# data/table2.txt
# becomes 2 runs of the copy command
# psql -d DBNAME -c "COPY table1 FROM '/tmp/data/table1.txt';"
# you need to give these values
# COPY table1 FROM '/tmp/data/table1.txt';
# COPY table2 FROM '/tmp/data/table2.txt';
for j in `ls *.txt`;do
if [ -f $j ];then
echo processing $file_name
echo `psql -d $dbname -c "COPY ${j%.txt} FROM '$file_name';"`
[postgres@www data]$

The conversion probably will produce errors. Read the message, and try to find out what the problem is with the datatype conversion. Then, you will have to drop all tables (easier) or delete data from all the tables (harder), and repeat the process of dumping from mysql and importing into postgres. As a sample problem, I love that in mysql a valid date-time is '0000-00-00 00:00:00' which is not even a time!

For this reason, the mysqldump command above is actually run from inside a shell script so I can modify easily the table list and add comments.

If you discover any data-type conversion problems which are amusing, please use the "comments" to notify us here. Eventually,it should happily look like this:

[postgres@www data]$ sh
processing /mnt/bu/var/myowndictionary/sqlwork/2pg/data/english_english.txt
processing /mnt/bu/var/myowndictionary/sqlwork/2pg/data/english_korean.txt

what are the commands to convert mysql2pgsql? [ reply ]
By: aditya gada on 2008-07-02 05:22
i cant find any resources in this site for the commands pertaining to convert from mysql to pgsql.
Pls reply with some resources.

Powered By FusionForge