public inbox for [email protected]help / color / mirror / Atom feed
directory name equal database name 6+ messages / 4 participants [nested] [flat]
* directory name equal database name @ 2020-02-04 11:01 squid ward <[email protected]> 0 siblings, 2 replies; 6+ messages in thread From: squid ward @ 2020-02-04 11:01 UTC (permalink / raw) To: [email protected] Greetings. I am better know how work with Microsoft SQL Server, but i try learn postgresql too for my work. Question is why directory name not equal as database name? For example i create database named «test1» but in directory it will be something like 1564512 or whatever number is. Why directory name randomize as number, but not as text? Can i change it? ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: directory name equal database name @ 2020-02-04 11:15 Dave Cramer <[email protected]> parent: squid ward <[email protected]> 1 sibling, 0 replies; 6+ messages in thread From: Dave Cramer @ 2020-02-04 11:15 UTC (permalink / raw) To: squid ward <[email protected]>; +Cc: [email protected] On Tue, 4 Feb 2020 at 06:01, squid ward <[email protected]> wrote: > Greetings. > I am better know how work with Microsoft SQL Server, but i try learn > postgresql too for my work. > Question is why directory name not equal as database name? For example i > create database named «test1» but in directory it will be something like > 1564512 or whatever number is. Why directory name randomize as number, but > not as text? Can i change it? > The name is not random it is the Oid of the database. You may not change it Dave > > > ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: directory name equal database name @ 2020-02-04 16:20 Tom Lane <[email protected]> parent: squid ward <[email protected]> 1 sibling, 1 reply; 6+ messages in thread From: Tom Lane @ 2020-02-04 16:20 UTC (permalink / raw) To: squid ward <[email protected]>; +Cc: [email protected] =?UTF-8?B?c3F1aWQgd2FyZA==?= <[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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re[2]: directory name equal database name @ 2020-02-07 08:47 squid ward <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 2 replies; 6+ messages in thread From: squid ward @ 2020-02-07 08:47 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected] But why «FOO» and «foo» are different just for PostgreSQL? In other DBMS this not happend. Is it historical specific that difficult 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? 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. >Вторник, 4 февраля 2020, 19:20 +03:00 от Tom Lane <[email protected]>: > >=?UTF-8?B?c3F1aWQgd2FyZA==?= < [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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Re[2]: directory name equal database name @ 2020-02-07 09:23 Dave Cramer <[email protected]> parent: squid ward <[email protected]> 1 sibling, 0 replies; 6+ messages in thread From: Dave Cramer @ 2020-02-07 09:23 UTC (permalink / raw) To: squid ward <[email protected]>; +Cc: Tom Lane <[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 > > > ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: directory name equal database name @ 2020-02-07 11:12 William B. Clay <[email protected]> parent: squid ward <[email protected]> 1 sibling, 0 replies; 6+ messages in thread From: William B. Clay @ 2020-02-07 11:12 UTC (permalink / raw) To: [email protected]; +Cc: squid ward <[email protected]> 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. ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-02-07 11:12 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-02-04 11:01 directory name equal database name squid ward <[email protected]> 2020-02-04 11:15 ` Dave Cramer <[email protected]> 2020-02-04 16:20 ` Tom Lane <[email protected]> 2020-02-07 08:47 ` Re[2]: directory name equal database name squid ward <[email protected]> 2020-02-07 09:23 ` Re: Re[2]: directory name equal database name Dave Cramer <[email protected]> 2020-02-07 11:12 ` William B. Clay <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox