Re: [Bacula-users] Errors migrating from mysql to postgres
Hello,
2017-12-01 20:01 GMT+01:00 Michel Figgins :
> Yes please on the explanation.
>
> When I use mysqldump –hex-blob , the RestoreObject field/column has the
> null byte equivalents (i.e. it starts with 0x3C0042004100 which is “ which I haven’t figured out how to decode.
>
So, use a "decode" function for that. :)
The example how it should looks like is as follow:
insert into restoreobject values (2088,'job_metadata.xml',decode('
3C004200410043004B00550050005F0043004F004D0050004F004E004500
4E0054005300200078006D006C006E0073003D00220078002D0073006300
680065006D0061003A00230056007300730043006F006D0070006F006E00
65006E0074004D0065007400610064006100740061002200200076006500
7200730069006F006E003D00220031002E0032002200200062006F006F00
7400610062006C006500530079007300740065006D005300740061007400
65004200610063006B00750070003D002200790065007300220020007300
65006C0065006300740043006F006D0070006F006E0065006E0074007300
3D002200790065007300220020006200610063006B007500700054007900
700065003D00220069006E006300720065006D0065006E00740061006C00
220020007000610072007400690061006C00460069006C00650053007500
700070006F00720074003D0022006E006F002200200073006E0061007000
730068006F007400530065007400490064003D0022003900330064006100
38003200630066002D0061003300650065002D0034006300620036002D00
61003800320033002D003200630066003000320032003100640039003600
6400380022002F003E00','hex'),'job',462,462,1506024766,25,596,26229,0);
I hope it helps.
best regards
--
Radosław Korzeniewski
rados...@korzeniewski.net
--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot___
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Errors migrating from mysql to postgres
Hello,
2017-11-30 17:14 GMT+01:00 Wanderlei Huttel :
> Hello Radoslaw
>
> Could you explain better about "mysqldump --hex-blob"?
>
>
The default mysqldump will export any blob in the form which cannot be used
to import it into a postgresql. It looks something like this:
INSERT INTO RestoreObject (RestoreObjectId, ObjectName, RestoreObject,
PluginName, ObjectLength, ObjectFullLength, ObjectIndex, ObjectType,
FileIndex, JobId, ObjectCompression) VALUES
(2088,'job_metadata.xml','\0\0\0','job',462,462,1506024766,25,596,26229,0);
what you could import into postgresql should be prepared as:
insert into restoreobject values
(2088,'job_metadata.xml',decode('3C004200410043004B00550050005F0043004F004D0050004F004E0045004E0054005300200078006D006C006E0073003D00220078002D0073006300680065006D0061003A00230056007300730043006F006D0070006F006E0065006E0074004D00650074006100640061007400610022002000760065007200730069006F006E003D00220031002E0032002200200062006F006F007400610062006C006500530079007300740065006D00530074006100740065004200610063006B00750070003D00220079006500730022002000730065006C0065006300740043006F006D0070006F006E0065006E00740073003D002200790065007300220020006200610063006B007500700054007900700065003D00220069006E006300720065006D0065006E00740061006C00220020007000610072007400690061006C00460069006C00650053007500700070006F00720074003D0022006E006F002200200073006E0061007000730068006F007400530065007400490064003D002200390033006400610038003200630066002D0061003300650065002D0034006300620036002D0061003800320033002D0032006300660030003200320031006400390036006400380022002F003E00','hex'),'job',462,462,1506024766,25,596,26229,0);
best regards
--
Radosław Korzeniewski
rados...@korzeniewski.net
--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot___
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Errors migrating from mysql to postgres
Yes please on the explanation. When I use mysqldump –hex-blob , the RestoreObject field/column has the null byte equivalents (i.e. it starts with 0x3C0042004100 which is “mailto:wanderleihut...@gmail.com] Sent: Thursday, November 30, 2017 8:14 AM To: Radosław Korzeniewski Cc: Martin Simmons; bacula-users Subject: Re: [Bacula-users] Errors migrating from mysql to postgres Hello Radoslaw Could you explain better about "mysqldump --hex-blob"? Maybe I could include in the script and improve for not happen any errors. Best regards Wanderlei Hüttel http://www.huttel.com.br 2017-11-30 9:10 GMT-02:00 Radosław Korzeniewski mailto:rados...@korzeniewski.net>>: Hello, 2017-11-28 11:55 GMT+01:00 Wanderlei Huttel mailto:wanderleihut...@gmail.com>>: Hello Michel I've created some scripts to do this job. https://github.com/wanderleihuttel/bacula-utils/tree/master/convert_mysql_to_postgresql I only had some troubles with the Log table, but I've changed some registries manually and everything worked fine. A guy in Brazil with a database with 6gb used these scripts and had no problems I have migrated a Bacula 5.x to 7.x from MySQL into a PostgreSQL and I had the same problem with RestoreObjects table. The solution was to use: --hex-blob parameter to mysqldump and use a decode() function from PostgreSQL. The Restore Objects table is populated when Bacula run a Windows VSS backup. If you backup all but Windows VSS you will have no problem with above script. :) The next problem I encountered was an ' character in some filenames which mysqldump is unable to proper escape, even with --compatible=postgresql option. So I had to escape it manually (with an additional script). Finally the migration was successful but due to above errors it took more time then expected. best regards -- Radosław Korzeniewski rados...@korzeniewski.net<mailto:rados...@korzeniewski.net> -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Errors migrating from mysql to postgres
Hello Radoslaw Could you explain better about "mysqldump --hex-blob"? Maybe I could include in the script and improve for not happen any errors. Best regards *Wanderlei Hüttel* http://www.huttel.com.br 2017-11-30 9:10 GMT-02:00 Radosław Korzeniewski : > Hello, > > 2017-11-28 11:55 GMT+01:00 Wanderlei Huttel : > >> Hello Michel >> >> I've created some scripts to do this job. >> https://github.com/wanderleihuttel/bacula-utils/tree/master/ >> convert_mysql_to_postgresql >> >> I only had some troubles with the Log table, but I've changed some >> registries manually and everything worked fine. >> A guy in Brazil with a database with 6gb used these scripts and had no >> problems >> > > I have migrated a Bacula 5.x to 7.x from MySQL into a PostgreSQL and I had > the same problem with RestoreObjects table. The solution was to use: > --hex-blob parameter to mysqldump and use a decode() function from > PostgreSQL. > The Restore Objects table is populated when Bacula run a Windows VSS > backup. If you backup all but Windows VSS you will have no problem with > above script. :) > > The next problem I encountered was an ' character in some filenames which > mysqldump is unable to proper escape, even with --compatible=postgresql > option. So I had to escape it manually (with an additional script). > > Finally the migration was successful but due to above errors it took more > time then expected. > > best regards > -- > Radosław Korzeniewski > rados...@korzeniewski.net > -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Errors migrating from mysql to postgres
Hello, 2017-11-30 12:10 GMT+01:00 Radosław Korzeniewski : > Hello, > > 2017-11-28 11:55 GMT+01:00 Wanderlei Huttel : > >> Hello Michel >> >> I've created some scripts to do this job. >> https://github.com/wanderleihuttel/bacula-utils/tree/master/ >> convert_mysql_to_postgresql >> >> I only had some troubles with the Log table, but I've changed some >> registries manually and everything worked fine. >> A guy in Brazil with a database with 6gb used these scripts and had no >> problems >> > And the database was 24GB in size. > > I have migrated a Bacula 5.x to 7.x from MySQL into a PostgreSQL and I had > the same problem with RestoreObjects table. The solution was to use: > --hex-blob parameter to mysqldump and use a decode() function from > PostgreSQL. > The Restore Objects table is populated when Bacula run a Windows VSS > backup. If you backup all but Windows VSS you will have no problem with > above script. :) > > The next problem I encountered was an ' character in some filenames which > mysqldump is unable to proper escape, even with --compatible=postgresql > option. So I had to escape it manually (with an additional script). > > Finally the migration was successful but due to above errors it took more > time then expected. > best regards -- Radosław Korzeniewski rados...@korzeniewski.net -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Errors migrating from mysql to postgres
Hello, 2017-11-28 11:55 GMT+01:00 Wanderlei Huttel : > Hello Michel > > I've created some scripts to do this job. > https://github.com/wanderleihuttel/bacula-utils/ > tree/master/convert_mysql_to_postgresql > > I only had some troubles with the Log table, but I've changed some > registries manually and everything worked fine. > A guy in Brazil with a database with 6gb used these scripts and had no > problems > I have migrated a Bacula 5.x to 7.x from MySQL into a PostgreSQL and I had the same problem with RestoreObjects table. The solution was to use: --hex-blob parameter to mysqldump and use a decode() function from PostgreSQL. The Restore Objects table is populated when Bacula run a Windows VSS backup. If you backup all but Windows VSS you will have no problem with above script. :) The next problem I encountered was an ' character in some filenames which mysqldump is unable to proper escape, even with --compatible=postgresql option. So I had to escape it manually (with an additional script). Finally the migration was successful but due to above errors it took more time then expected. best regards -- Radosław Korzeniewski rados...@korzeniewski.net -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Errors migrating from mysql to postgres
Hello Michel I've created some scripts to do this job. https://github.com/wanderleihuttel/bacula-utils/tree/master/convert_mysql_to_postgresql I only had some troubles with the Log table, but I've changed some registries manually and everything worked fine. A guy in Brazil with a database with 6gb used these scripts and had no problems Regards *Wanderlei Hüttel* http://www.huttel.com.br 2017-11-28 8:19 GMT-02:00 Martin Simmons : > > On Mon, 27 Nov 2017 19:13:15 +, Michel Figgins said: > > > > Hello, > > I'm trying to migrate from a mysql backend to a postgresql backend > and I'm getting an error importing the RestoreObject table. Specifically > I'm getting : > > ERROR: invalid input syntax for type bytea > > LINE 1: ...bjectCompression) VALUES (1588,'job_metadata.xml',' 0A\0C\... > > Googling around leads me to believe that Postgres isn't liking the > format of RestoreObject field, but I'm stumped as to how to change it. > > So my questions: Can anyone suggest a way to modify the INSERT > statement to one Postgres will understand? > > You will need to munge MySQL's output for the RestoreObject.RestoreObject > column into either bytea hex or escape format (see > https://www.postgresql.org/docs/9.6/static/datatype-binary.html). > > > >Do I actually need to import this table, or will the entries get > re-populated as successful backups are completed? > > Yes, I think you do need to import it if you want to be able to restore > these > jobs. It stores per-job information including Windows VSS data, but maybe > other things as well. > > __Martin > > > -- > Check out the vibrant tech community on one of the world's most > engaging tech sites, Slashdot.org! http://sdm.link/slashdot > ___ > Bacula-users mailing list > Bacula-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/bacula-users > -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Errors migrating from mysql to postgres
> On Mon, 27 Nov 2017 19:13:15 +, Michel Figgins said: > > Hello, > I'm trying to migrate from a mysql backend to a postgresql backend and > I'm getting an error importing the RestoreObject table. Specifically I'm > getting : > ERROR: invalid input syntax for type bytea > LINE 1: ...bjectCompression) VALUES (1588,'job_metadata.xml',' Googling around leads me to believe that Postgres isn't liking the > format of RestoreObject field, but I'm stumped as to how to change it. > So my questions: Can anyone suggest a way to modify the INSERT > statement to one Postgres will understand? You will need to munge MySQL's output for the RestoreObject.RestoreObject column into either bytea hex or escape format (see https://www.postgresql.org/docs/9.6/static/datatype-binary.html). >Do I actually need to import this table, or will the entries get > re-populated as successful backups are completed? Yes, I think you do need to import it if you want to be able to restore these jobs. It stores per-job information including Windows VSS data, but maybe other things as well. __Martin -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Errors migrating from mysql to postgres
>> -Original Message- >> From: Dan Langille [mailto:d...@langille.org] >> Sent: Monday, November 27, 2017 1:29 PM >> To: Michel Figgins >> Cc: bacula-users >> Subject: Re: [Bacula-users] Errors migrating from mysql to postgres >> >>> On Nov 27, 2017, at 2:13 PM, Michel Figgins >>> wrote: >>> >>> Hello, >>> I'm trying to migrate from a mysql backend to a postgresql backend and >>> I'm getting an error importing the RestoreObject table. Specifically I'm >>> getting : >>> ERROR: invalid input syntax for type bytea LINE 1: >>> ...bjectCompression) VALUES (1588,'job_metadata.xml','>> Googling around leads me to believe that Postgres isn't liking the >>> format of RestoreObject field, but I'm stumped as to how to change it. >>> So my questions: Can anyone suggest a way to modify the INSERT >>> statement to one Postgres will understand? Do I actually need to import >>> this table, or will the entries get re-populated as successful backups are >>> completed? >>> Additional Info: >>> This is on Fedora 27, Bacula version is 9.0.5, the MySQL database is >>> MariaDB 10.2.9, the PostgreSQL database is 9.6.6. The mysqldump command >>> used to generate the import file is : >>> mysqldump -t -n -c -e --compatible=postgresql --skip-quote-names --skip-opt >>> --disable-keys --lock-tables -u bacula -pX 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//' | sed -e "s#\\\'#\'\'#g" > bacula-export.sql >>> The pqsl command used to import the dump is: >>> psql -Ubacula bacula < bacula-export.sql >> >> Can you confirm your PostgreSQL is SQL_ASCII? >> >> $ psql -l >> List of databases >> Name | Owner | Encoding | Collate | Ctype | Access >> privileges >> --+--+---+-+---+ >> --+--+---+-+---+--- >> bacula | bacula | SQL_ASCII | C | C | >> >> >> > > > On Nov 27, 2017, at 7:25 PM, Michel Figgins wrote: > > Yes, it is: > postgres=# \l > List of databases > Name| Owner | Encoding | Collate |Ctype| Access > privileges > ---+--+---+-+-+--- > bacula| bacula | SQL_ASCII | C | C | =Tc/bacula > + > | | | | | > bacula=C*T*c*/bacula > The only other idea I have is escape notation. That would give you: VALUES (1588,'job_metadata.xml',E'http://sdm.link/slashdot ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Errors migrating from mysql to postgres
Yes, it is: postgres=# \l List of databases Name| Owner | Encoding | Collate |Ctype| Access privileges ---+--+---+-+-+--- bacula| bacula | SQL_ASCII | C | C | =Tc/bacula + | | | | | bacula=C*T*c*/bacula -Original Message- From: Dan Langille [mailto:d...@langille.org] Sent: Monday, November 27, 2017 1:29 PM To: Michel Figgins Cc: bacula-users Subject: Re: [Bacula-users] Errors migrating from mysql to postgres > On Nov 27, 2017, at 2:13 PM, Michel Figgins wrote: > > Hello, > I'm trying to migrate from a mysql backend to a postgresql backend and > I'm getting an error importing the RestoreObject table. Specifically I'm > getting : > ERROR: invalid input syntax for type bytea LINE 1: > ...bjectCompression) VALUES (1588,'job_metadata.xml',' Googling around leads me to believe that Postgres isn't liking the > format of RestoreObject field, but I'm stumped as to how to change it. > So my questions: Can anyone suggest a way to modify the INSERT > statement to one Postgres will understand? Do I actually need to import this > table, or will the entries get re-populated as successful backups are > completed? > Additional Info: > This is on Fedora 27, Bacula version is 9.0.5, the MySQL database is MariaDB > 10.2.9, the PostgreSQL database is 9.6.6. The mysqldump command used to > generate the import file is : > mysqldump -t -n -c -e --compatible=postgresql --skip-quote-names --skip-opt > --disable-keys --lock-tables -u bacula -pX 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//' | sed -e "s#\\\'#\'\'#g" > bacula-export.sql > The pqsl command used to import the dump is: > psql -Ubacula bacula < bacula-export.sql Can you confirm your PostgreSQL is SQL_ASCII? $ psql -l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges --+--+---+-+---+ --+--+---+-+---+--- bacula | bacula | SQL_ASCII | C | C | -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Errors migrating from mysql to postgres
> On Nov 27, 2017, at 2:13 PM, Michel Figgins wrote: > > Hello, > I'm trying to migrate from a mysql backend to a postgresql backend and > I'm getting an error importing the RestoreObject table. Specifically I'm > getting : > ERROR: invalid input syntax for type bytea > LINE 1: ...bjectCompression) VALUES (1588,'job_metadata.xml',' Googling around leads me to believe that Postgres isn't liking the > format of RestoreObject field, but I'm stumped as to how to change it. > So my questions: Can anyone suggest a way to modify the INSERT > statement to one Postgres will understand? Do I actually need to import this > table, or will the entries get re-populated as successful backups are > completed? > Additional Info: > This is on Fedora 27, Bacula version is 9.0.5, the MySQL database is MariaDB > 10.2.9, the PostgreSQL database is 9.6.6. The mysqldump command used to > generate the import file is : > mysqldump -t -n -c -e --compatible=postgresql --skip-quote-names --skip-opt > --disable-keys --lock-tables -u bacula -pX 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//' | sed -e "s#\\\'#\'\'#g" > bacula-export.sql > The pqsl command used to import the dump is: > psql -Ubacula bacula < bacula-export.sql Can you confirm your PostgreSQL is SQL_ASCII? $ psql -l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges --+--+---+-+---+--- bacula | bacula | SQL_ASCII | C | C | -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
[Bacula-users] Errors migrating from mysql to postgres
Hello, I'm trying to migrate from a mysql backend to a postgresql backend and I'm getting an error importing the RestoreObject table. Specifically I'm getting : ERROR: invalid input syntax for type bytea LINE 1: ...bjectCompression) VALUES (1588,'job_metadata.xml',' bacula-export.sql The pqsl command used to import the dump is: psql -Ubacula bacula < bacula-export.sql Thanks, Michel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
