Excessive space allocations in Postgresql 9.1.6 system files causing the file system to run out of space.
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>On tuesday, February 26, 2013, Freddie Burgess wrote:</div>
<div> </div>
<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 generated a script to perform the following actions.<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 contained within pg_class<BR> <BR>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 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 determine if I have stale, unnecessary data files on my file system?<BR></div></span></body></html>
"fburgess@radiantblue.com" <fburgess@radiantblue.com> wrote:
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
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 files that are
contained within pg_classThere 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 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 determine if I have
stale, unnecessary data files on my file system?
Yeah, it's good to be cautious -- deleting a needed file can render
your database cluster unusable. Be sure you have a good backup you
can go back to if you delete the wrong thing.
What directories are you looking in?
For a database or tablespace directory, are you excluding all files
which start with a filename you derived from pg_class and has a dot
or underscore followed by more characters?
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
<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@ymail.com">kgrittn@ymail.com</a>><BR>Date: Wed, February 27, 2013 1:16 pm<BR>To: "<a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a>" <<a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.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@radiantblue.com">fburgess@radiantblue.com</a>" <<a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a>><BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B> Kevin Grittner <<a href="mailto:kgrittn@ymail.com">kgrittn@ymail.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@ymail.com" rel=nofollow target=_blank ymailto="mailto:kgrittn@ymail.com">kgrittn@ymail.com</A>><BR>Date: Wed, February 27, 2013 8:55 am<BR>To: "<A href="mailto:fburgess@radiantblue.com" rel=nofollow target=_blank ymailto="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</A>" <<A href="mailto:fburgess@radiantblue.com" rel=nofollow target=_blank ymailto="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</A>>, <BR>"<A href="mailto:pgsql-bugs@postgresql.org" rel=nofollow target=_blank ymailto="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</A>" <<A href="mailto:pgsql-bugs@postgresql.org" rel=nofollow target=_blank ymailto="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</A>><BR><BR>"<A href="mailto:fburgess@radiantblue.com" rel=nofollow target=_blank ymailto="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</A>" <<A href="mailto:fburgess@radiantblue.com" rel=nofollow target=_blank ymailto="mailto:fburgess@radiantblue.com">fburgess@radiantblue.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>
Import Notes
Resolved by subject fallback
<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@ymail.com">kgrittn@ymail.com</A>><BR>Date: Wed, February 27, 2013 1:16 pm<BR>To: "<A href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</A>" <<A href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.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@radiantblue.com">fburgess@radiantblue.com</A>" <<A href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</A>><BR>To: Kevin Grittner <<A href="mailto:kgrittn@ymail.com">kgrittn@ymail.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@ymail.com">kgrittn@ymail.com</A>><BR>Date: Wed, February 27, 2013 8:55 am<BR>To: "<A href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</A>" <<A href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</A>>, <BR>"<A href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</A>" <<A href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</A>></div>
<div>"<A href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</A>" <<A href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.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@2ndquadrant.com">andres@2ndquadrant.com</a>><BR>Date: Thu, February 28, 2013 12:23 pm<BR>To: <a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a><BR><BR>Hi,<BR><BR>On 2013-02-27 14:21:44 -0700, <a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.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>
Import Notes
Resolved by subject fallback
"fburgess@radiantblue.com" <fburgess@radiantblue.com> wrote:
We did use pg_upgrade with the hard link option. We are not sure
if we ran the cleanup script.
Can we run this script now, even though its month's after we did
the upgrade?
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.
The vast majority of the "orphan" files are from the
/opt/PostgreSQL/9.1/data/user_data/19177 directory.
I don't have any reason to expect that you *can't* run the script
at this point; but being a cautious person, I would do this at a
point where I was confident I could recover from a backup, and I
would read through the scripts carefully before applying them.
What you want to be really careful that you *don't* do is to modify
or truncate any of the hard-linked files, as they are quite likely
to still be just another name for the same file that is in use for
production under the newer version. You want to simply remove the
older directory entry pointing to the file.
http://www.linfo.org/hard_link.html
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>Hi Kevin,</div>
<div> </div>
<div>Do you know the name that was given to the cleanup script?</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=replyBlockquote webmail="1">
<DIV id=wmQuoteWrapper>-------- 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@ymail.com">kgrittn@ymail.com</a>><BR>Date: Fri, March 01, 2013 9:00 am<BR>To: "<a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a>" <<a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a>>, Andres<BR>Freund <<a href="mailto:andres@2ndquadrant.com">andres@2ndquadrant.com</a>><BR>Cc: "<a href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</a>" <<a href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</a>><BR><BR>"<a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a>" <<a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a>> wrote:<BR><BR>> We did use pg_upgrade with the hard link option. We are not sure<BR>> if we ran the cleanup script.<BR><BR>> Can we run this script now, even though its month's after we did<BR>> the upgrade?<BR><BR>> Everything in the .../19177 directories represent data files<BR>> migrated over form postgres 8.4.3. All new files get placed into<BR>> the .../PG_9.1_201105231/16411 directories.<BR><BR>> The vast majority of the "orphan" files are from the<BR>> /opt/PostgreSQL/9.1/data/user_data/19177 directory.<BR><BR>I don't have any reason to expect that you *can't* run the script<BR>at this point; but being a cautious person, I would do this at a<BR>point where I was confident I could recover from a backup, and I<BR>would read through the scripts carefully before applying them.<BR><BR>What you want to be really careful that you *don't* do is to modify<BR>or truncate any of the hard-linked files, as they are quite likely<BR>to still be just another name for the same file that is in use for<BR>production under the newer version. You want to simply remove the<BR>older directory entry pointing to the file.<BR><BR><a href="http://www.linfo.org/hard_link.html">http://www.linfo.org/hard_link.html</a><BR><BR>-- <BR>Kevin Grittner<BR>EnterpriseDB: <a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><BR>The Enterprise PostgreSQL Company<BR><BR><BR>-- <BR>Sent via pgsql-bugs mailing list (<a href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</a>)<BR>To make changes to your subscription:<BR><a href="http://www.postgresql.org/mailpref/pgsql-bugs">http://www.postgresql.org/mailpref/pgsql-bugs</a><BR></DIV></BLOCKQUOTE></span></body></html>
Import Notes
Resolved by subject fallback
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>Does anyone know, what the names/location of the pg_upgrade cleanup scripts? We upgraded from 8.4.3 to 9.1.6 on linux</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=replyBlockquote webmail="1">
<DIV id=wmQuoteWrapper><SPAN style="FONT-FAMILY: Verdana; COLOR: #000000; FONT-SIZE: 10pt">
<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>-------- 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@ymail.com" target=_blank>kgrittn@ymail.com</A>><BR>Date: Fri, March 01, 2013 9:00 am<BR>To: "<A href="mailto:fburgess@radiantblue.com" target=_blank>fburgess@radiantblue.com</A>" <<A href="mailto:fburgess@radiantblue.com" target=_blank>fburgess@radiantblue.com</A>>, Andres<BR>Freund <<A href="mailto:andres@2ndquadrant.com" target=_blank>andres@2ndquadrant.com</A>><BR>Cc: "<A href="mailto:pgsql-bugs@postgresql.org" target=_blank>pgsql-bugs@postgresql.org</A>" <<A href="mailto:pgsql-bugs@postgresql.org" target=_blank>pgsql-bugs@postgresql.org</A>><BR><BR>"<A href="mailto:fburgess@radiantblue.com" target=_blank>fburgess@radiantblue.com</A>" <<A href="mailto:fburgess@radiantblue.com" target=_blank>fburgess@radiantblue.com</A>> wrote:<BR><BR>> We did use pg_upgrade with the hard link option. We are not sure<BR>> if we ran the cleanup script.<BR><BR>> Can we run this script now, even though its month's after we did<BR>> the upgrade?<BR><BR>> Everything in the .../19177 directories represent data files<BR>> migrated over form postgres 8.4.3. All new files get placed into<BR>> the .../PG_9.1_201105231/16411 directories.<BR><BR>> The vast majority of the "orphan" files are from the<BR>> /opt/PostgreSQL/9.1/data/user_data/19177 directory.<BR><BR>I don't have any reason to expect that you *can't* run the script<BR>at this point; but being a cautious person, I would do this at a<BR>point where I was confident I could recover from a backup, and I<BR>would read through the scripts carefully before applying them.<BR><BR>What you want to be really careful that you *don't* do is to modify<BR>or truncate any of the hard-linked files, as they are quite likely<BR>to still be just another name for the same file that is in use for<BR>production under the newer version. You want to simply remove the<BR>older directory entry pointing to the file.<BR><BR><A href="http://www.linfo.org/hard_link.html" target=_blank>http://www.linfo.org/hard_link.html</A><BR><BR>-- <BR>Kevin Grittner<BR>EnterpriseDB: <A href="http://www.enterprisedb.com/" target=_blank>http://www.enterprisedb.com</A><BR>The Enterprise PostgreSQL Company<BR><BR><BR>-- <BR>Sent via pgsql-bugs mailing list (<A href="mailto:pgsql-bugs@postgresql.org" target=_blank>pgsql-bugs@postgresql.org</A>)<BR>To make changes to your subscription:<BR><A href="http://www.postgresql.org/mailpref/pgsql-bugs" target=_blank>http://www.postgresql.org/mailpref/pgsql-bugs</A><BR></DIV></BLOCKQUOTE></SPAN></DIV></BLOCKQUOTE></span></body></html>
Import Notes
Resolved by subject fallback
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div><font face="verdana, geneva" size="2" style=""><span style="">Does anyone know, what the names/location of the pg_upgrade cleanup scripts? We upgraded from 8.4.3 to 9.1.6</span></font></div><div><font face="verdana, geneva" size="2" style=""><span style=""><br></span></font></div><div><font face="verdana, geneva" size="2" style=""><span style="">thanks</span></font></div><div><font face="verdana, geneva" size="2" style=""><span style=""><br></span></font></div><blockquote id="replyBlockquote" webmail="1" style="color: black; font-family: verdana; font-size: 10pt; border-left-width: 2px; border-left-style: solid; border-left-color: blue; margin-left: 8px; padding-left: 8px; "><div id="wmQuoteWrapper"><span style="font-family:Verdana; color:#000000; font-size:10pt;"> <div> </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"><span style="FONT-FAMILY: Verdana; COLOR: #000000; FONT-SIZE: 10pt"> <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">-------- 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@ymail.com" target="_blank">kgrittn@ymail.com</a>><br>Date: Fri, March 01, 2013 9:00 am<br>To: "<a href="mailto:fburgess@radiantblue.com" target="_blank">fburgess@radiantblue.com</a>" <<a href="mailto:fburgess@radiantblue.com" target="_blank">fburgess@radiantblue.com</a>>, Andres<br>Freund <<a href="mailto:andres@2ndquadrant.com" target="_blank">andres@2ndquadrant.com</a>><br>Cc: "<a href="mailto:pgsql-bugs@postgresql.org" target="_blank">pgsql-bugs@postgresql.org</a>" <<a href="mailto:pgsql-bugs@postgresql.org" target="_blank">pgsql-bugs@postgresql.org</a>><br><br>"<a href="mailto:fburgess@radiantblue.com" target="_blank">fburgess@radiantblue.com</a>" <<a href="mailto:fburgess@radiantblue.com" target="_blank">fburgess@radiantblue.com</a>> wrote:<br><br>> We did use pg_upgrade with the hard link option. We are not sure<br>> if we ran the cleanup script.<br><br>> Can we run this script now, even though its month's after we did<br>> the upgrade?<br><br>> Everything in the .../19177 directories represent data files<br>> migrated over form postgres 8.4.3. All new files get placed into<br>> the .../PG_9.1_201105231/16411 directories.<br><br>> The vast majority of the "orphan" files are from the<br>> /opt/PostgreSQL/9.1/data/user_data/19177 directory.<br><br>I don't have any reason to expect that you *can't* run the script<br>at this point; but being a cautious person, I would do this at a<br>point where I was confident I could recover from a backup, and I<br>would read through the scripts carefully before applying them.<br><br>What you want to be really careful that you *don't* do is to modify<br>or truncate any of the hard-linked files, as they are quite likely<br>to still be just another name for the same file that is in use for<br>production under the newer version. You want to simply remove the<br>older directory entry pointing to the file.<br><br><a href="http://www.linfo.org/hard_link.html" target="_blank">http://www.linfo.org/hard_link.html</a><br><br>-- <br>Kevin Grittner<br>EnterpriseDB: <a href="http://www.enterprisedb.com/" target="_blank">http://www.enterprisedb.com</a><br>The Enterprise PostgreSQL Company<br><br><br>-- <br>Sent via pgsql-bugs mailing list (<a href="mailto:pgsql-bugs@postgresql.org" target="_blank">pgsql-bugs@postgresql.org</a>)<br>To make changes to your subscription:<br><a href="http://www.postgresql.org/mailpref/pgsql-bugs" target="_blank">http://www.postgresql.org/mailpref/pgsql-bugs</a><br></div></blockquote></span></div></blockquote></span>
</div>
</blockquote></span></body></html>
Import Notes
Resolved by subject fallback
"fburgess@radiantblue.com" <fburgess@radiantblue.com> wrote:
Does anyone know, what the names/location of the pg_upgrade
cleanup scripts? We upgraded from 8.4.3 to 9.1.6
Sorry I didn't answer this sooner, but I didn't know off-hand and
hoped that someone who did would jump in. Since that didn't happen
...
The file is delete_old_cluster.sh on most platforms. The suffix is
.bat on Windows.
It can be run anytime after the upgrade *as long as you have not
moved the new cluster to the old location*.
Thanks to Bruce Momjian for telling me in chat when I asked just now.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><DIV>Thanks Kevin & Bruce for your replies,</DIV>
<DIV> </DIV>
<DIV>It looks like this script deletes all of the data file directories (and the data files) from the postgres 8.4.3 instance. Since we used the "-k" option, which calls for hard links rather than copying files to the new cluster, wouldn't this be deleting the files? If so, the DB would be corrupted beyond recovery if we run it. Or am I missing something?</DIV>
<DIV> </DIV>
<DIV>thanks</DIV>
<DIV> </DIV>
<DIV>Freddie</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=replyBlockquote webmail="1">
<DIV id=wmQuoteWrapper>-------- 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@ymail.com">kgrittn@ymail.com</a>><BR>Date: Thu, March 07, 2013 8:14 am<BR>To: "<a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a>" <<a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a>>, <BR>"<a href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</a>" <<a href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</a>><BR><BR>"<a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a>" <<a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a>> wrote:<BR><BR>> Does anyone know, what the names/location of the pg_upgrade<BR>> cleanup scripts? We upgraded from 8.4.3 to 9.1.6<BR><BR>Sorry I didn't answer this sooner, but I didn't know off-hand and<BR>hoped that someone who did would jump in. Since that didn't happen<BR>...<BR><BR>The file is <a href="http://delete_old_cluster.sh">delete_old_cluster.sh</a> on most platforms. The suffix is<BR>.bat on Windows.<BR><BR>It can be run anytime after the upgrade *as long as you have not<BR>moved the new cluster to the old location*.<BR><BR>Thanks to Bruce Momjian for telling me in chat when I asked just now.<BR><BR>--<BR>Kevin Grittner<BR>EnterpriseDB: <a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><BR>The Enterprise PostgreSQL Company<BR><BR><BR>-- <BR>Sent via pgsql-bugs mailing list (<a href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</a>)<BR>To make changes to your subscription:<BR><a href="http://www.postgresql.org/mailpref/pgsql-bugs">http://www.postgresql.org/mailpref/pgsql-bugs</a><BR></DIV></BLOCKQUOTE></span></body></html>
Import Notes
Resolved by subject fallback
"fburgess@radiantblue.com" <fburgess@radiantblue.com> wrote:
It looks like this script deletes all of the data file
directories (and the data files) from the postgres 8.4.3
instance. Since we used the "-k" option, which calls for hard
links rather than copying files to the new cluster, wouldn't this
be deleting the files? If so, the DB would be corrupted beyond
recovery if we run it. Or am I missing something?
You need to read up on hard links a bit more. Here's one source:
http://www.linfo.org/hard_link.html
In summary, a hard link allows multiple directory entries to point
to the same data location. Until you delete the last directory
entry the file remains.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs