Thread: Use environment variables in postgresql.conf

On 11/30/2013 3:54 PM, imagenesis@gmail.com wrote:
> Is it possible to use environment variables in postgresql.conf and
> perhaps related configuration files?
>
> For example, I would like to use an env variable to specify the data
> directory.

that specific case is generally handled not via postgresql.conf, but
rather by the arguments passed pg_ctl in the service
start/stop/restart/reload scripts.  on my RHEL platforms, its in turn
read from /etc/sysconfig/pgsql/$0 (where $0 is the service script name),
which in fact sets environment variables used by said service script.




--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On Sat, Nov 30, 2013 at 06:54:31PM -0500, imagenesis@gmail.com wrote:
> Is it possible to use environment variables in postgresql.conf and perhaps
> related configuration files?
>
> For example, I would like to use an env variable to specify the data
> directory.

On top of what John Pierce says in the thread, I would very strongly
urge you not to do this.  It makes troubleshooting on failure
notoriously hard.  Use the mechanisms the start-up files do, I
suggest.

That said, see section 18 of the manual -- in particular, 18.1.3 in
the 9.3 manual ("Other ways to set parameters").  I recommend against
all that on the basis of some somewhat painful experience, but you can
do it that way.

If you're determined to do it, I _strongly_ suggest using several
different postgres.conf files and then only using the config_file
parameter to control this.  At least then you can look at what the
settings were when you have a crashed or otherwise troublesome system.

Best,

A

--
Andrew Sullivan
ajs@crankycanuck.ca

imagenesis@gmail.com wrote
> The questions are:
>
> 1. Has var expansion in configuration files been contemplated?
> 2. Why not do it?

1. Probably
2. More important things to focus on; distros/installers all have their own
idea of what is "best" so the decisions have been left to these OS-specific
entities.


> Reasons why it's perhaps useful to change the presumed workflow:
>
> 1. It's perhaps inconvenient
> 2. Variables are a fundamental concept for configuration
> 3. Moving configuration to os specific scripts defies the DRY (don't
> repeat
> yourself) paradigm

1. Not enough for someone to propose a working patch apparently
2. OK
3. Possibly...and see my response to #2 above


> Proposed workflow:
> 1. Environment initialization, meaning the declaration of environment
> variables (in the sense that "env -i" is probably spawned in the OS
> specific scripts and is thus quite empty) for "pg_ctl" should be done in a
> postgresql specific shell file.
> 2. Variable expansion should be done in postgresql specific configuration
> files.

You seem to only care about Linux...and this workflow seem woefully
incomplete.  If you have something specific in mind please share.


> On the other hand:
> 1. One could just generate the conf files
> 2. Assign env vars to absolute paths/symbolic links

1. Yes, this seems to be a useful approach.
2. Hey, if it works for you.


> Thanks for your reply Andrew, however I do not necessarily wish to conform
> to arbitrary expectations forced by the current implementation if it is
> inconvenient/incomplete. Please evaluate the value of workflows
> facilitated
> by said modifications. Using the OS specific start up scripts for
> configuration of contexts they are intended to initiate is fundamentally
> incorrect.

To be fair the current implementation has probably been around for the past
10 years - well before current DevOps thinking, Puppet/Chef, etc... came
around.  It seems to work for the vast majority of cases, across numerous
OSs, and different distros have indeed done some tweaking to make things
easier for their platforms.

PostgreSQL itself can provide a more flexible and manageable installation
platform if the need can be sold to the current group of -hackers or if
someone with enough motivation comes in with a well-designed proposal.
Changing core in this way, though, might take 2-3 years.  Or that someone
takes what is currently in place and creates a friendly wrapper on top of it
- just like distros do currently - and releases/uses it whenever it is
ready.

So, in summary, it is better to contribute than to conform.

David J.







--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Use-environment-variables-in-postgresql-conf-tp5781027p5781037.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hi,

On Sat, Nov 30, 2013 at 08:43:53PM -0500, imagenesis@gmail.com wrote:
> The questions are:
>
> 1. Has var expansion in configuration files been contemplated?
> 2. Why not do it?

Several years ago on the -hackers list (I couldn't say how many, but I
was still supervising the database operations at Afilias so it's at
least pre-2006) there was a big fight about environment variables and
how to handle them.  Feelings ran high, and I seem to recall that one
of the participants involved basically pulled back from Postgres work
after that because of his frustration.  I'd suggest trolling the
-hackers archives for "environment variable" during the period (say)
from 2003 through 2005.

But in answer to (2), basically the reason is that if you have
multiple ways of setting a value, it is extremely hard for an operator
to figure out what the setting of a running process _actually_ is.
Environment variables are especially bad, because they're hard to
retrieve from a running session in an interoperable way and they're
impossible to inspect if the calling shell is gone.  If you say, "But
I choose to take that risk," you forget that the project incurs
support costs whenever such inconsistencies pop out.

> Reasons why it's perhaps useful to change the presumed workflow:
>
> 1. It's perhaps inconvenient

I don't see why.  If you're going to write a shell script anyway to
set these things, I don't see what the big deal is.

> 2. Variables are a fundamental concept for configuration
> 3. Moving configuration to os specific scripts defies the DRY (don't repeat
> yourself) paradigm

You can use include files against a base postgres.conf file to avoid
repeating yourself.  You're certainly going to have to repeat at least
the setting of some variables.

> Proposed workflow:
> 1. Environment initialization, meaning the declaration of environment
> variables (in the sense that "env -i" is probably spawned in the OS
> specific scripts and is thus quite empty) for "pg_ctl" should be done in a
> postgresql specific shell file.

Why in the world do you want to involve the shell in an
already-complex configuration situation?  Particularly since Postgres
also runs on Windows?  And that the "standard" Bourne shell is either
no standard at all, or else sufficiently limited for interoperation
that the paradigm effectively becomes "write once, test everywhere"?

What you are really saying is, "I have this idea to do something I can
already do, only using a different work flow, so someone else should
do the development work to support that."  I guess if you want this
badly enough you could propose the feature over on -hackers.  I think
it'd be a crazy feature, though.

> Thanks for your reply Andrew, however I do not necessarily wish to conform
> to arbitrary expectations forced by the current implementation if it is
> inconvenient/incomplete.

All engineering is a trade-off.  In this case, the program is designed
with a different mechanism to achieve the same end.  It is a bad idea
-- because it's a dangerous foot-gun -- to implement a different way
to achieve the same ends: it increases code complexity and
dramatically increases the risk that one part of the code will be
altered without correctly altering the other part.  What is
inconvenient for you incereases the reliability of Postgres, and I
humbly suggest that your evaluation of convenience in this case is
anyway incomplete.

Best,

A

--
Andrew Sullivan
ajs@crankycanuck.ca