PostgreSQL: Query for location of global tablespace?

select * from pg_tablespace; spcname | spcowner | spclocation | spcacl ------------+----------+-------------------+--------------------- pg_default | 10 | | pg_global | 10 | | adb | 2033793 | /database /adb | {adb =C/ adb}

I can‘t see how, from the client, to get the path to where the global tablespace is stored; an empty string is returned for it in the above query.

Unfortunately, we have many legacy systems in the field using a particular database created in the global tablespace, and it would be a monumental effort to get it moved into a user-created tablespace.

Hopefully I‘m just missing something really simple.     A:

pg_default and pg_global locations are "hardcoded".

pg_default lives in:  

test=# select setting||‘/base‘ from pg_settings where name=‘data_directory‘;

 and pg_global lives in:  

test=# 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       下面是代码中详细的说明：
   * Tablespaces in PostgreSQL are designed to allow users to determine
 * where the data file(s) for a given database object reside on the file
 * system.
 *
 * A tablespace represents a directory on the file system. At tablespace
 * creation time, the directory must be empty. To simplify things and
 * remove the possibility of having file name conflicts, we isolate
 * files within a tablespace into database-specific subdirectories.
 *
 * To support file access via the information given in RelFileNode, we
 * maintain a symbolic-link map in $PGDATA/pg_tblspc. The symlinks are
 * named by tablespace OIDs and point to the actual tablespace directories.
 * There is also a per-cluster version directory in each tablespace.
 * Thus the full path to an arbitrary file is
 *            $PGDATA/pg_tblspc/spcoid/PG_MAJORVER_CATVER/dboid/relfilenode
 * e.g.
 *            $PGDATA/pg_tblspc/20981/PG_9.0_201002161/719849/83292814
 *
 * 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
 *
 * To allow CREATE DATABASE to give a new database a default tablespace
 * that‘s different from the template database‘s default, we make the
 * provision that a zero in pg_class.reltablespace means the database‘s
 * default tablespace.    Without this, CREATE DATABASE would have to go in
 * and munge the system catalogs of the new database.
 *
    参考： http://dba.stackexchange.com/questions/9603/postgresql-query-for-location-of-global-tablespace src /backend/commands/tablespace.c

PostgreSQL: Query for location of global tablespace?

标签：