PostgreSQL systemd config scheme change

[Pavel Raiskup]

Hello all,

there is currently not so much things to configure in PostgreSQL from
systemd's service point of view (not taking postgresql.conf into account),
concretely, we configure PGPORT and PGDATA, from which the later is
important (and often re-configured to point e.g. to some dedicated mount
point, etc.).  That tells systemd's unit file where it should start
PostgreSQL server from.

What does not seem to be quite 100% OK is that, when user wants to change
PGDATA, we instruct him to make a separate service file:

  $ cat /etc/systemd/system/postgresql.service
  .include /usr/lib/systemd/system/postgresql.service
  [Service]
  Environment=PGDATA=/some/other/place/than/default

This works OK -- but the problem is that it is quite too much for just
changing one simple directory path.

What seems to be better approach is to have real configuration file, such one
for which '$ rpm -qc postgresql-server' would not be quiet.  Lets say:

  $ cat /etc/postgresql/postgresql
  PGDATA=/some/other/place/than/default

With some additional packager's work, that allows us to do yet another
configuration easily - and that is multiple PostgreSQL running on one
single machine (on different ports or IPs).  The only thing would be to
create yet another file:

  $ cat /etc/postgresql/postgresql@com_example
  PGDATA=/some/path/pg/com_example
  PGPORT=@SOMEPORT@
  $ systemctl start postgresql@com_example

Would you see something bad on that approach (please, take into account that
we would not stop supporting the old way, we just want to make future
configuration easier and straight-forward).

Pavel

[Florian Weimer]

On 06/23/2014 04:23 PM, Pavel Raiskup wrote:
>  What seems to be better approach is to have real configuration file,
> such one
>  for which '$ rpm -qc postgresql-server' would not be quiet.  Lets say:
>
>     $ cat /etc/postgresql/postgresql
>     PGDATA=/some/other/place/than/default 
I think for Fedora, the file ought to be in /etc/sysconfig.

>  Would you see something bad on that approach (please, take into
> account that
>  we would not stop supporting the old way, we just want to make future
>  configuration easier and straight-forward). 
It would be nice to have the option of installing and running multiple 
versions in parallel, just as with the Debian packaging.  Just saying. :-)

-- 
Florian Weimer / Red Hat Product Security

[Darin Perusich]

On Mon, Jun 23, 2014 at 3:00 PM, Florian Weimer <fweimer(a)redhat.com&gt; wrote:
>  On 06/23/2014 04:23 PM, Pavel Raiskup wrote:
> >
> > What seems to be better approach is to have real configuration file, such
> > one
> > for which '$ rpm -qc postgresql-server' would not be quiet.  Lets say:
> >
> >    $ cat /etc/postgresql/postgresql
> >    PGDATA=/some/other/place/than/default
>
>
>  I think for Fedora, the file ought to be in /etc/sysconfig. 
The packages from yum.postgresql.org leverage
/etc/sysconfig/pgsql/postgresql-VERSION. Theirs also leverages PGOPTS
so one can set additional CLI options, say if you want to place your
config files outside PGDATA with "-c
config_file=/etc/postgresql/postgresql.conf".

> > Would you see something bad on that approach (please, take into
> account
> > that
> > we would not stop supporting the old way, we just want to make future
> > configuration easier and straight-forward).
>
>
>  It would be nice to have the option of installing and running multiple
>  versions in parallel, just as with the Debian packaging.  Just saying. :-)
>
>  --
>  Florian Weimer / Red Hat Product Security
>  --
>  devel mailing list
>  devel(a)lists.fedoraproject.org
>  https://admin.fedoraproject.org/mailman/listinfo/devel
>  Fedora Code of Conduct: http://fedoraproject.org/code-of-conduct

[Pavel Raiskup]

<OT>  Argh, it is quite painful to keep all CC's in loop :(.  I suppose
that something is broken with this list.  Both of email replies missed my
MAILBOX and both have trimmed CC lists.  Trying to reattach but not giving
it a big chance. </OT>

>  ----- Original Message -----
>  From: Florian Weimer <fweimer(a)redhat.com&gt;
>
>  On 06/23/2014 04:23 PM, Pavel Raiskup wrote:
>  > What seems to be better approach is to have real configuration file,
>  > such one for which '$ rpm -qc postgresql-server' would not be quiet.
>  > Lets say:
>  >
>  >    $ cat /etc/postgresql/postgresql
>  >    PGDATA=/some/other/place/than/default
>
>  I think for Fedora, the file ought to be in /etc/sysconfig. 
I'm not sure about this;  right because /etc/sysconfig is Fedora specific
directory.  We are discouraged to use that directory by systemd..  simply,
dunno.  That said, both /etc/sysconfig/postgresql & /etc/postgresql would work
for me and I hope every admin is able to/aware of `rpm -qc postgresql-server`.

