[Qgis-user] Backup of styles inserted in database

[Víctor Manuel Herreros Villanueva]

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/b0a74494/attachment.html>