Home directory of PostgreSQL user

[Matthias Scheler]

Hello,

why does "PGHOME", the location of the home directory of the PostgreSQL user,
default to "${PREFIX}/${PGUSER}"? "${VARBASE}/${PGUSER}" would be a much
better choice. And if that directory is really required why does the
package not create it?

Kind regards

[Greg Troxel]

Post by Matthias Scheler
why does "PGHOME", the location of the home directory of the PostgreSQL user,
default to "${PREFIX}/${PGUSER}"? "${VARBASE}/${PGUSER}" would be a much
better choice. And if that directory is really required why does the
package not create it?

I think it's mostly historical. But it's slightly awkward to change,
because upgrades from before to after have to cope.

/var is often a smallish filesystem, at least for those who practice
traditional Unix separate filesystems. Real databases, as opposed to
auxiliary data that is needed to run a system, really don't belong in
/var, but in some place the database administrator has chosen. pgsql
databases get very big quickly, compared to the sorts of things that are
usually in /var (two my /var filesystems are about 400M and 900M right
now). /usr/pkg/pgsql seems as good a place as any as a default and
works for casual use.

Another thought is that pgsql db doesn't really belong in var under
hier(7), but as "user data" someplace. Otherwise, why are user home
directories not under /var?

I think all in all this is really arguing for VARBASE not being /var,
but /usr/pkg/var - I think it's in general a bug for pkgsrc to put
anything other than under /usr/pkg, and to impose space requirements on
more than one place. So I'd like to see VARBASE migrated to
${PREFIX}/var before changing PGHOME.

/etc/rc.d/pgsql will create the directory when the server is started. I
have seen this work. But I don't think it would be bad for the package
to create it.

(Just my view; I realize this is a complicated situation and there are
probably as many opinions as people.)

[Matthias Scheler]

Post by Greg Troxel

Post by Matthias Scheler
why does "PGHOME", the location of the home directory of the PostgreSQL user,
default to "${PREFIX}/${PGUSER}"? "${VARBASE}/${PGUSER}" would be a much
better choice. And if that directory is really required why does the
package not create it?

I think it's mostly historical.

That was my first guess. :-)

Post by Greg Troxel
But it's slightly awkward to change, because upgrades from before to
after have to cope.

That could be scripted if we wanted to.

Post by Greg Troxel
/var is often a smallish filesystem,

Is it?

Post by Greg Troxel
df -h

Filesystem Size Used Avail %Cap Mounted on
/dev/ld0a 122M 111M 5.0M 95% /
/dev/ld0e 19G 12G 6.7G 63% /var
/dev/ld0f 9.6G 6.2G 2.9G 68% /usr
/dev/ld0g 77G 26G 48G 34% /home
/dev/ld0h 96G 7.5G 84G 8% /archiv
tmpfs 192M 416K 192M 0% /tmp
procfs 4.0K 4.0K 0B 100% /proc
kernfs 1.0K 1.0K 0B 100% /kern
ptyfs 1.0K 1.0K 0B 100% /dev/pts

I've made "/var" larger than e.g. "/usr" on any kinds of server for
years. E-Mail, log files and a lot of other stuff require tons
of diskspace.

Post by Greg Troxel
... at least for those who practice traditional Unix separate filesystems.

The traditional UNIX filesystem separation is an artificat of the past
that IMHO only gets in the way. My own server install has root,
"/var" and "/usr" on the same largish file-system.

Post by Greg Troxel
Real databases, as opposed to
auxiliary data that is needed to run a system, really don't belong in
/var,

I disagree. "/var" is hystorically for system data. And that is what
database tablesa are.

Post by Greg Troxel
/usr/pkg/pgsql seems as good a place as any as a default and
works for casual use.

I disagree. "/usr" is for software installation. And you should be
abel to mount it read-only.

Post by Greg Troxel
Another thought is that pgsql db doesn't really belong in var under
hier(7), but as "user data" someplace. Otherwise, why are user home
directories not under /var?

Because their *user* data and not *system* data.

Post by Greg Troxel
I think all in all this is really arguing for VARBASE not being /var,
but /usr/pkg/var - I think it's in general a bug for pkgsrc to put
anything other than under /usr/pkg,

I disagree strongly which is I've set "PKG_SYSCONFBASE" to "/etc/pkg"
on my NetBSD system. A friend of mine use these settings under Solaris:

LOCALBASE= /opt/pkg
PKG_SYSCONFBASE= /etc/opt/pkg
VARBASE= /var/pkg

It keeps "pkgsrc" separate from the rest but in the conceptionally right
place. The default should probably be something like the above.

Post by Greg Troxel
/etc/rc.d/pgsql will create the directory when the server is started.

Thanks. I noticed that in the meantime.

Kind regards

[Greg Troxel]

Matthias Scheler <***@zhadum.org.uk> writes:

