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

Re: pg_restore TODO - delay PK creation

From: "Iain" <iain(at)mst(dot)co(dot)jp>
To: "Bruce Momjian" <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: pg_restore TODO - delay PK creation
Date: 2004-11-01 03:11:00
Message-ID: 017e01c4bfc0$6d17d140$7201a8c0@mst1x5r347kymb (view raw or flat)
Thread:
Lists: 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(at)candle(dot)pha(dot)pa(dot)us>
To: "Iain" <iain(at)mst(dot)co(dot)jp>
Cc: <pgsql-admin(at)postgresql(dot)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(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
>  +  If your life is a hard drive,     |  13 Roberts Road
>  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 
> 19073 


In response to

Responses

pgsql-admin by date

Next:From: Bruce MomjianDate: 2004-11-01 03:25:24
Subject: Re: pg_restore TODO - delay PK creation
Previous:From: Bruce MomjianDate: 2004-11-01 02:36:06
Subject: Re: pg_restore TODO - delay PK creation

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