4

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?

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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