Обсуждение: pg_dump, pg_restore, insert vs copy
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
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
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