Re: pg_restore TODO - delay PK creation - Mailing list pgsql-admin

Hi Bruce,

OK, I've just looked into it and you are right, thanks.

In the case I just tested I restored a 7.1 dump to a 7.4.6 db. I had assumed
that this is a restore time issue but in fact it is dependent on the format
of the dump file. I noticed the problem after the data load failed and I
checked the description of the table in question, it had a primary key.

Worth noting for anybody preparing to upgrade large databases from old
versions. This db takes 8 hours to restore on 7.1 but I just dumped it and
restored it in less than 20 minutes total on 7.4 :)

I havn't used 7.4 for dumping/restoring for a long time so I had forgotten
how it worked. We're just starting a redevelopment that will include an
upgrade of the production db so I'm looking forward to working with 7.4
again, though I'd like to be working on v8.

Out of interest, what is the word on using newer versions of pg_dump on
older verisons of  the DB - is it is possible or even wise to unload a 7.1
DB with the 7.4 version of pg?

Regards
iain



----- Original Message -----
From: "Bruce Momjian" <pgman@candle.pha.pa.us>
To: "Iain" <iain@mst.co.jp>
Cc: <pgsql-admin@postgresql.org>
Sent: Monday, November 01, 2004 11:36 AM
Subject: Re: [ADMIN] pg_restore TODO - delay PK creation


> Iain wrote:
>> Hi,
>>
>> I'm wondering if this is already on some todo list for pg_restore but I
>> didn't find any mention of it anywhere, so I thought I should post this
>> and
>> see what people think..
>>
>> Basically, I'd like to see an option at restore time to not include the
>> primary key constraint when issuing the create table command. I'd like
>> the
>> PK to be added after data has been loaded using an ALTER command.
>>
>> The principle reason for this is performance.
>>
>> There may also be a bug somewhere, or perhaps just a problem with my
>> system,
>> but I was trying to restore a fairly large table (over 7000000 rows)
>> which
>> would run for a couple hours before failing. Dropping the PK enabled the
>> load to complete in 3 or 4 minutes. Adding the PK took another 3 or 4
>> minutes which adds up to quite a difference.
>
> I don't know what PostgreSQL version you have but we currently do what
> you suggest and I think have been doing it for a few releases now:
>
> ---------------------------------------------------------------------------
>
> --
> -- Name: test; Type: TABLE; Schema: public; Owner: postgres
> --
>
> CREATE TABLE test (
>    x integer NOT NULL
> );
>
>
> ALTER TABLE public.test OWNER TO postgres;
>
> --
> -- Data for Name: test; Type: TABLE DATA; Schema: public; Owner: postgres
> --
>
> COPY test (x) FROM stdin;
> 1
> \.
>
>
> --
> -- Name: test_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
> --
>
> ALTER TABLE ONLY test
>    ADD CONSTRAINT test_pkey PRIMARY KEY (x);
>
>
>
> --
>  Bruce Momjian                        |  http://candle.pha.pa.us
>  pgman@candle.pha.pa.us               |  (610) 359-1001
>  +  If your life is a hard drive,     |  13 Roberts Road
>  +  Christ can be your backup.        |  Newtown Square, Pennsylvania
> 19073