Re: Excessive space allocations in Postgresql 9.1.6 system files causing the file system to run out of space. - Mailing list pgsql-bugs

<span style=3D"font-family:Verdana; color:#000000; font-size:10=
pt;">We did use pg_upgrade with the hard link option. We are not sure =
if we ran the cleanup script. =0A =0ANot sure wh=
ich script you are referring to? Is that script the one that removes the st=
uff in the source bin directory?=0A =0AWe did th=
e pg_largeobject.sql script, as we were instructed by the pg_upgrade proces=
s. We also ran vacuum --all --analyzeonly =0A =0A<div=
>Can we run this script now, even though its month's after we did the upgra=
de?=0A =0AOur tablespace structure to help sort =
out the previously sent directories list:=0A =0A=
CREATE TABLESPACE user_data LOCATION '/opt/PostgreSQL/9.1/data/user_da=
ta';=0A =0ACREATE TABLESPACE track_data_yea=
r_underflow LOCATION '/opt/PostgreSQL/9.1/data/track_data/year_underflow'; =
The "year_underflow" tablespace contains all data older than the oldest dat=
e.=0ACREATE TABLESPACE track_data_y2010 LOCATION '/opt/Post=
greSQL/9.1/data/track_data/year2010';=0ACREATE TABLESPACE t=
rack_data-y2011 LOCATION '/opt/PostgreSQL/9.1/data/track_data/year2011';</d=
iv>=0ACREATE TABLESPACE track_data-y2012 LOCATION '/opt/PostgreSQ=
L/9.1/data/track_data/year2012';=0A =0A-----=
--- Original Message --------Subject: Re: [BUGS] Excessive space alloca=
tions in Postgresql 9.1.6system files causing the file system to run ou=
t of space.From: Kevin Grittner <<a href=3D"mailto:kgrittn@ymail.com=
">kgrittn@ymail.com>Date: Wed, February 27, 2013 1:16 pmTo: =
"fburgess@radiantblue.com" =
<fburgess@radiantblue.com</a=
>>=0A<BLOCKQUOTE style=3D"BORDER-LEFT: blue 2px solid; PAD=
DING-LEFT: 8px; FONT-FAMILY: verdana; COLOR: black; MARGIN-LEFT: 8px; FONT-=
SIZE: 10pt" id=3DreplyBlockquote webmail=3D"1">=0A=
=0A<DIV style=3D"BACKGROUND-COLOR: #fff; FONT-FAMILY: times new roman, new =
york, times, serif; COLOR: #000; FONT-SIZE: 12pt">=0APlease keep=
 the list copied (use "Reply All").=0A<DIV style=3D"BACKGROUND=
-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman,new y=
ork,times,serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"></DIV=
>=0A<DIV style=3D"BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-F=
AMILY: times new roman,new york,times,serif; COLOR: rgb(0,0,0); FONT-SIZE: =
16px">When you do that, please describe how you upgraded.  Was i=
t with pg_upgrade?  Did you use the hard link option?  Did you ru=
n the cleanup script afterward?=0A<DIV style=3D"BACKGROUND-COL=
OR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman,new york,=
times,serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px">=0A=
<DIV style=3D"BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMIL=
Y: times new roman,new york,times,serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px=
">-Kevin=0A =0A=0A<BLOCKQUO=
TE style=3D"BORDER-LEFT: rgb(16,16,255) 2px solid; MARGIN-TOP: 5px; PADDING=
-LEFT: 5px; MARGIN-LEFT: 5px">=0A<DIV style=3D"FONT-FAMILY: times new roman=
, new york, times, serif; FONT-SIZE: 12pt">=0A<DIV style=3D"FONT-FAMILY: ti=
mes new roman, new york, times, serif; FONT-SIZE: 12pt">=0A<=
FONT size=3D2 face=3DArial>=0A=0A<SPAN style=3D"FONT-WEIGHT=
: bold">From: "=
fburgess@radiantblue.com" <<a href=3D"mailto:fburgess@radiantblue.co=
m">fburgess@radiantblue.com><SPAN style=3D"FONT-WEIGHT: bold"=
>To: Kevin Grittner <<a href=3D"mailto:kgrittn@ymail.com=
">kgrittn@ymail.com> Sent:<=
/SPAN> Wednesday, February 27, 2013 2:08 PM<SPAN style=3D"F=
ONT-WEIGHT: bold">Subject: RE: [BUGS] Excessive space alloc=
ations in Postgresql 9.1.6 system files causing the file system to run out =
of space.=0A=0A<SPAN styl=
e=3D"FONT-FAMILY: Verdana; COLOR: #000000; FONT-SIZE: 10pt">=0AI am lo=
oking in a variety of directories which include=0A =
=0A/opt/PostgreSQL/9.1/data/global/opt/PostgreSQL/9.1/data/base/16=
411/opt/PostgreSQL/9.1/data/user_data/PG_9.1_201105231/16411/opt/Po=
stgreSQL/9.1/data/user_data/PG_9.1_201105231/16416/opt/PostgreSQL/9.1/d=
ata/user_data/19177/opt/PostgreSQL/9.1/data/track_data/year2010/19177<B=
R>/opt/PostgreSQL/9.1/data/track_data/year2010/PG_9.1_201105231/16411/o=
pt/PostgreSQL/9.1/data/track_data/year2011/19177/opt/PostgreSQL/9.1/dat=
a/track_data/year2011/PG_9.1_201105231/16411/opt/PostgreSQL/9.1/data/tr=
ack_data/year2012/19177/opt/PostgreSQL/9.1/data/track_data/year2012/PG_=
9.1_201105231/16411/opt/PostgreSQL/9.1/data/track_data/year2013/PG_9.1_=
201105231/16411/opt/PostgreSQL/9.1/data/track_data/year_underflow/19177=
/opt/PostgreSQL/9.1/data/track_data/year_underflow/PG_9.1_201105231/164=
11 Everything in the .../19177 directories represent data file=
s migrated over form postgres 8.4.3.  All new files get placed into th=
e .../PG_9.1_201105231/16411 directories. Yes, I exclude all f=
iles derived from pg_class that include an underscore or period. <=
BR>The vast majority of the "orphan" files are from the /opt/PostgreSQL/9.1=
/data/user_data/19177  directory. =0A =0A<D=
IV>thanks=0A =0A<BLOCKQUOTE style=3D"BORDER-LEFT: blu=
e 2px solid; PADDING-LEFT: 8px; FONT-FAMILY: verdana; COLOR: black; MARGIN-=
LEFT: 8px; FONT-SIZE: 10pt" id=3Dyiv1220390063replyBlockquote>=0A<DIV id=3D=
yiv1220390063wmQuoteWrapper>-------- Original Message --------Subject: =
Re: [BUGS] Excessive space allocations in Postgresql 9.1.6system files =
causing the file system to run out of space.From: Kevin Grittner <<A=
 href=3D"mailto:kgrittn@ymail.com" rel=3Dnofollow target=3D_blank ymailto=
