We had a situation where external sort was creating a humungous temp
file and the space was reclaimed when the process was completed.
Thanks.
Dinesh
On 9/28/2012 8:59 AM, Steve Crawford wrote:
On 09/27/2012 07:01 PM, Greg Williamson wrote:
Steve (and others who replied):
...
The other is a slimmed-down version of our production database,
which gets
recreated hourly by a shell script which pulls data from remote
servers, does a
pg_dump of the resulting 3 gig database, and then drops it.
...
Could you explain this process in more detail? Are you creating a new
database, reading in data, dumping then dropping the whole database or
just manipulating tables within an existing database?
Have you checked to see if there are any processes that have open
handles to
deleted files (lsof -X | grep deleted). Deleted files won't show up
in du
but won't release their disk space until the process exits. Perhaps
a script
or scripts, even one of your hourly ones, that terminate when the
server
restarts? You could save the output of lsof and ps immediately
before and after
a restart and compare them.
lsof -X | grep deleted | wc -l
shows: 835 such files.
A couple:
postgres 2540 postgres 50u REG 8,3
409600 93429 /var/lib/postgresql/9.1/main/base/2789
200/11816 (deleted)
postgres 2540 postgres 51u REG 8,3 18112512
49694570 /var/lib/postgresql/9.1/main/base/2789
200/2791679 (deleted)
<...>...
I'll leave it to you and Tom to puzzle over the the postgres-related
open files. Meanwhile, I'm a bit curious about the other 800+ and
whether they are associated with scripts or processes that are
connected to PostgreSQL.
First, what is the output of "select * from pg_stat_activity;"? Are
there connections you don't expect to see? If you force any of them
closed (after checking with anyone who may be impacted), do you see
any file handles released or disk-space freed?
Second, do any of the processes associated with the other
open-but-deleted files relate to programs or scripts that connect to
PostgreSQL? Next time you do a restart, do any of the processes exit
or do any of the deleted files get closed?
I'm wondering if you have processes that connect to PostgreSQL which
terminate and release their file-handles when PG is restarted.
Cheers,
Steve
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
