Re: RESTORE IS TO SLOW

On Thu, Sep 15, 2011 at 4:12 PM, marvin.deoliveira
<marvin(dot)deoliveira(at)gmail(dot)com> wrote:
> I'm using postgres 9.0.2 32 bits on Debian 5.
> The hardware is a pc with 2 GB RAM, with 2 sata disks. Well, that's what I
> have at the moment.
>
> The restore was started like:  pg_restore -U postgres --data-only
> --disable-triggers -v /bck/<dump file>.sql -d <database>
> The pg_restore shows:
> pg_restore: disabling triggers for <table name>
> pg_restore: restoring data for table "<table name>"  <- that's taking more
> time that I'd like.
> pg_restore: enabling triggers for <table name>
>
> I've read the posts that you send, and saw a lot of things that I didn't do.
> I'm pretty sure that I'm going to have much better performance after change
> the parameters.
> But I still have a doubt: Droping the indexes, could give me more
> performance?

The output that you included doesn't show it taking a long time on
indices. Regardless, assuming that this PC has more than a single CPU
core, you should make use of the -j option to better parallelize the
import. Beyond that, you should attempt to determine where the
bottleneck is in the import. Is it CPU bound or disk(IO) bound?

You stated that you have two disks. Hopefully your dumpfile is not on
the same disk as $PGDATA, or its going to have to read & write the
same disk, which is definitely going to hurt performance.