On 03/26/2010 12:46 PM, Matija Nalis wrote: > > On Sun, Mar 21, 2010 at 10:20:22PM +0000, Alan Brown wrote: >> On 21/03/10 20:43, Bruno Friedmann wrote: >>> After moving your data from mysql to postgresql, it's important to adjust >>> the pkey index to the correct values >>> (last of the table) >>> Check for each table of the db. >>> >>> If I remember correctly this is noted in the manual or the wiki. >> >> It's not, but I've found how to fix it. The steps given in both >> documents are deficient and I'll submit updates when done. > > Hi Alan, > > have you perhaps succeded in transition from MySQL to PostgreSQL for > bacula ? > > An update to the documentation (or your explanation about problems > and what you did) would be greately appreciated, as we're also being > pinned down with extreme MySQL slowdowns in 5.0.x (bacula bug 1472) > and must move to PostgreSQL without losing catalog data (or die > trying, which I'd like to avoid if at all possible :) > >
Here we have done the following Ref : http://www.bacula.org/manuals/en/catalog/catalog/Installi_Configur_PostgreS.html#1107 1 : Be sure the two db are at the same level Make all upgrade to a temporary database if needed on Mysql Create a export directory (ensure there's suffisant place) mkdir -p /var/tmp/export chown mysql:postgres /var/tmp/export 2 : Export Import script #!/bin/sh # Script to extract mysql bacula catalog, and inject it to postgresql # One line ! TABLES="BaseFiles CDImages Client Counters Device File FileSet Filename Job JobHisto JobMedia Location LocationLog Log Media MediaType Path Pool Status Storage UnsavedFiles Version" for TBL in $TABLES do echo "Saving Mysql $TBL"; mysqldump -t -c \ --compatible=postgresql \ --skip-quote-names \ --skip-opt --disable-keys --lock-tables \ bacula $TBL > $TBL.sql # We don't do this immediately as we must change some wrong record # with very very old jobs [2] # echo "Import postgresql $TBL" # psql -d bacula -U bacula < $TBL.sql # echo '--end--' echo "" done echo '-- Finish ! --' 3: Troubles If you have enough chance you can inject all table directly to pg. We encounter some error with date ending to 0000-00-00 which are not valid under pg. so we correct manually some records (Our db was setup with bacula 1.36 version and have been upgraded version by version so we can accept some errors (3 for us) 4: Sequences ATTENTION : we need to adjust the auto-increment sequence in postgresql as insert into doesn't count ! Use older value for the mysql dump minus 1 or use max(...id) on each table. -- SELECT setval('public.media_mediaid_seq', 187, true); Hope this help you. We worked on test mysql db :-) -- Bruno Friedmann ------------------------------------------------------------------------------ Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users