"Leaking" disk space

[Eduardo Morras]

On Wed, 20 Mar 2013 16:55:34 +0000
Dan Thomas <godders at gmail.com> wrote:

> > a) Where do you have the wal files?
> 
> pg_xlog is symlinked to /usr/local/pglog/pg_xlog (ie, out of the
> partition mounted as /usr/local/pgsql which is exhibiting this
> behaviour).
> 

As Matthew Seaman says in other answer, this is the problem. Check http://lists.freebsd.org/pipermail/freebsd-fs/2013-March/016702.html and next thread messages.

It seems that writing file follow the symlink but makes a shadow/ghost file entry in original directory/disk. I see that you don't have trim enabled on the postgres fs, tunefs -p /usr/local/pgsql/  shows option t disabled. Is trim enabled on the fs where the symlink points? (Show the output of tunefs -p /dev/_don't_know_the_dev_entry_name)

> b) Are you sure that unused/old wal files are erased?
> 
> As above, but yes they seem to be being deleted properly
> 
> c) Do you have any postgres log level activated (like the ones used
> for long queries)?
> 
> Yes we have slow query logging enabled. pg_log is symlinked out of
> that partition to /usr/local/pglog/pg_log as well.
> 
> d) Does your queries have GROUP BY on very big data sets? Those create
> big temporal data files.
> 
> Yes we do a lot of that! However there are definitely no unlinked
> files, and the problem doesn't go away when pg is shut down. However a
> reboot does fix it.

Those questions were only to check and be sure is not a "normal" temp files problem.

What does dmesg show about filesystem check? Does it mark dirty filesystem? 

# WARNING!! Make a backup first!!!
If you stop postgres, and shoot #fsck_ffs -E /dev/mfid1s1d , does the problem solve?
# END WARNING!!

Please post the output of the fsck_ffs.

If the fsck_ffs doesn't solve the problem, check if there exist a lost+found directory on /usr/local/pgsql/ and it's content.


> 
> e) With question a) and b), do you use streaming replication?
> 
> Yes we do. This problem is not present on the warm standby servers
> that are being streamed to. We have failed over to the warm standbys
> previously (we're currently doing this regularly to work around the
> problem without too much downtime). Once we switch the warm standby to
> primary, it begins leaking space.

It may store old&all wal files, but it seems a bug at filesystem level. Trim support in ufs was added to 9.0 and backported to 8 and may be a candidate to watch.


---   ---
Eduardo Morras <emorrasg at yahoo.es>