Thread: Excessive space allocations in Postgresql 9.1.6 system files causing the file system to run out of space.
<span style=3D"font-family:Verdana; color:#000000; font-size:10= pt;">On tuesday, February 26, 2013, Freddie Burgess wrote:=0A<di= v> =0AWe have a Postgres database that was recently upgrade= d from 8.4.3 to 9.1.6. We have noticed unusual growth in the data fil= es and generated a script to perform the following actions.1. Query pg_= class for all records2. Generate a file listing of all postgres data fi= les3. Compare the two lists and eliminate all files that are contained = within pg_class There are 17359 data files. After runnin= g the script, there are 5802 data files remaining that are not listed in pg= _class. Due to the size of the (5802) data files (~4TB), I am not com= fortable about deleting them from the file system. Does postgres 9.1.= 6 catalog every data file in pg_class? Or does it leave some da= ta files off of this table? If so, how can I determine if I have stal= e, unnecessary data files on my file system?
Re: Excessive space allocations in Postgresql 9.1.6 system files causing the file system to run out of space.
From
Kevin Grittner
Date:
"fburgess@radiantblue.com" <fburgess@radiantblue.com> wrote:=0A=0A> We have= a Postgres database that was recently upgraded from 8.4.3=0A> to 9.1.6.=A0= We have noticed unusual growth in the data files and=0A> generated a scrip= t to perform the following actions.=0A=0A> 1. Query pg_class for all record= s=0A> 2. Generate a file listing of all postgres data files=0A> 3. Compare = the two lists and eliminate all files that are=0A>=A0=A0=A0 contained withi= n pg_class=0A> =0A> There are 17359 data files.=A0 After running the script= , there are=0A> 5802 data files remaining that are not listed in pg_class.= =A0 Due=0A> to the size of the (5802) data files (~4TB), I am not comfortab= le=0A> about deleting them from the file system.=A0 Does postgres 9.1.6=0A>= catalog every data file in pg_class?=A0=A0 Or does it leave some data=0A> = files off of this table?=A0 If so, how can I determine if I have=0A> stale,= unnecessary data files on my file system?=0A=0AYeah, it's good to be cauti= ous -- deleting a needed file can render=0Ayour database cluster unusable.= =A0 Be sure you have a good backup you=0Acan go back to if you delete the w= rong thing.=0A=0AWhat directories are you looking in?=0A=0AFor a database o= r tablespace directory, are you excluding all files=0Awhich start with a fi= lename you derived from pg_class and has a dot=0Aor underscore followed by = more characters?=0A=0A--=0AKevin Grittner=0AEnterpriseDB: http://www.enterp= risedb.com=0AThe Enterprise PostgreSQL Company