Обсуждение: ineffiency of pg_restore

pg_restore give admin a lot of flexabilities, it can restore any object
from a single dump file. But if the table is very big, e.g. 2M records,
it will take a long time to restore because it use INSERTs.
I believe that if we can have following feature:
1. quick restore(use COPY not INSERTs),
2. restore related objects, e.g. when we restore a table, we can choose
   to restore its indexes, triggers and related sequence etc.

that will be great, I expect that we have them in next version of release.


test log:

su-2.05$ time psql urldb<jurloldmail.out
You are now connected as new user jliang.
CREATE

real    0m0.662s
user    0m0.045s
sys    0m0.015s
su-2.05$ time pg_restore -cvt jurloldmail -d urldb urldb.out.020810
pg_restore: connecting to database for restore
pg_restore: dropping TABLE jurloldmail
pg_restore: connecting to database urldb as user jliang
pg_restore: creating TABLE jurloldmail
pg_restore: restoring data for table jurloldmail

real    0m6.880s
user    0m0.296s
sys    0m0.028s



10 times slower!!!




Jie Liang

Jie Liang <jie@stbernard.com> writes:
> pg_restore give admin a lot of flexabilities, it can restore any object
> from a single dump file. But if the table is very big, e.g. 2M records,
> it will take a long time to restore because it use INSERTs.

This is demonstrably not the case --- at least not by default.  You
get the same commands you would've gotten from pg_dump.  Sure you
didn't create the dump file with "-d" ?

            regards, tom lane

I use:
pg_dump -Fc -d urldb > urldb.out.020810
I can make another test and get the log




Jie Liang

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, August 12, 2002 3:38 PM
To: Jie Liang
Cc: 'Bruce Momjian '; 'pgsql-admin@postgresql.org ';
'pgsql-bugs@postgresql.org'
Subject: Re: [ADMIN] ineffiency of pg_restore


Jie Liang <jie@stbernard.com> writes:
> pg_restore give admin a lot of flexabilities, it can restore any object
> from a single dump file. But if the table is very big, e.g. 2M records,
> it will take a long time to restore because it use INSERTs.

This is demonstrably not the case --- at least not by default.  You
get the same commands you would've gotten from pg_dump.  Sure you
didn't create the dump file with "-d" ?

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Jie Liang <jie@stbernard.com> writes:
> I use:
> pg_dump -Fc -d urldb > urldb.out.020810
              ^^

There you have it.  Better read the pg_dump help output again.

            regards, tom lane

Thanks Tom,

I thought that -d is the flag of dbname :(


Jie Liang




-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, August 12, 2002 3:53 PM
To: Jie Liang
Cc: 'Bruce Momjian '; 'pgsql-admin@postgresql.org ';
'pgsql-bugs@postgresql.org'
Subject: Re: [ADMIN] ineffiency of pg_restore


Jie Liang <jie@stbernard.com> writes:
> I use:
> pg_dump -Fc -d urldb > urldb.out.020810
              ^^

There you have it.  Better read the pg_dump help output again.

            regards, tom lane