Re: pg_buffercache - A lot of "unnamed" relfilenodes?

What I ended up doing to work around not seeing anything besides your
active database for my own tests was write a simple shell script that
connected to all of the database in succession, saving everything in the
catalog that pg_buffercache needs to join against to a table. Then I
joined against that table rather than the catalog entries. This is
potentially a security issue on a real system, but if you're the admin and
want to analyze the whole database, it's easy enough to make that table
invisible to everyone but you.

Don't have that code available to give out, but it wasn't hard to write;
something like this did the main work:

#!/bin/bash
dbs=`psql -At -c "select datname from pg_database"`
for d in $dbs
do
psql -d $d -c "INSERT INTO full_catalog SELECT relname, relfilenode, (SELECT oid FROM pg_database WHERE datname = current_database()) from
pg_class"
done

Add a simple schema that follows the structure of the fields in pg_class
and pg_database needed and you're off. You may still get unnamed stuff
that's just not visible yet, the cross-database stuff was the source for
most of the missing bits I ran into.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD