Thanks for the reply, 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? Thanks ________________________________ From: Tom Lane <t...@sss.pgh.pa.us> To: Sergey Konoplev <gray...@gmail.com> Cc: salah jubeh <s_ju...@yahoo.com>; pgsql <pgsql-general@postgresql.org> Sent: Thursday, March 28, 2013 5:08 AM Subject: Re: [GENERAL] bloating index, pg_restore Sergey Konoplev <gray...@gmail.com> writes: > On Wed, Mar 27, 2013 at 9:56 AM, salah jubeh <s_ju...@yahoo.com> wrote: >> I have a database which is bloated because of vacuum full, so you find >> indexes bigger than the table itself. > Table can not be bloated because of vacuum full, it removes bloat from > the table and its indexes. Um, well, that depends a lot on which PG version the OP is running (which he didn't say). The pre-9.0 implementation of VACUUM FULL was notorious for creating index bloat, because it shuffled heap entries around to compact heap space, but created an additional index entry for each such heap-tuple motion. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
