setup: postgresql 8.1.8, x86_64, gnu/linux
I recently used createdb -T to copy a large database and the process
failed due to disk full, but the space wasn't freed after that failure.
That led me to wonder how much of my disk usage was from failed or
interrupted operations.
In the 8.1.8 manual, Chapter 50, I read:
For each database in the cluster there is a subdirectory within
PGDATA/base, named after the database's OID in pg_database. This
subdirectory is the default location for the database's files;
in particular, its system catalogs are stored there.
So, in pg_database I see:
rkh@csb-dev=> select oid,datname from pg_database order by 1;
oid | datname
---------+-----------------------------
1 | template1
10792 | template0
10793 | postgres
1235174 | csb_02_02_2007
1379984 | csb-dev
2121234 | csb
2296808 | csb-dev-snapshot-2007-03-07
2422198 | csb-dev-snapshot-2007-03-08
2558026 | csb-dev-snapshot-2007-03-13
(9 rows)
And in PGDATA/base:
postgres@csb:/srv/postgresql/base> ls -l
total 207
drwx------ 3 postgres postgres 2640 2007-03-12 22:07 1
drwx------ 2 postgres postgres 2576 2007-02-16 17:21 10792
drwx------ 3 postgres postgres 2640 2007-03-12 20:48 10793
drwx------ 3 postgres Bioinfo 21472 2007-03-12 20:47 1235174
drwx------ 2 postgres Bioinfo 3872 2006-10-19 21:25 1363637 ?
drwx------ 2 postgres Bioinfo 1888 2006-10-20 08:32 1371793 ?
drwx------ 3 postgres Bioinfo 31336 2007-03-12 20:48 1379984
drwx------ 3 postgres Bioinfo 21336 2007-03-12 20:51 2121234
drwx------ 3 postgres Bioinfo 30720 2007-03-13 08:57 2296808
drwx------ 3 postgres Bioinfo 30680 2007-03-12 20:47 2422198
drwx------ 2 postgres Bioinfo 21304 2007-03-12 18:50 2549963 ?
drwx------ 2 postgres Bioinfo 20504 2007-03-12 20:47 2549977 ?
drwx------ 2 postgres Bioinfo 4320 2007-03-12 20:47 2550014 ?
drwx------ 2 postgres Bioinfo 18448 2007-03-13 03:57 2558026
I've put ? after the base/ subdirs that have no correspondent in
pg_database.
The manual clearly says that a each row in pg_database will have a
corresponding subdirectory in base/. Is the converse also true, ie,
should every subdir in base/ also have a row in pg_database? If so, am
I safe to rm the unreferenced subdirectories?
FWIW, I know that I've interrupted (^C) a few createdb -T operations
during the course of this installation, and I recently had the disk full
error. My best guess is that these orphaned directories result from
those interruptions and failure.
Thanks,
Reece
--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush
kernel warning: universe consuming too many resources. Killing.
universe killed due to catastrophic leadership. Try -uspres carter.
