<div dir="ltr">Thanks for the idea but I've read about that and tried setting XML option both to 'content' or 'document', without success. public.layer_styles is not easy to handle...<br><div><div class="gmail_extra">
<br><br><div class="gmail_quote">2014-04-01 13:51 GMT+02:00 Gino Pirelli <span dir="ltr"><<a href="mailto:luipir@gmail.com" target="_blank">luipir@gmail.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">probably set xml parsing option<div><br></div><div><a href="http://www.postgresql.org/docs/9.2/static/datatype-xml.html" target="_blank">http://www.postgresql.org/docs/9.2/static/datatype-xml.html</a><br></div>
<div><br></div>
<div>SET XML OPTION { DOCUMENT | CONTENT };<br></div><div><br></div><div>in doc you can read that:</div><div><br></div><div>Note: With the default XML option setting, you cannot directly cast character strings to type xml if they contain a document type declaration, because the definition of XML content fragment does not accept them. If you need to do that, either use XMLPARSE or change the XML option.<br>
</div><div><br></div><div>ciao Luigi Pirelli</div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On 1 April 2014 13:35, Víctor Manuel Herreros Villanueva <span dir="ltr"><<a href="mailto:vherreros@ucm.es" target="_blank">vherreros@ucm.es</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div><div><div>Hello,<br><br></div>I'm trying to make a proper backup of all the tables related to one QGIS project and I've seen there is one conflicting table.<br>
<br></div><div>My environment is:<br>
</div><ul><li>PostgreSQL 9.3 (both Windows and Linux, x-64)</li><li>PostGIS 2.1 (x-64)</li><li>QGIS 2.2 (both Windows and Linux, x-64)</li><li>Backup/restore procedure:</li><ul><li>Backup via pg_dump: </li><ul><li>pg_dump -h origin_host_IP -U postgres -F c -b -v -c -o -C -f /backup_data_file.compressed Database_to_backup_to 2> /backup_log_file.log)</li>
</ul><li>Restore via <a href="http://postgis_restore.pl" target="_blank">postgis_restore.pl</a>:</li><ul><li>createdb -h target_host_IP -U postgres -E UTF8 -O postgres -T template_postgis Database_to_restore_to</li><li>perl /opt/PostgreSQL/9.3/PostGIS/utils/<a href="http://postgis_restore.pl" target="_blank">postgis_restore.pl</a> /backup_data_file.compressed | psql -h target_host_IP -U postgres Database_to_restore_to 2>/restore_log_file.log</li>
</ul></ul></ul><div><br></div>When layer styles are stored in the database instead of local files, they are inserted as records inside the table public.layer_styles (one record per style). Two of the fields of that table are xml types.<br>
<br></div>When backing up the database, public.layer_styles backs up properly. But when trying to restore to another postgres server, I get the following error:<br><span style="font-family:courier new,monospace"><br></span><div style="margin-left:40px">
<span style="font-family:courier new,monospace">ERROR: invalid XML content<br>DETAIL: line 1: StartTag: invalid element name<br><!DOCTYPE qgis PUBLIC '<a href="http://mrcc.com/qgis.dtd" target="_blank">http://mrcc.com/qgis.dtd</a>' 'SYSTEM'><br>
^<br>CONTEXT: COPY layer_styles, línea 1, columna styleqml: «<!DOCTYPE qgis PUBLIC '<a href="http://mrcc.com/qgis.dtd" target="_blank">http://mrcc.com/qgis.dtd</a>' 'SYSTEM'><br><qgis version="2.0.1-Dufour" minimumScale...»<br>
</span></div><br clear="all"><div><div><div><div><div>I've been looking around for a solution but I've not been able to find it. I know the error is due to the constraint checks PostgreSQL poses over XML field types, but I don't know how to solve it.<br>
<br></div><div>The only procedure I've discovered to backup / restore layer styles is through QGIS desktop client (connect to the database hosting the styles, save them as local files, create a new project associated to the target database, apply the locally saved styles to layers and finally save them in the target database), but this is not affordable in an automatic backup / restore procedure.<br>
<br>Any ideas?<br><br></div><div>Thanks a lot.</div></div></div></div></div></div></blockquote></div></div></blockquote></div><br><div dir="ltr"><div dir="ltr">
<table style="border-right:black 2px solid;width:470px;font-size:11pt;border-bottom:black 2px solid;border-top:black 1px solid;border-left:black 1px solid" cellpadding="2" cellspacing="2" border="0">
<tbody>
<tr>
<td rowspan="2" align="center" valign="center" width="35%"><a href="http://www.ucm.es/" target="_blank"><img src="http://www.ucm.es/nodes/web/00media/img/ucm-ws.png" align="middle" border="0" height="70" width="70"></a> </td>
<td colspan="3" align="center" bgcolor="#a50000"><a href="http://www.ucm.es/" target="_blank"><font style="TEXT-DECORATION:none" color="white" face="Verdana"><b>Universidad Complutense de Madrid</b></font></a> </td>
</tr>
<tr>
<td colspan="3" align="center"><font color="#000099" face="Verdana"><b>Víctor Manuel Herreros Villanueva</b>, PhD.</font><br><font color="#000099" face="Verdana" size="1">Project Manager</font>
</td>
</tr>
<tr>
<td colspan="2" align="left"><font color="#000099" face="Verdana"><b>Facilities Management<br>Data Warehouse</b></font><br>
</td>
<td colspan="2" align="right" width="48%"><font color="#336699" face="Verdana">Jardín Botánico<br>
Av. Complutense s/n<br>28040 Madrid<br>Spain</font>
</td>
</tr>
<tr>
<td colspan="2" align="left"><font color="#000099" face="Verdana" size="1"><i>Phn. no.
(+34) 91 394 <b>7275</b><br>Fax no. (+34) 91 394 <b>4773</b></i> </font>
</td>
<td align="right"><a href="mailto:vherreros@ucm.es" target="_blank"><font style="TEXT-DECORATION:none" color="#000099" face="Verdana" size="1">vherreros@ucm.es</font></a>
</td>
</tr>
</tbody>
</table>
<br></div>
</div>
<br><br></div></div></div>