(I'm omitting things which are just a difference of opinion.)

Post by Matthias Scheler

Real databases, as opposed to auxiliary data that is needed to run a
system, really don't belong in /var,

I disagree. "/var" is hystorically for system data. And that is what
database tablesa are.

They really aren't "system data". There's data that belongs to named
users (homedirs), and data that is used to operate the system. Adding
a database, or a web server, or something like that is really a third
category where the data belongs to a non-human application user
(logically). Were we using pgsql to store wtmp, it would be system
data. But that's not how people use it. For example, most people
running ftp servers don't put everything under /var/ftp.

Post by Matthias Scheler
LOCALBASE= /opt/pkg
PKG_SYSCONFBASE= /etc/opt/pkg
VARBASE= /var/pkg

So I would be far happier with /var/pkg as VARBASE than /var, because it
reduces to 2 (or 3, with PKG_SYSCONFBASE) the set of prefixes that make
up the pkgsrc installation (/var/db/pkg aside). More importantly, it
keeps all the pkgsrc stuff separate from the base system.



This isn't relevant to the prefix discussion, but postgresql does
support multiple database locations. That tends to be for advanced used
when one starts worrying about balancing disk IO, etc.

[David Holland]

Post by Matthias Scheler

Post by Greg Troxel
Real databases, as opposed to
auxiliary data that is needed to run a system, really don't belong in
/var,

I disagree. "/var" is hystorically for system data. And that is what
database tablesa are.

Uh... my database tables are not system data; at a pinch they're
application data, but mostly they're user data. /var is the wrong
place. /home/${PGUSER} would be better from this point of view, but of
course has other issues. Maybe ~${PGUSER}...

Post by Matthias Scheler

Post by Greg Troxel
/usr/pkg/pgsql seems as good a place as any as a default and
works for casual use.

I disagree. "/usr" is for software installation. And you should be
abel to mount it read-only.

Yes... which is one of the reasons /usr/pkg/var is a bad idea.

[Pierre Pronchery]

Hi there,

Post by David Holland

Post by Matthias Scheler

Post by Greg Troxel
Real databases, as opposed to
auxiliary data that is needed to run a system, really don't belong in
/var,

I disagree. "/var" is hystorically for system data. And that is what
database tablesa are.

Uh... my database tables are not system data; at a pinch they're
application data, but mostly they're user data. /var is the wrong
place. /home/${PGUSER} would be better from this point of view, but of
course has other issues. Maybe ~${PGUSER}...

The point is that you do not want users to be able to fill their
directories with data, to the point that the system crashes (or stops
logging and hides hacking attempts for one thing). So all my systems
separate /home and /var.

On my critical systems running PostgreSQL, I setup a separate partition
dedicated to it. It is database data, not user data or system data. I
want the database to keep running even if a user goes crazy (happens
easily with bad cron jobs), and I want the system to keep running even
if the database gets too big (helps logging in and working on the issue).

What would be very cool to add to pkgsrc's PostgreSQL packages would be
cluster management scripts as seen in Debian (pg_upgradecluster, etc).
They really speak for the power and flexibility of PostgreSQL.

HTH,

[Richard PALO]

Post by Greg Troxel

Post by Matthias Scheler
why does "PGHOME", the location of the home directory of the PostgreSQL user,
default to "${PREFIX}/${PGUSER}"? "${VARBASE}/${PGUSER}" would be a much
better choice. And if that directory is really required why does the
package not create it?

I think it's mostly historical. But it's slightly awkward to change,
because upgrades from before to after have to cope.
/var is often a smallish filesystem, at least for those who practice
traditional Unix separate filesystems. Real databases, as opposed to
auxiliary data that is needed to run a system, really don't belong in
/var, but in some place the database administrator has chosen. pgsql
databases get very big quickly, compared to the sorts of things that are
usually in /var (two my /var filesystems are about 400M and 900M right
now). /usr/pkg/pgsql seems as good a place as any as a default and
works for casual use.
Another thought is that pgsql db doesn't really belong in var under
hier(7), but as "user data" someplace. Otherwise, why are user home
directories not under /var?
I think all in all this is really arguing for VARBASE not being /var,
but /usr/pkg/var - I think it's in general a bug for pkgsrc to put
anything other than under /usr/pkg, and to impose space requirements on
more than one place. So I'd like to see VARBASE migrated to
${PREFIX}/var before changing PGHOME.
/etc/rc.d/pgsql will create the directory when the server is started. I
have seen this work. But I don't think it would be bad for the package
to create it.
(Just my view; I realize this is a complicated situation and there are
probably as many opinions as people.)

We sort of discussed this a few months ago already in the pkg-user topic
"Re: PGHOME defaults inconsistent..." but with what concerns the
database tables, the db admin *should* define tablespaces for the
database objects that point off elsewhere, but the system data IMHO
should most definitely stay (in /var).

Forcing everything to /usr is most definitely a controversial location,
even /opt can be...

[r***@NetBSD.org]

Post by Greg Troxel
/var is often a smallish filesystem, at least for those who practice
traditional Unix separate filesystems. Real databases, as opposed to
auxiliary data that is needed to run a system, really don't belong in
/var, but in some place the database administrator has chosen.

Hmm, not if you're keeping lots of logs. Small /var mounts will get choked
quickly on the IP logs alone. /var is for variable data. It's one of the
mounts we don't have to mount read-only.

Given that the data in ${PGHOME} is owned/grouped by ${PGUSER}, I'd like it
much better if we made ${PGHOME}=${VARBASE}/chroot/pgsql.