For the more cross-distro solution, I would prefer /etc/postgresql/
though.

>  ----- Original Message -----
>  From: Darin Perusich <darin(a)darins.net&gt;
>  On Mon, Jun 23, 2014 at 3:00 PM, Florian Weimer <fweimer(a)redhat.com&gt; wrote:
>  > On 06/23/2014 04:23 PM, Pavel Raiskup wrote:
>  >> What seems to be better approach is to have real configuration file, such
>  >> one
>  >> for which '$ rpm -qc postgresql-server' would not be quiet.  Lets say:
>  >>
>  >>    $ cat /etc/postgresql/postgresql
>  >>    PGDATA=/some/other/place/than/default
>  >
>  > I think for Fedora, the file ought to be in /etc/sysconfig.
>  
>  The packages from yum.postgresql.org leverage
>  /etc/sysconfig/pgsql/postgresql-VERSION. Theirs also leverages PGOPTS
>  so one can set additional CLI options, say if you want to place your
>  config files outside PGDATA with "-c
>  config_file=/etc/postgresql/postgresql.conf". 
I think that the part of README.rpm-dist from postgresql9{3,4} is outdated
as those parts describe SysV initscripts.  Devrim?

Btw., that /etc/postgresql could calmly be shared by yum.postgresql.org
or any future SCL PostgreSQL as it should not collide by design - as long
as unit name do not clash, configuration should also not.

Pavel

[Devrim Gündüz]

Hi,

On Tue, 2014-06-24 at 06:36 -0400, Pavel Raiskup wrote:
>  I think that the part of README.rpm-dist from postgresql9{3,4} is
>  outdated as those parts describe SysV initscripts.  Devrim? 
Could be. I'll spend some cycles soon to fix these, while polishing RHEL
7 RPMs.

>  Btw., that /etc/postgresql could calmly be shared by
>  yum.postgresql.org or any future SCL PostgreSQL as it should not
>  collide by design - as long as unit name do not clash, configuration
>  should also not. 
It looks Debian'ish a bit ;) I'd love to go with /etc/sysconfig
personally, but you said it is encouraged by systemd, so...

Regards,

[Lennart Poettering]

On Mon, 23.06.14 16:23, Pavel Raiskup (praiskup(a)redhat.com) wrote:

>  Hello all,
>  
>  there is currently not so much things to configure in PostgreSQL from
>  systemd's service point of view (not taking postgresql.conf into account),
>  concretely, we configure PGPORT and PGDATA, from which the later is
>  important (and often re-configured to point e.g. to some dedicated mount
>  point, etc.).  That tells systemd's unit file where it should start
>  PostgreSQL server from.
>  
>  What does not seem to be quite 100% OK is that, when user wants to change
>  PGDATA, we instruct him to make a separate service file:
>  
>    $ cat /etc/systemd/system/postgresql.service
>    .include /usr/lib/systemd/system/postgresql.service 
Please migrate away from ".include", please use .d/ drop-ins instead. We
kinda are deprecating ".include", only support it for compatibility
instead. 

>    [Service]
>    Environment=PGDATA=/some/other/place/than/default
>  
>  This works OK -- but the problem is that it is quite too much for just
>  changing one simple directory path.
>  
>  What seems to be better approach is to have real configuration file, such one
>  for which '$ rpm -qc postgresql-server' would not be quiet.  Lets say:
>  
>    $ cat /etc/postgresql/postgresql
>    PGDATA=/some/other/place/than/default
>  
>  With some additional packager's work, that allows us to do yet another
>  configuration easily - and that is multiple PostgreSQL running on one
>  single machine (on different ports or IPs).  The only thing would be to
>  create yet another file:
>  
>    $ cat /etc/postgresql/postgresql@com_example
>    PGDATA=/some/path/pg/com_example
>    PGPORT=@SOMEPORT@
>    $ systemctl start postgresql@com_example
>  
>  Would you see something bad on that approach (please, take into account that
>  we would not stop supporting the old way, we just want to make future
>  configuration easier and straight-forward). 
Unit files are configuration files. It's OK to copy unit files from
/usr/lib/systemd/system into /etc/systemd/system and edit it there. 

