Re: Excessive space allocations in Postgresql 9.1.6 system files causing the file system to run out of space.
От | |
---|---|
Тема | Re: Excessive space allocations in Postgresql 9.1.6 system files causing the file system to run out of space. |
Дата | |
Msg-id | 20130227142144.5a830134ae84016b0174832fdc1a3173.53bd04e878.wbe@email11.secureserver.net обсуждение исходный текст |
Список | 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= >
В списке pgsql-bugs по дате отправления: