public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dave Cramer <[email protected]>
To: squid ward <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: Re[2]: directory name equal database name
Date: Fri, 7 Feb 2020 04:23:30 -0500
Message-ID: <CADK3HHK7U=WeVkWyHOyzx-xQY0Aiw_VJhzrikzhVQ1-7p9ZjqQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>

On Fri, 7 Feb 2020 at 03:47, squid ward <[email protected]> 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 <[email protected]>:
>
> =?UTF-8?B?c3F1aWQgd2FyZA==?= <[email protected]
> <http:///[email protected]>; 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
>
>
>

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Re[2]: directory name equal database name
  In-Reply-To: <CADK3HHK7U=WeVkWyHOyzx-xQY0Aiw_VJhzrikzhVQ1-7p9ZjqQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox