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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