I'd always advise against inventing addition configuration files that
are neither the daemons own, nor systemd's.

Lennart

-- 
Lennart Poettering, Red Hat

[Pavel Raiskup]

On Friday 04 of July 2014 00:09:03 Lennart Poettering wrote:
>  On Mon, 23.06.14 16:23, Pavel Raiskup (praiskup(a)redhat.com) wrote:
>  >   $ cat /etc/postgresql/postgresql@com_example
>  >   PGDATA=/some/path/pg/com_example
>  >   PGPORT=@SOMEPORT@
>  >   $ systemctl start postgresql@com_example
>  >
>  > Would you see something bad on that approach (please, take into
>  > account that we would not stop supporting the old way, we just want to
>  > make future configuration easier and straight-forward).
>
>  Unit files are configuration files. 
Kind of - but not so "pure" configuration files because we can not mark
them %config based on packaging guildelines.

>  It's OK to copy unit files from
>  /usr/lib/systemd/system into /etc/systemd/system and edit it there. 
Copying does not work for us (copied service file is no longer packager's
responsibility - which may seem like a benefit but it is at the expense of
admin's comfort).  Drop-in feature would be OK from this POV (.. but see
below the downsides.. ).

>  I'd always advise against inventing addition configuration files
> that
>  are neither the daemons own, nor systemd's. 
I hope we can call this as a systemd's configuration, or no?  It sounds
like EnvironmentFile is also starting to be deprecated, is it so?

The first neat benefit about this is that the configuration file may be
sourced by any shell script.  The second benefit is that user doesn't need
to bother with so long directory names:

  $ cat /etc/systemd/system/postgresql(a)com.example.d/redef_sth.conf
  [service]
  Environ=PGDATA=_Pathname_without_spaces_

---

