Re: [Bacula-users] Migrating from mariadb to postgresql
Hello Martin,
thanks, this type of cast has helped with the volume names in the
postgres catalog!
On Wed, Sep 07, 2022 at 04:32:58PM +0100, Martin Simmons wrote:
> This might work but I've not tested it:
>
> cast type tinyblob to text using varbinary-to-string
>
I've extended the CAST to include "blob" types as well, now it looks
like bacula finds previous jobs, volumes etc. in the converted
database using this pgloader ("import_bacula.lisp") file:
###
LOAD DATABASE
FROM mysql://bacula:@localhost/bacula
INTO postgresql://bacula:X@localhost/bacula
CAST type tinyblob to text using varbinary-to-string,
type blob to text using varbinary-to-string,
type binary to char using varbinary-to-string
BEFORE LOAD DO
$$ set schema 'bacula' ; $$ ;
###
and then running the following sequence of commands (I'm using local
copies of the bacula scripts in postgres' HOME for convenience):
echo "drop database bacula; drop role bacula; " | psql
~/create_postgresql_database
~/make_postgresql_tables
~/grant_postgresql_privileges
pgloader import_bacula.lisp
I think switching the schema to "bacula" (which is created by the
scripts) is also critical for bacula to be able to find its stuff in
the postgres catalog.
Some issues remain though:
o auto-purging / recycling doesn't work. I see a message about a disk
volume being purged, but the status in "media" remains "Used" until I
purge the volume in question manually.
o query.sql probably needs to be replaced with a postgres-compatible
version.
Thanks again for your help folks, it's much appreciated. I think we're
one step closer in creating a working pgloader config file to help
folks who want to migrate from mariadb to postgresql :-)
All the best,
Uwe
--
Uwe Schürkamp // email:
___
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
> On Wed, 7 Sep 2022 12:08:10 +0200, Uwe Schuerkamp said: > > On Mon, Sep 05, 2022 at 05:14:28PM +0100, Martin Simmons wrote: > > > On Mon, 5 Sep 2022 11:21:52 +0200, Uwe Schuerkamp said: > > > > > > I've tried casting "blob" and "tinyblob" (the mariadb column types for > > > VolumeName, for example) to "text", but pgloader just hangs when > > > including those cast statements. > > > > What exact cast statement did you use? > > > > Hello Martin, > > I think I used > > cast type tinyblob to text > > for testing purposes, but I don't have the pgloader config file any more, > sorry. > > All the best, This might work but I've not tested it: cast type tinyblob to text using varbinary-to-string __Martin ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
On 2022-09-07 5:27 AM, Uwe Schuerkamp wrote:
sql_create.c:483 Create DB Media record INSERT INTO Media
(VolumeName,MediaType,MediaTypeId,PoolId,MaxVolBytes,VolCapacityBytes,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,VolStatus,Slot,VolBytes,InChanger,VolReadTime,VolWriteTime,VolType,VolParts,VolCloudParts,LastPartBytes,EndFile,EndBlock,LabelType,StorageId,DeviceId,LocationId,ScratchPoolId,RecyclePoolId,Enabled,ActionOnPurge,CacheRetention)VALUES
('testvolume','File_zif',0,5,0,0,1,3456000,0,1,0,'Append',0,231,0,0,0,1,0,0,'0',0,0,0,2,0,0,0,0,1,1,0)
failed. ERR=ERROR: duplicate key value violates unique constraint "media_pkey"
DETAIL: Key (mediaid)=(1) already exists.
You'll need to look at the DDL and see how mediaid is defined: hopefully
it's a 'default next value for $some_sequence' (whatever postgres syntax
is for that), then you find the the next available number from media
table. I.e. if the last mediaid in mysql dump is 42, you want
$some_sequence start with 43.
psql> alter sequence $some_sequence restart with 43;
(Or, before the import, you can edit the DDL and change $some_sequence
definition to 'start with 43'.)
Repeat for all sequences in the database. (There is a '\d - something'
command in psql to list all sequences.)
Dima
___
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
On Mon, Sep 05, 2022 at 11:57:43AM -0500, dmitri maziuk wrote:
> Right, I saw the starting '\x' and looked no further. OP will have to figure
> out what encoding that POS is actually dumping into -- keeping in mind that
> it may be the OS messing it up when saving to text file -- and then figure
> out the right spell to un$#@! it.
>
Hello Dima et al.,
I don't think pgloader uses any files as intermediate storage but will
connect both to mysql / mariadb and transfer the data between them
(but that's just guessing).
I also tried the other method recommended in this thread, using only
the INSERT statements that mysqldump creates (minus the JobStatus
table which is created & filled by bacula's script) on a postgres
database created by the bacula-provided scripts.
I've gotten as far as successfully importing the resulting mysql dump
file into postgres, the volumenames look ok (no hex strings this time)
but as soon as I start a backup job, bacula complains about not being
able to find an appendable volume.
When I try to label a new volume (again, for testing purposes) I get a
"duplicate key" error from postgres as bacula tries to insert the data
into the media table:
$ echo "drop database bacula;" | psql
$ ./create_postgresql_database
$ ./make_postgresql_tables
$ ./grant_postgresql_privileges
... snip snip
GRANT
GRANT
GRANT
GRANT
Privileges for user bacula granted on database bacula.
Then I create the dump, mangling it in a way so that postgres imports
it without errors (I don't care about the mangled \' filenames for now
that are in the File table):
$ (mysqldump -u bacula -p -nc --compatible=postgresql
--default-character-set=latin1 bacula | grep INSERT | grep -v JobStatus | sed
's/"//ig' | sed "s/\\\'//ig") | psql bacula
... snip snip
INSERT 0 14026
INSERT 0 14000
INSERT 0 13746
INSERT 0 13778
INSERT 0 13073
INSERT 0 13817
INSERT 0 6915
INSERT 0 6
INSERT 0 3
INSERT 0 1
$
I can then start up bacula-postgres with the resulting catalog just fine:
# /server/bacula/etc/bacula stop ; /server/bacula-13.0.1_postgres/etc/bacula
start
a "stat dir" looks good w/r to volume names:
# echo stat dir | bconsole
1000 OK: 10002 zif-dir Version: 13.0.1 (05 August 2022)
Enter a period to cancel a command.
stat dir
Scheduled Jobs:
Level Type Pri Scheduled Job Name Volume
===
IncrementalBackup10 08-Sep-22 10:45zifzif-incr-0024
Starting a job I receive the "no appendable volumes found" message,
then trying to a label a new volume (for testing purposes) I get this
message:
*label volume="testvolume" pool=Online_zif_incr storage=FileStorage_zif_incr
Automatically selected Catalog: MyCatalog
Using Catalog "MyCatalog"
Sending label command for Volume "testvolume" Slot 0 ...
3000 OK label. VolBytes=231 VolABytes=0 VolType=1 Volume="testvolume"
Device="FileStorage_zif_incr" (/backup/online_backup_zif/)
sql_create.c:483 Create DB Media record INSERT INTO Media
(VolumeName,MediaType,MediaTypeId,PoolId,MaxVolBytes,VolCapacityBytes,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,VolStatus,Slot,VolBytes,InChanger,VolReadTime,VolWriteTime,VolType,VolParts,VolCloudParts,LastPartBytes,EndFile,EndBlock,LabelType,StorageId,DeviceId,LocationId,ScratchPoolId,RecyclePoolId,Enabled,ActionOnPurge,CacheRetention)VALUES
('testvolume','File_zif',0,5,0,0,1,3456000,0,1,0,'Append',0,231,0,0,0,1,0,0,'0',0,0,0,2,0,0,0,0,1,1,0)
failed. ERR=ERROR: duplicate key value violates unique constraint "media_pkey"
DETAIL: Key (mediaid)=(1) already exists.
Sorry for the longish post & lines, I just wanted to ensure I include
all the info.
All the best,
Uwe
___
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
On Mon, Sep 05, 2022 at 05:14:28PM +0100, Martin Simmons wrote: > > On Mon, 5 Sep 2022 11:21:52 +0200, Uwe Schuerkamp said: > > > > I've tried casting "blob" and "tinyblob" (the mariadb column types for > > VolumeName, for example) to "text", but pgloader just hangs when > > including those cast statements. > > What exact cast statement did you use? > Hello Martin, I think I used cast type tinyblob to text for testing purposes, but I don't have the pgloader config file any more, sorry. All the best, Uwe ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
On 2022-09-05 11:14 AM, Martin Simmons wrote: ... I think you need convert_from(), not encode(). Something like select convert_from(VolumeName::bytea, 'SQL_ASCII') from media; Right, I saw the starting '\x' and looked no further. OP will have to figure out what encoding that POS is actually dumping into -- keeping in mind that it may be the OS messing it up when saving to text file -- and then figure out the right spell to un$#@! it. Dima ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
On 2022-09-05 4:21 AM, Uwe Schuerkamp wrote: bacula=# select encode(VolumeName, 'hex') from media; encode 7a69662d66756c6c2d30303031 7a69662d696e63722d30303032 7a69662d696e63722d30303033 ... Is the "\xss850938sdkl" you posted before an actual value from your table? -- It's not hex even though it starts with \x. You should be able to mysqldump just that one table with some combination of --compatible and maybe --hex-blob, in a decipherable form. Then you could load it in psql (after truncating the existing table). FWIW I use python to move databases. Character encodings is one of the problems that solves. Creating/checking the table/column mappings between source and destination is a lot of boring work but IME the alternatives take even longer and don't work half the time -- as you're finding out. Dima ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
> On Mon, 5 Sep 2022 11:21:52 +0200, Uwe Schuerkamp said: > > I've tried casting "blob" and "tinyblob" (the mariadb column types for > VolumeName, for example) to "text", but pgloader just hangs when > including those cast statements. What exact cast statement did you use? > > Also, Dima wrote earlier: > > > Because it's a hex text, presumably ;) > > > This is likely happening when pulling the data out of mysql, not when > > displaying it in bconsole. > > > You could try `select encode(Volume, 'hex') from` whatever table it's in, in > > psql. If that looks OK: `update $table set Volume=encode(Volume, 'hex')` > > would > > be quick fix. Maybe add a guard along the lines of "where Volume like '\x%'" > > or something. > > When doing the "select" outlined above I simply end up with the same > volume names minus the leading "\x": > > bacula=# select encode(VolumeName, 'hex') from media; >encode > > 7a69662d66756c6c2d30303031 > 7a69662d696e63722d30303032 > 7a69662d696e63722d30303033 > ... I think you need convert_from(), not encode(). Something like select convert_from(VolumeName::bytea, 'SQL_ASCII') from media; __Martin ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
Hello Uwe Could you send me the dumps of MySQL that script had generated? Best regards *Wanderlei Hüttel* Em seg., 5 de set. de 2022 às 06:21, Uwe Schuerkamp < uwe.schuerk...@nionex.net> escreveu: > Hi folks, > > I've now tried to migrate my mariadb bacula db to postgres using > Wanderlei's scripts linked below. Sadly I end up with the same "hex > value" volume names in the media table. :.( It looks like newer > mariadb / mysql catalogs might require some extra steps to enable a > successful migration to postgresql. > > I've tried casting "blob" and "tinyblob" (the mariadb column types for > VolumeName, for example) to "text", but pgloader just hangs when > including those cast statements. > > Also, Dima wrote earlier: > > > Because it's a hex text, presumably ;) > > > This is likely happening when pulling the data out of mysql, not when > > displaying it in bconsole. > > > You could try `select encode(Volume, 'hex') from` whatever table it's > in, in > > psql. If that looks OK: `update $table set Volume=encode(Volume, 'hex')` > would > > be quick fix. Maybe add a guard along the lines of "where Volume like > '\x%'" > > or something. > > When doing the "select" outlined above I simply end up with the same > volume names minus the leading "\x": > > bacula=# select encode(VolumeName, 'hex') from media; >encode > > 7a69662d66756c6c2d30303031 > 7a69662d696e63722d30303032 > 7a69662d696e63722d30303033 > ... > > > All the best, Uwe > > > > > On Tue, Aug 30, 2022 at 08:12:53PM -0300, Wanderlei Huttel wrote: > >Hello Uwe > >I've made a script to migrate MySQL/MariaDB to PostgreSQL > >[1] > https://github.com/wanderleihuttel/bacula-utils/tree/master/convert_mysql_to_postgresql > >I've found errors only in the Log table. > >I've looked for chars with wrong encoding and make an update in > MySQL, did > >a dump and import again for PostgreSQL only this table > >Best regards > > -- > Uwe Schürkamp // email: > > > > ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
Hi folks, I've now tried to migrate my mariadb bacula db to postgres using Wanderlei's scripts linked below. Sadly I end up with the same "hex value" volume names in the media table. :.( It looks like newer mariadb / mysql catalogs might require some extra steps to enable a successful migration to postgresql. I've tried casting "blob" and "tinyblob" (the mariadb column types for VolumeName, for example) to "text", but pgloader just hangs when including those cast statements. Also, Dima wrote earlier: > Because it's a hex text, presumably ;) > This is likely happening when pulling the data out of mysql, not when > displaying it in bconsole. > You could try `select encode(Volume, 'hex') from` whatever table it's in, in > psql. If that looks OK: `update $table set Volume=encode(Volume, 'hex')` would > be quick fix. Maybe add a guard along the lines of "where Volume like '\x%'" > or something. When doing the "select" outlined above I simply end up with the same volume names minus the leading "\x": bacula=# select encode(VolumeName, 'hex') from media; encode 7a69662d66756c6c2d30303031 7a69662d696e63722d30303032 7a69662d696e63722d30303033 ... All the best, Uwe On Tue, Aug 30, 2022 at 08:12:53PM -0300, Wanderlei Huttel wrote: >Hello Uwe >I've made a script to migrate MySQL/MariaDB to PostgreSQL > > [1]https://github.com/wanderleihuttel/bacula-utils/tree/master/convert_mysql_to_postgresql >I've found errors only in the Log table. >I've looked for chars with wrong encoding and make an update in MySQL, did >a dump and import again for PostgreSQL only this table >Best regards -- Uwe Schürkamp // email: ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
Thanks Radosław! Best regards *Wanderlei Hüttel* Em sex., 2 de set. de 2022 às 06:40, Radosław Korzeniewski < rados...@korzeniewski.net> escreveu: > Hi, > > śr., 31 sie 2022 o 01:14 Wanderlei Huttel > napisał(a): > >> Hello Uwe >> >> I've made a script to migrate MySQL/MariaDB to PostgreSQL >> >> https://github.com/wanderleihuttel/bacula-utils/tree/master/convert_mysql_to_postgresql >> >> I've found errors only in the Log table. >> I've looked for chars with wrong encoding and make an update in MySQL, >> did a dump and import again for PostgreSQL only this table >> Best regards >> >> *Wanderlei Hüttel* >> >> > Confirm! Did some migration that way and customers are very happy! > Really useful. Handles not so obvious quirks during the process! > > Thanks a lot for this script! > -- > Radosław Korzeniewski > rados...@korzeniewski.net > ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
Hi, śr., 31 sie 2022 o 01:14 Wanderlei Huttel napisał(a): > Hello Uwe > > I've made a script to migrate MySQL/MariaDB to PostgreSQL > > https://github.com/wanderleihuttel/bacula-utils/tree/master/convert_mysql_to_postgresql > > I've found errors only in the Log table. > I've looked for chars with wrong encoding and make an update in MySQL, did > a dump and import again for PostgreSQL only this table > Best regards > > *Wanderlei Hüttel* > > Confirm! Did some migration that way and customers are very happy! Really useful. Handles not so obvious quirks during the process! Thanks a lot for this script! -- Radosław Korzeniewski rados...@korzeniewski.net ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
On 2022-09-01 7:57 AM, Uwe Schuerkamp wrote: ... So I'm wondering why "text" would end up displayed as "hex" in bconsole? Because it's a hex text, presumably ;) This is likely happening when pulling the data out of mysql, not when displaying it in bconsole. You could try `select encode(Volume, 'hex') from` whatever table it's in, in psql. If that looks OK: `update $table set Volume=encode(Volume, 'hex')` would be quick fix. Maybe add a guard along the lines of "where Volume like '\x%'" or something. Dima ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
Yes, that's what I meant.
With the default casts, pgloader will convert those fields to binary and use
hex-encoded strings in the SQL
(https://www.postgresql.org/docs/current/datatype-binary.html#id-1.5.7.12.9).
Since the actual PostgreSQL field type is text, the encoded strings are then
inserted directly into the database as seen in the output of list jobs etc.
__Martin
> On Thu, 1 Sep 2022 10:24:30 -0300, Wanderlei Huttel said:
>
> Hello Uwe
>
> I found a document on the internet about pgloader.
> Maybe it would be necessary to make a cast of the fields.
>
> https://pgloader.readthedocs.io/en/latest/ref/mysql.html#mysql-database-casting-rules
>
>
> Best regards
>
> *Wanderlei Hüttel*
>
>
>
> Em qui., 1 de set. de 2022 às 10:00, Uwe Schuerkamp <
> uwe.schuerk...@nionex.net> escreveu:
>
> > On Thu, Sep 01, 2022 at 01:01:31PM +0100, Martin Simmons wrote:
> >
> > > The volume name above is "zif-incr-0019" if you decode the hex, so it
> > looks
> > > like you need to add some translation from the various BLOB types to
> > text in
> > > the pgloader configuration if that is possible. By default, pgloader
> > converts
> > > the BLOB types to binary.
> > >
> >
> > Hello Martin et al.,
> >
> > I just checked the table definition in postgres (as it's created by
> > bacula's script) and the fields in question are all of type "text" in
> > postgres, not binary, even after pgloader has imported the mysql data.
> >
> > I may well be mis-interpreting psql's output, but this is what I
> > see when I look at the job table for instance:
> >
> > \d+ job
> > Table
> > "public.job"
> > Column |Type | Collation | Nullable |
> > Default | Storage | Stats target | Description
> >
> > -+-+---+--++--+--+-
> > jobid | integer | | not null |
> > nextval('job_jobid_seq'::regclass) | plain| |
> > job | text| | not null |
> > | extended | |
> > name| text| | not null |
> > | extended | |
> >
> >
> > So I'm wondering why "text" would end up displayed as "hex" in bconsole?
> >
> > Thanks again for your help (and your patience with a postgres noob :-)),
> >
> > Uwe
> >
> >
> > --
> > Uwe Schürkamp // email:
> >
> >
> >
> >
> >
> > ___
> > Bacula-users mailing list
> > Bacula-users@lists.sourceforge.net
> > https://lists.sourceforge.net/lists/listinfo/bacula-users
> >
>
___
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
Hello Uwe
I found a document on the internet about pgloader.
Maybe it would be necessary to make a cast of the fields.
https://pgloader.readthedocs.io/en/latest/ref/mysql.html#mysql-database-casting-rules
Best regards
*Wanderlei Hüttel*
Em qui., 1 de set. de 2022 às 10:00, Uwe Schuerkamp <
uwe.schuerk...@nionex.net> escreveu:
> On Thu, Sep 01, 2022 at 01:01:31PM +0100, Martin Simmons wrote:
>
> > The volume name above is "zif-incr-0019" if you decode the hex, so it
> looks
> > like you need to add some translation from the various BLOB types to
> text in
> > the pgloader configuration if that is possible. By default, pgloader
> converts
> > the BLOB types to binary.
> >
>
> Hello Martin et al.,
>
> I just checked the table definition in postgres (as it's created by
> bacula's script) and the fields in question are all of type "text" in
> postgres, not binary, even after pgloader has imported the mysql data.
>
> I may well be mis-interpreting psql's output, but this is what I
> see when I look at the job table for instance:
>
> \d+ job
> Table
> "public.job"
> Column |Type | Collation | Nullable |
> Default | Storage | Stats target | Description
>
> -+-+---+--++--+--+-
> jobid | integer | | not null |
> nextval('job_jobid_seq'::regclass) | plain| |
> job | text| | not null |
> | extended | |
> name| text| | not null |
> | extended | |
>
>
> So I'm wondering why "text" would end up displayed as "hex" in bconsole?
>
> Thanks again for your help (and your patience with a postgres noob :-)),
>
> Uwe
>
>
> --
> Uwe Schürkamp // email:
>
>
>
>
>
> ___
> Bacula-users mailing list
> Bacula-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/bacula-users
>
___
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
On Thu, Sep 01, 2022 at 01:01:31PM +0100, Martin Simmons wrote:
> The volume name above is "zif-incr-0019" if you decode the hex, so it looks
> like you need to add some translation from the various BLOB types to text in
> the pgloader configuration if that is possible. By default, pgloader converts
> the BLOB types to binary.
>
Hello Martin et al.,
I just checked the table definition in postgres (as it's created by
bacula's script) and the fields in question are all of type "text" in
postgres, not binary, even after pgloader has imported the mysql data.
I may well be mis-interpreting psql's output, but this is what I
see when I look at the job table for instance:
\d+ job
Table
"public.job"
Column |Type | Collation | Nullable |
Default | Storage | Stats target | Description
-+-+---+--++--+--+-
jobid | integer | | not null |
nextval('job_jobid_seq'::regclass) | plain| |
job | text| | not null |
| extended | |
name| text| | not null |
| extended | |
So I'm wondering why "text" would end up displayed as "hex" in bconsole?
Thanks again for your help (and your patience with a postgres noob :-)),
Uwe
--
Uwe Schürkamp // email:
___
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
> On Thu, 1 Sep 2022 09:38:08 +0200, Uwe Schuerkamp said: > > I've now used the bacula provided scripts to create the database, > tables and grants and have reloaded the catalog using this gploader > file: > > LOAD DATABASE > FROM mysql://bacula:vampyre2020k@localhost/bacula > INTO postgresql://bacula:vampyre2020k@localhost/bacula ; > > > I didn't see any errors during the import and postgres-bacula (hehe > :-)) starts up fine. When I issue a "stat dir" in bconsole I'm seeing > the mangled volume names again though: > > cheduled Jobs: > Level Type Pri Scheduled Job Name Volume > === > IncrementalBackup10 01-Sep-22 10:45zif > \x7a69662d696e63722d30303139 > > > The funny thing is that while the "job" name above looks fine, they're > mangled too when I do a "list jobs", same goes for the pool, media and > other objects that can be inspected using the "list" command. The difference in mangling is because the job name in the output above comes from the bacula-dir.conf, but the volume name and and output of list jobs comes from the catalog. The volume name above is "zif-incr-0019" if you decode the hex, so it looks like you need to add some translation from the various BLOB types to text in the pgloader configuration if that is possible. By default, pgloader converts the BLOB types to binary. __Martin ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
Hi folks, On Tue, Aug 30, 2022 at 03:53:47PM +0100, Martin Simmons wrote: > Bacula will probably not work if pgloader created the schema. I think you > should do that part with Bacula's make_postgresql_tables script and configure > pgloader to keep that schema (i.e. the opposite of most of the "WITH" clauses > that you mentioned originally. > I've now used the bacula provided scripts to create the database, tables and grants and have reloaded the catalog using this gploader file: LOAD DATABASE FROM mysql://bacula:vampyre2020k@localhost/bacula INTO postgresql://bacula:vampyre2020k@localhost/bacula ; I didn't see any errors during the import and postgres-bacula (hehe :-)) starts up fine. When I issue a "stat dir" in bconsole I'm seeing the mangled volume names again though: cheduled Jobs: Level Type Pri Scheduled Job Name Volume === IncrementalBackup10 01-Sep-22 10:45zif \x7a69662d696e63722d30303139 The funny thing is that while the "job" name above looks fine, they're mangled too when I do a "list jobs", same goes for the pool, media and other objects that can be inspected using the "list" command. Bacula no longer complains about the non-ASCII database scheme so it seems like the script-created database survived the import by pgload intact. I'll also have a look at Wanderlei's scripts to see if they work fine. I managed to import the db using the "INSERT" filtering outlined above and omitting the JobStatus table, but then bacula was unable to find any appendable volumes when trying to run a test job. All the best, Uwe -- Uwe Schürkamp // email: ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
Hello Uwe I've made a script to migrate MySQL/MariaDB to PostgreSQL https://github.com/wanderleihuttel/bacula-utils/tree/master/convert_mysql_to_postgresql I've found errors only in the Log table. I've looked for chars with wrong encoding and make an update in MySQL, did a dump and import again for PostgreSQL only this table Best regards *Wanderlei Hüttel* Em ter., 30 de ago. de 2022 às 13:44, Martin Simmons escreveu: > > On Tue, 30 Aug 2022 16:04:08 +0200, Uwe Schuerkamp said: > > > > Hello Charles, > > > > thanks for "INSERT only" idea... it's worked partially. After removing > > some double quotes and other characters using sed psql didn't like, > > the import runs for a while and then stops with the following error: > > > > INSERT 0 6 > > ERROR: duplicate key value violates unique constraint "status_pkey" > > DETAIL: Key (jobstatus)=(A) already exists. > > > > > > Any idea what could be causing this? > > That looks like the JobStatus table. Don't migrate that one because the > make_postgresql_tables script inserts all of the rows that are needed. > > __Martin > > > ___ > Bacula-users mailing list > Bacula-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/bacula-users > ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
Hi Uwe, I think Martin was right with this one. You are probably going to have to edit the export file from mysqldump to remove the inserts for tables that the Postgres schema maker creates automatically. To make this easier, so you don't have to recreate the schema each time, you can use transactions. So run "BEGIN WORK;", import the dump file, if something throws an error enter "ROLLBACK WORK;" and edit out those lines, then try again from "BEGIN WORK;" Once it runs without errors do "COMMIT WORK;" to save the import state. On 2022-08-30 13:41, Martin Simmons wrote: On Tue, 30 Aug 2022 16:04:08 +0200, Uwe Schuerkamp said: Hello Charles, thanks for "INSERT only" idea... it's worked partially. After removing some double quotes and other characters using sed psql didn't like, the import runs for a while and then stops with the following error: INSERT 0 6 ERROR: duplicate key value violates unique constraint "status_pkey" DETAIL: Key (jobstatus)=(A) already exists. Any idea what could be causing this? That looks like the JobStatus table. Don't migrate that one because the make_postgresql_tables script inserts all of the rows that are needed. __Martin ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
> On Tue, 30 Aug 2022 16:04:08 +0200, Uwe Schuerkamp said: > > Hello Charles, > > thanks for "INSERT only" idea... it's worked partially. After removing > some double quotes and other characters using sed psql didn't like, > the import runs for a while and then stops with the following error: > > INSERT 0 6 > ERROR: duplicate key value violates unique constraint "status_pkey" > DETAIL: Key (jobstatus)=(A) already exists. > > > Any idea what could be causing this? That looks like the JobStatus table. Don't migrate that one because the make_postgresql_tables script inserts all of the rows that are needed. __Martin ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
On 2022-08-30 9:04 AM, Uwe Schuerkamp wrote: INSERT 0 6 ERROR: duplicate key value violates unique constraint "status_pkey" DETAIL: Key (jobstatus)=(A) already exists. Any idea what could be causing this? Without looking at the actual DDL, I'd say your tables are wrong and primary key should be (jobid,jobstatus). ICBW and all that. Dima ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
> On Mon, 29 Aug 2022 08:28:31 +0200, Uwe Schuerkamp said: > Mail-Followup-To: Uwe Schuerkamp , > > On Thu, Aug 25, 2022 at 06:57:46PM +0100, Martin Simmons wrote: > > Do you have non-ASCII characters in your volume, job or client names? If > > not, > > then I don't see why the warning would cause them to look quite funny > > (whatever that means). > > > > __Martin > > Hi folks, > > thanks for your answers. No, I don't have any non-ascii chars in my > client, job or volume names. By "looking funny" I mean that for > instance the disk volume name "zif-full-0001" turns into > \xverlongrowofrandomcharacters. > > Of course I could start with an empty catalog but it'd be nice to > transfer the existing history to the postgres db which I guess would > (or should, even) be possible somehow. In another experiment I > migrated a mariadb django backend to postgresql on the same machine > without any issues, it's just bacula that's acting up in this case. Bacula will probably not work if pgloader created the schema. I think you should do that part with Bacula's make_postgresql_tables script and configure pgloader to keep that schema (i.e. the opposite of most of the "WITH" clauses that you mentioned originally. Many of the fields in Bacula's mysql schema use BLOB types. The doc for pgloader says that blob is converted to the bytea type in postgresql, so I don't know if that will cause translation problems after using make_postgresql_tables. You might need to configure pgloader's type conversion rules. __Martin ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
Hello Charles, thanks for "INSERT only" idea... it's worked partially. After removing some double quotes and other characters using sed psql didn't like, the import runs for a while and then stops with the following error: INSERT 0 6 ERROR: duplicate key value violates unique constraint "status_pkey" DETAIL: Key (jobstatus)=(A) already exists. Any idea what could be causing this? Again thanks for your help! All the best, Uwe -- Uwe Schürkamp // email: ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
Hi Uwe, You might want to try just dumping the data as full inserts and then import it with pgsql. IE: mysqldump -nc bacula |grep INSERT >output.sql psql bacula \. output.sql Another option wqould be to use the --default-character-set option to mysqldump to switch the output to just latin1, ie: mysqldump -nc bacula --default-character-set=latin1 |grep INSERT >output.sql On 2022-08-29 03:43, Uwe Schuerkamp wrote: Hello Eric, thanks much for your reply. On Thu, Aug 25, 2022 at 03:36:09PM +0200, Eric Bollengier via Bacula-users wrote: Bacula might have to store characters coming from the different clients (filename and path mostly), and we have no guarantee that they will be in valid UTF8. The setup I'm trying to convert is as simple as it gets: a single host that backs up itself, no other clients are involved. I've tried using the create_postgres db and table scripts provided by the bacula install before running pgloader to import the data from mariadb, but sadly this hasn't helped. The warning in bconsole disappears, but bacula is still unable to find any volumes from the old catalog as they're displayed as "\xss850938sdkl" or similar as described in my previous email. It might well be that pgloader mangles the db data in some way. Are there other proven ways to import a mariadb database into postgres that folks here might have any experience with? Thanks, Uwe ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
Hello Eric, thanks much for your reply. On Thu, Aug 25, 2022 at 03:36:09PM +0200, Eric Bollengier via Bacula-users wrote: > > Bacula might have to store characters coming from the different clients > (filename and > > path mostly), and we have no guarantee that they will be in valid UTF8. > The setup I'm trying to convert is as simple as it gets: a single host that backs up itself, no other clients are involved. I've tried using the create_postgres db and table scripts provided by the bacula install before running pgloader to import the data from mariadb, but sadly this hasn't helped. The warning in bconsole disappears, but bacula is still unable to find any volumes from the old catalog as they're displayed as "\xss850938sdkl" or similar as described in my previous email. It might well be that pgloader mangles the db data in some way. Are there other proven ways to import a mariadb database into postgres that folks here might have any experience with? Thanks, Uwe -- Uwe Schürkamp // email: ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
On Thu, Aug 25, 2022 at 06:57:46PM +0100, Martin Simmons wrote: > Do you have non-ASCII characters in your volume, job or client names? If not, > then I don't see why the warning would cause them to look quite funny > (whatever that means). > > __Martin Hi folks, thanks for your answers. No, I don't have any non-ascii chars in my client, job or volume names. By "looking funny" I mean that for instance the disk volume name "zif-full-0001" turns into \xverlongrowofrandomcharacters. Of course I could start with an empty catalog but it'd be nice to transfer the existing history to the postgres db which I guess would (or should, even) be possible somehow. In another experiment I migrated a mariadb django backend to postgresql on the same machine without any issues, it's just bacula that's acting up in this case. All the best, Uwe -- Uwe Schürkamp // email: ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
> On Thu, 25 Aug 2022 14:25:30 +0200, Uwe Schuerkamp said: > > I've imported the bacula catalog using pgloader and this config: > > LOAD DATABASE > FROM mysql://bacula:X@localhost/bacula > INTO postgresql://bacula:X@localhost/bacula > > WITH include drop, create tables, no truncate, > create indexes, reset sequences, foreign keys; > > > The import works fine, however when connecting to bacula 13.0.1 (compiled > from source for postgres use) I get the following message in bconsole: > > 25-Aug 14:08 zif-dir JobId 0: Warning: Encoding error for database > "bacula". Wanted SQL_ASCII, got UTF8 > > Most web searches are ancient so I'm wondering if the ASCII bit is > still a valid requirement? In bconsole, the volume, job and client > names all look quite funny when doing a stat dir and the volume isn't > found when I run a backup job (probably due to this encoding issue). Do you have non-ASCII characters in your volume, job or client names? If not, then I don't see why the warning would cause them to look quite funny (whatever that means). __Martin ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from mariadb to postgresql
Hello Uwe, On 25.08.22 14:25, Uwe Schuerkamp wrote: Hi folks, I'm trying (for educational purposes) to migrate an existing bacula catalog to use with a postgres backend (mariadb10 / postgresql12, Ubuntu 20.04). I've imported the bacula catalog using pgloader and this config: LOAD DATABASE FROM mysql://bacula:X@localhost/bacula INTO postgresql://bacula:X@localhost/bacula WITH include drop, create tables, no truncate, create indexes, reset sequences, foreign keys; The import works fine, however when connecting to bacula 13.0.1 (compiled from source for postgres use) I get the following message in bconsole: 25-Aug 14:08 zif-dir JobId 0: Warning: Encoding error for database "bacula". Wanted SQL_ASCII, got UTF8 The database has to be created with the -E SQL_ASCII, it is done before you load the catalog I believe. Make sure you use the create_bacula_database script. Bacula might have to store characters coming from the different clients (filename and path mostly), and we have no guarantee that they will be in valid UTF8. If you try to store only UTF8 filenames, it will be ok, but if one of your clients is in ISO8859-15 for example, it will fail the attribute insertion. So, the DB has to be created with this attribute. An other way of solving the issue would have been to escape anything coming from the client to valid UTF8, but instead, we have changed the postgresql parameter. Hope it helps! Best Regards, Eric Most web searches are ancient so I'm wondering if the ASCII bit is still a valid requirement? In bconsole, the volume, job and client names all look quite funny when doing a stat dir and the volume isn't found when I run a backup job (probably due to this encoding issue). I've also checked the configure script for any options related to the encoding, but could not find anything, also I've used the provided create_postgres_database script to (hopefully) initialize the catalog db in postgres with the correct encoding. Is there anything I'm doing wrong? Please excuse my ignorance, as I'm only now getting my feet wet with postgres especially w/r to the mariadb import... Thanks in advance & all the best, Uwe ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
Great. Thanks again Martin. -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
On Mon, 19 Nov 2018 17:29:21 GMT Martin Simmons wrote: > rpm -qf ...path..to..libpq.so... # rpm -qf /usr/lib64/libpq.so postgresql10-devel-10.5-lp150.3.3.1.x86_64 How do I use this info? -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
> On Mon, 19 Nov 2018 19:04:06 +0200, George Anchev via Bacula-users said: > > Thanks for clarifying. My only concern is to be able > to backup and restore correctly files with non-ASCII > names (e.g. UTF-8 or anything that the file systems > support). Yes, that's exactly what using SQL_ASCII is designed to achieve! __Martin ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
> On Mon, 19 Nov 2018 19:00:55 +0200, George Anchev via Bacula-users said: > > Same empty output. The libpq files must be in some other package then. If you have found the .so file then you can check what owns it by rpm -qf ...path..to..libpq.so... __Martin ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
On Mon, 19 Nov 2018 11:32:24 GMT Martin Simmons wrote: > SQL_ASCII [...] Thanks for clarifying. My only concern is to be able to backup and restore correctly files with non-ASCII names (e.g. UTF-8 or anything that the file systems support). -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
On Mon, 19 Nov 2018 10:40:42 GMT Martin Simmons wrote: > Sorry, I meant -ql rather than -qi. Same empty output. -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
> On Mon, 19 Nov 2018 01:24:18 +0200, George Anchev via Bacula-users said: > > On Fri, 16 Nov 2018 12:50:12 +0200 George Anchev wrote: > > > https://www.bacula.org/9.2.x-manuals/en/main/Installing_Configuring_Post.html > > Can anyone please explain why SQL_ASCII is strongly > recommended? Won't that be a problem for unicode file > names? The problem is that many commonly used filesystems do not have "unicode file names" -- they allow an arbitrary sequence of 8-bit bytes. Some programs interpret these bytes as UTF-8 encoded unicode (or something else) according to the locale, but Bacula passes them to the database as-is. If you specify something like UTF8 in PostgreSQL, it will signal an error if you back up a file whose name is not a valid sequence of bytes for UTF-8 (e.g. the single byte 0xA1). SQL_ASCII actually means "any sequence of 8-bit bytes" in PostgreSQL, so you will not get an error from any file names, which is probably what you want for a backup program. __Martin ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
> On Sat, 17 Nov 2018 00:25:17 +0200, George Anchev via Bacula-users said: > > On Fri, 16 Nov 2018 18:15:43 GMT Martin Simmons wrote: > > > rpm -qi postgresql | grep libpq > > That shows empty output. Sorry, I meant -ql rather than -qi. __Martin ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
Hello George. I don't know why, but I guess is the default charset of Bacula with PostgreSQL. I had troubles with "Log" table when had Windows Jobs with errors. But how I only had a few errors, I updated some informations in the MySQL Catalog manually (using update), and generate this single table again Best regards *Wanderlei Hüttel* Em dom, 18 de nov de 2018 às 21:26, George Anchev via Bacula-users < bacula-users@lists.sourceforge.net> escreveu: > On Fri, 16 Nov 2018 12:50:12 +0200 George Anchev wrote: > > > > https://www.bacula.org/9.2.x-manuals/en/main/Installing_Configuring_Post.html > > Can anyone please explain why SQL_ASCII is strongly > recommended? Won't that be a problem for unicode file > names? > > -- > George > > > ___ > Bacula-users mailing list > Bacula-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/bacula-users > ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
On Fri, 16 Nov 2018 12:50:12 +0200 George Anchev wrote: > https://www.bacula.org/9.2.x-manuals/en/main/Installing_Configuring_Post.html Can anyone please explain why SQL_ASCII is strongly recommended? Won't that be a problem for unicode file names? -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
On Sat, 17 Nov 2018 11:39:46 -0600 Dmitri Maziuk via Bacula-users wrote: > As per the fine manual: create bacula role, create > bacula tables, etc. OK, I have done that. The problem is that the manual seems not to be so fine as the process described further there results in errors which make it impossible to proceed, so I filed in a bug report about it. Fortunately Wanderlei's scripts seem to work quite well (still testing the result). -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
On 11/17/2018 10:56 AM, George Anchev via Bacula-users wrote: # systemctl status postgresql starts the service. In the 'status' details I see: /usr/lib/postgresql10/bin/postgres -D /var/lib/pgsql/data So from here: How should I proceed with Bacula? As per the fine manual: create bacula role, create bacula tables, etc. Dimitri ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
On Sat, 17 Nov 2018 18:56:53 +0200 George Anchev wrote: > # systemctl status postgresql > > starts the service. Correction: I mean "start" (not "status") -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
# systemctl status postgresql starts the service. In the 'status' details I see: /usr/lib/postgresql10/bin/postgres -D /var/lib/pgsql/data So from here: How should I proceed with Bacula? -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
On Sat, 17 Nov 2018 08:55:27 -0600 Dmitri Maziuk via Bacula-users wrote: > It may be that you need to run 'pg_ctl initdb' to > get it all created. $ pg_ctl initdb pg_ctl: no database directory specified and environment variable PGDATA unset Try "pg_ctl --help" for more information. -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
On 11/17/2018 5:37 AM, George Anchev via Bacula-users wrote: On Fri, 16 Nov 2018 17:46:13 -0600 Dimitri Maziuk via Bacula-users wrote: Well, that's b0rk3d. What can be done about it? You'd have to ask suse. It may be that you need to run 'pg_ctl initdb' to get it all created. Dimitri ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
On Fri, 16 Nov 2018 17:46:13 -0600 Dimitri Maziuk via Bacula-users wrote: > Well, that's b0rk3d. What can be done about it? > This is probably it. Check > ~postgres/data/pg_hba.conf as per previous message: > specifically, if auth method is "ident", it won't > work without identd server. "peer" should work and > "trust" will work. Where is ~postgres directory? FWIW such file doesn't exist anywhere, checked using: # find / -name pg_hba.conf -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
On 11/16/2018 05:06 PM, George Anchev via Bacula-users wrote: >> -bash-4.2$ echo $PGDATA >> /var/lib/pgsql/data > > I get the same echo output but: > > # ls /var/lib/pgsql/data > ls: cannot access '/var/lib/pgsql/data': No such file or directory Well, that's b0rk3d. > POSTGRES_DATADIR="~postgres/data" This is probably it. Check ~postgres/data/pg_hba.conf as per previous message: specifically, if auth method is "ident", it won't work without identd server. "peer" should work and "trust" will work. -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu signature.asc Description: OpenPGP digital signature ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
On Fri, 16 Nov 2018 16:41:40 -0600 Dimitri Maziuk via Bacula-users wrote: > # su - postgres > Last login: Wed Jan 24 15:59:20 CST 2018 on pts/1 > -bash-4.2$ echo $PGDATA > /var/lib/pgsql/data I get the same echo output but: # ls /var/lib/pgsql/data ls: cannot access '/var/lib/pgsql/data': No such file or directory Also: # find /etc -name *postgres* /etc/alternatives/postgresql /etc/alternatives/postgres /etc/sysconfig/postgresql /etc/sysconfig/SuSEfirewall2.d/services/postgresql The last 2 are text files. /etc/sysconfig/postgresql looks like some config (eliminating comments and empty lines): # grep -vE "^#" /etc/sysconfig/postgresql | grep -E ".+" POSTGRES_DATADIR="~postgres/data" POSTGRES_OPTIONS="" POSTGRES_TIMEOUT="600" POSTGRES_LANG="" What else do I need? -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
On 11/16/2018 04:25 PM, George Anchev via Bacula-users wrote: > I still wonder though why there is nothing postgresql > related in /etc... Need I install any other packages > or how should this be set up? # su - postgres Last login: Wed Jan 24 15:59:20 CST 2018 on pts/1 -bash-4.2$ echo $PGDATA /var/lib/pgsql/data This is on centos, suse may have put it someplace else. .conf files are in there, though you may not even need to touch them for a basic bacula setup. You may need to edit pg_hba.conf and set method to "trust" for local connections, that's about it. -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu signature.asc Description: OpenPGP digital signature ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
On Fri, 16 Nov 2018 18:15:43 GMT Martin Simmons wrote: > rpm -qi postgresql | grep libpq That shows empty output. > Also, you may only have .so files installed, in > which case try: > > objdump -T ...path..to..libpq.so... | grep > pthread_mutex_lock > > and look for something like: > > DF *UND* > GLIBC_2.2.5 pthread_mutex_lock This one works exactly as you described. Thanks! I still wonder though why there is nothing postgresql related in /etc... Need I install any other packages or how should this be set up? -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
On 11/16/2018 12:15 PM, Martin Simmons wrote: >> On Fri, 16 Nov 2018 12:50:12 +0200, George Anchev via Bacula-users said: >> nm: '/usr/lib/libpq.a': No such file > Also, you may only have .so files installed, That is the most likely cause, many distros don't even ship .a's in -devel packages anymore as nobody can figure out how to build static binaries with gunk libtool anyway... They definitely wouldn't be in the regular -libs packages. ... in which case try> objdump -T ...path..to..libpq.so... | grep pthread_mutex_lock > > and look for something like: > > DF *UND* GLIBC_2.2.5 > pthread_mutex_lock -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu signature.asc Description: OpenPGP digital signature ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
> On Fri, 16 Nov 2018 12:50:12 +0200, George Anchev via Bacula-users said: > > and I reached the point where I got stuck with: > > # nm /usr/lib/libpq.a | grep pthread_mutex_lock > nm: '/usr/lib/libpq.a': No such file > > Am I missing something and how should I do all this > please? The libraries might be somewhere else. Try this to locate them: rpm -qi postgresql | grep libpq Also, you may only have .so files installed, in which case try: objdump -T ...path..to..libpq.so... | grep pthread_mutex_lock and look for something like: DF *UND* GLIBC_2.2.5 pthread_mutex_lock __Martin ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
On Fri, 16 Nov 2018 16:19:02 +0100 Kern Sibbald wrote: > I don't believe you need postgresql-devel, Will Bacula build '--with-postgresql' without it? > but you should have something like postgresql-client. That's what postgresql is: [~]: rpm -qi postgresql | grep -i summary Summary : Basic Clients and Utilities for PostgreSQL > I am not familiar any more with SuSE, so I am just > guessing at the names. Also on .debs platforms > there is a postgresql-common and > postgresql-client-common, but those two packages are > usually automatically loaded. Here is the full list of all packages which contain the 'postgresql' string in their name: https://susepaste.org/fbb40291 Do you see any which I must install? -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Migrating from MariaDB to PostgreSQL
Hello, I don't believe you need postgresql-devel, but you should have something like postgresql-client. I am not familiar any more with SuSE, so I am just guessing at the names. Also on .debs platforms there is a postgresql-common and postgresql-client-common, but those two packages are usually automatically loaded. Best regards, Kern On 11/16/18 11:50 AM, George Anchev via Bacula-users wrote: Hi, Currently I am using Bacula 9.2.2 (06 November 2018) with MariaDB. In earlier discussions here it was recommended to use PostgreSQL instead and a link was shared about a tool which migrates MySQL to PostgreSQL: https://sourceforge.net/p/bacula/mailman/message/36387001/ I have never used and know nothing about PostgreSQL, so my intention was to: 1. Install necessary PostgreSQL packages 2. Build Bacula using --with-postgresql 3. Learn how to configure and run the PostgreSQL server 4. Use Wanderlei Huttel's script to migrate the catalog 5. Test and upon success switch to PostgreSQL version Following that plan so far I installed packages (using openSUSE Leap 15): postgresql postgresql-devel postgresql-server Strangely I couldn't find anything in /etc which allows configuration. So I started reading: https://www.bacula.org/9.2.x-manuals/en/main/Installing_Configuring_Post.html and I reached the point where I got stuck with: # nm /usr/lib/libpq.a | grep pthread_mutex_lock nm: '/usr/lib/libpq.a': No such file Am I missing something and how should I do all this please? -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
