5

I created a PostGIS database on a PostgreSQL server and stored some QGIS styles (in public.layer_styles). When dumping and restoring the database only this table gives me an error and stays empty.

I tried only dumping the public schema as .backup and .sql. What's wrong with xml?

 pg_restore: [archiver (db)] COPY failed for table "layer_styles": FEHLER:  invalid XML content
DETAIL:  line 1: StartTag: invalid element name
<!DOCTYPE qgis PUBLIC 'http://mrcc.com/qgis.dtd' 'SYSTEM'>
 ^
CONTEXT:  COPY layer_styles, Zeile 1, Spalte styleqml: „<!DOCTYPE qgis PUBLIC 'http://mrcc.com/qgis.dtd' 'SYSTEM'>
<qgis version="2.8.1-Wien" minimumScale="...“

-- Dumped from database version 9.3.10 to version 9.4.5 <- is this the problem?

Improve this question

1 Answer 1

Reset to default
4

You can add this statement SET XML OPTION DOCUMENT; at the beginning of the SQL backup file.

Source : https://hub.qgis.org/issues/10079

Improve this answer
4
  • This is what I was looking for! But only works with plain sql dump.
    – LudEE
    Commented Dec 27, 2015 at 22:50
  • What are you using ?
    – etrimaille
    Commented Dec 28, 2015 at 8:53
  • 1
    I confirm that this answers work, however as mentioned this only work when you can specify this option. In my use case I migrated a database using a custom dump created by pg_dump and restored using pg_restore. I got a COPY error on public.layer_styles so I restored it separately using plain SQL format.
    – MarHoff
    Commented Feb 18, 2016 at 10:26
  • I am getting a duplicate key error when trying to restore layer_styles in SQL format. Can you post your pg_dump and restore command (if other than "\i dump.sql")? I'm using the following to dump styles table: pg_dump -U postgres dbase -n public -t layer_styles --format=p --data-only -f dump.sql
    – Cliff
    Commented Apr 5, 2016 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.