Thread: Database subdirectories in V7.1.x
Back in August there was a posting regarding the names of database subdirectories. Under PostgreSQL 7.0.x the subdirectory names were the name of the database. Made a lot of sense. Now under V7.1.x we're supposed to somehow know that some numbered subdirectory happens to be the location of a certain database's files. Doesn't make /any/ sense at all. Why? I have databases that I want to segregate onto their own filesystem so I have to be able to mount that filesystem under $PGDATA. A mount point that has an understandable name would be nice. Is there some way to revert back to the V7.0.x way of naming things? Or is there some workaround to allow one to reasonably manage large databases under V7.1.x?
On 16 Oct 2001, Rick Turner wrote: > Is there some way to revert back to the V7.0.x way of naming things? Probably not. > Or is there some workaround to allow one to reasonably manage large > databases under V7.1.x? In contrib there's a utility oid2name which will give the number/name mappings for databases and tables. The reason tables got renamed into numbers was due to rollbacks of drops or drops followed by creates in the same transaction. I assume that the plan was to move databases in the same direction (although it looks like create database and drop database both error when called within an explicit begin block).
/contrib/oid2name does the job. > Back in August there was a posting regarding the names of database > subdirectories. Under PostgreSQL 7.0.x the subdirectory names were > the name of the database. Made a lot of sense. Now under V7.1.x > we're supposed to somehow know that some numbered subdirectory happens > to be the location of a certain database's files. Doesn't make /any/ > sense at all. Why? I have databases that I want to segregate onto > their own filesystem so I have to be able to mount that filesystem > under $PGDATA. A mount point that has an understandable name would be > nice. > > Is there some way to revert back to the V7.0.x way of naming things? > Or is there some workaround to allow one to reasonably manage large > databases under V7.1.x? > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> The reason tables got renamed into numbers was due to rollbacks of
> drops or drops followed by creates in the same transaction.
That was one reason, but an equally big reason was to have a compact way
of referencing tables in the WAL logs, such that a process re-applying
changes during crash recovery wouldn't need any outside info to apply
the changes. Database and table OIDs fit that description, names do
not.
> I assume that the plan was to move databases in the same direction
I don't think that there are any plans to make DROP DATABASE
rollback-able, but the WAL recovery issue is sufficient reason to use
database OIDs for directory names.
The short answer is that this design decision will not be reversed...
learn about oid2name if you feel a need to understand the substructure
of $PGDATA.
regards, tom lane
Thanks guys... I was able to do employ the symbolic link trick to get my old filesystems set up for use with 7.1's way of doing things and am glad I don't /really/ need to remember what the heck is that mounted on /opt/postgresql/data/base/19162, etc.. This would have been less of a PITB if it had been more obvious in the v7.1 notes that something like the naming change was going to happen and what the impact was going to be. I went back last night and looked again and -- for the life of me -- still didn't see anything except that one-line mention in the HISTORY which (obviously) didn't raise any flags. I was only too happy to dump all the databases if it meant that longer rows were going to be available but I sure felt blindsided by this new naming scheme. At least there's a solution. Thanks again. BTW, anyone know how to put the hair back in once you've torn it out?
> Thanks guys... > > I was able to do employ the symbolic link trick to get my old > filesystems set up for use with 7.1's way of doing things and am glad > I don't /really/ need to remember what the heck is that mounted on > /opt/postgresql/data/base/19162, etc.. > > This would have been less of a PITB if it had been more obvious in the > v7.1 notes that something like the naming change was going to happen > and what the impact was going to be. I went back last night and > looked again and -- for the life of me -- still didn't see anything > except that one-line mention in the HISTORY which (obviously) didn't > raise any flags. I was only too happy to dump all the databases if it > meant that longer rows were going to be available but I sure felt > blindsided by this new naming scheme. At least there's a solution. > Thanks again. I totally agree with you here. Numeric file names are a pain for administrators that want to do database accounting at the file system level. Surprisingly, we haven't had too many people complain so it seems there aren't as many people doing this as I thought. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026