Thread: Database subdirectories in V7.1.x

Database subdirectories in V7.1.x

From
rnturn@baxter.com (Rick Turner)
Date:
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?

Re: Database subdirectories in V7.1.x

From
Stephan Szabo
Date:
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).


Re: Database subdirectories in V7.1.x

From
Bruce Momjian
Date:
/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

Re: Database subdirectories in V7.1.x

From
Tom Lane
Date:
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

Re: Database subdirectories in V7.1.x

From
rnturn@baxter.com (Rick Turner)
Date:
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?

Re: Database subdirectories in V7.1.x

From
Bruce Momjian
Date:
> 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