[ot] pg_restore > ascii dump size

Luis Motta Campos luismottacampos at yahoo.co.uk
Wed Nov 22 17:27:51 GMT 2006

Raf wrote:
> Perly People,
> I don't like postgres very much.

  I would suggest you to like girls, but this is only my personal

> We had a really large database which wasn't being vacuumed.  We
> attempted to perform a full vacuum on it and this stalled.  Taking an
> alternative approach, we made a full pg_dump of the database and
> dropped the original. Fine.  This dump, in ascii and uncompressed,
> totalled about 4GB; when compared to the original 20GB (not my fault,
> I'm new here) being taken up - one should get quite excited.
> Fine.  Now we pg_restored that dump.  It's still restoring and the
> size of the physical database now exceeds 15GB again.  This is from a
> 4GB dump. I don't think that the DB is so badly designed that this
> would result from badly chosen data types in the schema.

  This could result from many things - the most important is indexes.
  How many indexes have you dropped, and of which type?
  It seems that you database is about 75% composed of indexes.

  [several assumptions about your operating system and filesystem here]

  Hope you ensured you got a complete backup *before* dropping the old
database. I hate when dumped data fills in the 4GB standard limits on
some linuxes and one screws the database for only having a (silently)
truncated database dump file to restore it.

Luis Motta Campos
Senior System Engineer at Segula.FR
Hobbyist Cooker and Photograph

More information about the london.pm mailing list