On Mon, Nov 5, 2012 at 8:01 PM, Lists <lists@xxxxxxxxxxxxxxxxxx> wrote: > We upgraded to 9.1 from 8.4 over the summer a few months ago, to new DB > servers with more disk space and memory. Unexpectedly, the DB servers have > steadily increased their disk space usage since. Reported system load > doesn't seem to be affected. It's happening to all our DB servers running > 9.1. > > When we reload all pg_dumps from our worst-affected server into an offline > server, the disk space usage is about 26 GB, but the production database is > using 166 GB. (# df /var/lib/pgsql;) > > To resolve this, we've tried: > > 1) reindexed everything (cut about 10% of disk usage temporarily) > > 2) tried vacuum full, and vacuum analyze on all databases. (to minimal > effect) > > 3) Restarting PG (no discernable effect) including a full stop/start. > > 4) We've looked for stale prepared transactions (none found) > > 5) instructions from the wiki to try to determine what the cause of all the > disk usage is: http://wiki.postgresql.org/wiki/Disk_Usage but when we add up > all the results for all the different databases, tables, indexes, etc. in a > script, we get a number very close to the usage of the freshly loaded > server. (24 GB) What does du -sh have to say about it? Use unix tools to examine your file system and see where the usage is going. For instance, I can do this: cd /var/lib/postgresql/8.4/main/ du -s *|sort -n 0 server.crt 0 server.key 4 pg_tblspc 4 pg_twophase 4 PG_VERSION 4 postmaster.opts 4 postmaster.pid 12 pg_clog 12 pg_stat_tmp 12 pg_subtrans 28 pg_multixact 460 global 16392 pg_xlog 16396 base which tells me that I'm using about 16MB for each pg_xlog and base. I can then do cd into base and look around: cd base du -s *|sort -n 5416 1 5416 11563 5560 11564 Which shows me using about 5MB each for three different dbs. And so on. On an off guess, did you go from a SQL_ASCII encoding to UTF8? That might increase disk space usage a bit. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
