Skip site navigation (1) Skip section navigation (2)

Re: pg 7.4.x - pg_restore impossibly slow

From: "patrick keshishian" <pkeshish(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: pg 7.4.x - pg_restore impossibly slow
Date: 2006-04-14 01:26:00
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Hi Tom,,

So I changed the following settings in postgresql.conf file and
restarted PostgreSQL and then proceeded with pg_restore:

# new changes for this test-run
log_statement = true
sort_mem = 10240 # default 1024
vacuum_mem = 20480 # default 8192
# from before
checkpoint_segments = 10
log_pid = true
log_timestamp = true

With these settings and running:

pg_restore -vaOd dbname dbname.DUMP

Things seem to progress better. The first of the large
tables got COPY'ed within 1 hr 40 min:

start: 2006-04-13 11:44:19
finish: 2006-04-13 13:25:36

I ended up ctrl-C'ing out of the pg_restore as the second
large table was taking over 3 hours and the last PostgreSQL
log entry was from over 2.5hrs ago, with message:

2006-04-13 14:09:29 [3049] LOG:  recycled transaction log file

Time for something different. Before attempting the same
procedure with fsync off, I ran the following sequence of

$ dropdb dbname
$ createdb dbname
$ pg_restore -vsOd dbname dbname.DUMP
$ date > db.restore ; pg_restore -vcOd dbname \
    dbname.DUMP ; date >> db.restore
$ cat db.restore
Thu Apr 13 18:02:51 PDT 2006
Thu Apr 13 18:17:16 PDT 2006

That's just over 14 minutes!


Is this because the -c option drops all foreign keys and
so the restore goes faster?  Should this be the preferred,
recommended and documented method to run pg_restore?
Any drawbacks to this method?


On 4/12/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "patrick keshishian" <pkeshish(at)gmail(dot)com> writes:
> > My dev box is much slower hardware than the customer's
> > server.  Even with that difference I expected to be able to
> > pg_restore the database within one day.  But no.
> Seems a bit odd.  Can you narrow down more closely which step of the
> restore is taking the time?  (Try enabling log_statements.)
> One thought is that kicking up work_mem and vacuum_mem is likely to
> help for some steps (esp. CREATE INDEX and foreign-key checking).
> And be sure you've done the usual tuning for write-intensive activity,
> such as bumping up checkpoint_segments.  Turning off fsync wouldn't
> be a bad idea either.
>                         regards, tom lane

In response to


pgsql-performance by date

Next:From: Tom LaneDate: 2006-04-14 03:15:17
Subject: Re: pg 7.4.x - pg_restore impossibly slow
Previous:From: Tom LaneDate: 2006-04-14 00:43:54
Subject: Re: pgmemcache

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group