Migrating from mysql to postgresql
George Kontostanos
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
George Kontostanos
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?
Thanks
Jörg Steffens
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.s...@bareos.com
Bareos GmbH & Co. KG Phone: +49 221 630693-91
http://www.bareos.com Fax: +49 221 630693-10
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
George Kontostanos
--
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...@googlegroups.com.
To post to this group, send email to bareos...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
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 |Thanks