On Sat, Jan 21, 2012 at 1:37 AM, Dan Charrois <dan...@syz.com> wrote:
> Hi everyone. I'm currently in the situation of administering a rather large
> PostgreSQL database which for some reason seems to be even much larger than
> it should be.
>
> I'm currently running version 8.4.5 - not the latest and greatest, I know -
> but this is a live database that would problematic to take down to upgrade
> unless all else fails - especially considering its size if it does need to be
> rebuilt somehow.
>
> Anyway, I'm no stranger to SQL, but new to PostgreSQL - all my SQL
> administration in the past has been with MySQL. So I'm somewhat bumbling my
> way through administrative commands trying to solve this - please bear with
> me.
>
> The size of the tables reported by \dt+ add up to around 120 GB. The size of
> the indexes reported with \di+ adds up to around 15 GB. This is pretty
> consistent with what I would expect the data to require.
>
> The problem is, the disk usage of the pgsql directory where the data is kept
> (as reported by 'du') comes to 647 GB - significantly more than it should.
> select pg_database_size('mydatabase') confirms this, returning 690830939920.
>
> Vacuuming the tables (full and otherwise) hasn't helped, but then considering
> how the database is used, I didn't really expect it to. It's strictly a
> read-only database, with the exception of once a month when it is refreshed
> by loading new data into newly created tables, and once that is done, vacuum
> analyzing the new tables, dropping the old tables, then renaming the new ones
> to have the name of the old ones. Vacuums never claim to recover any space,
> and the disk usage stays the same.
>
> So how do I find out what's eating up all this extra space?
Real quick, if you run pg_database_size(name) for each db, including
template1 and postgres, what do you get back?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
