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

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 <tgl@sss.pgh.pa.us> wrote:

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

--
Thanks,
Vijay
Mumbai, India

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&gt;

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

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 <adrian.klaver@aklaver.com>
wrote: