Re: BUG #8400: DB size changed after restore

"ajayaksadvance(at)gmail(dot)com" <ajayaksadvance(at)gmail(dot)com> wrote:

> PostgreSQL version: 9.2.0

There have been many fixes for bugs and security vulnerabilities in
the 9.2 minor releases since 9.2.0.

http://www.postgresql.org/support/versioning/

> I am not sure its a bug or not but need some information on this.

There's no real evidence of a bug, it would have been better to
post a question to: pgsql-general(at)postgresql(dot)org

> I am doing upgrade from 8.4 to 9.2 hence DB dump and then
> restore.

Normally when upgrading with this technique you should use the
pg_dump executable from the target version to dump the old
database.  The newer software understands previous storage formats,
but the older software doesn't know anything about versions that
were written later.

> On postgrse 8.4 , i have DB of size 32 GB.
> SELECT pg_size_pretty(pg_database_size('<DBNAME>'))
>   As fulldbsize;
>
> I have done vacummdb before going for dump.

That will make space available for re-use, but generally won't
eliminate dead space or reduce file sizes.

> I have first done schema dump and then DB data and all is fine.
>
> But on restore its running successfully but i am getting only
> size 9213 MB.
>
> Dont know, what exactly happend?

Well, you could narrow the possibilities by restoring that dump to
an 8.4 database and see how big that is.  Some major releases have
improved the storage formats for some data types, like numeric; but
you might just be dealing with dead space from deletes and updates.

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