Re: directory name equal database name

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.