Обсуждение: BUG #8400: DB size changed after restore

The following bug has been logged on the website:

Bug reference:      8400
Logged by:          Ajay
Email address:      ajayaksadvance@gmail.com
PostgreSQL version: 9.2.0
Operating system:   ubuntu
Description:

Hi,


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


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


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.


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?


I have checked rows count, random data its seems fine but why there is
difference?


How to make sure restore is done properly?


But ifi do below on 8.4 and 9.2 then i am getting different size and
obviously small on 9.2 .
SELECT pg_size_pretty(pg_total_relation_size('package')) As fulldbsize;


Please suggest.

"ajayaksadvance@gmail.com" <ajayaksadvance@gmail.com> wrote:=0A=0A> Postgre=
SQL version: 9.2.0=0A=0AThere have been many fixes for bugs and security vu=
lnerabilities in=0Athe 9.2 minor releases since 9.2.0.=0A=0Ahttp://www.post=
gresql.org/support/versioning/=0A=0A> I am not sure its a bug or not but ne=
ed some information on this.=0A=0AThere's no real evidence of a bug, it wou=
ld have been better to=0Apost a question to: pgsql-general@postgresql.org=
=0A=0A> I am doing upgrade from 8.4 to 9.2 hence DB dump and then=0A> resto=
re.=0A=0ANormally when upgrading with this technique you should use the=0Ap=
g_dump executable from the target version to dump the old=0Adatabase.=A0 Th=
e newer software understands previous storage formats,=0Abut the older soft=
ware doesn't know anything about versions that=0Awere written later.=0A=0A>=
 On postgrse 8.4 , i have DB of size 32 GB.=0A> SELECT pg_size_pretty(pg_da=
tabase_size('<DBNAME>'))=0A>=A0=A0 As fulldbsize;=0A>=0A> I have done vacum=
mdb before going for dump.=0A=0AThat will make space available for re-use, =
but generally won't=0Aeliminate dead space or reduce file sizes.=0A=0A> I h=
ave first done schema dump and then DB data and all is fine.=0A>=0A> But on=
 restore its running successfully but i am getting only=0A> size 9213 MB.=
=0A>=0A> Dont know, what exactly happend?=0A=0AWell, you could narrow the p=
ossibilities by restoring that dump to=0Aan 8.4 database and see how big th=
at is.=A0 Some major releases have=0Aimproved the storage formats for some =
data types, like numeric; but=0Ayou might just be dealing with dead space f=
rom deletes and updates.=0A=0A--=0AKevin Grittner=0AEDB: http://www.enterpr=
isedb.com=0AThe Enterprise PostgreSQL Company

On 8/30/2013 11:33 AM, Kevin Grittner wrote:
> but
> you might just be dealing with dead space from deletes and updates.


I would suggest 'likely are' rather than 'might be'.


--
john r pierce                                      37N 122W
somewhere on the middle of the left coast