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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-admin by date

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