Обсуждение: Data Loss After Restore?

I restored a 7.1.3 database to another machine which seems to have went
fine. However, the data on the disk is less than the original machine...
There is only 6.5GB in pgsql/dta/base on my test machine and the same
directory on the production machine was at 8.4GB at the time of the dump.
This was after a vacuum.

I didn't see any errors when restoring the database, so I'm confused why
the restored database takes up so much less space... Is there any reason
why this would happen? Is there any easy way to see where I may have lost
data?

Let me know if I can supply any additional info that may help.


Any help is appreciated.

On Wed, 3 Apr 2002, Trevor Astrope wrote:

> I restored a 7.1.3 database to another machine which seems to have went
> fine. However, the data on the disk is less than the original machine...
> There is only 6.5GB in pgsql/dta/base on my test machine and the same
> directory on the production machine was at 8.4GB at the time of the dump.
> This was after a vacuum.
>
> I didn't see any errors when restoring the database, so I'm confused why
> the restored database takes up so much less space... Is there any reason
> why this would happen? Is there any easy way to see where I may have lost
> data?

My initial guess is that 'dead-space' within the original files has now
been freed up by unloading and then reloading the data.  Such dead space
occurs when rows are deleted, etc.  vacuum cleans up this space.
Although I'm not sure if a vacuum would also reduce the database size
(i.e. the disk space consumed).

Trevor Astrope <astrope@e-corp.net> writes:
> I restored a 7.1.3 database to another machine which seems to have went
> fine. However, the data on the disk is less than the original machine...
> There is only 6.5GB in pgsql/dta/base on my test machine and the same
> directory on the production machine was at 8.4GB at the time of the dump.
> This was after a vacuum.

> I didn't see any errors when restoring the database, so I'm confused why
> the restored database takes up so much less space... Is there any reason
> why this would happen?

Index bloat in the old database, perhaps?

> Is there any easy way to see where I may have lost data?

Count rows in the tables, maybe.  Also, if you still have the old DB
online, comparing reltuples & relpages columns in pg_class for both
databases (after a VACUUM, to be sure they're up to date) would tell
the tale.

            regards, tom lane

Trevor Astrope wrote:

> I didn't see any errors when restoring the database, so I'm confused why
> the restored database takes up so much less space... Is there any reason
> why this would happen? Is there any easy way to see where I may have lost
> data?

You could also (if you have the disk space) export the tables
to delimited text and compare them with diff.

--Jeremy