Reply-To: William.B.Clay@acm.org
Subject: Re: directory name equal database name
To: pgsql-interfaces@lists.postgresql.org
References: <1580814072.863026304@f532.i.mail.ru>
 <7029.1580833215@sss.pgh.pa.us> <1581065244.573732834@f438.i.mail.ru>
From: "William B. Clay" <bill@italianaccent.com>
Cc: squid ward <squidw@mail.ru>
Message-ID: <94b3a8eb-a6e9-cd74-2dcb-54efc97e72be@italianaccent.com>
Date: Fri, 7 Feb 2020 12:12:42 +0100
MIME-Version: 1.0
In-Reply-To: <1581065244.573732834@f438.i.mail.ru>
Content-Type: multipart/alternative;
 boundary="------------AB18C3A799EC16B0CCE39258"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------AB18C3A799EC16B0CCE39258
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

On 2/7/20 9:47 AM, squid ward wrote:
> I try ask in another way. If I will not rename dabases of existing 
> PostgreSQL installation, but i configure PostgreSQL from the 
> beggining, to make directory name equal to database name, or whatever 
> existing installation or preparation before installation from zero i 
> cant change this mechanism?
...
> As sysadmin i support many companies where i come as remote specialist 
> from time to time. Sometime in companies happen this: server crashed, 
> no one configure backup with pg_dump, left only directory with bases 
> PostgreSQL where inside list of OID databases, over 100 OIDs directory 
> name that nothing mean. Some databases not critical for work, so can 
> be connected later, but some databases asking connect very fast. So 
> when i have 100 databases i even dont know what i need to do for 
> understanding which one of this databases are «Accounting» database 
> for example.

Squid,

As others have explained, you cannot control the filenames PostgreSQL 
uses to store databases, but you CAN control how databases and tables 
are mapped onto the host OS filesystem so as to ease DBMS 
administration, including for space management and backup and recovery.

The PostgreSQL way of doing this is through the use of TABLESPACEs, 
which you can name with any valid PostgreSQL identifier not beginning 
with "_pg" (e.g., "Accounting").  From PostgreSQL documentation, chapter 
22.6:

    Tablespaces in PostgreSQL allow database administrators to define
    locations in the file system where the files representing database
    objects can be stored. Once created, a tablespace can be referred to
    by name when creating database objects.

    ...

    The location [in which the TABLESPACE is created] must be an
    existing, empty directory that is owned by the PostgreSQL operating
    system user. All objects subsequently created within the tablespace
    will be stored in files underneath this directory. The location must
    not be on removable or transient storage, as the cluster might fail
    to function if the tablespace is missing or lost.

    ...

    Creation of the tablespace itself must be done as a database
    superuser, but after that you can allow ordinary database users to
    use it. To do that, grant them the CREATE privilege on it.

    Tables, indexes, and entire databases can be assigned to particular
    tablespaces. To do so, a user with the CREATE privilege on a given
    tablespace must pass the tablespace name as a parameter to the
    relevant command.

See also Section IV.I (Reference/SQL Commands), the "CREATE TABLESPACE" 
command and the TABLESPACE parameter of, e.g., the CREATE DATABASE command.

If you install PostgreSQL and create databases and tables using all 
defaults, you'll never even notice tablespaces, but to obtain the degree 
of control you seek, this facility has always been available and is 
well-documented.


--------------AB18C3A799EC16B0CCE39258
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 2/7/20 9:47 AM, squid ward wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:1581065244.573732834@f438.i.mail.ru">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      I try ask in another way. If I will not rename dabases of existing
      PostgreSQL installation, but i configure PostgreSQL from the
      beggining, to make directory name equal to database name, or
      whatever existing installation or preparation before installation
      from zero i cant change this mechanism?<br>
    </blockquote>
    ...<br>
    <blockquote type="cite"
      cite="mid:1581065244.573732834@f438.i.mail.ru">
      <div>As sysadmin i support many companies where i come as remote
        specialist from time to time. Sometime in companies happen this:
        server crashed, no one configure backup with pg_dump, left only
        directory with bases PostgreSQL where inside list of OID
        databases, over 100 OIDs directory name that nothing mean. Some
        databases not critical for work, so can be connected later, but
        some databases asking connect very fast. So when i have 100
        databases i even dont know what i need to do for understanding
        which one of this databases are «Accounting» database for
        example.</div>
    </blockquote>
    <p>Squid,</p>
    <p>As others have explained, you cannot control the filenames
      PostgreSQL uses to store databases, but you CAN control how
      databases and tables are mapped onto the host OS filesystem so as
      to ease DBMS administration, including for space management and
      backup and recovery.<br>
    </p>
    <p>The PostgreSQL way of doing this is through the use of
      TABLESPACEs, which you can name with any valid PostgreSQL
      identifier not beginning with "_pg" (e.g., "Accounting").  From
      PostgreSQL documentation, chapter 22.6:</p>
    <blockquote>
      <p>Tablespaces in <span class="PRODUCTNAME">PostgreSQL</span>
        allow database administrators to define locations in the file
        system where the files representing database objects can be
        stored. Once created, a tablespace can be referred to by name
        when creating database objects. <br>
      </p>
      <p>...</p>
      <p>The location [in which the TABLESPACE is created] must be an
        existing, empty directory that is owned by the <span
          class="PRODUCTNAME">PostgreSQL</span> operating system user.
        All objects subsequently created within the tablespace will be
        stored in files underneath this directory. The location must not
        be on removable or transient storage, as the cluster might fail
        to function if the tablespace is missing or lost. </p>
      <p>...</p>
      <p> Creation of the tablespace itself must be done as a database
        superuser, but after that you can allow ordinary database users
        to use it. To do that, grant them the <tt class="LITERAL">CREATE</tt>
        privilege on it. </p>
      <p> Tables, indexes, and entire databases can be assigned to
        particular tablespaces. To do so, a user with the <tt
          class="LITERAL">CREATE</tt> privilege on a given tablespace
        must pass the tablespace name as a parameter to the relevant
        command.</p>
    </blockquote>
    <p>See also Section IV.I (Reference/SQL Commands), the "CREATE
      TABLESPACE" command and the TABLESPACE parameter of, e.g., the
      CREATE DATABASE command.</p>
    <p>If you install PostgreSQL and create databases and tables using
      all defaults, you'll never even notice tablespaces, but to obtain
      the degree of control you seek, this facility has always been
      available and is well-documented.</p>
  </body>
</html>

--------------AB18C3A799EC16B0CCE39258--