Re: [Bacula-users] Migrating form mysql to postgresql: Loading the database takes very long
Am 26.06.2011 00:18, schrieb Gavin McCullagh: It seems you need to drop --quick which is implied in --skip-opt. The resulting command that I'm working with at the moment is: mysqldump -t -n -c --compatible=postgresql --skip-quote-names --quick \ --lock-tables --add-drop-table --add-locks --create-options --disable-keys \ --extended-insert --set-charset -u bacula -ppassword bacula \ | grep -v INSERT INTO Status\ | sed -e 's/-00-00 00:00:00/1970-01-01 00:00:00/g' \ | sed -e 's/\\0//' bacula-backup.sql` That being said, this is untested so far -- I haven't actually done the migration -- but this is the plan thus far :-) Feedback/corrections welcome... Thanks for this nice line :) The whole process now took less than three hours, whereas before I had to quit it after three days. Not being an SQL expert that saved me a lot of man-reading. Thanks again! Marcus -- All of the data generated in your IT infrastructure is seriously valuable. Why? It contains a definitive record of application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-d2d-c2 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating form mysql to postgresql: Loading the database takes very long
On Sat, 25 Jun 2011, Gavin McCullagh wrote: It seems you need to drop --quick which is implied in --skip-opt. The resulting command that I'm working with at the moment is: mysqldump -t -n -c --compatible=postgresql --skip-quote-names --quick \ --lock-tables --add-drop-table --add-locks --create-options --disable-keys \ --extended-insert --set-charset -u bacula -ppassword bacula \ | grep -v INSERT INTO Status\ | sed -e 's/-00-00 00:00:00/1970-01-01 00:00:00/g' \ | sed -e 's/\\0//' bacula-backup.sql` I should clarify this slightly. I needed to remove --skip-opt which implies --skip-quick and add --quick: This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out. --opt (which is on by default) sets all of: --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset --skip-opt disables --opt, so presumably it negates the above. My command above doesn't go quite that far. Things like table locking shouldn't be necessary if the bacula director is actually stopped at the time. Gavin -- All of the data generated in your IT infrastructure is seriously valuable. Why? It contains a definitive record of application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-d2d-c2 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating form mysql to postgresql: Loading the database takes very long
On Tue, 21 Jun 2011, Marcus Mülbüsch wrote: I want to migrate my bacula installation from mysql to postgresql, following the guide in http://www.bacula.org/5.0.x-manuals/en/main/main/Installing_Configuring_Post.html#SECTION00445 After dumping and converting the database the sql-file now has 9GiB. One note I'd like to make on the page above is that the mysqldump command includes the --skip-opt command. This is not reliable to use if you have a reasonably large File table as it includes an option to buffer query result in RAM before outputting to file. We have 65 million entries and our 6GB RAM + 2GB swap is not enough to hold the buffer, so mysqldump ends up getting killed every time before it finishes. It seems you need to drop --quick which is implied in --skip-opt. The resulting command that I'm working with at the moment is: mysqldump -t -n -c --compatible=postgresql --skip-quote-names --quick \ --lock-tables --add-drop-table --add-locks --create-options --disable-keys \ --extended-insert --set-charset -u bacula -ppassword bacula \ | grep -v INSERT INTO Status\ | sed -e 's/-00-00 00:00:00/1970-01-01 00:00:00/g' \ | sed -e 's/\\0//' bacula-backup.sql` That being said, this is untested so far -- I haven't actually done the migration -- but this is the plan thus far :-) Feedback/corrections welcome... Gavin -- Gavin McCullagh Senior System Administrator IT Services Griffith College South Circular Road Dublin 8 Ireland Tel: +353 1 4163365 http://www.gcd.ie http://www.gcd.ie/brochure.pdf http://www.gcd.ie/opendays http://www.gcd.ie/ebrochure This E-mail is from Griffith College. The E-mail and any files transmitted with it are confidential and may be privileged and are intended solely for the use of the individual or entity to whom they are addressed. If you are not the addressee you are prohibited from disclosing its content, copying it or distributing it otherwise than to the addressee. If you have received this e-mail in error, please immediately notify the sender by replying to this e-mail and delete the e-mail from your computer. Bellerophon Ltd, trades as Griffith College (registered in Ireland No. 60469) with its registered address as Griffith College Campus, South Circular Road, Dublin 8, Ireland. -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense.. http://p.sf.net/sfu/splunk-d2d-c1 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating form mysql to postgresql: Loading the database takes very long
On 06/23/11 01:55, Bruno Friedmann wrote: Did you prepare ( I suppose ) postgresql to manage bacula database. As most of distribution around send postgresql with a very very little setup (like use 32MB ram etc) Adjusting the setup of postgresql can make a huge difference. Rule of thumb: Assume all out-of-the-box DB engine configurations, be it MySQL, PostgreSQL or whatever, are worthless. -- Phil Stracchino, CDK#2 DoD#299792458 ICBM: 43.5607, -71.355 ala...@caerllewys.net ala...@metrocast.net p...@co.ordinate.org Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater It's not the years, it's the mileage. -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense.. http://p.sf.net/sfu/splunk-d2d-c1 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating form mysql to postgresql: Loading the database takes very long
Am 22.06.2011 06:14, schrieb Stuart McGraw: You can identify the indexes from the CREATE INDEX commands in /usr/libexec/bacula/make_postgresql_tables. Drop them prior to loading, and then rerun the CREATE INDEX commands after loading -- it can make a big difference in loading time. I will watch whether psql will be finished today, and if it's not: switch back to mysql for this weekend (which thanks to a holiday and a bridging day will start this evening ;) Next week I'll try your suggestion. Thanks for the instructions. Marcus -- Simplify data backup and recovery for your virtual environment with vRanger. Installation's a snap, and flexible recovery options mean your data is safe, secure and there when you need it. Data protection magic? Nope - It's vRanger. Get your free trial download today. http://p.sf.net/sfu/quest-sfdev2dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating form mysql to postgresql: Loading the database takes very long
On Wed, 22 Jun 2011 10:54:31 +0200, Marcus Mülbüsch muelbue...@as-infodienste.de wrote: Am 22.06.2011 06:14, schrieb Stuart McGraw: You can identify the indexes from the CREATE INDEX commands in /usr/libexec/bacula/make_postgresql_tables. Drop them prior to loading, and then rerun the CREATE INDEX commands after loading -- it can make a big difference in loading time. I will watch whether psql will be finished today, and if it's not: switch back to mysql for this weekend (which thanks to a holiday and a bridging day will start this evening ;) Next week I'll try your suggestion. Thanks for the instructions. Marcus Hello, I don't know how you dumped your mysql database. Did you try to use mysqldump -T ? mysql2pgsql ? I used some other scripts to migrate data pieces. I will look for them this evening... HTH. Jérôme Blion. -- Simplify data backup and recovery for your virtual environment with vRanger. Installation's a snap, and flexible recovery options mean your data is safe, secure and there when you need it. Data protection magic? Nope - It's vRanger. Get your free trial download today. http://p.sf.net/sfu/quest-sfdev2dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating form mysql to postgresql: Loading the database takes very long
Am 22.06.2011 12:05, schrieb Jérôme Blion: I don't know how you dumped your mysql database. Did you try to use mysqldump -T ? mysql2pgsql ? I used some other scripts to migrate data pieces. I will look for them this evening... mysqldump -t -n -c --compatible=postgresql --skip-quote-names --skip-opt --disable-keys --lock-tables -ubacula -p -q bacula | grep -v INSERT INTO Status | sed -e 's/-00-00 00:00:00/1970-01-01 00:00:00/g' | sed -e 's/\\0//' bacula-backup.sql Marcus -- Simplify data backup and recovery for your virtual environment with vRanger. Installation's a snap, and flexible recovery options mean your data is safe, secure and there when you need it. Data protection magic? Nope - It's vRanger. Get your free trial download today. http://p.sf.net/sfu/quest-sfdev2dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating form mysql to postgresql: Loading the database takes very long
On Wed, 22 Jun 2011 12:22:34 +0200, Marcus Mülbüsch muelbue...@as-infodienste.de wrote: Am 22.06.2011 12:05, schrieb Jérôme Blion: I don't know how you dumped your mysql database. Did you try to use mysqldump -T ? mysql2pgsql ? I used some other scripts to migrate data pieces. I will look for them this evening... mysqldump -t -n -c --compatible=postgresql --skip-quote-names --skip-opt --disable-keys --lock-tables -ubacula -p -q bacula | grep -v INSERT INTO Status | sed -e 's/-00-00 00:00:00/1970-01-01 00:00:00/g' | sed -e 's/\\0//' bacula-backup.sql So you should really try mysqldump -T... This is known to be much faster (then use copy from ...) Did you try some tools like http://www.sql-workbench.net/ or http://sqlfairy.sourceforge.net/ ? Cordialement. Jérôme Blion. -- Simplify data backup and recovery for your virtual environment with vRanger. Installation's a snap, and flexible recovery options mean your data is safe, secure and there when you need it. Data protection magic? Nope - It's vRanger. Get your free trial download today. http://p.sf.net/sfu/quest-sfdev2dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating form mysql to postgresql: Loading the database takes very long
On 06/21/2011 09:59 AM, Marcus Mülbüsch wrote: I want to migrate my bacula installation from mysql to postgresql, following the guide in http://www.bacula.org/5.0.x-manuals/en/main/main/Installing_Configuring_Post.html#SECTION00445 After dumping and converting the database the sql-file now has 9GiB. I began loading the db into postgres about 18 hours before, but the process is not yet finished. Is this normal? Or did I miss an important step? And the command to load the database is indeed: psql -Ubacula bacula bacula-backup.sql and not the mangled version given on the linked page? Marcus Did you prepare ( I suppose ) postgresql to manage bacula database. As most of distribution around send postgresql with a very very little setup (like use 32MB ram etc) Adjusting the setup of postgresql can make a huge difference. Here a 4.5Gb sql text dump, need approximatively less than 2 hours to be inserted on blank db. -- Bruno Friedmann Ioda-Net Sàrl www.ioda-net.ch openSUSE Member Ambassador GPG KEY : D5C9B751C4653227 irc: tigerfoot -- Simplify data backup and recovery for your virtual environment with vRanger. Installation's a snap, and flexible recovery options mean your data is safe, secure and there when you need it. Data protection magic? Nope - It's vRanger. Get your free trial download today. http://p.sf.net/sfu/quest-sfdev2dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating form mysql to postgresql: Loading the database takes very long
On Jun 21, 2011, at 3:59 AM, Marcus Mülbüsch wrote: I want to migrate my bacula installation from mysql to postgresql, following the guide in http://www.bacula.org/5.0.x-manuals/en/main/main/Installing_Configuring_Post.html#SECTION00445 After dumping and converting the database the sql-file now has 9GiB. I began loading the db into postgres about 18 hours before, but the process is not yet finished. Is this normal? Or did I miss an important step? And the command to load the database is indeed: psql -Ubacula bacula bacula-backup.sql and not the mangled version given on the linked page? My guess: the output is a series of insert statements, each of which is run in its own transaction. Someone else might be able to help more. -- Dan Langille - http://langille.org -- Simplify data backup and recovery for your virtual environment with vRanger. Installation's a snap, and flexible recovery options mean your data is safe, secure and there when you need it. Data protection magic? Nope - It's vRanger. Get your free trial download today. http://p.sf.net/sfu/quest-sfdev2dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating form mysql to postgresql: Loading the database takes very long
On 06/21/11 22:06, Dan Langille wrote: On Jun 21, 2011, at 3:59 AM, Marcus Mülbüsch wrote: I want to migrate my bacula installation from mysql to postgresql, following the guide in http://www.bacula.org/5.0.x-manuals/en/main/main/Installing_Configuring_Post.html#SECTION00445 After dumping and converting the database the sql-file now has 9GiB. I began loading the db into postgres about 18 hours before, but the process is not yet finished. Is this normal? Or did I miss an important step? And the command to load the database is indeed: psql -Ubacula bacula bacula-backup.sql and not the mangled version given on the linked page? My guess: the output is a series of insert statements, each of which is run in its own transaction. Someone else might be able to help more. It always take longer - usually several times longer - to load any given SQL symbolic dump back into any SQL DB than it took to dump it in the first place. Just for starters, dumping the DB does not alter any indexes, while when reloading it, all of the indexes need to get recreated and rebuilt. -- Phil Stracchino, CDK#2 DoD#299792458 ICBM: 43.5607, -71.355 ala...@caerllewys.net ala...@metrocast.net p...@co.ordinate.org Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater It's not the years, it's the mileage. -- Simplify data backup and recovery for your virtual environment with vRanger. Installation's a snap, and flexible recovery options mean your data is safe, secure and there when you need it. Data protection magic? Nope - It's vRanger. Get your free trial download today. http://p.sf.net/sfu/quest-sfdev2dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating form mysql to postgresql: Loading the database takes very long
On 06/21/2011 08:06 PM, Dan Langille wrote: On Jun 21, 2011, at 3:59 AM, Marcus Mülbüsch wrote: I want to migrate my bacula installation from mysql to postgresql, following the guide in http://www.bacula.org/5.0.x-manuals/en/main/main/Installing_Configuring_Post.html#SECTION00445 After dumping and converting the database the sql-file now has 9GiB. I began loading the db into postgres about 18 hours before, but the process is not yet finished. Is this normal? Or did I miss an important step? And the command to load the database is indeed: psql -Ubacula bacula bacula-backup.sql and not the mangled version given on the linked page? My guess: the output is a series of insert statements, each of which is run in its own transaction. Someone else might be able to help more. The other thing that can be problematic when loading large amounts of data are indexes on the tables. Common practice is to drop indexes (and foreign key constraints but I don't think Bacula uses any) before loading, and recreating them after. You can identify the indexes from the CREATE INDEX commands in /usr/libexec/bacula/make_postgresql_tables. Drop them prior to loading, and then rerun the CREATE INDEX commands after loading -- it can make a big difference in loading time. HTH -- Simplify data backup and recovery for your virtual environment with vRanger. Installation's a snap, and flexible recovery options mean your data is safe, secure and there when you need it. Data protection magic? Nope - It's vRanger. Get your free trial download today. http://p.sf.net/sfu/quest-sfdev2dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
