[Qgis-user] Backup of styles inserted in database

[Víctor Manuel Herreros Villanueva]

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...


2014-04-01 13:51 GMT+02:00 Gino Pirelli <luipir at gmail.com>:

> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20140401/43bdf886/attachment.html>