BUG #2781: database dump/restore problems

Started by Greg Petersover 19 years ago5 messagesbugs
Jump to latest
#1Greg Peters
gregpeters79@gmail.com

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.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Greg Peters (#1)
Re: BUG #2781: database dump/restore problems

"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

#3Thomas H.
me@alternize.com
In reply to: Greg Peters (#1)
Re: BUG #2781: database dump/restore problems

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

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: Thomas H. (#3)
Re: BUG #2781: database dump/restore problems

"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

#5Thomas H.
me@alternize.com
In reply to: Greg Peters (#1)
Re: BUG #2781: database dump/restore problems

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