On Tue, Apr 3, 2018 at 7:35 PM Jörg Steffens <joerg.steff...@bareos.com> wrote:
> On 03.04.2018 at 13:33 wrote George Kontostanos: > > On Tuesday, April 3, 2018 at 12:10:54 PM UTC+3, George Kontostanos wrote: > >> Hi everyone, > >> > >> We are using bareos 16.2.4 with a rather large database. Around 200GB > >> > >> Due to performance issues I have decided to switch to postgresql. I > used pgloader (https://pgloader.io/about/) on a testing environment to > convert the database. The process went fine : > >> > >> table name read imported errors total > time > >> ------------------------- --------- --------- --------- > -------------- > >> fetch meta data 72 72 0 > 0.549s > >> Create Schemas 0 0 0 > 0.002s > >> Create SQL Types 3 3 0 > 0.026s > >> Create tables 60 60 0 > 0.916s > >> Set Table OIDs 30 30 0 > 0.012s > >> ------------------------- --------- --------- --------- > -------------- > >> bareos.basefiles 0 0 0 > 0.083s > >> bareos.client 186 186 0 > 0.211s > >> bareos.counters 0 0 0 > 0.055s > >> bareos.devicestats 0 0 0 > 0.076s > >> bareos.device 0 0 0 > 0.075s > >> bareos.file 754079396 754079396 0 > 9h14m15.621s > >> bareos.filename 330788514 330788514 0 > 2h40m38.519s > >> bareos.job 9499 9499 0 > 10.407s > >> bareos.jobmedia 187142 187142 0 > 9.888s > >> bareos.location 0 0 0 > 3.592s > >> bareos.log 257481 257481 0 > 29.240s > >> bareos.mediatype 1 1 0 > 14.270s > >> bareos.ndmplevelmap 0 0 0 > 13.837s > >> bareos.pathhierarchy 20151647 20151647 0 > 9m19.525s > >> bareos.pool 190 190 0 > 3.336s > >> bareos.restoreobject 195 195 0 > 0.827s > >> bareos.storage 8 8 0 > 0.309s > >> bareos.unsavedfiles 0 0 0 > 0.206s > >> bareos.fileset 10 10 0 > 1.679s > >> bareos.jobhisto 0 0 0 > 0.257s > >> bareos.jobstats 0 0 0 > 1.241s > >> bareos.locationlog 0 0 0 > 1.112s > >> bareos.media 18177 18177 0 > 3.374s > >> bareos.ndmpjobenvironment 0 0 0 > 0.321s > >> bareos.path 24552285 24552285 0 > 23m41.957s > >> bareos.pathvisibility 67970675 67970675 0 > 24m8.095s > >> bareos.quota 0 0 0 > 0.957s > >> bareos.status 26 26 0 > 1.007s > >> bareos.tapealerts 0 0 0 > 2.198s > >> bareos.version 1 1 0 > 2.643s > >> ------------------------- --------- --------- --------- > -------------- > >> COPY Threads Completion 4 4 0 > 10h2m5.254s > >> Create Indexes 42 42 0 > 10h9m47.594s > >> Index Build Completion 42 42 0 > 2h6m12.416s > >> Reset Sequences 18 18 0 > 0.988s > >> Primary Keys 25 25 0 > 0.117s > >> Create Foreign Keys 0 0 0 > 0.000s > >> Create Triggers 0 0 0 > 0.000s > >> Install Comments 0 0 0 > 0.000s > >> ------------------------- --------- --------- --------- > -------------- > >> Total import time 1198015433 1198015433 0 > 12h8m23.566s > >> > >> However, it looks like there are some problems with the converted > database. Running "grant_bareos_privileges" displays the following errors: > >> > >> Warning: failed to get "dbname" from config, using default value > "bareos", see /tmp/bareos-config.21216.log > >> Warning: failed to get "dbuser" from config, using default value > "bareos", see /tmp/bareos-config.21216.log > >> Warning: failed to get "dbpassword" from config, see > /tmp/bareos-config.21216.log > >> Warning: failed to get "dbdriver" from config, see > /tmp/bareos-config.21216.log > >> Granting postgresql tables > >> psql:/tmp/grants.sql.21216:1: ERROR: role "bareos" already exists > >> psql:/tmp/grants.sql.21216:4: ERROR: relation "unsavedfiles" does not > exist > >> psql:/tmp/grants.sql.21216:5: ERROR: relation "basefiles" does not > exist > >> psql:/tmp/grants.sql.21216:6: ERROR: relation "jobmedia" does not exist > >> psql:/tmp/grants.sql.21216:7: ERROR: relation "file" does not exist > >> psql:/tmp/grants.sql.21216:8: ERROR: relation "job" does not exist > >> psql:/tmp/grants.sql.21216:9: ERROR: relation "media" does not exist > >> psql:/tmp/grants.sql.21216:10: ERROR: relation "client" does not exist > >> psql:/tmp/grants.sql.21216:11: ERROR: relation "pool" does not exist > >> psql:/tmp/grants.sql.21216:12: ERROR: relation "fileset" does not exist > >> psql:/tmp/grants.sql.21216:13: ERROR: relation "path" does not exist > >> psql:/tmp/grants.sql.21216:14: ERROR: relation "filename" does not > exist > >> psql:/tmp/grants.sql.21216:15: ERROR: relation "counters" does not > exist > >> psql:/tmp/grants.sql.21216:16: ERROR: relation "version" does not exist > >> psql:/tmp/grants.sql.21216:17: ERROR: relation "mediatype" does not > exist > >> psql:/tmp/grants.sql.21216:18: ERROR: relation "storage" does not exist > >> psql:/tmp/grants.sql.21216:19: ERROR: relation "device" does not exist > >> psql:/tmp/grants.sql.21216:20: ERROR: relation "status" does not exist > >> psql:/tmp/grants.sql.21216:21: ERROR: relation "location" does not > exist > >> psql:/tmp/grants.sql.21216:22: ERROR: relation "locationlog" does not > exist > >> psql:/tmp/grants.sql.21216:23: ERROR: relation "log" does not exist > >> psql:/tmp/grants.sql.21216:24: ERROR: relation "jobhisto" does not > exist > >> psql:/tmp/grants.sql.21216:25: ERROR: relation "pathhierarchy" does > not exist > >> psql:/tmp/grants.sql.21216:26: ERROR: relation "pathvisibility" does > not exist > >> psql:/tmp/grants.sql.21216:27: ERROR: relation "restoreobject" does > not exist > >> psql:/tmp/grants.sql.21216:28: ERROR: relation "quota" does not exist > >> psql:/tmp/grants.sql.21216:29: ERROR: relation "ndmplevelmap" does not > exist > >> psql:/tmp/grants.sql.21216:30: ERROR: relation "ndmpjobenvironment" > does not exist > >> psql:/tmp/grants.sql.21216:31: ERROR: relation "devicestats" does not > exist > >> psql:/tmp/grants.sql.21216:32: ERROR: relation "jobstats" does not > exist > >> psql:/tmp/grants.sql.21216:33: ERROR: relation "tapealerts" does not > exist > >> psql:/tmp/grants.sql.21216:36: ERROR: relation > "filename_filenameid_seq" does not exist > >> psql:/tmp/grants.sql.21216:37: ERROR: relation "path_pathid_seq" does > not exist > >> psql:/tmp/grants.sql.21216:38: ERROR: relation "fileset_filesetid_seq" > does not exist > >> psql:/tmp/grants.sql.21216:39: ERROR: relation "pool_poolid_seq" does > not exist > >> psql:/tmp/grants.sql.21216:40: ERROR: relation "client_clientid_seq" > does not exist > >> psql:/tmp/grants.sql.21216:41: ERROR: relation "media_mediaid_seq" > does not exist > >> psql:/tmp/grants.sql.21216:42: ERROR: relation "job_jobid_seq" does > not exist > >> psql:/tmp/grants.sql.21216:43: ERROR: relation "file_fileid_seq" does > not exist > >> psql:/tmp/grants.sql.21216:44: ERROR: relation > "jobmedia_jobmediaid_seq" does not exist > >> psql:/tmp/grants.sql.21216:45: ERROR: relation "basefiles_baseid_seq" > does not exist > >> psql:/tmp/grants.sql.21216:46: ERROR: relation "storage_storageid_seq" > does not exist > >> psql:/tmp/grants.sql.21216:47: ERROR: relation > "mediatype_mediatypeid_seq" does not exist > >> psql:/tmp/grants.sql.21216:48: ERROR: relation "device_deviceid_seq" > does not exist > >> psql:/tmp/grants.sql.21216:49: ERROR: relation > "location_locationid_seq" does not exist > >> psql:/tmp/grants.sql.21216:50: ERROR: relation > "locationlog_loclogid_seq" does not exist > >> psql:/tmp/grants.sql.21216:51: ERROR: relation "log_logid_seq" does > not exist > >> psql:/tmp/grants.sql.21216:52: ERROR: relation > "restoreobject_restoreobjectid_seq" does not exist > >> Privileges for user bareos granted ON database bareos. > >> > >> Is there a way to actually convert a mysql database or this is > something not possible? > >> > >> Thanks > >> > >> George > > > > It looks like that had something to do with permissions which is now > fixed. > > > > However, I now have another problem, from bconsole i get those errors : > > > > bareos-dir JobId 0: Warning: Encoding error for database "bareos". > Wanted SQL_ASCII, got UTF8 > > > > And in the webUI I see funny characters. > > > > Any ideas? > > Sure, a lot of. To be compatibly with older Bacula databases, we had to > stick to some old settings. Normally databases are created with > > CREATE DATABASE ${db_name} ENCODING 'SQL_ASCII' LC_COLLATE 'C' LC_CTYPE > 'C' TEMPLATE template0; > > see > > https://github.com/bareos/bareos/blob/master/src/cats/create_bareos_database.in > > Also there is a MySQL and Postgresql differ how they handle upper and > lower case table names, at least when not quotated. And Bareos do not > use quoted table names. > > The *_seq tables are for the sequence numbers. I guess, these are > required, so the new entries can be created with the correct ids. > > My colleague is working on a mysql to postgres migration for a support > customer. However, I'm not aware about the current status. > > > regards, > Jörg > > -- > Jörg Steffens joerg.steff...@bareos.com > Bareos GmbH & Co. KG Phone: +49 221 630693-91 > <+49%20221%2063069391> > http://www.bareos.com Fax: +49 221 630693-10 > <+49%20221%2063069310> > > Sitz der Gesellschaft: Köln | Amtsgericht Köln: HRA 29646 > Komplementär: Bareos Verwaltungs-GmbH > Geschäftsführer: > S. Dühr, M. Außendorf, Jörg Steffens, P. Storz > > -- > You received this message because you are subscribed to a topic in the > Google Groups "bareos-users" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/bareos-users/iCLXvX5jWv8/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > bareos-users+unsubscr...@googlegroups.com. > To post to this group, send email to bareos-users@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > I had missed that part obviously! I did repeat the procedure with the correct encoding this time and transferred all data again. However, although the director does not give me any encoding errors, it looks like there is something still very wrong: Automatically selected FileSet: \x4c696e7578416c6c +--------+-------+------------+--------------+------------------------+----------------------------------------------------------+ | jobid | level | jobfiles | jobbytes | starttime | volumename | +--------+-------+------------+--------------+------------------------+----------------------------------------------------------+ | 27,331 | \x46 | 11,975,721 | 465650709849 | 2018-03-20 06:00:03-07 | \x7070672d736572766572322d66642d706f6f6c30312d30313832 | | 27,331 | \x46 | 11,975,721 | 465650709849 | 2018-03-20 06:00:03-07 | \x7070672d736572766572322d66642d706f6f6c30312d30313935 | | 27,331 | \x46 | 11,975,721 | 465650709849 | 2018-03-20 06:00:03-07 | \x7070672d736572766572322d66642d706f6f6c30312d30313936 | | 27,331 | \x46 | 11,975,721 | 465650709849 | 2018-03-20 06:00:03-07 | \x7070672d736572766572322d66642d706f6f6c30312d30323335 | | 27,331 | \x46 | 11,975,721 | 465650709849 | 2018-03-20 06:00:03-07 | \x7070672d736572766572322d66642d706f6f6c30312d30323336 | | 27,331 | \x46 | 11,975,721 | 465650709849 | 2018-03-20 06:00:03-07 | \x7070672d736572766572322d66642d706f6f6c30312d30323739 | | 27,331 | \x46 | 11,975,721 | 465650709849 | 2018-03-20 06:00:03-07 | \x7070672d736572766572322d66642d706f6f6c30312d30323830 | Any ideas? Thanks -- George Kontostanos NetActuate, Inc e: gkontosta...@netactuate.com p: +1 919 727 9999 (ext 3021) -- You received this message because you are subscribed to the Google Groups "bareos-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to bareos-users+unsubscr...@googlegroups.com. To post to this group, send email to bareos-users@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
