2

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.

  1. 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?

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

  • I am not sure I have done exactly what you've stated. Please instruct if I haven't. In DB Manager, in source database, I exported layer_styles and saved in documents. In DB Manager, in target database, I imported the same file. This returns error message: " Error 6 Feature write errors: Creation error for features from #0 to #0. Provider errors was: PostGIS error while adding features: ERROR: value too long for type character varying(255) Only 0 of 10 features written." Do you know how I should proceed? The resulting layer_styles table in the target database is empty. – Demus Apr 8 at 12:59
  • you should not export the table, just load it in QGIS. Then import it in the target database, using DB-manager. When retrying, be sure to check 'Replace destination table (if exists)'. – PieterB Apr 8 at 13:01
  • I can see it now. I've done as you said and it is working perfectly. For reference for others reading this; ensure you edit the f_table_catalog column to match the name of the new database. Many thanks! – Demus Apr 8 at 15:03

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.