Symbolic link breaks for postgresql.auto.conf

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Symbolic link breaks for postgresql.auto.conf

RAJAMOHAN
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



Thanks & Regards,
Rajamohan.J

Reply | Threaded
Open this post in threaded view
|

Re: Symbolic link breaks for postgresql.auto.conf

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Symbolic link breaks for postgresql.auto.conf

Tom Lane-2
In reply to this post by RAJAMOHAN
RAJAMOHAN <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Symbolic link breaks for postgresql.auto.conf

Vijaykumar Jain-2
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).


the below confirms the file is touched with alter system.

root@go:~# ps -aef | grep post
root        1873    1493  0 22:22 pts/1    00:00:00 su - postgres
postgres    1874    1873  0 22:22 pts/1    00:00:00 -bash
postgres    1916       1  0 22:24 ?        00:00:00 /opt/postgresql-13/local/bin/postgres -D /opt/postgresql-13/pgsql/data
postgres    1918    1916  0 22:24 ?        00:00:00 postgres: checkpointer
postgres    1919    1916  0 22:24 ?        00:00:00 postgres: background writer
postgres    1920    1916  0 22:24 ?        00:00:00 postgres: walwriter
postgres    1921    1916  0 22:24 ?        00:00:00 postgres: archiver
postgres    1922    1916  0 22:24 ?        00:00:00 postgres: stats collector
postgres    1923    1916  0 22:24 ?        00:00:00 postgres: logical replication launcher
postgres    1925    1874  0 22:24 pts/1    00:00:00 psql
postgres    1926    1916  0 22:24 ?        00:00:00 postgres: postgres postgres [local] idle   ---the psql session where i run alter system
root        1929    1594  0 22:24 pts/0    00:00:00 grep --color=auto post

root@go:~# strace  -e trace=%file -p 1916 -p 1918 -p 1919 -p 1926
strace: Process 1916 attached
strace: Process 1918 attached
strace: Process 1919 attached
strace: Process 1926 attached
[pid  1926] stat("postgresql.auto.conf", {st_mode=S_IFREG|0664, st_size=0, ...}) = 0
[pid  1926] openat(AT_FDCWD, "postgresql.auto.conf", O_RDONLY) = 24
[pid  1926] openat(AT_FDCWD, "postgresql.auto.conf.tmp", O_RDWR|O_CREAT|O_TRUNC, 0600) = 24
[pid  1926] openat(AT_FDCWD, "postgresql.auto.conf.tmp", O_RDWR) = 24
[pid  1926] openat(AT_FDCWD, "postgresql.auto.conf", O_RDWR) = 24
[pid  1926] rename("postgresql.auto.conf.tmp", "postgresql.auto.conf") = 0
[pid  1926] openat(AT_FDCWD, "postgresql.auto.conf", O_RDWR) = 24
[pid  1926] openat(AT_FDCWD, ".", O_RDONLY) = 24
[pid  1926] +++ exited with 0 +++
[pid  1916] --- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=1926, si_uid=1001, si_status=0, si_utime=0, si_stime=0} ---



On Sun, 6 Jun 2021 at 22:17, Tom Lane <[hidden email]> wrote:
RAJAMOHAN <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Symbolic link breaks for postgresql.auto.conf

Adrian Klaver-4
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 <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     RAJAMOHAN <[hidden email] <mailto:[hidden email]>> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Symbolic link breaks for postgresql.auto.conf

Adrian Klaver-4
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 <[hidden email]
> <mailto:[hidden email]>> 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 <[hidden email]
>     <mailto:[hidden email]>
>      > <mailto:[hidden email] <mailto:[hidden email]>>> wrote:
>      >
>      >     RAJAMOHAN <[hidden email]
>     <mailto:[hidden email]> <mailto:[hidden email]
>     <mailto:[hidden email]>>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Symbolic link breaks for postgresql.auto.conf

RAJAMOHAN
Thanks guys.

Able to resolve the issue, by using the include_if_exists parameter as Adrian suggested.

Thanks & Regards,
Rajamohan.J



On Mon, Jun 7, 2021 at 1:02 AM Adrian Klaver <[hidden email]> wrote:
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 <[hidden email]
> <mailto:[hidden email]>> 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 <[hidden email]
>     <mailto:[hidden email]>
>      > <mailto:[hidden email] <mailto:[hidden email]>>> wrote:
>      >
>      >     RAJAMOHAN <[hidden email]
>     <mailto:[hidden email]> <mailto:[hidden email]
>     <mailto:[hidden email]>>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]