PostgreSQL: Query for location of global tablespace?

pg_default and pg_global locations are "hardcoded".

pg_default lives in:

select setting||'/base' from pg_settings where name='data_directory';

and pg_global lives in:

select setting||'/global' from pg_settings where name='data_directory';

src/backend/commands/tablespace.c says so:

 * There are two tablespaces created at initdb time: pg_global (for shared
 * tables) and pg_default (for everything else).  For backwards compatibility
 * and to remain functional on platforms without symlinks, these tablespaces
 * are accessed specially: they are respectively
 *          $PGDATA/global/relfilenode
 *          $PGDATA/base/dboid/relfilenode

Please also note, that exposing location of data directory is a - not so terrible but still - security hole.

app01@postgres=> show data_directory;
ERROR:  must be superuser to examine "data_directory"

PostgreSQL creates pg_default and pg_global when you create a cluster, perhaps by using initdb directly. The initdb utility can take an argument that sets the data directory, but no arguments about where to put the pg_default and pg_global tablespaces.

I'd conclude they're always created in the data directory. I could easily be wrong about that. I don't think they can be moved. I could be wrong about that, too.

But if I'm right up to this point, you can derive their physical location by

show data_directory;

The pg_global tablespace is almost certainly is the "global" subdirectory; pg_default probably is, too.

If I had time, I'd read the source code. That would remove all doubt.