help with moving tablespace - Mailing list pgsql-general

Back in the early dawn of time (before I came here :) the tablespace for our DB was put under the data dir, which gives
usthe warning "tablespace location should not be inside the data directory", which I'm trying to fix -- and I'm also
tryingto fix ownerships so everything isn't owned by postgres. 

So in our application "upgrade" script (in bash), I'm doing:

ts_location=/db/tablespaces
mkdir $ts_location
chmod 700 $ts_location
chown postgres:dba $ts_location
echo "CREATE TABLESPACE new_ts OWNER ourowner LOCATION '$ts_location';
          ALTER DATABASE ourdb OWNER TO ourowner;
          ALTER DATABASE ourdb SET TABLESPACE new_ts;
          DROP TABLESPACE old_ts; " |
    $PGPATH/psql template1
echo "ALTER SCHEMA public OWNER TO ourowner;" |
    $PGPATH/psql ourdb
# the only reason this should fail is if the above command failed
rmdir $PGDATA/ourdb || { echo "ERROR: Failed to remove $PGDATA/ourdb; the alters must have failed."; exit 1; }

Also, $PGDATA (and therefore the old tablespace) is under /db.

I appreciate the ease of the above commands, but sadly there are 2 issues with it that I need help with.

First, the above works only *most* of the time in our testing on multiple servers. When it fails, it's because not
everythingwas moved out of the old tablespace and I don't understand why. An "ls $PGDATA/ourdb/PG*/" shows files are
stillpresent. According to some searching, I should be able to do: 

SELECT c.relname, t.spcname
FROM   pg_class c JOIN pg_tablespace t ON c.reltablespace = t.oid
WHERE  t.spcname = 'old_name';

But that always returns 0 rows. So how do I track this down?

Second, the "ALTER DATABASE ourdb SET TABLESPACE new_ts" which does the move is slow even on our smaller test DBs,
almostas if it is having to dump and reload (or more likely copy) the data. This raises the concern of how long this is
goingto take on our bigger DBs. Is there a faster way to accomplish the same thing especially since the new and old
tablespacesare on the same disk partition? 

For example, from what I can see the data is sitting in a dir and there is a symlink to it in $PGDATA/pg_tblspc. Could
Ishut down PG, move the DB dir, recreate the symlink in pg_tblspc, then restart PG and all would be well in only a few
seconds?

Bonus question: I found an ER diagram of some of the pg_* tables at
http://www.slideshare.net/oddbjorn/Get-to-know-PostgreSQL.Is there an ERD of all of them so a person can better
understandhow to use them when one must? I suppose the same question applies to information_schema since I probably
shouldbe using that over the pg_* tables when possible (and as the above example shows, sometimes you have to go look
atthe pg_* tables). 

Thanks!
Kevin