Re: Restore performance?

On Apr 10, 2006, at 3:55 AM, Jesper Krogh wrote:

> I'd run pg_dump | gzip > sqldump.gz on the old system. That took
> about
> 30 hours and gave me an 90GB zipped file. Running
> cat sqldump.gz | gunzip | psql
> into the 8.1 database seems to take about the same time. Are there
> any tricks I can use to speed this dump+restore process up?
>
> The database contains quite alot of BLOB, thus the size.

Well, your pg_dump command lost your BLOBs since the plain text
format doesn't support them.

But once you use the -Fc format on your dump and enable blob backups,
you can speed up reloads by increasing your checkpoint segments to a
big number like 256 and the checkpoint timeout to something like 10
minutes. All other normal tuning parameters should be what you plan
to use for your normal operations, too.