<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>Not sure why my emails replies went out in HTML format, I'm re-sending the email trail to date. Thanks Andres for pointing this out.</div>
<div> </div>
<div>Freddie</div>
<div> </div>
<div>----- Most recent Reply -------</div>
<div>We did use pg_upgrade with the hard link option. We are not sure if we ran the cleanup script. <BR> <BR>Not sure which script you are referring to? Is that script the one that removes the stuff in the source bin directory?<BR> <BR>We did the pg_largeobject.sql script, as we were instructed by the pg_upgrade process. We also ran vacuum --all --analyzeonly <BR> <BR>Can we run this script now, even though its month's after we did the upgrade?<BR> <BR>Our tablespace structure to help sort out the previously sent directories list:<BR> <BR>CREATE TABLESPACE user_data LOCATION '/opt/PostgreSQL/9.1/data/user_data';<BR> <BR>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.<BR>CREATE TABLESPACE track_data_y2010 LOCATION '/opt/PostgreSQL/9.1/data/track_data/year2010';<BR>CREATE TABLESPACE track_data-y2011 LOCATION '/opt/PostgreSQL/9.1/data/track_data/year2011';<BR>CREATE TABLESPACE track_data-y2012 LOCATION '/opt/PostgreSQL/9.1/data/track_data/year2012';</div>
<div> </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>></div>
<div><BR>Please keep the list copied (use "Reply All").</div>
<div><BR>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?</div>
<div><BR>-Kevin</div>
<div>--------------------------------------------------------------------------------<BR>From: "<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>To: Kevin Grittner <<A href="mailto:kgrittn(at)ymail(dot)com">kgrittn(at)ymail(dot)com</A>> <BR>Sent: Wednesday, February 27, 2013 2:08 PM<BR>Subject: RE: [BUGS] Excessive space allocations in Postgresql 9.1.6 system files causing the file system to run out of space.</div>
<div> </div>
<div>I am looking in a variety of directories which include<BR> <BR>/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. <BR> <BR>thanks<BR> <BR>-------- 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 8:55 am<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>"<A href="mailto:pgsql-bugs(at)postgresql(dot)org">pgsql-bugs(at)postgresql(dot)org</A>" <<A href="mailto:pgsql-bugs(at)postgresql(dot)org">pgsql-bugs(at)postgresql(dot)org</A>></div>
<div>"<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>> wrote:</div>
<div>> 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.</div>
<div>> 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?</div>
<div>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.</div>
<div>What directories are you looking in?</div>
<div>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?</div>
<div>--<BR>Kevin Grittner<BR>EnterpriseDB: <A href="http://www.enterprisedb.com">http://www.enterprisedb.com</A><BR>The Enterprise PostgreSQL Company</div>
<div><BR> -------- 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: Andres Freund <<a href="mailto:andres(at)2ndquadrant(dot)com">andres(at)2ndquadrant(dot)com</a>><BR>Date: Thu, February 28, 2013 12:23 pm<BR>To: <a href="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</a><BR><BR>Hi,<BR><BR>On 2013-02-27 14:21:44 -0700, <a href="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</a> wrote:<BR>> <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> ...<BR>> OTE></span></body></html><BR><BR>youre more likely to get help if you send your emails either in<BR>plain-text only or at least plain-text & html...<BR><BR>Greetings,<BR><BR>Andres Freund<BR><BR>-- <BR>Andres Freund <a href="http://www.2ndQuadrant.com">http://www.2ndQuadrant.com</a>/<BR>PostgreSQL Development, 24x7 Support, Training & Services<BR></div></span></body></html>