Re: Improve dump and restore time

Hi,

Pascal Cohen a écrit :
> I am studying how to migrate our Production Database which is running
> under PG 8.2 and we would like to move to PG 8.3
> I have read that the only safe solution is to perform a dump and restore.
> Our DB is around 6GB large.
> I wanted to have an expectation of the migration duration and performed
> it on a less powerful machine than the one we have in Production.
> Unfortunately it took very very long time (around 27 hours!).

27 hours feels really strange for only 6GB. I'm sure there's something
wrong here.

General advices. If you have big indexes, you should probably grow
maintenance_work_mem. You should also take a look at your
checkpoint_segments settings.

> Are there best practices to reduce the migration time ?

Using Slony is one. I'm not sure I would go this way for a 6GB database.
But it would assure you a really small downtime.

> Some kind of questions I try to solve:
> - Is it better to use a compressed dump or not ?

You'll loose time doing the compressed dump.

> - Should I use a kind of trick like reading dump file from network while
> restoring on the machine to reduce concurrent I/O and so on

I would find this a better way than the compressed dump.

Regards.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com