Обсуждение: Alternative cluster location

MY version 7.3 setup uses the default cluster data directory of

/var/lib/pgsql/data

and I'm aware that one suggested alternative is

/usr/local/pgsql/data

In my reading of server setup configuration and and drive partitioning,
though, I came to believe that the partition mounted at /var should be
used for "temporary" data, such as mail spool files and log files, and
typically, that that partition doesn't need to be large (in comparision,
for instance, to other partitions such as that mounted at /usr, which is
large to accomodate installation of applications, or /home, which is
large because I typically define that partition to "remainder of disk
space").

So I've been thinking that the /var/lib/pgsql/data default cluster data
directory was an odd choice, since a database, which while definitely
subject to lots of change (which favors /var...), is likely neither
temporary nor small (which argues for somewhere else, I think).

But since I know enough to know that I don't really know enough, and that
the team that decided on the /var... or /usr... default directory
probably did so with good reason, I'm compelled to ask what do some of
you think about the idea of creating a "home" directory for postgres on
the partition mounted at /home, and using, say, /home/postgres/pgsql/data
as the cluster data directory (or with specific reference to the thread I
started about "conservation of OIDS, I would use three cluster data
directories: /home/postgres/pgsql/data/prod,
/home/postgres/pgsql/data/qat,and /home/postgres/pgsql/data/dev )?

What would be the downside to this approach?

~Berend Tober

btober@seaworthysys.com writes:

> So I've been thinking that the /var/lib/pgsql/data default cluster data
> directory was an odd choice, since a database, which while definitely
> subject to lots of change (which favors /var...), is likely neither
> temporary nor small (which argues for somewhere else, I think).

The /var directory is for variable data files.  Nothing says anything
about temporary or small.

> probably did so with good reason, I'm compelled to ask what do some of
> you think about the idea of creating a "home" directory for postgres on
> the partition mounted at /home, and using, say, /home/postgres/pgsql/data
> as the cluster data directory

Sure you can do that, but it doesn't seem particularly cleaner to me.  You
might argue that "home" is for personal files, so you would be at the
wrong place again.

In the end, it's purely a matter of personal (or organizational) taste,
not a technical tradeoff.

--
Peter Eisentraut   peter_e@gmx.net

On Mon, 2003-11-17 at 10:23, Peter Eisentraut wrote:
> btober@seaworthysys.com writes:
>
> > So I've been thinking that the /var/lib/pgsql/data default cluster data
> > directory was an odd choice, since a database, which while definitely
> > subject to lots of change (which favors /var...), is likely neither
> > temporary nor small (which argues for somewhere else, I think).
>
> The /var directory is for variable data files.  Nothing says anything
> about temporary or small.
>
> > probably did so with good reason, I'm compelled to ask what do some of
> > you think about the idea of creating a "home" directory for postgres on
> > the partition mounted at /home, and using, say, /home/postgres/pgsql/data
> > as the cluster data directory

On linux, you may also want to consider the most recent proposed FHS,
which suggests a top-level /srv directory used for 'data generated by
users for the services the system offers'

Personally, I like the FHS and would prefer that the various distros
installed postgresql in a manner more consistent with it (and if
necessary that the postgresql configs were modified to make that and
easier alternative.) But as you say, Peter, there is a great deal of
personal preference that goes into these considerations.

--
Karl DeBisschop <kdebisschop@alert.infoplease.com>
Pearson Education/Information Please

> btober@seaworthysys.com writes:
>
>> So I've been thinking that the /var/lib/pgsql/data default cluster
>> data directory was an odd choice, since a database, which while
>> definitely subject to lots of change (which favors /var...), is
>> likely neither temporary nor small (which argues for somewhere else,
>> I think).
>
> The /var directory is for variable data files.  Nothing says anything
> about temporary or small.

Agreed that nothing says the files are necessarily small, but the
"advice" regarding disk partitioning I read implied small by saying that
the hard drive partition created with the intention of mounting as /var
*usually* does not need to be large. O.k., the writer probably was not
thinking of RDBMS.

>
>> probably did so with good reason, I'm compelled to ask what do some
>> of you think about the idea of creating a "home" directory for
>> postgres on the partition mounted at /home, and using, say,
>> /home/postgres/pgsql/data as the cluster data directory
>
> Sure you can do that, but it doesn't seem particularly cleaner to me.

Only as an anthropomorphism: Since even though no such actual person
exists, all the database files are "owned" by the "user" postgres, which
is what leads me to think of this approach.

> You might argue that "home" is for personal files, so you would be at
> the wrong place again.
> In the end, it's purely a matter of personal (or organizational) taste,
> not a technical tradeoff.

Thank you.

~Berend Tober

> btober@seaworthysys.com writes:
>
>> So I've been thinking that the /var/lib/pgsql/data default cluster
>> data directory was an odd choice, since a database, which while
>
> Sure you can do that, but it doesn't seem particularly cleaner to me.
> You might argue that "home" is for personal files, so you would be at
> the wrong place again.
>
> In the end, it's purely a matter of personal (or organizational) taste,
> not a technical tradeoff.

