Re: Database size stays constant but disk space keeps shrinking -- postgres 9.1

Dinesh --


----- Original Message -----
> From: Dinesh Bhandary 
> To: Greg Williamson 
> Cc: "pgsql-admin@postgresql.org" 
> Sent: Thursday, September 27, 2012 7:19 PM
> Subject: Re: [ADMIN] Database size stays constant but disk space keeps shrinking -- postgres 9.1
> 
> 
> I'd also keep track of tmp directory while building. I hope it is not
> creating a huge external sort file. By building you meant to say
> pg_restore, I assume.

Actually everything is done a single SQL commands; the DDL from the source gets run on the new database, and then we do a series of SQL commands. The tmp space doesn't seem to get hit as there are only a few sorts being run as we reduce a list of "users from" and "users to" to a single list of unique user ids.

> How big is your maintenance_work_mem? Index creation pools memory from
> this buffer. 


maintenance_work_mem = 1500MB# min 1MB

Which seems to be enough for this task set.

postgres@db11:~/9.1/main$ ls -lrt base/pgsql_tmp
total 0

Something malign in one of our applications, perhaps interacting with this hourly drop. Still working with the developers to see what they are doing "under the hood."

Thanks for the suggestions!

Greg