[Qgis-user] Backup of styles inserted in database

[Gino Pirelli]

probably set xml parsing option

http://www.postgresql.org/docs/9.2/static/datatype-xml.html

SET XML OPTION { DOCUMENT | CONTENT };

in doc you can read that:

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.

ciao Luigi Pirelli


On 1 April 2014 13:35, Víctor Manuel Herreros Villanueva
<vherreros at ucm.es>wrote:

> Hello,
>
> 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.
>
> My environment is:
>
>    - PostgreSQL 9.3 (both Windows and Linux, x-64)
>    - PostGIS 2.1 (x-64)
>    - QGIS 2.2 (both Windows and Linux, x-64)
>    - Backup/restore procedure:
>       - Backup via pg_dump:
>          - 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)
>       - Restore via postgis_restore.pl:
>          - createdb -h target_host_IP -U postgres -E UTF8 -O postgres -T
>          template_postgis Database_to_restore_to
>          - perl /opt/PostgreSQL/9.3/PostGIS/utils/postgis_restore.pl/backup_data_file.compressed | psql -h target_host_IP -U postgres
>          Database_to_restore_to 2>/restore_log_file.log
>
>
> 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.
>
> 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:
>
> ERROR:  invalid XML content
> DETAIL:  line 1: StartTag: invalid element name
> <!DOCTYPE qgis PUBLIC 'http://mrcc.com/qgis.dtd' 'SYSTEM'>
>  ^
> CONTEXT:  COPY layer_styles, línea 1, columna styleqml: «<!DOCTYPE qgis
> PUBLIC 'http://mrcc.com/qgis.dtd' 'SYSTEM'>
> <qgis version="2.0.1-Dufour" minimumScale...»
>
> 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.
>
> 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.
>
> Any ideas?
>
> Thanks a lot.
>
> --
>
>   <http://www.ucm.es/> *Universidad Complutense de Madrid*<http://www.ucm.es/>  *Víctor
> Manuel Herreros Villanueva*, PhD.
> Project Manager
> *Facilities ManagementData Warehouse*
>  Jardín Botánico
> Av. Complutense s/n
> 28040 Madrid
> Spain
> *Phn. no. (+34) 91 394 7275Fax no.  (+34) 91 394 4773*  vherreros at ucm.es
>
>
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-user
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20140401/be2d774d/attachment.html>