Re: [BUGS] Excessive space allocations in Postgresql 9.1.6 system files causing the file system to run out of space.

[fburgess]

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.

 

Freddie

 

----- Most recent Reply -------

We did use pg_upgrade with the hard link option. We are not sure if we ran the cleanup script.
 
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 process. 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 list:
 
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_underflow" 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 LOCATION '/opt/PostgreSQL/9.1/data/track_data/year2012';

 

-------- Original Message --------
Subject: Re: [BUGS] Excessive space allocations in Postgresql 9.1.6
system files causing the file system to run out of space.
From: Kevin Grittner <kgri...@ymail.com>
Date: Wed, February 27, 2013 1:16 pm
To: "fburg...@radiantblue.com" <fburg...@radiantblue.com>

Please keep the list copied (use "Reply All").

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?

-Kevin

--------------------------------------------------------------------------------
From: "fburg...@radiantblue.com" <fburg...@radiantblue.com>
To: Kevin Grittner <kgri...@ymail.com>
Sent: Wednesday, February 27, 2013 2:08 PM
Subject: RE: [BUGS] Excessive space allocations in Postgresql 9.1.6 system files causing the file system to run out of space.

 

I 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/16411
/opt/PostgreSQL/9.1/data/user_data/PG_9.1_201105231/16416
/opt/PostgreSQL/9.1/data/user_data/19177
/opt/PostgreSQL/9.1/data/track_data/year2010/19177
/opt/PostgreSQL/9.1/data/track_data/year2010/PG_9.1_201105231/16411
/opt/PostgreSQL/9.1/data/track_data/year2011/19177
/opt/PostgreSQL/9.1/data/track_data/year2011/PG_9.1_201105231/16411
/opt/PostgreSQL/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/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/16411
 
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.
 
Yes, I exclude all files derived from pg_class that include an underscore or period.
 
The vast majority of the "orphan" files are from the /opt/PostgreSQL/9.1/data/user_data/19177  directory.
 
thanks
 
-------- Original Message --------
Subject: Re: [BUGS] Excessive space allocations in Postgresql 9.1.6
system files causing the file system to run out of space.
From: Kevin Grittner <kgri...@ymail.com>
Date: Wed, February 27, 2013 8:55 am
To: "fburg...@radiantblue.com" <fburg...@radiantblue.com>,
"pgsql-bugs@postgresql.org" <pgsql-bugs@postgresql.org>

"fburg...@radiantblue.com" <fburg...@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_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 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

 -------- Original Message --------
Subject: Re: [BUGS] Excessive space allocations in Postgresql 9.1.6
system files causing the file system to run out of space.
From: Andres Freund <and...@2ndquadrant.com>
Date: Thu, February 28, 2013 12:23 pm
To: fburg...@radiantblue.com

Hi,

On 2013-02-27 14:21:44 -0700, fburg...@radiantblue.com wrote:
> <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> ...
> OTE></span></body></html>

youre more likely to get help if you send your emails either in
plain-text only or at least plain-text & html...

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services