From: Glyn Astill <glynastill@yahoo.co.uk>
To: Kenneth Marshall <ktm@rice.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: Bloated indexes from pg_restore? (Was: Index fillfactor changed in pg9?)
Date: Mon, 4 Apr 2011 16:28:58 +0100 (BST)
Message-ID: <669511.20278.qm@web26006.mail.ukl.yahoo.com> (raw)
In-Reply-To: <983729.42184.qm@web26003.mail.ukl.yahoo.com>
--- On Fri, 1/4/11, Glyn Astill <glynastill@yahoo.co.uk> wrote:
> --- On Fri, 1/4/11, Kenneth Marshall wrote:
> What
> > happens
> > if you run a REINDEX on both DB's to the index sizes?
> >
> > Regards,
> > Ken
> >
>
> Sorry for the lack of info there. Both are 64 bit, both
> have ext3 filesystems set up the same, the 8.4 machine is on
> kernel 2.6.26 whereas the 9.0 machine is on 2.6.32.
>
> REINDEX does indeed decreace the size. I guess the
> question is why does pg_restore create them bloated? Could
> it be the parrallel (-j) option?
>
So it appears now that if I restore the database using pg_restore, I end up with bloated indexes, which are fixed with a vacuum full.
The dump is a data only dump with the -Fc flag, taken with pg_dump as follows
pg_dump -Fc mydatabase -U postgres -h localhost -a --disable-triggers -f data-dump.gz
That appears to restore with COPY, using the following
pg_restore -U postgres --disable-triggers -c -d mydatabase data-dump.gz
I'm a bit perplexed by this
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: glynastill@yahoo.co.uk, ktm@rice.edu
Subject: Re: Bloated indexes from pg_restore? (Was: Index fillfactor changed in pg9?)
In-Reply-To: <669511.20278.qm@web26006.mail.ukl.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox