Обсуждение: Symbolic link breaks for postgresql.auto.conf

On 6/6/21 9:00 AM, RAJAMOHAN wrote:
> Hello all,
> 
> Can someone explain how *postgresql.auto.conf *file contents gets 
> updated, when running an alter system statement followed by pg_reload_conf?
> 
> I am trying to keep a symbolic link for the 
> *postgresql.auto.conf* outside the data directory. The link gets created 
> but whenever I issue an alter system statement it gets broken.
> 
> 
> The main reason for this symbolic link creation is to retain the 
> parameter values associated with this cluster, because we will be 
> copying the entire data directory from another server to this server 
> from time to time for testing purposes.
> And no issues with other files such as hba and postgresql.conf.
> 
> *Operating System: Ubuntu 18.04.1 LTS
> *
> *Postgresql : 12.7*
> 
> Step1:
> Created symbolic links and i am inside data directory
> lrwxrwxrwx 1 postgres postgres   41 Jun  3 05:12 postgresql.conf -> 
> /etc/postgresql/12/test1/postgresql.conf
> lrwxrwxrwx 1 postgres postgres   37 Jun  3 05:12 pg_hba.conf -> 
> /etc/postgresql/12/test1/pg_hba.conf
> lrwxrwxrwx 1 postgres postgres   46 Jun  6 15:19 postgresql.auto.conf -> 
> /etc/postgresql/12/test1/postgresql.auto.conf
> 
> Step2:
> alter system set checkpoint_timeout='2min';select pg_reload_conf();
> ALTER SYSTEM
>   pg_reload_conf
> ----------------
>   t
> (1 row)
> 
> Step3:
> Symbolic link got broken
> lrwxrwxrwx 1 postgres postgres   41 Jun  3 05:12 postgresql.conf -> 
> /etc/postgresql/12/test1/postgresql.conf
> lrwxrwxrwx 1 postgres postgres   37 Jun  3 05:12 pg_hba.conf -> 
> /etc/postgresql/12/test1/pg_hba.conf
> lrwxrwxrwx 1 postgres postgres   46 Jun  6 15:19 postgresql.auto.conf
> 

Have to believe it is due to:

~/src/backend/utils/misc/guc.c

/*
  * Execute ALTER SYSTEM statement.
  *
  * Read the old PG_AUTOCONF_FILENAME file, merge in the new variable value,
  * and write out an updated file.  If the command is ALTER SYSTEM RESET 
ALL,
  * we can skip reading the old file and just write an empty file.
  *
  * An LWLock is used to serialize updates of the configuration file.
  *
  * In case of an error, we leave the original automatic
  * configuration file (PG_AUTOCONF_FILENAME) intact.
  */

In particular:

  /*
          * To ensure crash safety, first write the new file data to a 
temp file,
          * then atomically rename it into place.
          *
          * If there is a temp file left over due to a previous crash, 
it's okay to
          * truncate and reuse it.
          */

> 
> 
> Thanks & Regards,
> Rajamohan.J
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

RAJAMOHAN <garajamohan@gmail.com> writes:
> I am trying to keep a symbolic link for the *postgresql.auto.conf* outside
> the data directory. The link gets created but whenever I issue an alter
> system statement it gets broken.

This is not supported.  Don't do it.

The right way to keep the hand-maintained config files outside the
data directory is not the way you've done it here, either.  It might
accidentally work, but the preferred way is to put "data_directory =
whatever" in postgresql.conf and then start the postmaster with -D
pointing at where the config files are.

But in any case, postgresql.auto.conf is not hand-maintained; it
is part of the cluster data, so it belongs in the data directory.

            regards, tom lane

On 6/6/21 10:02 AM, Vijaykumar Jain wrote:
> Yes, I learnt it the hard way as well :) I made changes via the alter 
> system, and did a pg_restore, and the changes were lost  :)
> 
> use the alter system for ad hoc changes, but make sure those changes are 
> added back to the postgresql.conf file immediately (or however the main 
> conf file is managed).

I would suggest taking a look at 'includes':

https://www.postgresql.org/docs/13/config-setting.html#CONFIG-INCLUDES


> 
> 
> On Sun, 6 Jun 2021 at 22:17, Tom Lane <tgl@sss.pgh.pa.us 
> <mailto:tgl@sss.pgh.pa.us>> wrote:
> 
>     RAJAMOHAN <garajamohan@gmail.com <mailto:garajamohan@gmail.com>> writes:
>      > I am trying to keep a symbolic link for the
>     *postgresql.auto.conf* outside
>      > the data directory. The link gets created but whenever I issue an
>     alter
>      > system statement it gets broken.
> 
>     This is not supported.  Don't do it.
> 
>     The right way to keep the hand-maintained config files outside the
>     data directory is not the way you've done it here, either.  It might
>     accidentally work, but the preferred way is to put "data_directory =
>     whatever" in postgresql.conf and then start the postmaster with -D
>     pointing at where the config files are.
> 
>     But in any case, postgresql.auto.conf is not hand-maintained; it
>     is part of the cluster data, so it belongs in the data directory.
> 
>                              regards, tom lane
> 
> 
> 
> 
> -- 
> Thanks,
> Vijay
> Mumbai, India


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 6/6/21 10:50 AM, Vijaykumar Jain wrote:

Please reply to list also.
Ccing list

Also please use inline or bottom posting, it makes things easier to follow.

> Yeah I am aware of that :)
> 
> I was referring to if the main config is managed via some config 
> management tool like puppet, ansible or  anything else that would wipe 
> the changes made temporarily.

Which 'includes' could handle depending on what the config management 
tool is using as the starting main config(postgresql.conf I presume). If 
that file is seeded with an include, or better yet include_if_exists, 
that point at files outside PG_DATA then you would have the ability to 
override settings at will with a reload/restart.

If the above does not cover your use case you will need to provide a 
more detailed description of how your configuration management is done.

> 
> 
> On Sun, Jun 6, 2021, 10:45 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 6/6/21 10:02 AM, Vijaykumar Jain wrote:
>      > Yes, I learnt it the hard way as well :) I made changes via the
>     alter
>      > system, and did a pg_restore, and the changes were lost  :)
>      >
>      > use the alter system for ad hoc changes, but make sure those
>     changes are
>      > added back to the postgresql.conf file immediately (or however
>     the main
>      > conf file is managed).
> 
>     I would suggest taking a look at 'includes':
> 
>     https://www.postgresql.org/docs/13/config-setting.html#CONFIG-INCLUDES
>     <https://www.postgresql.org/docs/13/config-setting.html#CONFIG-INCLUDES>
> 
> 
>      >
>      >
>      > On Sun, 6 Jun 2021 at 22:17, Tom Lane <tgl@sss.pgh.pa.us
>     <mailto:tgl@sss.pgh.pa.us>
>      > <mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>> wrote:
>      >
>      >     RAJAMOHAN <garajamohan@gmail.com
>     <mailto:garajamohan@gmail.com> <mailto:garajamohan@gmail.com
>     <mailto:garajamohan@gmail.com>>> writes:
>      >      > I am trying to keep a symbolic link for the
>      >     *postgresql.auto.conf* outside
>      >      > the data directory. The link gets created but whenever I
>     issue an
>      >     alter
>      >      > system statement it gets broken.
>      >
>      >     This is not supported.  Don't do it.
>      >
>      >     The right way to keep the hand-maintained config files
>     outside the
>      >     data directory is not the way you've done it here, either. 
>     It might
>      >     accidentally work, but the preferred way is to put
>     "data_directory =
>      >     whatever" in postgresql.conf and then start the postmaster
>     with -D
>      >     pointing at where the config files are.
>      >
>      >     But in any case, postgresql.auto.conf is not hand-maintained; it
>      >     is part of the cluster data, so it belongs in the data directory.
>      >
>      >                              regards, tom lane
>      >
>      >
>      >
>      >
>      > --
>      > Thanks,
>      > Vijay
>      > Mumbai, India
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com