Re: Excessive space allocations in Postgresql 9.1.6 system files causing the file system to run out of space. - Mailing list pgsql-bugs
| From | |
|---|---|
| Subject | Re: Excessive space allocations in Postgresql 9.1.6 system files causing the file system to run out of space. |
| Date | |
| Msg-id | 20130301082031.5a830134ae84016b0174832fdc1a3173.c8e37496f5.wbe@email11.secureserver.net Whole thread Raw |
| Responses |
Re: Excessive space allocations in Postgresql 9.1.6 system files causing the file system to run out of space.
|
| List | pgsql-bugs |
<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
pgsql-bugs by date: