Copy the layer_styles table from one database to another?

Question

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?

Answer 1

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