=3D"mailto:kgrittn@ymail.com">kgrittn@ymail.com>Date: Wed, Febru=
ary 27, 2013 8:55 amTo: "<A href=3D"mailto:fburgess@radiantblue.com" re=
l=3Dnofollow target=3D_blank ymailto=3D"mailto:fburgess@radiantblue.com">fb=
urgess@radiantblue.com" <<A href=3D"mailto:fburgess@radiantblue.com"=
 rel=3Dnofollow target=3D_blank ymailto=3D"mailto:fburgess@radiantblue.com"=
>fburgess@radiantblue.com>, "<A href=3D"mailto:pgsql-bugs@postgr=
esql.org" rel=3Dnofollow target=3D_blank ymailto=3D"mailto:pgsql-bugs@postg=
resql.org">pgsql-bugs@postgresql.org" <<A href=3D"mailto:pgsql-bugs@=
postgresql.org" rel=3Dnofollow target=3D_blank ymailto=3D"mailto:pgsql-bugs=
@postgresql.org">pgsql-bugs@postgresql.org>"<A href=3D"mailt=
o:fburgess@radiantblue.com" rel=3Dnofollow target=3D_blank ymailto=3D"mailt=
o:fburgess@radiantblue.com">fburgess@radiantblue.com" <<A href=3D"ma=
ilto:fburgess@radiantblue.com" rel=3Dnofollow target=3D_blank ymailto=3D"ma=
ilto:fburgess@radiantblue.com">fburgess@radiantblue.com> wrote:<=
BR>> We have a Postgres database that was recently upgraded from 8.4.3<B=
R>> to 9.1.6.  We have noticed unusual growth in the data files and=
> generated a script to perform the following actions.> 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 file=
s that are>    contained within pg_class> =
> There are 17359 data files.  After running the script, there are<=
BR>> 5802 data files remaining that are not listed in pg_class.  Du=
e> to the size of the (5802) data files (~4TB), I am not comfortable=
> about deleting them from the file system.  Does postgres 9.1.=
6> catalog every data file in pg_class?   Or does it leave=
 some data> files off of this table?  If so, how can I determin=
e if I have> stale, unnecessary data files on my file system?<BR=
>Yeah, it's good to be cautious -- deleting a needed file can renderyou=
r database cluster unusable.  Be sure you have a good backup youca=
n go back to if you delete the wrong thing.What directories are you=
 looking in?For a database or tablespace directory, are you excludi=
ng all fileswhich start with a filename you derived from pg_class and h=
as a dotor underscore followed by more characters?--Kevin G=
rittnerEnterpriseDB: http://www=
.enterprisedb.comThe Enterprise PostgreSQL Company</BLOCK=
QUOTE></DIV=
>