Thread: Migrating from 9.2.4 to 9.3.0 with XML DOCTYPE
Hi all,
I’m migrating a database from 9.2.4 to 9.3.0 and encountering an issue with an XML field failing to restore.
I’m using pg_dump 9.3.0 as follows (with 9.2.4 on 5433 and 9.3.0 on 5432). I have verified my paths, it’s definitely using the 9.3.0 binaries. Both source and target are using UTF8 encoding.
pg_dump -p 5433 content_xml_test | psql content_xml_test -p 5432 –v ON_ERROR_STOP=1
<snipped>
CREATE SEQUENCE
Time: 0.870 ms
ALTER TABLE
Time: 0.199 ms
ALTER SEQUENCE
Time: 0.380 ms
ALTER TABLE
Time: 0.931 ms
Time: 10.883 ms
ERROR: invalid XML content
DETAIL: line 1: StartTag: invalid element name
<!DOCTYPE Catalog SYSTEM "../../../../../../../../test.dtd">
^
CONTEXT: COPY xml_test, line 13, column document: "<!DOCTYPE Catalog SYSTEM "../../../../../../../../test.dtd">
However, if I do the following:
ALTER DATABASE content_xml_test SET XMLOPTION TO DOCUMENT;
ERROR: invalid XML document
DETAIL: line 1: Start tag expected, '<' not found
1046710
^
CONTEXT: COPY test_lookup, line 1, column external_ref: "{1046710,1046711}"
Time: 14.828 ms
This is even more interesting, since the external_ref column is a totally different field, and a different type. I suspect the failure is still valid, but it’s reporting the wrong field name as being the culprit.
I suspect this might be resolved in a newer point release of 9.3, however I don’t have the luxury on this particular host right now.
Any suggestions how I might convince 9.3.0 to accept the dump from 9.2.4?
Cheers,
Tim
Tim Kane wrote > I suspect this might be resolved in a newer point release of 9.3, however > I > don’t have the luxury on this particular host right now. > > Any suggestions how I might convince 9.3.0 to accept the dump from 9.2.4? Given the history of 9.3 if you cannot use the most current point release - and really you should probably wait until the next minor release in the next month or so - you should just stick to 9.2 I don't know if later 9.3 releases bug-fix your problem - though you can look at the release notes for each version to check if that indeed has been addressed. Even you cannot update a production host to a particular version maybe you can setup a development machine with various versions to test things like this? You are 4 releases behind on the 9.2 series. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Migrating-from-9-2-4-to-9-3-0-with-XML-DOCTYPE-tp5805980p5805982.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Tim Kane <tim.kane@gmail.com> writes:
> I’m migrating a database from 9.2.4 to 9.3.0 and encountering an issue with
> an XML field failing to restore.
Hm, can you restore it into 9.2 either?
AFAICS, pg_dump has absolutely no idea that it should be worried about the
value of xmloption, despite the fact that that setting affects what is
considered valid XML data. What's worse, even if it were attempting to do
something about xmloption, I don't see how it could deal with a case like
this where you have different values inside the same database that require
two different settings in order to parse.
This isn't a 9.3.x bug, it's an aboriginal misdesign of the XML datatype.
Not sure what we can do about it at this point. Perhaps we could invent
a "document_or_content" setting that would tell xml_in to accept either
case? And then have pg_dump force that setting to be used during restore?
regards, tom lane
PS: BTW, I agree with the advice expressed by David J: under no
circumstances put any data you care about on 9.3.0. That release
was rather a disaster from a quality-control standpoint :-(
But that's unrelated to your XML issue.
Hm, can you restore it into 9.2 either?AFAICS, pg_dump has absolutely no idea that it should be worried about thevalue of xmloption, despite the fact that that setting affects what isconsidered valid XML data. What's worse, even if it were attempting to dosomething about xmloption, I don't see how it could deal with a case likethis where you have different values inside the same database that requiretwo different settings in order to parse.This isn't a 9.3.x bug, it's an aboriginal misdesign of the XML datatype.Not sure what we can do about it at this point. Perhaps we could inventa "document_or_content" setting that would tell xml_in to accept eithercase? And then have pg_dump force that setting to be used during restore?
This sounds reasonable. My use case is purely as a document store, with the ability to perform xml parse functions against it – as such, I’m not concerned wether it’s a document or content – hence why we have both types recorded against that field.
For the minute, I’m getting around the restore problem by mangling the dump such that the table is created using the text type rather than xml. This at least gets the data onto a 9.3 cluster, even if it’s cosmetically represented as text instead of xml. I can worry about the document vs content problem at a later stage.
PS: BTW, I agree with the advice expressed by David J: under nocircumstances put any data you care about on 9.3.0. That releasewas rather a disaster from a quality-control standpoint :-(But that's unrelated to your XML issue.
Ack. Thanks for the info. I’ll push the upgrade-path agenda a little harder.