Обсуждение: Database directory names

I'm in the final stages of setting up database access for our domain
hosting customers.  The current system is running PostgreSQL 7.0- with
which I haven't met any major problems (other than trying to figure out
how to restrict access to each domain's database- solved).

However...  That box is slated for some major hardware upgrades, and an
update from RedHat 7.0 to 7.3- bringing Postgres up to 7.2.  This is not
in and of itself a problem...  but one particular attribute of 7.2 has
me very puzzled and concerned.

In 7.0, each database resided in /var/lib/pgsql/data/base/{database
name}. This appears to have changed in 7.2 to an indecipherable number,
with no apparent relation to the database name.

I've spent a little time playing with
   CREATE DATABASE dbname WITH LOCATION = 'dbpath';
but it doesn't really suit my needs;  I don't want to specify where the
base/ directory goes!  I don't need to.  I do want to be able to easily
track disk usage on a per-database basis- and while I could just make
note of the directory created when a database is created, it would be
*much* simpler to automate information-generating scripts based on the
database name directly.  (Nor do I want to hassle with trying to get
appropriate environment variables set for the postmaster;  I *really*
don't want to have to modify the init script.)

Is this a feature, a bug, or a change related to some other critical
functional capability of Postgres?  I haven't found anything else that
meets our requirements here, and I can work around this annoyance if
required- but I'd rather be lazy.  ;)

-Kris
WebHart Internet Linux Sysadmin

On Tue, 2 Jul 2002, Kris Deugau wrote:

> I'm in the final stages of setting up database access for our domain
> hosting customers.  The current system is running PostgreSQL 7.0- with
> which I haven't met any major problems (other than trying to figure out
> how to restrict access to each domain's database- solved).
>
> However...  That box is slated for some major hardware upgrades, and an
> update from RedHat 7.0 to 7.3- bringing Postgres up to 7.2.  This is not
> in and of itself a problem...  but one particular attribute of 7.2 has
> me very puzzled and concerned.
>
> In 7.0, each database resided in /var/lib/pgsql/data/base/{database
> name}. This appears to have changed in 7.2 to an indecipherable number,
> with no apparent relation to the database name.
>
> I've spent a little time playing with
>    CREATE DATABASE dbname WITH LOCATION = 'dbpath';
> but it doesn't really suit my needs;  I don't want to specify where the
> base/ directory goes!  I don't need to.  I do want to be able to easily
> track disk usage on a per-database basis- and while I could just make
> note of the directory created when a database is created, it would be
> *much* simpler to automate information-generating scripts based on the
> database name directly.  (Nor do I want to hassle with trying to get
> appropriate environment variables set for the postmaster;  I *really*
> don't want to have to modify the init script.)
>
> Is this a feature, a bug, or a change related to some other critical
> functional capability of Postgres?  I haven't found anything else that
> meets our requirements here, and I can work around this annoyance if
> required- but I'd rather be lazy.  ;)

Switching to numbers for tables and databases helped a few things
(including for tables making it easier to rollback a drop table).
There's a contrib item, oid2name which will print out a mapping
of directory number to database name.  The readme gives an example
of using it with du.

Stephan Szabo wrote:
> > Is this a feature, a bug, or a change related to some other critical
> > functional capability of Postgres?  I haven't found anything else that
> > meets our requirements here, and I can work around this annoyance if
> > required- but I'd rather be lazy.  ;)
>
> Switching to numbers for tables and databases helped a few things
> (including for tables making it easier to rollback a drop table).
> There's a contrib item, oid2name which will print out a mapping
> of directory number to database name.  The readme gives an example
> of using it with du.

Uh, actually, only the readme in the current CVS has the example, 7.2.X
doesn't.  I recommend getting oid2name from the snapshot/CVS and using
that.  It has the newer readme and some changes to make the examples
display properly.  Also see the new doc section for 7.3 on disk usage:

    http://developer.postgresql.org/docs/postgres/diskusage.html

--
  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

On Wed, 3 Jul 2002, Bruce Momjian wrote:

> Stephan Szabo wrote:
> > > Is this a feature, a bug, or a change related to some other critical
> > > functional capability of Postgres?  I haven't found anything else that
> > > meets our requirements here, and I can work around this annoyance if
> > > required- but I'd rather be lazy.  ;)
> >
> > Switching to numbers for tables and databases helped a few things
> > (including for tables making it easier to rollback a drop table).
> > There's a contrib item, oid2name which will print out a mapping
> > of directory number to database name.  The readme gives an example
> > of using it with du.
>
> Uh, actually, only the readme in the current CVS has the example, 7.2.X
> doesn't.  I recommend getting oid2name from the snapshot/CVS and using
> that.  It has the newer readme and some changes to make the examples
> display properly.  Also see the new doc section for 7.3 on disk usage:
>
>     http://developer.postgresql.org/docs/postgres/diskusage.html

My bad. I assumed that oid2name wasn't likely to have changed without
actually checking.

> Stephan Szabo wrote:
> > Switching to numbers for tables and databases helped a few things
> > (including for tables making it easier to rollback a drop table).
> > There's a contrib item, oid2name which will print out a mapping
> > of directory number to database name.  The readme gives an example
> > of using it with du.

Ah, option number 3.  I *thought* there had to be a good reason for it,
but I couldn't imagine how it might help.  (I still can't, but I'm not
in any way an expert on DBMS programming.)

Bruce Momjian wrote:
> Uh, actually, only the readme in the current CVS has the example, 7.2.X
> doesn't.  I recommend getting oid2name from the snapshot/CVS and using
> that.  It has the newer readme and some changes to make the examples
> display properly.  Also see the new doc section for 7.3 on disk usage:
>         http://developer.postgresql.org/docs/postgres/diskusage.html

Snagged, tested, and I'm happy.  Thanks for the heads-up on where to
find oid2name.

-Kris
WebHart Internet Linux Sysadmin