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@xxxxxxxxxxxxx> wrote:
RAJAMOHAN <garajamohan@xxxxxxxxx> 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