Little off-topic, but imo worth saying.  What I think we miss in systemd
in this regard is (because there are some convenience scripts trying to
automatize usual admin's tasks - and these are dependant on systemd's
configuration):

  * Automatic way of exporting service's environment.  Something like:

    $ systemctl environment postgresql.service
    PGDATA=/path/to/a/data/dir
    PGPORT=5433

    The 'systemctl show -p Environment' does not work for us, because it
    does not expand contents of EnvironmentFile :(.  Also white-space
    problems ..

  * I feel that you try to "standardize" the place where services are
    configured.  What about to have some "standard" location with
    files in EnvironementFile= format.  Could we then mark those as
    %config in spec file?

Pavel

[Lennart Poettering]

On Mon, 07.07.14 14:48, Pavel Raiskup (praiskup(a)redhat.com) wrote:

>  
>  On Friday 04 of July 2014 00:09:03 Lennart Poettering wrote:
>  > On Mon, 23.06.14 16:23, Pavel Raiskup (praiskup(a)redhat.com) wrote:
>  > >   $ cat /etc/postgresql/postgresql@com_example
>  > >   PGDATA=/some/path/pg/com_example
>  > >   PGPORT=@SOMEPORT@
>  > >   $ systemctl start postgresql@com_example
>  > >
>  > > Would you see something bad on that approach (please, take into
>  > > account that we would not stop supporting the old way, we just want to
>  > > make future configuration easier and straight-forward).
>  >
>  > Unit files are configuration files.
>  
>  Kind of - but not so "pure" configuration files because we can not mark
>  them %config based on packaging guildelines. 
Well, I am not sure what the benefit would be, but you could %config +
%ghost them.

>  > I'd always advise against inventing addition configuration
> files that
>  > are neither the daemons own, nor systemd's.
>  
>  I hope we can call this as a systemd's configuration, or no?  It sounds
>  like EnvironmentFile is also starting to be deprecated, is it so? 
No. Things you pull in via EnvironmentFile= are not systemd's
configuration file. They are something you defined.

>  The first neat benefit about this is that the configuration file may
> be
>  sourced by any shell script.  The second benefit is that user doesn't need
>  to bother with so long directory names:
>  
>    $ cat /etc/systemd/system/postgresql(a)com.example.d/redef_sth.conf
>    [service]
>    Environ=PGDATA=_Pathname_without_spaces_ 
Honestly, the right fix of this is to simply turn these two env vars
into proper, native postgresql configuration directives, and then not invent
anything new. Actually, aren't they proper, native postgresql
configuration directives anyway? i mean, there's the "port" setting in
postgresql.conf if I google for it. Seems to have something for PGDATA
as well. 

With that in place you could just make this a template service that
simply invokes postgresql with a different configuration file derived
from the instance name:

postgresql@.service:

        [Service]
        ExecStart=/usr/sbin/postgres -c config_file=/etc/postgres/postgresql-%i.conf

Then, people can just place their own postgresql.conf file with their
individual settings in /etc/potsgres/postgresql-foobar.conf and simply
enable, disable, start stop the instnace with that configuration file with:

systemctl enable postgresql(a)foobar.service
systemctl disable postgresql(a)foobar.service
systemctl start postgresql(a)foobar.service
systemctl stop postgresql(a)foobar.service

This way you need no hack environment variable files or settings or
anything, everything is just natural with native configuration
directives.

And if you are concerenced that this way you lose rpm control over the
config files: postgersql actually knows an "include" directive...

>  Little off-topic, but imo worth saying.  What I think we miss in
> systemd
>  in this regard is (because there are some convenience scripts trying to
>  automatize usual admin's tasks - and these are dependant on systemd's
>  configuration):
>  
>    * Automatic way of exporting service's environment.  Something like:
>  
>      $ systemctl environment postgresql.service
>      PGDATA=/path/to/a/data/dir
>      PGPORT=5433
>  
>      The 'systemctl show -p Environment' does not work for us, because it
>      does not expand contents of EnvironmentFile :(.  Also white-space
>      problems .. 
What's the usecase? (not really against this, just want to hear the
precise usecase)

>    * I feel that you try to "standardize" the place where
> services are
>      configured.  What about to have some "standard" location with
>      files in EnvironementFile= format.  Could we then mark those as
>      %config in spec file? 
No. This is not sysvinit. Service files are something admins should
totally edit all the time. They should copy the files, or use drop-ins,
or use "systemctl set-property" or the bus apis, but they are intended
to be something the admin changes if needed. For example, if the admin
wants to limit postgersql's memory usage to 2G or bind it to some CPU,
he should do that. We should not invent a second layer of configuration
files, that allow everything to set up multiple different ways...

Lennart

-- 
Lennart Poettering, Red Hat

[Pavel Raiskup]

On Monday 07 of July 2014 15:57:30 Lennart Poettering wrote:
>  On Mon, 07.07.14 14:48, Pavel Raiskup (praiskup(a)redhat.com) wrote:
>  > > I'd always advise against inventing addition configuration files that
>  > > are neither the daemons own, nor systemd's.
>  >
>  > I hope we can call this as a systemd's configuration, or no?  It sounds
>  > like EnvironmentFile is also starting to be deprecated, is it so?
>
>  No. Things you pull in via EnvironmentFile= are not systemd's
>  configuration file. They are something you defined. 
I defined the _place_ where the systemd's configuration is placed.  Truth.

>  > The first neat benefit about this is that the configuration file
> may be
>  > sourced by any shell script.  The second benefit is that user doesn't need
>  > to bother with so long directory names:
>  >
>  >   $ cat /etc/systemd/system/postgresql(a)com.example.d/redef_sth.conf
>  >   [service]
>  >   Environ=PGDATA=_Pathname_without_spaces_
>
>  Honestly, the right fix of this is to simply turn these two env vars
>  into proper, native postgresql configuration directives, and then not
>  invent anything new. Actually, aren't they proper, native postgresql
>  configuration directives anyway? i mean, there's the "port" setting in
>  postgresql.conf if I google for it. Seems to have something for PGDATA
>  as well. 
How I understand the PostgreSQL intentions, its complete and extensive
configuration tends to be moved to database itself.  So any other info
than PGDATA is not in our interest.  Tom is in loop but I would guess
that standardizing path to file where PGDATA is configured is not
something worth specifying upstream..

You use PGDATA to init/upgrade/check database..  cross any platform.  And
for multiple instances of PostgreSQL server you need to have easy way how
to specify PGDATA (which is our task now).

And, yes, one of my intentions is to move configuration of PGPORT into
postgresql.conf directly (which is quite easily possible).

>  With that in place you could just make this a template service that
>  simply invokes postgresql with a different configuration file derived
>  from the instance name:
>
>  postgresql@.service:
>
>          [Service]
>          ExecStart=/usr/sbin/postgres -c config_file=/etc/postgres/postgresql-%i.conf
>
>  Then, people can just place their own postgresql.conf file with their
>  individual settings in /etc/potsgres/postgresql-foobar.conf and simply
>  enable, disable, start stop the instnace with that configuration file with:
>
>  systemctl enable postgresql(a)foobar.service
>  [...]
>
>  This way you need no hack environment variable files or settings or
>  anything, everything is just natural with native configuration
>  directives. 
See above (this way I think it would be too against PG upstream).

>  > Little off-topic, but imo worth saying.  What I think we miss in
> systemd
>  > in this regard is (because there are some convenience scripts trying to
>  > automatize usual admin's tasks - and these are dependant on systemd's
>  > configuration):
>  >
>  >   * Automatic way of exporting service's environment.  Something like:
>  >
>  >     $ systemctl environment postgresql.service
>  >     PGDATA=/path/to/a/data/dir
>  >     PGPORT=5433
>  >
>  >     The 'systemctl show -p Environment' does not work for us, because it
>  >     does not expand contents of EnvironmentFile :(.  Also white-space
>  >     problems ..
>
>  What's the usecase? (not really against this, just want to hear the
>  precise usecase) 
You can look e.g. at postgresql-setup script (in Fedora's
postgresql-server package).  That script needs to know the PGDATA place -
and currently we need to parse the 'show -p Environment' output.

Pavel

[Lennart Poettering]

On Mon, 07.07.14 17:07, Pavel Raiskup (praiskup(a)redhat.com) wrote:

>  On Monday 07 of July 2014 15:57:30 Lennart Poettering wrote:
>  > On Mon, 07.07.14 14:48, Pavel Raiskup (praiskup(a)redhat.com) wrote:
>  > > > I'd always advise against inventing addition configuration files that
>  > > > are neither the daemons own, nor systemd's.
>  > >
>  > > I hope we can call this as a systemd's configuration, or no?  It sounds
>  > > like EnvironmentFile is also starting to be deprecated, is it so?
>  >
>  > No. Things you pull in via EnvironmentFile= are not systemd's
>  > configuration file. They are something you defined.
>  
>  I defined the _place_ where the systemd's configuration is placed.
>  Truth. 
Well, inventing some environemnt-block-style files and setting
EnvironmentFile= to them doesn't make that file a systemd configuration
file...

Again, I really wished you wouldn't dump three kinds of configuration
files on the admins: postgres' own, your own environment file (which
duplicates at leats the port thing you can also specify in the
configuration file), and systemds own.

