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

Re: pg_restore TODO - delay PK creation

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
Subject: Re: pg_restore TODO - delay PK creation
Date: 2004-11-01 02:36:06
Message-ID: 200411010236.iA12a6V19009@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-admin
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: IainDate: 2004-11-01 03:11:00
Subject: Re: pg_restore TODO - delay PK creation
Previous:From: IainDate: 2004-11-01 02:22:31
Subject: pg_restore TODO - delay PK creation

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