Goal: copy a single table including all columns from one database to another using psql on the command line, avoiding an issue with XML from QGIS layer styles.

I am running the following on the command line

pg_dump -t table_to_copy source_db | psql target_db

as shown in places like this.

The table_to_copy is public.layer_styles generated from QGIS containing layer styles.

The command seems to be copying the table_to_copy to the target_db just fine, but when I look in table_to_copy itself in target_db, there are no data, just empty columns. The existing table from which I'm copying has 13 columns, each with 6 rows of data.

I'm getting the following error during this operation:

ERROR: invalid XML content DETAIL: line 1: StartTag: invalid element name <!DOCTYPE qgis PUBLIC 'http://mrcc.com/qgis.dtd' 'SYSTEM'> CONTEXT: COPY layer_styles, line 1, column styleqml: "<!DOCTYPE qgis PUBLIC 'http://mrcc.com/qgis.dtd' 'SYSTEM'>

This suggests an issue with the XML from QGIS. I've seen some responses like this suggesting using SQL in pgAdmin to avoid this, but I want to do this on the command line so I can script it. Can this be done on the command line with pg_dump, pg_restore or some other method?

I'm running Postgres 10 on macOS 10.13.6.

  • It looks like the destination database isn't prepared to accept that XML content. is that XML? – Jasen Jul 28 '18 at 2:11
  • Yes, it is XML. Any suggestions on how to handle that? All solutions I've come up with are kludgy at best. – Chris Meder Jul 30 '18 at 17:17

Your Answer

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

Browse other questions tagged or ask your own question.