Thread: BUG #2781: database dump/restore problems

The following bug has been logged online:

Bug reference:      2781
Logged by:          Greg Peters
Email address:      gregpeters79@gmail.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.

"Greg Peters" <gregpeters79@gmail.com> writes:
> 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:

This is an intentional change that fixes a lot of corner cases such as
renamed sequences.  The former behavior looked simple, perhaps, but it
failed outright in too many scenarios.  See
http://archives.postgresql.org/pgsql-committers/2006-08/msg00376.php
and preceding discussions in pgsql-hackers.

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

We have never promised backward compatibility of pg_dump output to older
server versions.

            regards, tom lane

> We have never promised backward compatibility of pg_dump output to older
> server versions.

regarding pg_dump: where there some changes from b3 to rc1 that would
explain the resulting rc1 pg_dump output (-c) being half as big as with b3?
i've rerun pg_dump several times with the same result, and no error
messages.

- thomas

"Thomas H." <me@alternize.com> writes:
> regarding pg_dump: where there some changes from b3 to rc1 that would
> explain the resulting rc1 pg_dump output (-c) being half as big as with b3?

No...

            regards, tom lane

>> regarding pg_dump: where there some changes from b3 to rc1 that would
>> explain the resulting rc1 pg_dump output (-c) being half as big as with
>> b3?
>
> No...
>
> regards, tom lane
>

well, it was 300mb before rc1, and now its only 188mb. inbetween i did a
vacuum full on one table. that shoulnd't affect backup size, should it? i'll
restore one of the new backups later on to be sure all data is still there.

- thomas