Skip site navigation (1) Skip section navigation (2)

BUG #2781: database dump/restore problems

From: "Greg Peters" <gregpeters79(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2781: database dump/restore problems
Date: 2006-11-26 11:38:56
Message-ID: 200611261138.kAQBcuEJ021648@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      2781
Logged by:          Greg Peters
Email address:      gregpeters79(at)gmail(dot)com
PostgreSQL version: 8.1/8.2beta3
Operating system:   WInXP
Description:        database dump/restore problems
Details: 

Hello,

I recently performed a database dump with 8.2b3 for a complete database. An
example of the SQL output for a single table is below:

CREATE TABLE admin_field_list (
    "key" bigint NOT NULL,
    field character varying(25) NOT NULL,
    added_by character varying(25) NOT NULL,
    add_date timestamp without time zone DEFAULT now() NOT NULL,
    mod_date timestamp without time zone,
    modified_by character varying(25)
);


ALTER TABLE public.admin_field_list OWNER TO root;

--
-- TOC entry 1351 (class 1259 OID 16425)
-- Dependencies: 1352 4
-- Name: admin_field_list_key_seq; Type: SEQUENCE; Schema: public; Owner:
root
--

CREATE SEQUENCE admin_field_list_key_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

As you can see, the primary key is exported as a bigint, with a separate
section for the sequence. This differs to the way 8.1 dumps the same table
below:

CREATE TABLE admin_field_list (
    "key" bigserial NOT NULL,
    field character varying(25) NOT NULL,
    added_by character varying(25) NOT NULL,
    add_date timestamp without time zone DEFAULT now() NOT NULL,
    mod_date timestamp without time zone,
    modified_by character varying(25)
);

I then tried to restore the DB dumped by 8.2b3 into 8.1, with no success. It
didn't like the sequence part. This probably isn't so much a bug, but more a
backwards compatibility issue. Also, it seems strange to make the newer
database dumps more verbose and complicated. Surely the simple "bigserial"
datatype is a better method, and easier to implement then the sequence
statements?

Regards,

Greg.

Responses

pgsql-bugs by date

Next:From: Alaa El GoharyDate: 2006-11-26 13:58:37
Subject: BUG #2782: Too Many open files in system
Previous:From: Chris JonesDate: 2006-11-26 08:21:46
Subject: Re: BUG #2769: "invalid memory alloc request size <n>" on

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group