[Spacewalk-list] Please help with Oracle to PostgreSQL migration

[James Fillman]

Hey Jan, 

I've just gotten a chance to try and export the oracle db after applying the patch you provided. I was able to successfully output a full dump of the oracle database. Thanks!

I am, however, running into another problem. This time it's with the postgresql import. It bombs out with this error message:

ERROR:  invalid byte sequence for encoding "UTF8": 0xeda080
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
CONTEXT:  COPY rhnpackagechangelogdata, line 136452

I found a posting from July where someone had a similar problem. You had him apply a patch to the spacewalk-dump-schema script. I went to apply the same patch and found it to already be there.

Here's a snippit from that old post:

On Tue, Jul 12, 2011 at 02:01:19PM +0000, Silvio.Schanz at elektrobit.com wrote:
> Hi,
> 
> fgrep -B1 rhnpackagerepodata spacewalk-oracle.dmp ...
> -- Types for rhnpackagerepodata: DOUBLE PRECISION BLOB BLOB BLOB DATE 
> DATE

Can you apply the following patch to your spacewalk-dump-schema and dump and import the schema again, to see if it addresses the problem for you?

diff --git a/utils/spacewalk-dump-schema b/utils/spacewalk-dump-schema index e391111..0068ed4 100755
--- a/utils/spacewalk-dump-schema
+++ b/utils/spacewalk-dump-schema
@@ -115,7 +115,7 @@ sub process_table {
                                if (ref $row->[$i] and ref $row->[$i] eq 'ARRAY') {     # user types
                                        no warnings 'uninitialized';
                                        $row->[$i] = "(@{[ join ',', @{$row->[$i]} ]})";
-                               } elsif ($types[$i] eq 'unknown') {                     # blobs
+                               } elsif ($types[$i] eq 'unknown' or $types[$i] eq 'BLOB') {     # blobs
                                        $row->[$i] =~ s!(.)! sprintf "\\\\%03o", ord($1) !seg;
                                } else {
                                        $row->[$i] =~ s!([\x00-\x1f\x5c])! sprintf "\\x%02x", ord($1) !seg;


The patch apparently worked for the other guy. When I check the oracle dump, I find the same 'Types' line:

-- Types for rhnpackagerepodata: DOUBLE PRECISION BLOB BLOB BLOB DATE DATE
copy rhnpackagerepodata(package_id,primary_xml,filelist,other,created,modified) from stdin;

Any idea's?

Since I have to scheduled downtime in advance, it's taking me a while to try out your suggestions and make any progress. I appreciated the quickness of your previous responses.

Cheers,
--james



-----Original Message-----
From: spacewalk-list-bounces at redhat.com [mailto:spacewalk-list-bounces at redhat.com] On Behalf Of Jan Pazdziora
Sent: Tuesday, November 22, 2011 4:32 AM
To: spacewalk-list at redhat.com
Subject: Re: [Spacewalk-list] Please help with Oracle to PostgreSQL migration

On Mon, Nov 21, 2011 at 12:47:12PM -0800, James Fillman wrote:
> Thanks for the help Jan,
> 
> I increased the LongReadLen value. That fixed the initial problem. I am still left with another error:
> 
> [root at vacslp01spacewalk db_1]# spacewalk-dump-schema --db=spacewal 
> --user=spacewalk --password=spacewalk > /tmp/oracle-dump.sql Malformed UTF-8 character (fatal) at /usr/bin/spacewalk-dump-schema line 121.
> 
> Any suggestions on this one??

Could you try

diff --git a/utils/spacewalk-dump-schema b/utils/spacewalk-dump-schema index 0068ed4..b40593b 100755
--- a/utils/spacewalk-dump-schema
+++ b/utils/spacewalk-dump-schema
@@ -118,8 +118,9 @@ sub process_table {
 				} elsif ($types[$i] eq 'unknown' or $types[$i] eq 'BLOB') {	# blobs
 					$row->[$i] =~ s!(.)! sprintf "\\\\%03o", ord($1) !seg;
 				} else {
+					utf8::encode($row->[$i]);
 					$row->[$i] =~ s!([\x00-\x1f\x5c])! sprintf "\\x%02x", ord($1) !seg;
-                                        utf8::encode($row->[$i]); utf8::decode($row->[$i]);
+					utf8::decode($row->[$i]);
 				}
 				print $row->[$i];
 			} else {

?

--
Jan Pazdziora
Principal Software Engineer, Satellite Engineering, Red Hat

_______________________________________________
Spacewalk-list mailing list
Spacewalk-list at redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-list