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,

"Lee Wu" <Lwu@mxlogic.com> writes:
> 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.

Not for me...

That decision is fixed at pg_dump time; it's not possible for pg_restore
to change it, because the data is already that way (or not) in the dump
file. Maybe you misinterpreted what you saw?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Thu, Mar 24, 2005 at 10:52:06AM -0500, Tom Lane wrote:
> "Lee Wu" <Lwu@mxlogic.com> writes:
> > 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.
>
> Not for me...
>
> That decision is fixed at pg_dump time; it's not possible for pg_restore
> to change it, because the data is already that way (or not) in the dump
> file. Maybe you misinterpreted what you saw?

Is there any reason why we don't use a binary storage in custom format
dumps? I mean, we could open a binary cursor and write the results to
the file, and read it back at restore time. This is just handwaving of
course.

I guess the reason is cross-version portability?

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"World domination is proceeding according to plan" (Andrew Morton)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> Is there any reason why we don't use a binary storage in custom format
> dumps? I mean, we could open a binary cursor and write the results to
> the file, and read it back at restore time. This is just handwaving of
> course.

> I guess the reason is cross-version portability?

Cross-platform portability, too.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly