Re: bloating index, pg_restore

salah jubeh <s_jubeh(at)yahoo(dot)com> wrote:

> Well my question was not very precise, the postgresql version is
> 8.3 which is not supported, so I wanted to migrate to a newer
> version which is 9.1.
>
> I have used pg_dump with -Fc option and I was monitoring the
> pg_restore activity.  Normally, the dump and restore takes from
> 30-40 minutes; but yesterday when the indexes are bloated  - I do
> not know how this could happen in one or two days, the database
> size increased from 700 MiB to 13 GiB -  the pg_restore on 9.1
> takes around 6 hours.  Since pg_restore is using insert into
> (....). How can bloated indexes affect the restore performance.
>
> I have re-indexed one table  and the size dropped to again 700
> MiB. So what could be the problem here?

You are still leaving way to much to the imagination here.

What version of pg_dump are you using for the dump?

Why are there enough dumps to have a "normallY' timing?

What is this "one or two day" gap you're talking about?  What
happened during that time?

Are you doing multiple tests with a new database to restore to each
time, dumping and restoring multiple databases within one cluster,
or what?

Without more detail, we can only guess.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company