PostgreSQL: Query for location of global tablespace

disk-spacepostgresql

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.

Best Answer

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"

Related Solutions

Postgresql – Choosing A PostgreSQL Authentication Method For A Large Course

Sounds like no fuss to me:

Create an OS account for each user, preferably with simple ssh key based authentication;
in pg_hba.conf use peer for local connections;
Optionally also use ident for host connections from 127.0.0.1 and run identd on the server. Not strictly required.
Set a password on their role for use with md5 authentication, then use md5 authentication for connections from allowed remote hosts with the hostssl method.

That way they'll be able to connect over SSL directly with a password, and locally (after ssh'ing in) with no password prompt.

What you describe:

My ideal connection method would have PostgreSQL ask the OS on which it is running to ask for a username and password, no matter where the client software is running.

sounds a bit like remote ident. This exists and works, but it's completely and utterly insecure. It was written for the days when "my personal computer" would elicit gales of laughter and nobody but nobody had root. These days anyone can set up a laptop (or phone) to claim to be any user they want over ident, so it's completely useless except on the local loopback where you control it.

It does sound like Kerberos is closer to what you want, but honestly it's going to be way more messing around than seems worthwhile. I'd just use peer for local connections and md5 for remote ones.

Postgres pg_basebackup – Resolving ‘Directory Exists but is Not Empty’ Error

In my opinion it is not related to mklink or Windows issues.

Per pg_basebackup docs, the main data directory will be placed in the target directory, but all other tablespaces will be placed in the same absolute path as they have on the server.

-F format
--format=format

    Selects the format for the output. format can be one of the following:

    p
    plain

    Write the output as plain files, with the same layout as the current data directory and tablespaces. When the cluster has no
    additional tablespaces, the whole database will be placed in the
    target directory. If the cluster contains additional tablespaces, the
    main data directory will be placed in the target directory, but all
    other tablespaces will be placed in the same absolute path as they
    have on the server.

    This is the default format.

Solution: do not run it on the same server, or change backup format.

Best Answer

Related Solutions

Postgresql – Choosing A PostgreSQL Authentication Method For A Large Course

Postgres pg_basebackup – Resolving ‘Directory Exists but is Not Empty’ Error

Related Question