Thread: Unaccounted for disk use
I was running the pgMigration plugin for PgAdminII and half way along, it failed. Thing is, there's about 142 megs of space being used up by my data/ directory that really doesn't show up as tables anywhere. The database that was receiving all this information is empty. I've done what little I could think of which is turn Postgres off and on again, and vaccumm the DB(s). The space is still being used. How is it I can get rid of this? Thanks, Chris -- Chris Cameron UpNIX Internet Administrator ardvark.upnix.net bitbucket.upnix.net -- http://www.upnix.com
did you do vacuum full? Or just a vacuum? -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Chris Cameron Sent: Tuesday, May 20, 2003 7:51 PM To: pgsql-novice@postgresql.org Subject: [NOVICE] Unaccounted for disk use I was running the pgMigration plugin for PgAdminII and half way along, it failed. Thing is, there's about 142 megs of space being used up by my data/ directory that really doesn't show up as tables anywhere. The database that was receiving all this information is empty. I've done what little I could think of which is turn Postgres off and on again, and vaccumm the DB(s). The space is still being used. How is it I can get rid of this? Thanks, Chris -- Chris Cameron UpNIX Internet Administrator ardvark.upnix.net bitbucket.upnix.net -- http://www.upnix.com ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
Chris Cameron <chris@upnix.com> writes:
> I was running the pgMigration plugin for PgAdminII and half way along,
> it failed.
> Thing is, there's about 142 megs of space being used up by my data/
> directory that really doesn't show up as tables anywhere.
> The database that was receiving all this information is empty. I've done
> what little I could think of which is turn Postgres off and on again,
> and vaccumm the DB(s). The space is still being used.
Indexes? WAL space? Poke around with "du" ...
regards, tom lane
I've tried: vacuumdb -f -U postgres dbname vacuumdb -a -f -z -U postgres As suggested in another email, I've been looking around to see where this space was getting used. [root@ilearner base]# pwd /var/lib/pgsql/data/base [root@ilearner base]# du -hs * 3.5M 1 3.6M 16975 103M 16976 [root@ilearner base]# cd 16976 [root@ilearner 16976]# du -hs * | grep M 26M 1456419 6.1M 1456422 35M 639251 30M 639253 [root@ilearner 16976]# So, that means nothing to me, hopefully it helps someone else though. Thanks, Chris On Tue, 2003-05-20 at 21:27, Paul Fontenot wrote: > did you do vacuum full? Or just a vacuum? -- Chris Cameron UpNIX Internet Administrator ardvark.upnix.net bitbucket.upnix.net -- http://www.upnix.com
Chris Cameron <chris@upnix.com> writes:
> [root@ilearner 16976]# du -hs * | grep M
> 26M 1456419
> 6.1M 1456422
> 35M 639251
> 30M 639253
^^^^^^
The file names correspond to values in the relfilenode column of
pg_class. Now look in pg_class to discover exactly which tables or
indexes these large files are.
regards, tom lane
Start psql and run:
steve=# select oid,datname from pg_database;
1 | template1
16975 | template0
557126 | phones
557196 | steve
Those oids will relate to the directories in the database:
betelgeuse:/var/lib/pgsql/data/base # ls -l
drwx------ 2 postgres postgres 4096 May 16 10:09 1
drwx------ 2 postgres postgres 4096 May 15 17:15 16975
drwx------ 2 postgres postgres 4096 May 16 10:12 557126
drwx------ 2 postgres postgres 4096 May 22 10:42 557196
Then run:
select oid,relname from pg_class;
oid | relname
--------+---------------------------------
...
557249 | byteatest
557275 | projectcheck
...
Then switch to the appropriate database subdirectory and look for files with
the same name as the oids from pg_class. (Of course I have oversimplified a
bit in that you could have the same table name in multiple databases but it
will be pretty easy to distinguish as you will only find one of them in your
subdirectory.)
Cheers,
Steve
On Wednesday 21 May 2003 09:12, Chris Cameron wrote:
> I've tried:
>
> vacuumdb -f -U postgres dbname
> vacuumdb -a -f -z -U postgres
>
>
> As suggested in another email, I've been looking around to see where
> this space was getting used.
>
> [root@ilearner base]# pwd
> /var/lib/pgsql/data/base
> [root@ilearner base]# du -hs *
> 3.5M 1
> 3.6M 16975
> 103M 16976
> [root@ilearner base]# cd 16976
> [root@ilearner 16976]# du -hs * | grep M
> 26M 1456419
> 6.1M 1456422
> 35M 639251
> 30M 639253
> [root@ilearner 16976]#
>
>
> So, that means nothing to me, hopefully it helps someone else though.
>
>
> Thanks,
> Chris
>
> On Tue, 2003-05-20 at 21:27, Paul Fontenot wrote:
> > did you do vacuum full? Or just a vacuum?