pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump, pg_restore, insert vs copy
9+ messages / 4 participants
[nested] [flat]
* pg_dump, pg_restore, insert vs copy
@ 2005-03-24 15:22 Lee Wu <Lwu@mxlogic.com>
2005-03-24 15:52 ` Re: pg_dump, pg_restore, insert vs copy Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Lee Wu @ 2005-03-24 15:22 UTC (permalink / raw)
To: 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,
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pg_dump, pg_restore, insert vs copy
2005-03-24 15:22 pg_dump, pg_restore, insert vs copy Lee Wu <Lwu@mxlogic.com>
@ 2005-03-24 15:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-24 16:44 ` Re: pg_dump, pg_restore, insert vs copy Alvaro Herrera <alvherre@dcc.uchile.cl>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2005-03-24 15:52 UTC (permalink / raw)
To: Lee Wu <Lwu@mxlogic.com>; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pg_dump, pg_restore, insert vs copy
2005-03-24 15:22 pg_dump, pg_restore, insert vs copy Lee Wu <Lwu@mxlogic.com>
2005-03-24 15:52 ` Re: pg_dump, pg_restore, insert vs copy Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-03-24 16:44 ` Alvaro Herrera <alvherre@dcc.uchile.cl>
2005-03-24 16:51 ` Re: pg_dump, pg_restore, insert vs copy Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Alvaro Herrera @ 2005-03-24 16:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Lee Wu <Lwu@mxlogic.com>; pgsql-admin
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pg_dump, pg_restore, insert vs copy
2005-03-24 15:22 pg_dump, pg_restore, insert vs copy Lee Wu <Lwu@mxlogic.com>
2005-03-24 15:52 ` Re: pg_dump, pg_restore, insert vs copy Tom Lane <tgl@sss.pgh.pa.us>
2005-03-24 16:44 ` Re: pg_dump, pg_restore, insert vs copy Alvaro Herrera <alvherre@dcc.uchile.cl>
@ 2005-03-24 16:51 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2005-03-24 16:51 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@dcc.uchile.cl>; +Cc: Lee Wu <Lwu@mxlogic.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pg_dump, pg_restore, insert vs copy
@ 2005-03-24 16:05 Lee Wu <Lwu@mxlogic.com>
2005-03-24 16:10 ` Re: pg_dump, pg_restore, insert vs copy Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Lee Wu @ 2005-03-24 16:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
OK, here is what I see:
from top:
17718 postgres 25 0 293M 293M 292M R 22.2 7.6 28:26 2
postgres: postgres mydb 127.0.0.1:41972 INSERT
MY OS command is:
pg_restore -v -t mytable -d mydb -U postgres -R my.dmp
SQL from database:
mydb=# select datname, procpid, current_query
mydb-# from pg_stat_activity
mydb-# where current_query != '<IDLE>';
datname | procpid |
current_query
---------+---------+----------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
-------------------------------------------------------------
mydb | 17718 | INSERT INTO mytable VALUES (...
(1 row)
Thanks,
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, March 24, 2005 8:52 AM
To: Lee Wu
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] pg_dump, pg_restore, insert vs copy
"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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pg_dump, pg_restore, insert vs copy
2005-03-24 16:05 Re: pg_dump, pg_restore, insert vs copy Lee Wu <Lwu@mxlogic.com>
@ 2005-03-24 16:10 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2005-03-24 16:10 UTC (permalink / raw)
To: Lee Wu <Lwu@mxlogic.com>; +Cc: pgsql-admin
"Lee Wu" <Lwu@mxlogic.com> writes:
> MY OS command is:
> pg_restore -v -t mytable -d mydb -U postgres -R my.dmp
That doesn't prove a thing; the question is what you typed at pg_dump.
Thinking about it, I wonder if you did "pg_dump -d mydb ..."
-d means something different to pg_dump than pg_restore.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pg_dump, pg_restore, insert vs copy
@ 2005-03-24 16:35 Lee Wu <Lwu@mxlogic.com>
2005-03-25 03:57 ` Re: pg_dump, pg_restore, insert vs copy Uwe C. Schroeder <uwe@oss4u.com>
0 siblings, 1 reply; 9+ messages in thread
From: Lee Wu @ 2005-03-24 16:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
I believe you're right.
Any easy way to find out if -d was used if dump is done someone else?
Thanks,
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, March 24, 2005 9:11 AM
To: Lee Wu
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] pg_dump, pg_restore, insert vs copy
"Lee Wu" <Lwu@mxlogic.com> writes:
> MY OS command is:
> pg_restore -v -t mytable -d mydb -U postgres -R my.dmp
That doesn't prove a thing; the question is what you typed at pg_dump.
Thinking about it, I wonder if you did "pg_dump -d mydb ..."
-d means something different to pg_dump than pg_restore.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pg_dump, pg_restore, insert vs copy
2005-03-24 16:35 Re: pg_dump, pg_restore, insert vs copy Lee Wu <Lwu@mxlogic.com>
@ 2005-03-25 03:57 ` Uwe C. Schroeder <uwe@oss4u.com>
0 siblings, 0 replies; 9+ messages in thread
From: Uwe C. Schroeder @ 2005-03-25 03:57 UTC (permalink / raw)
To: Lee Wu <Lwu@mxlogic.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
On Thursday 24 March 2005 08:35, Lee Wu wrote:
> I believe you're right.
>
> Any easy way to find out if -d was used if dump is done someone else?
yes, just look into your dump file. If it contains INSERT statements -d was
used, if it contains COPY statement -d was not used.
>
> Thanks,
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, March 24, 2005 9:11 AM
> To: Lee Wu
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] pg_dump, pg_restore, insert vs copy
>
> "Lee Wu" <Lwu@mxlogic.com> writes:
> > MY OS command is:
> > pg_restore -v -t mytable -d mydb -U postgres -R my.dmp
>
> That doesn't prove a thing; the question is what you typed at pg_dump.
>
> Thinking about it, I wonder if you did "pg_dump -d mydb ..."
> -d means something different to pg_dump than pg_restore.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pg_dump, pg_restore, insert vs copy
@ 2005-03-25 04:41 Lee Wu <Lwu@mxlogic.com>
0 siblings, 0 replies; 9+ messages in thread
From: Lee Wu @ 2005-03-25 04:41 UTC (permalink / raw)
To: Uwe C. Schroeder <uwe@oss4u.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
The dump file is binary format when using:
pg_dump -Fc --compress=9
________________________________
From: Uwe C. Schroeder [mailto:uwe@oss4u.com]
Sent: Thu 3/24/2005 8:57 PM
To: Lee Wu
Cc: Tom Lane; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] pg_dump, pg_restore, insert vs copy
On Thursday 24 March 2005 08:35, Lee Wu wrote:
> I believe you're right.
>
> Any easy way to find out if -d was used if dump is done someone else?
yes, just look into your dump file. If it contains INSERT statements -d was
used, if it contains COPY statement -d was not used.
>
> Thanks,
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, March 24, 2005 9:11 AM
> To: Lee Wu
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] pg_dump, pg_restore, insert vs copy
>
> "Lee Wu" <Lwu@mxlogic.com> writes:
> > MY OS command is:
> > pg_restore -v -t mytable -d mydb -U postgres -R my.dmp
>
> That doesn't prove a thing; the question is what you typed at pg_dump.
>
> Thinking about it, I wonder if you did "pg_dump -d mydb ..."
> -d means something different to pg_dump than pg_restore.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2005-03-25 04:41 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-24 15:22 pg_dump, pg_restore, insert vs copy Lee Wu <Lwu@mxlogic.com>
2005-03-24 15:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-24 16:44 ` Alvaro Herrera <alvherre@dcc.uchile.cl>
2005-03-24 16:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-24 16:05 Re: pg_dump, pg_restore, insert vs copy Lee Wu <Lwu@mxlogic.com>
2005-03-24 16:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-24 16:35 Re: pg_dump, pg_restore, insert vs copy Lee Wu <Lwu@mxlogic.com>
2005-03-25 03:57 ` Uwe C. Schroeder <uwe@oss4u.com>
2005-03-25 04:41 Re: pg_dump, pg_restore, insert vs copy Lee Wu <Lwu@mxlogic.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox