Backup Central

Hello
Dos someone have already do a migration of the Catalogue from a PostgreSQL =
to y MySQL? Or know how it has to work?
I tried to dump Postgres so that he dump only the Data and use INSERT in st=
ead of COPY. Then I grep only the lines with Inserts.
I create the bacula-tables with the script (make_mysql_tables) and try to i=
nsert the Data dumped from Postgres. But the Tables in MySQL seams to have =
different Names then in PostgreSQL at lest different upper/lower case.

thanks for any help

Berner Martin

On Wednesday 13 June 2007 10:18:44 Berner Martin wrote:
Hello
Dos someone have already do a migration of the Catalogue from a PostgreSQL
to y MySQL? Or know how it has to work? I tried to dump Postgres so that he
dump only the Data and use INSERT in stead of COPY. Then I grep only the
lines with Inserts. I create the bacula-tables with the script
(make_mysql_tables) and try to insert the Data dumped from Postgres. But
the Tables in MySQL seams to have different Names then in PostgreSQL at
lest different upper/lower case.

thanks for any help

Berner Martin

I don't see any way to get past the case sensitivity problem, except by
replacing all table names with their mysql versions in the dump (a simple
perl or sed script will do the trick)

But I feel I should ask : maybe you don't really need to leave postgresql ?
What I really mean is that if it's for performance reasons, things will
definitely get better with the next bacula release (on par or even better
than mysql), with the batch insert code.

If it's because you're more at ease with mysql or some other good reason of
this kind, forget what I've just said. Both database have their advantages...

Marc Cousin wrote:
On Wednesday 13 June 2007 10:18:44 Berner Martin wrote:
Hello
Dos someone have already do a migration of the Catalogue from a PostgreSQL
to y MySQL? Or know how it has to work? I tried to dump Postgres so that he
dump only the Data and use INSERT in stead of COPY. Then I grep only the
lines with Inserts. I create the bacula-tables with the script
(make_mysql_tables) and try to insert the Data dumped from Postgres. But
the Tables in MySQL seams to have different Names then in PostgreSQL at
lest different upper/lower case.

thanks for any help

Berner Martin

I don't see any way to get past the case sensitivity problem, except by
replacing all table names with their mysql versions in the dump (a simple
perl or sed script will do the trick)

You can actually tell MySQL to be case insensitive on table names with the
lower_case_table_names variable.

http://www.mysql.org/doc/refman/5.0/en/identifier-case-sensitivity.html

--
Frank Sweetser fs at wpi.edu | For every problem, there is a solution that
WPI Senior Network Engineer | is simple, elegant, and wrong. - HL Mencken
GPG fingerprint = 6174 1257 129E 0D21 D8D4 E8A3 8E39 29E3 E2E8 8CEC

Yes it is because I have some bottleneck with PostgreSQL as backend. So I t=
ry to give MySQL a try on a Testenvirement. But it has no sense if I cant m=
igrate the catalogue. And to make a true comparison it is important to have=
as much entries in both Databases. Before migrating I made some Tests with=
a empty Mysql-DB. I back up a Directory with 10000 textfiles each contains=
only its name. Postgres 1.5 Minutes, Mysql 20-40 seconds. Compared with th=
e tared directory (one File with same size as all together both Databases t=
ake about 2 seconds.
You talk about Performance with Postgres. What can I do to push up the perf=
ormance with Postgres till next release will bring me the "batch insert"?

best regards

Berner Martin
=

-----Ursprungliche Nachricht-----
Von: bacula-users-bounces < at > li...
[mailto:bacula-users-bounces < at > li...]Im Auftrag von Marc
Cousin
Gesendet: Mittwoch, 13. Juni 2007 18:28
An: bacula-users < at > li...; martin.berner < at > br...
Betreff: Re: [Bacula-users] migrate catalog from PostgreSQL to MySQL
=

=

On Wednesday 13 June 2007 10:18:44 Berner Martin wrote:
Hello
Dos someone have already do a migration of the Catalogue =

from a PostgreSQL
to y MySQL? Or know how it has to work? I tried to dump =

Postgres so that he
dump only the Data and use INSERT in stead of COPY. Then I =

grep only the
lines with Inserts. I create the bacula-tables with the script
(make_mysql_tables) and try to insert the Data dumped from =

Postgres. But
the Tables in MySQL seams to have different Names then in =

PostgreSQL at
lest different upper/lower case.

thanks for any help

Berner Martin
=

I don't see any way to get past the case sensitivity problem, =

except by
replacing all table names with their mysql versions in the =

dump (a simple
perl or sed script will do the trick)
=

But I feel I should ask : maybe you don't really need to =

leave postgresql ?
What I really mean is that if it's for performance reasons, =

things will
definitely get better with the next bacula release (on par or =

even better
than mysql), with the batch insert code.
=

If it's because you're more at ease with mysql or some other =

good reason of
this kind, forget what I've just said. Both database have =

their advantages...
=

--------------------------------------------------------------
-----------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Bacula-users mailing list
Bacula-users < at > li...
https://lists.sourceforge.net/lists/listinfo/bacula-users

On Thursday 14 June 2007 08:36:19 Berner Martin wrote:
Yes it is because I have some bottleneck with PostgreSQL as backend. So I
try to give MySQL a try on a Testenvirement. But it has no sense if I cant
migrate the catalogue. And to make a true comparison it is important to
have as much entries in both Databases. Before migrating I made some Tests
with a empty Mysql-DB. I back up a Directory with 10000 textfiles each
contains only its name. Postgres 1.5 Minutes, Mysql 20-40 seconds. Compared
with the tared directory (one File with same size as all together both
Databases take about 2 seconds. You talk about Performance with Postgres.
What can I do to push up the performance with Postgres till next release
will bring me the "batch insert"?

For now, you can turn fsync to off in postgresql.conf. Be aware that you may
corrupt the database in case of a system crash.

Thanks for the Hint. But I have done this trick alredy. I tryed to tune the=
Postgre, installed more RAM.. But the impact is still huge.
Thus the "helper Indexes" commented out in the make_postgre_tables (file_pa=
thid_idx, file_filenameid_idx, file_jpfid_idx) halps pushing up the Perform=
ance?

=

-----Ursprungliche Nachricht-----
Von: bacula-users-bounces < at > li...
[mailto:bacula-users-bounces < at > li...]Im Auftrag von Marc
Cousin
Gesendet: Donnerstag, 14. Juni 2007 08:45
An: martin.berner < at > br...
Cc: bacula-users < at > li...
Betreff: Re: [Bacula-users] migrate catalog from PostgreSQL to MySQL
=

=

On Thursday 14 June 2007 08:36:19 Berner Martin wrote:
Yes it is because I have some bottleneck with PostgreSQL as =

backend. So I
try to give MySQL a try on a Testenvirement. But it has no =

sense if I cant
migrate the catalogue. And to make a true comparison it is =

important to
have as much entries in both Databases. Before migrating I =

made some Tests
with a empty Mysql-DB. I back up a Directory with 10000 =

textfiles each
contains only its name. Postgres 1.5 Minutes, Mysql 20-40 =

seconds. Compared
with the tared directory (one File with same size as all =

together both
Databases take about 2 seconds. You talk about Performance =

with Postgres.
What can I do to push up the performance with Postgres till =

next release
will bring me the "batch insert"?
=

For now, you can turn fsync to off in postgresql.conf. Be =

aware that you may
corrupt the database in case of a system crash.
=

--------------------------------------------------------------
-----------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Bacula-users mailing list
Bacula-users < at > li...
https://lists.sourceforge.net/lists/listinfo/bacula-users

There is nothing that will totally solve the performance problems you're=20
facing... For now, bacula's code does many small operations, each in it's=20
transaction, and postgresql isn't good at that...
Removing indexes may help the insert speed, but don't have too much hopes

Le Thursday 14 June 2007 09:02:40 Berner Martin, vous avez =E9crit=A0:
Thanks for the Hint. But I have done this trick alredy. I tryed to tune t=
he
Postgre, installed more RAM.. But the impact is still huge. Thus the
"helper Indexes" commented out in the make_postgre_tables (file_pathid_id=
x,
file_filenameid_idx, file_jpfid_idx) halps pushing up the Performance?

-----Ursprungliche Nachricht-----
Von: bacula-users-bounces < at > li...
[mailto:bacula-users-bounces < at > li...]Im Auftrag von Marc
Cousin
Gesendet: Donnerstag, 14. Juni 2007 08:45
An: martin.berner < at > br...
Cc: bacula-users < at > li...
Betreff: Re: [Bacula-users] migrate catalog from PostgreSQL to MySQL

On Thursday 14 June 2007 08:36:19 Berner Martin wrote:
Yes it is because I have some bottleneck with PostgreSQL as

backend. So I

try to give MySQL a try on a Testenvirement. But it has no

sense if I cant

migrate the catalogue. And to make a true comparison it is

important to

have as much entries in both Databases. Before migrating I

made some Tests

with a empty Mysql-DB. I back up a Directory with 10000

textfiles each

contains only its name. Postgres 1.5 Minutes, Mysql 20-40

seconds. Compared

with the tared directory (one File with same size as all

together both

Databases take about 2 seconds. You talk about Performance

with Postgres.

What can I do to push up the performance with Postgres till

next release

will bring me the "batch insert"?

For now, you can turn fsync to off in postgresql.conf. Be
aware that you may
corrupt the database in case of a system crash.

--------------------------------------------------------------
-----------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Bacula-users mailing list
Bacula-users < at > li...
https://lists.sourceforge.net/lists/listinfo/bacula-users

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Bacula-users mailing list
Bacula-users < at > li...
https://lists.sourceforge.net/lists/listinfo/bacula-users

On Thursday 14 June 2007 10:53:09 Marc Cousin wrote:
There is nothing that will totally solve the performance problems you're
facing... For now, bacula's code does many small operations, each in it's
transaction, and postgresql isn't good at that...
Removing indexes may help the insert speed, but don't have too much hopes

One thing that I've seen with Postgresql in general is that a "VACUUM
ANALYZE" can give a big perfomaceboost on writes/updates. This should be run
before backupjobs begin.

--
Svein-Erik Lund