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: (view raw, whole thread or download thread mbox)
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

    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;

-- Name: test_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres


  Bruce Momjian                        |
  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


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-2017 The PostgreSQL Global Development Group