<html><body><span style="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 cleanup script. </div>
<div> </div>
<div>Not sure which script you are referring to? Is that script the one that removes the stuff in the source bin directory?</div>
<div> </div>
<div>We did the pg_largeobject.sql script, as we were instructed by the pg_upgrade process. We also ran vacuum --all --analyzeonly </div>
<div> </div>
<div>Can we run this script now, even though its month's after we did the upgrade?</div>
<div> </div>
<div>Our tablespace structure to help sort out the previously sent directories list:</div>
<div> </div>
<div>CREATE TABLESPACE user_data LOCATION '/opt/PostgreSQL/9.1/data/user_data';</div>
<div> </div>
<div>CREATE TABLESPACE track_data_year_underflow LOCATION '/opt/PostgreSQL/9.1/data/track_data/year_underflow'; The "year_underflow" tablespace contains all data older than the oldest date.</div>
<div>CREATE TABLESPACE track_data_y2010 LOCATION '/opt/PostgreSQL/9.1/data/track_data/year2010';</div>
<div>CREATE TABLESPACE track_data-y2011 LOCATION '/opt/PostgreSQL/9.1/data/track_data/year2011';</div>
<div>CREATE TABLESPACE track_data-y2012 LOCATION '/opt/PostgreSQL/9.1/data/track_data/year2012';</div>
<div><BR> </div>
<div>-------- Original Message --------<BR>Subject: Re: [BUGS] Excessive space allocations in Postgresql 9.1.6<BR>system files causing the file system to run out of space.<BR>From: Kevin Grittner <<a href="mailto:kgrittn(at)ymail(dot)com">kgrittn(at)ymail(dot)com</a>><BR>Date: Wed, February 27, 2013 1:16 pm<BR>To: "<a href="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</a>" <<a href="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</a>><BR><BR></div>
<BLOCKQUOTE style="BORDER-LEFT: blue 2px solid; PADDING-LEFT: 8px; FONT-FAMILY: verdana; COLOR: black; MARGIN-LEFT: 8px; FONT-SIZE: 10pt" id=replyBlockquote webmail="1">
<DIV id=wmQuoteWrapper>
<DIV style="BACKGROUND-COLOR: #fff; FONT-FAMILY: times new roman, new york, times, serif; COLOR: #000; FONT-SIZE: 12pt">
<DIV><SPAN>Please keep the list copied (use "Reply All").</SPAN></DIV>
<DIV style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman,new york,times,serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><BR><SPAN></SPAN></DIV>
<DIV style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman,new york,times,serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><SPAN>When you do that, please describe how you upgraded. Was it with pg_upgrade? Did you use the hard link option? Did you run the cleanup script afterward?</SPAN></DIV>
<DIV style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman,new york,times,serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><BR><SPAN></SPAN></DIV>
<DIV style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman,new york,times,serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><SPAN>-Kevin<BR></SPAN></DIV>
<DIV> </DIV>
<DIV><BR>
<BLOCKQUOTE style="BORDER-LEFT: rgb(16,16,255) 2px solid; MARGIN-TOP: 5px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px">
<DIV style="FONT-FAMILY: times new roman, new york, times, serif; FONT-SIZE: 12pt">
<DIV style="FONT-FAMILY: times new roman, new york, times, serif; FONT-SIZE: 12pt">
<DIV dir=ltr><FONT size=2 face=Arial>
<HR SIZE=1>
<B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B> "<a href="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</a>" <<a href="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</a>><BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B> Kevin Grittner <<a href="mailto:kgrittn(at)ymail(dot)com">kgrittn(at)ymail(dot)com</a>> <BR><B><SPAN style="FONT-WEIGHT: bold">Sent:</SPAN></B> Wednesday, February 27, 2013 2:08 PM<BR><B><SPAN style="FONT-WEIGHT: bold">Subject:</SPAN></B> RE: [BUGS] Excessive space allocations in Postgresql 9.1.6 system files causing the file system to run out of space.<BR></FONT></DIV><BR>
<DIV id=yiv1220390063>
<DIV><SPAN style="FONT-FAMILY: Verdana; COLOR: #000000; FONT-SIZE: 10pt">
<DIV>I am looking in a variety of directories which include</DIV>
<DIV> </DIV>
<DIV>/opt/PostgreSQL/9.1/data/global<BR>/opt/PostgreSQL/9.1/data/base/16411<BR>/opt/PostgreSQL/9.1/data/user_data/PG_9.1_201105231/16411<BR>/opt/PostgreSQL/9.1/data/user_data/PG_9.1_201105231/16416<BR>/opt/PostgreSQL/9.1/data/user_data/19177<BR>/opt/PostgreSQL/9.1/data/track_data/year2010/19177<BR>/opt/PostgreSQL/9.1/data/track_data/year2010/PG_9.1_201105231/16411<BR>/opt/PostgreSQL/9.1/data/track_data/year2011/19177<BR>/opt/PostgreSQL/9.1/data/track_data/year2011/PG_9.1_201105231/16411<BR>/opt/PostgreSQL/9.1/data/track_data/year2012/19177<BR>/opt/PostgreSQL/9.1/data/track_data/year2012/PG_9.1_201105231/16411<BR>/opt/PostgreSQL/9.1/data/track_data/year2013/PG_9.1_201105231/16411<BR>/opt/PostgreSQL/9.1/data/track_data/year_underflow/19177<BR>/opt/PostgreSQL/9.1/data/track_data/year_underflow/PG_9.1_201105231/16411<BR> <BR>Everything in the .../19177 directories represent data files migrated over form postgres 8.4.3. All new files get placed into the .../PG_9.1_201105231/16411 directories.<BR> <BR>Yes, I exclude all files derived from pg_class that include an underscore or period.<BR> <BR>The vast majority of the "orphan" files are from the /opt/PostgreSQL/9.1/data/user_data/19177 directory. </DIV>
<DIV> </DIV>
<DIV>thanks</DIV>
<DIV> </DIV>
<BLOCKQUOTE style="BORDER-LEFT: blue 2px solid; PADDING-LEFT: 8px; FONT-FAMILY: verdana; COLOR: black; MARGIN-LEFT: 8px; FONT-SIZE: 10pt" id=yiv1220390063replyBlockquote>
<DIV id=yiv1220390063wmQuoteWrapper>-------- Original Message --------<BR>Subject: Re: [BUGS] Excessive space allocations in Postgresql 9.1.6<BR>system files causing the file system to run out of space.<BR>From: Kevin Grittner <<A href="mailto:kgrittn(at)ymail(dot)com" rel=nofollow target=_blank ymailto="mailto:kgrittn(at)ymail(dot)com">kgrittn(at)ymail(dot)com</A>><BR>Date: Wed, February 27, 2013 8:55 am<BR>To: "<A href="mailto:fburgess(at)radiantblue(dot)com" rel=nofollow target=_blank ymailto="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</A>" <<A href="mailto:fburgess(at)radiantblue(dot)com" rel=nofollow target=_blank ymailto="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</A>>, <BR>"<A href="mailto:pgsql-bugs(at)postgresql(dot)org" rel=nofollow target=_blank ymailto="mailto:pgsql-bugs(at)postgresql(dot)org">pgsql-bugs(at)postgresql(dot)org</A>" <<A href="mailto:pgsql-bugs(at)postgresql(dot)org" rel=nofollow target=_blank ymailto="mailto:pgsql-bugs(at)postgresql(dot)org">pgsql-bugs(at)postgresql(dot)org</A>><BR><BR>"<A href="mailto:fburgess(at)radiantblue(dot)com" rel=nofollow target=_blank ymailto="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</A>" <<A href="mailto:fburgess(at)radiantblue(dot)com" rel=nofollow target=_blank ymailto="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</A>> wrote:<BR><BR>> We have a Postgres database that was recently upgraded from 8.4.3<BR>> to 9.1.6. We have noticed unusual growth in the data files and<BR>> generated a script to perform the following actions.<BR><BR>> 1. Query pg_class for all records<BR>> 2. Generate a file listing of all postgres data files<BR>> 3. Compare the two lists and eliminate all files that are<BR>> contained within pg_class<BR>> <BR>> There are 17359 data files. After running the script, there are<BR>> 5802 data files remaining that are not listed in pg_class. Due<BR>> to the size of the (5802) data files (~4TB), I am not comfortable<BR>> about deleting them from the file system. Does postgres 9.1.6<BR>> catalog every data file in pg_class? Or does it leave some data<BR>> files off of this table? If so, how can I determine if I have<BR>> stale, unnecessary data files on my file system?<BR><BR>Yeah, it's good to be cautious -- deleting a needed file can render<BR>your database cluster unusable. Be sure you have a good backup you<BR>can go back to if you delete the wrong thing.<BR><BR>What directories are you looking in?<BR><BR>For a database or tablespace directory, are you excluding all files<BR>which start with a filename you derived from pg_class and has a dot<BR>or underscore followed by more characters?<BR><BR>--<BR>Kevin Grittner<BR>EnterpriseDB: <a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><BR>The Enterprise PostgreSQL Company<BR></DIV></BLOCKQUOTE></SPAN></DIV></DIV><BR><BR></DIV></DIV></BLOCKQUOTE></DIV></DIV></DIV></BLOCKQUOTE></span></body></html>