Re: pg_Restore

On 01/20/2013 11:17 PM, bhanu udaya wrote:
> I am trying to restore 9.5G database (1GB dumpfile) which has 500
> schemas with 1 lakh rows in each schema. Could take the data dump using
> pg_dump and it takes around 40 minutes. I tried to use pg_restore to
> restore this dump, but it takes hours to restore the dump. I have used
> the configurations parameters as
....
> But, have same problem. It is almost 1 hour now, the restoration is
> still going on. After every test case execution, we would like to
> refresh the database and expected refresh should be completed in less
> than 10 minutes. Is this achievable with the kind of configuration I
> have listed in my earlier email.

Probably not what you want to hear, but I think This is a completely
unrealistic expectation. If it takes 40 minutes for pg_dump, I would
expect pg_restore to take at least as long and likely significantly
longer (assuming both are done on similar hardware).

pg_dump only has to read the schema(s) and data and write them to a
file. pg_restore has to read write the schema and data into a new
database *AND* re-create all of the indexes, analyze, check referential
integrity......

So if the dump is taking 40 minutes, I would expect the restore to take
somewhere in the 60-90 minute range, depending on the number of and
complexity of the indexing.

- Chris