Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have been trying to make our database clients pro-active about not filling up the partition on which the database they are using resides.

As all our clients are on the same host as the database manager, it should be easy enough for user-created tablespaces; the client can look up the filesystem path for the tablespace (in spclocation), and use OS calls to check how much available space is available:

adb=> 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.

share|improve this question
add comment

2 Answers 2

up vote 5 down vote accepted

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"
share|improve this answer
    
I'll upvote that. –  Mike Sherrill 'Cat Recall' Dec 22 '11 at 19:57
    
Thanks for the reply. Unfortunately, in our installations at least, there is no matching row for name='data_directory'in pg_settings, even though the location on disk for the postgreSQL database is definitely not the default. –  Peter Dec 22 '11 at 23:52
    
@Peter, the row is not visible for non-superuser account. –  filiprem Dec 23 '11 at 9:56
add comment

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.

share|improve this answer
    
Thanks for the reply. Unfortunately, (as I said to filiprem), in our installations at least, we don't seem to have the data_directory available via settings lookup (as superuser), even though the location on disk for the postgreSQL database is definitely not the default (we're using 8.3.11-1). –  Peter Dec 22 '11 at 23:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.