Re: 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;">Not sure why my emails replies went out in HTML fo=
rmat, I'm re-sending the email trail to date. Thanks Andres for pointing th=
is out.=0A =0AFreddie=0A =
=0A----- Most recent Reply -------=0AWe did use pg_upgrade =
with the hard link option. We are not sure if we ran the cleanup script. <B=
R> Not sure which script you are referring to? Is that script the =
one that removes the stuff in the source bin directory? We did=
 the pg_largeobject.sql script, as we were instructed by the pg_upgrade pro=
cess. We also ran vacuum --all --analyzeonly  Can we run this =
script now, even though its month's after we did the upgrade? =
Our tablespace structure to help sort out the previously sent directories l=
ist: CREATE TABLESPACE user_data LOCATION '/opt/PostgreSQL/9.1=
/data/user_data'; CREATE TABLESPACE track_data_year_underflow =
LOCATION '/opt/PostgreSQL/9.1/data/track_data/year_underflow'; The "year_un=
derflow" tablespace contains all data older than the oldest date.CREATE=
 TABLESPACE track_data_y2010 LOCATION '/opt/PostgreSQL/9.1/data/track_data/=
year2010';CREATE TABLESPACE track_data-y2011 LOCATION '/opt/PostgreSQL/=
9.1/data/track_data/year2011';CREATE TABLESPACE track_data-y2012 LOCATI=
ON '/opt/PostgreSQL/9.1/data/track_data/year2012';=0A </div=
>=0A-------- Original Message --------Subject: Re: [BUGS] Excessiv=
e space allocations in Postgresql 9.1.6system files causing the file sy=
stem to run out of space.From: Kevin Grittner <<A href=3D"mailto:kgr=
ittn@ymail.com">kgrittn@ymail.com>Date: Wed, February 27, 2013 1=
:16 pmTo: "fburgess@radiant=
blue.com" <fburgess@radi=
antblue.com>=0APlease keep the list copied (use "Repl=
y All").=0AWhen you do that, please describe how you upgrade=
d.  Was it with pg_upgrade?  Did you use the hard link option?&nb=
sp; Did you run the cleanup script afterward?=0A-Kevin=
=0A-------------------------------------------------------------------=
-------------From: "fburges=
s@radiantblue.com" <fbur=
gess@radiantblue.com>To: Kevin Grittner <<A href=3D"mailto:kg=
rittn@ymail.com">kgrittn@ymail.com> Sent: Wednesday, February 27=
, 2013 2:08 PMSubject: RE: [BUGS] Excessive space allocations in Postgr=
esql 9.1.6 system files causing the file system to run out of space.=
=0A =0AI am looking in a variety of directories which =
include /opt/PostgreSQL/9.1/data/global/opt/PostgreSQL/9.1=
/data/base/16411/opt/PostgreSQL/9.1/data/user_data/PG_9.1_201105231/164=
11/opt/PostgreSQL/9.1/data/user_data/PG_9.1_201105231/16416/opt/Pos=
tgreSQL/9.1/data/user_data/19177/opt/PostgreSQL/9.1/data/track_data/yea=
r2010/19177/opt/PostgreSQL/9.1/data/track_data/year2010/PG_9.1_20110523=
1/16411/opt/PostgreSQL/9.1/data/track_data/year2011/19177/opt/Postg=
reSQL/9.1/data/track_data/year2011/PG_9.1_201105231/16411/opt/PostgreSQ=
L/9.1/data/track_data/year2012/19177/opt/PostgreSQL/9.1/data/track_data=
/year2012/PG_9.1_201105231/16411/opt/PostgreSQL/9.1/data/track_data/yea=
r2013/PG_9.1_201105231/16411/opt/PostgreSQL/9.1/data/track_data/year_un=
derflow/19177/opt/PostgreSQL/9.1/data/track_data/year_underflow/PG_9.1_=
201105231/16411 Everything in the .../19177 directories repres=
ent data files migrated over form postgres 8.4.3.  All new files get p=
laced into the .../PG_9.1_201105231/16411 directories. Yes, I =
exclude all files derived from pg_class that include an underscore or perio=
d. The vast majority of the "orphan" files are from the /opt/P=
ostgreSQL/9.1/data/user_data/19177  directory.  thanks<BR=
> -------- Original Message --------Subject: Re: [BUGS] Excess=
ive space allocations in Postgresql 9.1.6system files causing the file =
system to run out of space.From: Kevin Grittner <<A href=3D"mailto:k=
grittn@ymail.com">kgrittn@ymail.com>Date: Wed, February 27, 2013=
 8:55 amTo: "fburgess@radia=
ntblue.com" <fburgess@ra=
diantblue.com>, "pg=
sql-bugs@postgresql.org" <<A href=3D"mailto:pgsql-bugs@postgresql.or=
g">pgsql-bugs@postgresql.org>=0A"<A href=3D"mailto:fburge=
ss@radiantblue.com">fburgess@radiantblue.com" <<A href=3D"mailto:fbu=
rgess@radiantblue.com">fburgess@radiantblue.com> wrote:=0A<div=
>> We have a Postgres database that was recently upgraded from 8.4.3=
> to 9.1.6.  We have noticed unusual growth in the data files and<B=
R>> generated a script to perform the following actions.=0A&g=
t; 1. Query pg_class for all records> 2. Generate a file listing of =
all postgres data files> 3. Compare the two lists and eliminate all =
files that are>    contained within pg_class> =
> There are 17359 data files.  After running 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 comfort=
able> about deleting them from the file system.  Does postgres =
9.1.6> catalog every data file in pg_class?   Or does it l=
eave some data> files off of this table?  If so, how can I dete=
rmine if I have> stale, unnecessary data files on my file system?</d=
iv>=0AYeah, it's good to be cautious -- deleting a needed file can ren=
deryour database cluster unusable.  Be sure you have a good backup=
 youcan go back to if you delete the wrong thing.=0AWhat dir=
ectories are you looking in?=0AFor a database or tablespace dire=
ctory, are you excluding all fileswhich start with a filename you deriv=
ed from pg_class and has a dotor underscore followed by more characters=
?=0A--Kevin GrittnerEnterpriseDB: <A href=3D"http://www.=
enterprisedb.com">http://www.enterprisedb.comThe Enterprise Postgre=
SQL Company=0A -------- Original Message --------Su=
bject: Re: [BUGS] Excessive space allocations in Postgresql 9.1.6system=
 files causing the file system to run out of space.From: Andres Freund =
<andres@2ndquadrant.com>=
;Date: Thu, February 28, 2013 12:23 pmTo: <a href=3D"mailto:fburges=
s@radiantblue.com">fburgess@radiantblue.comHi,On 2013-0=
2-27 14:21:44 -0700, fburgess@r=
adiantblue.com wrote:> <html><body><span style=3D=
"font-family:Verdana; color:#000000; font-size:10pt;"><div>We did =
use pg_upgrade with the hard link option. We are not sure if we ran the cle=
anup script. </div> ...> OTE></span></body><=
/html>youre more likely to get help if you send your emails eith=
er inplain-text only or at least plain-text & html...Greeti=
ngs,Andres Freund-- Andres Freund <a href=3D"http://www=
.2ndQuadrant.com">http://www.2ndQuadrant.com/PostgreSQL Development=
, 24x7 Support, Training & Services