Re: 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