I think the original driving thought technically has something to do with
system backup, and restore or system re-install in the case of
catastrophic failure, as well as what I've alluded to in regards to
original server setup planning and how intended usage impacts decisions
with respect to hard drive partitioning (and vice versa?). I don't think
there is much under /var that I usually consider necessary to back up.
The postgresql.conf and pg_hba.conf files are valuable, and while the
data base obviously is THE principle value, tar on the cluster data
directory is not useful.

Maybe at least piping the output of pg_dump for backup purposes to
/home/postgres would make sense, so as to catch the database backup snap
shot along with the user data in a nightly run of tar on the /home
partition.

~Berend Tober

Karl DeBisschop writes:

> On linux, you may also want to consider the most recent proposed FHS,
> which suggests a top-level /srv directory used for 'data generated by
> users for the services the system offers'

Interesting, but I'm not sure it's appropriate.  Considering the examples
offered for /srv: www, ftp, rsync, cvs, it's more intended for placing
actual files there to be served to the outside.  (In case of CVS that's
not quite right, admittedly.)  On the other hand, database files are more
like IMAP folders, which are internal state information that can only
(reasonably) be read or written via some (possibly remote) program.
Maybe we should pose that question to the FHS group.

> Personally, I like the FHS and would prefer that the various distros
> installed postgresql in a manner more consistent with it

I think they are doing quite well.  What complaints do you have?

> (and if necessary that the postgresql configs were modified to make that
> and easier alternative.)

I don't think there are any problems on the configure/make side.  Do you
know of any?

--
Peter Eisentraut   peter_e@gmx.net

btober@seaworthysys.com writes:

> I think the original driving thought technically has something to do with
> system backup, and restore or system re-install in the case of
> catastrophic failure, as well as what I've alluded to in regards to
> original server setup planning and how intended usage impacts decisions
> with respect to hard drive partitioning (and vice versa?). I don't think
> there is much under /var that I usually consider necessary to back up.

Good point.  Again, maybe we should just ask the FHS group about it.

Personally, if I had to do a slightly more complex than average database
system setup with a separate hard disk, possibly even more than one, I'd
just mount them to /mnt/data{1-n} and pointed the postmaster there.

--
Peter Eisentraut   peter_e@gmx.net

On Mon, 2003-11-17 at 11:07, Peter Eisentraut wrote:
> Karl DeBisschop writes:
>
> > On linux, you may also want to consider the most recent proposed FHS,
> > which suggests a top-level /srv directory used for 'data generated by
> > users for the services the system offers'
>
> Interesting, but I'm not sure it's appropriate.  Considering the examples
> offered for /srv: www, ftp, rsync, cvs, it's more intended for placing
> actual files there to be served to the outside.  (In case of CVS that's
> not quite right, admittedly.)  On the other hand, database files are more
> like IMAP folders, which are internal state information that can only
> (reasonably) be read or written via some (possibly remote) program.

I'm not sure where you read that, other than by the examples given. Were
there other statements that led you to this conclusion?

I read '/srv contains site-specific data which is served by this
system'. If postgresql were to use this model on a Linux-FHS system, I
would suggest something like:

1) config files in /etc
2) data tables in /srv
3) indexes, WAL data, temp files stay in /var/lib/pgsql

This fits in with the general idea that this user data is often rather
valuable, and is one of the partitions that needs to be backed up with
special care (although this is also true of big parts of /var). In a
typical database implementation, this is not as true of the indices,
which can just be regenerated.

It also provides a framework for those site that split data and indices
onto different disks for performance reasons.

> Maybe we should pose that question to the FHS group.

It might be worth doing. Like you, I'm not sure how/if this model would
appliy. But now is the time to think about how it might be applied and
what implications that has for stablility and performance of the DBMS.
In this case, I think it might be an overall benefit.

> > Personally, I like the FHS and would prefer that the various distros
> > installed postgresql in a manner more consistent with it
>
> I think they are doing quite well.  What complaints do you have?

Just that the config files for postgresql are not in /etc. This was
primarily aimed at RedHat, because that is what I'm most familiar with.

> > (and if necessary that the postgresql configs were modified to make that
> > and easier alternative.)
>
> I don't think there are any problems on the configure/make side.  Do you
> know of any?

I was recalling a thread some time ago where someone (I thought it was
you) was concerned that the postgresql security model made it hard to
place the config files in /etc. Or something like that. It's a very
vague recollection - I referred to it of of respect for the fact that
other people have thought about it more than it, rather than from any
specific knowledge.

--
Karl DeBisschop <kdebisschop@alert.infoplease.com>
Pearson Education/Information Please

btober@seaworthysys.com writes:

> I think the original driving thought technically has something to do with
> system backup, and restore or system re-install in the case of
> catastrophic failure, as well as what I've alluded to in regards to
> original server setup planning and how intended usage impacts decisions
> with respect to hard drive partitioning (and vice versa?).

I believe some of the partitioning idea for /var was the idea that it was a
filesystem that would often be written to, unlike /, and that it was partly a
filesystem corruption safety net to have it as a separate filesystem. This of
course is what gives us the impression that it's suitable for ommission from
backups, i.e. stuff we don't worry too much about losing in the case of a
filesystem failure.


> I don't think there is much under /var that I usually consider necessary to
> back up.

It's always bugged me about /var/named/


--
Nigel Andrews