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

pg_dump, pg_restore, insert vs copy

From: "Lee Wu" <Lwu(at)mxlogic(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: pg_dump, pg_restore, insert vs copy
Date: 2005-03-24 15:22:59
Message-ID: ECAB83AA52BCC043A0E24BBC00001024D2E6BC@mxhq-exch.corp.mxlogic.com (view raw or flat)
Thread:
Lists: pgsql-admin
Hi,

 

When I use pg_dump to back up the whole database and then pg_restore an
individual table, 

pg_restore uses COPY. Great.

 

When I use pg_dump to back up an individual table and pg_restore it,
pg_restore uses INSERT. 

 

 

Method 1:

pg_dump mydb -R -Fc --compress=9 > method1.dmp

pg_restore -t mytable -d mydb -R method1.dmp

I will see COPY in both OS and SQL.

 

Method 2:

pg_dump mydb -t mytable -R -Fc --compress=9 > method2.dmp

pg_restore -d mydb -R method2.dmp

I will see INSERT in both OS and SQL.

 

Is this an expected behavior?  

 

This is a big table with million rows in a big database. How can I speed
up individual table back and restore

if this assessment is true?

 

My PG version is 7.3.2 (I know, I know, it'd old...)

 

Thanks,

 

 

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2005-03-24 15:52:06
Subject: Re: pg_dump, pg_restore, insert vs copy
Previous:From: Tom LaneDate: 2005-03-24 14:49:16
Subject: Re: Why this Query Plan is different

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