时间戳

首页 > 解决方案 > PostgreSQL 8.4 和 9.3

database - PostgreSQL 8.4 和 9.3

问题描述

我在将数据库备份 pg8.4 (.dump) 恢复到 pg9.3 时遇到了一些问题

当我将 8.4 恢复到 9.2 时它工作正常，但由于某种原因在 9.3 中不起作用

设置是：

Centos 6 pg8.4

Centos 7 pg9.2

Centos 7 pg9.3

所有服务器都自带 Plesk，pg9.2 是通过 Plesk 安装的，而 9.3 来自官网

所有服务器也使用 Postgis（9.2 和 9.3 中的 2.1.8 和 8.4 中的旧版本）

我尝试通过 Pgadmin 和终端恢复转储文件，9.2 和 9.3 有什么区别，为什么我无法恢复数据？

可能是因为9.2的数据文件夹在/var/lib/pgsql/data，如果通过官方网站安装在/var/lib/pgsql/9.3/data？

这些是我遇到的一些错误：

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;

我尝试在创建表之前和之后不创建表的情况下进行还原。

这是脚本的一部分：

  -- 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);

标签： databasepostgresqlpostgiscentos7centos6

解决方案

没错，这是意料之中的。

您必须使用pg_dump9.3 的可执行文件转储 8.4 数据库。

9.3 知道如何转储 8.4 数据库，以便在 9.3 中加载转储。

推荐阅读