Hi,
On Thu, July 10, 2008 21:15, Yuri Timofeev wrote:
> Try
>
> bacula_DB_copy_from_MySQL_to_PGSQL.php
>
> http://webacula.svn.sourceforge.net/viewvc/webacula/devel/pgsql_tests/bacula_DB_copy_from_MySQL_to_PGSQL.php?view=markup
>
> Warning. I used this script only to test the database.
Thanks for your script. I started to use it and it was working fine.
Unfortunately it was really slow because there are lots of data to
fetch/insert and both database engines are running on the same host,
competing both for the same disk.
So I ended doing the following, based on your script, and general advice I
could gather on the net about speeding postgresql data import:
1) I dumped the mysql database in CSV format:
mysqldump --fields-terminated-by=',' --fields-optionally-enclosed-by='"'
--lines-terminated-by='\n' -T /tmp/dump
This produces .txt files in the /tmp/dump folder.
2) I added to postgresql the bacula schema and table definition as
explained in Bacula documentation.
3) I rewrote (used sed in fact) some of the .txt file to change the
various \N to nothing (NULL definition for postgresql copy), and did the
same for 0000-00-00 00:00:00.
4) in psql I used the copy with csv postgresql command to import the files:
Basically, for each table I did:
copy file from '/tmp/dumptest/File.txt' with csv;
The only caveats were:
* in my bacula version (2.2.8), the postgresql schema of jobmedia doesn't
contain the stripe column. I manually added it the schema.
* the job table fields are not ordered the same in mysql and postgresql,
so I had to list the columns in the aforementioned copy statement.
5) I updated the sequences to match the last id. Copy doesn't increment
the sequence unlike inserts, so I manually reset the various sequences
manually, with this kind of command:
select setval('storage_storageid_seq', max(storageid)) from storage;
6) I tested a few backup and restore, and everything seems OK. dbcheck
warns about the same issues as with mysql :-)
Anyway, thanks for pointing me to your script, it helped me for the full
of 0 date that should be mapped to NULL :-)
--
Brice Figureau
Days of Wonder
-------------------------------------------------------------------------
Sponsored by: SourceForge.net Community Choice Awards: VOTE NOW!
Studies have shown that voting for your favorite open source project,
along with a healthy diet, reduces your potential for chronic lameness
and boredom. Vote Now at http://www.sourceforge.net/community/cca08
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