With your scheme the admin would need to know and modify all three all
the time. I am simply suggesting you just stick to two: the
postgresql-specific one, and the systemd-specific one, but not another,
redundant one in the middle.

>  > > The first neat benefit about this is that the configuration
> file may be
>  > > sourced by any shell script.  The second benefit is that user doesn't need
>  > > to bother with so long directory names:
>  > >
>  > >   $ cat /etc/systemd/system/postgresql(a)com.example.d/redef_sth.conf
>  > >   [service]
>  > >   Environ=PGDATA=_Pathname_without_spaces_
>  >
>  > Honestly, the right fix of this is to simply turn these two env vars
>  > into proper, native postgresql configuration directives, and then not
>  > invent anything new. Actually, aren't they proper, native postgresql
>  > configuration directives anyway? i mean, there's the "port" setting
> in
>  > postgresql.conf if I google for it. Seems to have something for PGDATA
>  > as well.
>  
>  How I understand the PostgreSQL intentions, its complete and extensive
>  configuration tends to be moved to database itself.  So any other info
>  than PGDATA is not in our interest.  Tom is in loop but I would guess
>  that standardizing path to file where PGDATA is configured is not
>  something worth specifying upstream.. 
Hmmm? I mean, if you insist that environment variables are a great way
to pass configuration around (which I strongly disgaree with, but well),
then sure go ahead, you can also do this:

    [Service]
    Environment=PGDATA=%I
    ExecStart=/usr/sbin/postgres

That way you pass thing through via the env block rather then arg
block. Not sure I understand why env vars would be better for this, but
well, whatever....

then, you can place the port configuration in that specific $PGDATA's
configuration file, and things are simple and easy.

>  You use PGDATA to init/upgrade/check database..  cross any platform. 
> And
>  for multiple instances of PostgreSQL server you need to have easy way how
>  to specify PGDATA (which is our task now). 
Well, 

      Environment=PGDATA=%I
      ExecStart=/usr/sbin/postgres

and

      ExecStart=/usr/sbin/postgres "-c config_file=%I"

Are pretty much identical in their effect. Choose either of them. I'd
take the latter, but if you are married to env vars, pick the former.

>  > This way you need no hack environment variable files or settings
> or
>  > anything, everything is just natural with native configuration
>  > directives.
>  
>  See above (this way I think it would be too against PG upstream). 
Would it? Why so?

Lennart

-- 
Lennart Poettering, Red Hat