Re: Re[2]: directory name equal database name

On Fri, 7 Feb 2020 at 03:47, squid ward <squidw(at)mail(dot)ru> wrote:

> But why «FOO» and «foo» are different just for PostgreSQL? In other DBMS
> this not happend. Is it historical specific that difficult change?
>

This would be very difficult to change.

> 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?ut
>

Well you have the source code and it may be possible to change it, but it's
not a trivial change.

I try put example for understanding. I use PostgreSQL as backend for
> another applications. This applications use many people for accounting,
> hrm, erp, etc. In my case this applications have one vendor, but in the
> same time they all are separated. I mean in Microsoft SQL Server
> application called «Accounting» as frontend for users equal to Microsoft
> SQL Server database name «Accounting» AND equal directory name of database
> «Accounting». In PostgreSQL the last one is named by OID. Its passable when
> we have 1-5 bases, not much time waste to understand which database i am
> looking for, but when its over 10 databases its become difficult support
> and administrate. 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. In Microsoft SQL Server this never happen, just because database
> equal directory name.
> In this example abstractedly question, how to attach PostgreSQL database
> when have just directory left from PostgreSQL with databases after server
> crashed without backup. In Microsoft SQL Server i know how do it in 2
> click. Maybe this last paragraph with question i should ask in another
> mail list, but thanks in advance if answer this too.
>

The simple answer to your question is to use the following query to find
the name of the database

select datname,oid from pg_database

This will show you the names and the filenames of the database.

you can also query using the filename to see what database it represents

select datname,oid from pg_database where oid = 'oidfromfilename'

>
>
>
>
>
> Вторник, 4 февраля 2020, 19:20 +03:00 от Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>
> =?UTF-8?B?c3F1aWQgd2FyZA==?= <squidw(at)mail(dot)ru
> <http:///compose?To=squidw(at)mail(dot)ru>> writes:
> > Question is why directory name not equal as database name?
>
> Because (1) it doesn't work for database names that aren't legal as
> filenames (according to the none-too-well-standardized rules of your
> particular filesystem); (2) SQL requires us to treat "FOO" and "foo"
> as distinct names, but the filesystem might not; (3) renaming a database
> is dangerous, as there's no way to make the directory rename action
> atomic with commit of the system catalog updates; (4) renaming a database
> causes problems for concurrent sessions that might be in the middle of
> opening files in that database. And probably a few other reasons I
> don't recall off the top of my head.
>
> > Can i change it?
>
> No.
>
> regards, tom lane
>
>
>