Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1izzrb-0001jX-9R for pgsql-interfaces@arkaria.postgresql.org; Fri, 07 Feb 2020 09:23:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1izzra-0003kd-4H for pgsql-interfaces@arkaria.postgresql.org; Fri, 07 Feb 2020 09:23:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1izzrZ-0003kW-S7 for pgsql-interfaces@lists.postgresql.org; Fri, 07 Feb 2020 09:23:49 +0000 Received: from mail-pf1-f179.google.com ([209.85.210.179]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1izzrV-0003q1-6p for pgsql-interfaces@lists.postgresql.org; Fri, 07 Feb 2020 09:23:49 +0000 Received: by mail-pf1-f179.google.com with SMTP id x185so973545pfc.5 for ; Fri, 07 Feb 2020 01:23:44 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=FRpS8tlxbGPlEi1pBg9tXcJ3d9gfIcQK7CQBkzUUGL0=; b=B7VPFi7vUe45VoAGlS8tzbY91asq0kRVhGb+VHDpmrUPxwhcUkaglMViyWxlOjEtzI cGqgetOaJISQ2+hkhiCoXxv75C7HN6poQedcSQVKu3cT55OWYCftZ+KJoFUaXIO58j0p CGyczaQxEnNXr0OwRrsj0X1nzAs4xJetMoTn6VzexP+667nETLWR2BSBM2xkHA0T79eA +XdI7tUobcd9tbpWSW7NXIb+UIPqGAuas9w1NgV1dKOFo2TJBRLKTtfO8R44tyYYIX63 ZDn0LhLf51KQDpfTb9bqoCk2TktxOnY8onmx+VpLAQjFrCf8gGRDsce4491V1DSKmu3V DuRQ== X-Gm-Message-State: APjAAAUPhTXYWqJNeCnkoUuzLiWC2PRl0StSvXbFN+7k/12KGN8pgqie TJQwQep8vBF3LM81GKmO5QC56ihm9FhGn6Wp5A0= X-Google-Smtp-Source: APXvYqz+vmVCre1FnUo9R+H70ZX2fpbmTDbeKU9ybmJHC16RmHswKl8byx1okFmstHLDyStSfyMA/6qHchvkeXdc29o= X-Received: by 2002:a63:48d:: with SMTP id 135mr8573758pge.350.1581067422956; Fri, 07 Feb 2020 01:23:42 -0800 (PST) MIME-Version: 1.0 References: <1580814072.863026304@f532.i.mail.ru> <7029.1580833215@sss.pgh.pa.us> <1581065244.573732834@f438.i.mail.ru> In-Reply-To: <1581065244.573732834@f438.i.mail.ru> From: Dave Cramer Date: Fri, 7 Feb 2020 04:23:30 -0500 Message-ID: Subject: Re: Re[2]: directory name equal database name To: squid ward Cc: Tom Lane , pgsql-interfaces@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000001c832d059df8f2da" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --0000000000001c832d059df8f2da Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, 7 Feb 2020 at 03:47, squid ward wrote: > But why =C2=ABFOO=C2=BB and =C2=ABfoo=C2=BB are different just for Postgr= eSQL? 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, t= o > 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 =C2=ABAccounting=C2=BB as frontend for users equal to = Microsoft > SQL Server database name =C2=ABAccounting=C2=BB AND equal directory name = of database > =C2=ABAccounting=C2=BB. In PostgreSQL the last one is named by OID. Its p= assable 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, s= o > 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 =C2=ABAccounting=C2=BB data= base 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 =3D 'oidfromfilename' > > > > > > =D0=92=D1=82=D0=BE=D1=80=D0=BD=D0=B8=D0=BA, 4 =D1=84=D0=B5=D0=B2=D1=80=D0= =B0=D0=BB=D1=8F 2020, 19:20 +03:00 =D0=BE=D1=82 Tom Lane : > > =3D?UTF-8?B?c3F1aWQgd2FyZA=3D=3D?=3D > 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 > > > --0000000000001c832d059df8f2da Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Fri, 7 Feb 2020 at 03:47, squid wa= rd <squidw@mail.ru> wrote:
<= /div>
But why =C2=ABFOO=C2=BB and =C2=ABfoo=C2=BB are different just fo= r PostgreSQL? In other DBMS this not happend. Is it historical specific tha= t difficult change?

This wo= uld be very difficult to change.
=C2=A0
I try ask in another way. If I will= not rename dabases of existing PostgreSQL installation, but i configure Po= stgreSQL 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=C2=A0

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

I try put example for under= standing. I use PostgreSQL as backend for another applications. This applic= ations use many people for accounting, hrm, erp, etc. In my case this appli= cations have one vendor, but in the same time they all are separated. I mea= n in Microsoft SQL Server application called =C2=ABAccounting=C2=BB as fron= tend for users equal to Microsoft SQL Server database name =C2=ABAccounting= =C2=BB AND equal directory name of database =C2=ABAccounting=C2=BB. In Post= greSQL 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 suppor= t and administrate. As sysadmin i support many companies where i come as re= mote specialist from time to time. Sometime in companies happen this: serve= r crashed, no one configure backup with pg_dump, left only directory with b= ases PostgreSQL where inside list of OID databases, over 100 OIDs directory= name that nothing mean. Some databases not critical for work, so can be co= nnected 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 on= e of this databases are =C2=ABAccounting=C2=BB database for example. In Mic= rosoft SQL Server this never happen, just because database equal directory = name.
In this example abstractedly question, how to attach PostgreSQL da= tabase when have just directory left from PostgreSQL with databases after s= erver 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 adva= nce if answer this too.

The sim= ple answer to your question is to use the following query to find the name = of the database

select dat= name,oid from pg_database

This will show y= ou the names and the filenames of the database.

yo= u can also query using the filename to see what database it represents

select datname,oid from pg_database where oi=
d =3D 'oidfromfilename'
=C2=A0
=C2=A0
=C2= =A0
=C2=A0
=C2=A0
= =D0=92=D1=82=D0=BE=D1=80=D0=BD=D0=B8=D0=BA, 4 =D1=84=D0=B5=D0=B2=D1=80=D0= =B0=D0=BB=D1=8F 2020, 19:20 +03:00 =D0=BE=D1=82 Tom Lane <tgl@sss.pgh.pa.us>:
=C2= =A0
=3D?UTF-8?B?c3F1aWQgd2Fy= ZA=3D=3D?=3D <squidw@mail.ru> writes:
> Question is why directory na= me not equal as database name?

Because (1) it doesn't work for d= atabase names that aren't legal as
filenames (according to the none-= too-well-standardized rules of your
particular filesystem); (2) SQL requ= ires us to treat "FOO" and "foo"
as distinct names, = but the filesystem might not; (3) renaming a database
is dangerous, as t= here'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 t= he top of my head.

> Can i change it?

No.

regards, = tom lane
=C2=A0
--0000000000001c832d059df8f2da--