Hfrhyu

I am trying to copy the layer_styles table to another database. A quick and painless process is preferred, to make the movement of the layer_styles tables easy to repeat in the future.

I have tried two methods so far:
1. pg_dump and psql
2. Creating a dblink

Neither seem to work. The culprit seems to be the xml type in the layer_styles table.

1. I've had success using pg-dump and restore with other tables, however not the layer_styles table. Using pg_restore returns 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, line 1, column styleqml: "<!DOCTYPE qgis PUBLIC 'http://mrcc.com/qgis.dtd' 'SYSTEM'>
 <qgis simplifyLocal="1" maxScale="0" minS..."
 setval

The end result is an empty layer_styles table in the target database.

2. After creating the dblink, I run the following code:

CREATE TABLE layer_styles
AS
SELECT
from dblink('host=localhost
 user=postgres
 password=postgres
 dbname=database3',
 'select *
 from layer_styles') AS linktable(
 a integer,
 b varchar,
 c varchar,
 d varchar,
 e text,
 f xml,
 g xml,
 h boolean,
 i text,
 j varchar,
 k xml,
 l timestamp);

This returns the following error:

> ERROR: invalid XML content
> DETAIL: line 1: StartTag: invalid element name
> <!DOCTYPE qgis PUBLIC 'http://mrcc.com/qgis.dtd' 'SYSTEM'>
> ^

I have had previous success with the dblink when using different tables without an XML type.

Does anyone have any advice?

Via DB-manager:

• you can load the table into QGIS.


• Then connect to another database


• import the layer_styles-table

Or you can create an empty table in the target_database:

CREATE TABLE public.layer_styles
(
 id integer NOT NULL DEFAULT nextval('layer_styles_id_seq'::regclass),
 f_table_catalog character varying COLLATE pg_catalog."default",
 f_table_schema character varying COLLATE pg_catalog."default",
 f_table_name character varying COLLATE pg_catalog."default",
 f_geometry_column character varying COLLATE pg_catalog."default",
 stylename character varying(30) COLLATE pg_catalog."default",
 styleqml xml,
 stylesld xml,
 useasdefault boolean,
 description text COLLATE pg_catalog."default",
 owner character varying(30) COLLATE pg_catalog."default",
 ui xml,
 update_time timestamp without time zone DEFAULT now(),
 CONSTRAINT layer_styles_pkey PRIMARY KEY (id)
)
WITH (
 OIDS = FALSE
)
TABLESPACE pg_default;

Then load the content into it with DB-manager