What would account for the difference in table sizes between one PostgreSQL database and another restored from its dump file?

On an Ubuntu 12.04 host, I'm running PostgreSQL 9.1, and I dumped my database using:

pg_dump -c -U myuser --blobs --format=c mydb -n public > mydb.dump

Prior to the dump, with autovacuum enabled, the table mytable was listed in pg_tables as being 31GB in size and has 21961903 rows.

I loaded this dump file onto a separate Ubuntu 12.04 host, using:

pg_restore --jobs=8 -U postgres --create --dbname=mydb mydb.dump

It loaded mostly fine, only reporting one error creating an index because of an "unexpected end of data" on table mytable, which it says it ignored. However, pg_table reports that the size of mytable is only 7.7GB but still has 21961903 rows.

Why the disparity in table size measurements? Should I be concerned with the error creating the index on my table?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.