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?

1 Answer 1

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

4
  • This is what I was looking for! But only works with plain sql dump.
    – LudEE
    Dec 27, 2015 at 22:50
  • What are you using ?
    – etrimaille
    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
    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
    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.