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.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"Greg Peters" <gregpeters79 (AT) gmail (DOT) com> writes:
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...8/msg00376.php
and preceding discussions in pgsql-hackers.

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

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"Thomas H." <me (AT) alternize (DOT) com> writes:
No...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster