postgreSQL 8.4 and 9.3

Question

I'm having some problem in restoring a DB backup pg8.4 (.dump) into pg9.3

It works ok when I restore the 8.4 to the 9.2 but for some reason doesn't work in 9.3

The setup are:

Centos 6 pg8.4

Centos 7 pg9.2

Centos 7 pg9.3

All the server comes with Plesk, pg9.2 is installed through Plesk while the 9.3 from the official site

All the server also use Postgis (2.1.8 in 9.2 and 9.3 and an older version in 8.4)

I tried to restore the dump file through Pgadmin and through terminal, what is the difference between 9.2 and 9.3 why I can't restore the data?

Can be because of the 9.2 data folder is in /var/lib/pgsql/data and if installed through the official site is in /var/lib/pgsql/9.3/data?

these are some of the error I have:

pg_restore: creating SEQUENCE OWNED BY "public.mordersl_ogc_fid_seq"
pg_restore: creating TABLE "public.mordersp"
pg_restore: [archiver (db)] Error from TOC entry 160; 1259 18553 TABLE mordersp postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "mordersp" already exists
    Command was: CREATE TABLE mordersp (
    ogc_fid integer NOT NULL,
    wkb_geometry geometry(Geometry,27700),
    item_ref numeric(254,0)...
pg_restore: creating SEQUENCE "public.mordersp_ogc_fid_seq"
pg_restore: [archiver (db)] Error from TOC entry 159; 1259 18551 SEQUENCE mordersp_ogc_fid_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "mordersp_ogc_fid_seq" already exists
    Command was: CREATE SEQUENCE mordersp_ogc_fid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

pg_restore: creating SEQUENCE OWNED BY "public.docstore_ogc_fid_seq"
pg_restore: creating VIEW "public.geography_columns"
pg_restore: [archiver (db)] Error from TOC entry 143; 1259 17626 VIEW geography_columns postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "geography_columns" already exists
    Command was: CREATE VIEW geography_columns AS
SELECT current_database() AS f_table_catalog, n.nspname AS f_table_schema, c.relname AS f_t...
pg_restore: creating VIEW "public.geometry_columns"
pg_restore: [archiver (db)] Error from TOC entry 144; 1259 17730 VIEW geometry_columns postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "geometry_columns" already exists
    Command was: CREATE VIEW geometry_columns AS
SELECT (current_database())::character varying(256) AS f_table_catalog, (n.nspname)::charact...
pg_restore: creating TABLE "public.mapscheds"
pg_restore: [archiver (db)] Error from TOC entry 154; 1259 18517 TABLE mapscheds postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "mapscheds" already exists
    Command was: CREATE TABLE mapscheds (
    ogc_fid integer NOT NULL,
    wkb_geometry geometry(Geometry,27700),
    msid numeric(254,0),
 ...
pg_restore: creating SEQUENCE "public.mapscheds_ogc_fid_seq"
pg_restore: [archiver (db)] Error from TOC entry 153; 1259 18515 SEQUENCE mapscheds_ogc_fid_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "mapscheds_ogc_fid_seq" already exists
    Command was: CREATE SEQUENCE mapscheds_ogc_fid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

pg_restore: creating SEQUENCE OWNED BY "public.mapscheds_ogc_fid_seq"
pg_restore: creating TABLE "public.mapschedsback"
pg_restore: [archiver (db)] Error from TOC entry 165; 1259 191782 TABLE mapschedsback postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "mapschedsback" already exists
    Command was: CREATE TABLE mapschedsback (
    ogc_fid integer,
    wkb_geometry geometry(Geometry,27700),
    msid numeric(254,0),
    ms...
pg_restore: creating TABLE "public.minv"
pg_restore: [archiver (db)] Error from TOC entry 156; 1259 18529 TABLE minv postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "minv" already exists
    Command was: CREATE TABLE minv (
    ogc_fid integer NOT NULL,
    wkb_geometry geometry(Geometry,27700),
    item_ref numeric(254,0),
  ...
pg_restore: creating SEQUENCE "public.minv_ogc_fid_seq"
pg_restore: [archiver (db)] Error from TOC entry 155; 1259 18527 SEQUENCE minv_ogc_fid_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "minv_ogc_fid_seq" already exists
    Command was: CREATE SEQUENCE minv_ogc_fid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

I tried to restore without creating the table before and after creating the table.

this is a part of the script:

  -- Table: ordersl

-- DROP TABLE ordersl;

CREATE TABLE ordersl
(
  ogc_fid serial NOT NULL,
  wkb_geometry geometry(Geometry,27700),
  item_ref numeric(254,0),
  order_ref character varying(32),
  locality character varying(30),
  ....
  pm_id numeric(254,0),
  order_id numeric(254,0),
  order_doc character varying(254),
  ms_grid character varying(20),
  CONSTRAINT ordersl_pk PRIMARY KEY (ogc_fid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE ordersl
  OWNER TO postgres;

-- Index: ordersl_geom_idx

-- DROP INDEX ordersl_geom_idx;

CREATE INDEX ordersl_geom_idx
  ON ordersl
  USING gist
  (wkb_geometry);

How are you restoring, what exactly is the problem? Any specific error messages? — deceze

Laurenz Albe Laurenz Albe · Accepted Answer · 2018-07-20T14:57:28

Right, that's expected.

You have to dump the 8.4 database with the pg_dump executable from 9.3.

9.3 knows how to dump an 8.4 database so that the dump loads in 9.3.

postgreSQL 8.4 and 9.3

1 Answers