Обсуждение: 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

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

"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

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

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)

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

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