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

Re: Symbolic link breaks for postgresql.auto.conf

[Adrian Klaver]

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.kla...@aklaver.com

Re: Symbolic link breaks for postgresql.auto.conf

[Tom Lane]

RAJAMOHAN  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

Re: Symbolic link breaks for postgresql.auto.conf

[Vijaykumar Jain]

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
root18731493  0 22:22 pts/100:00:00 su - postgres
postgres18741873  0 22:22 pts/100:00:00 -bash
postgres1916   1  0 22:24 ?00:00:00
/opt/postgresql-13/local/bin/postgres -D /opt/postgresql-13/pgsql/data
postgres19181916  0 22:24 ?00:00:00 postgres: checkpointer
postgres19191916  0 22:24 ?00:00:00 postgres: background
writer
postgres19201916  0 22:24 ?00:00:00 postgres: walwriter
postgres19211916  0 22:24 ?00:00:00 postgres: archiver
postgres19221916  0 22:24 ?00:00:00 postgres: stats
collector
postgres19231916  0 22:24 ?00:00:00 postgres: logical
replication launcher
postgres19251874  0 22:24 pts/100:00:00 psql
postgres19261916  0 22:24 ?00:00:00 postgres: postgres
postgres [local] idle   -*--the psql session where i run alter system*
root19291594  0 22:24 pts/000: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  wrote:

> RAJAMOHAN  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

Re: Symbolic link breaks for postgresql.auto.conf

[Adrian Klaver]

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 > wrote:


RAJAMOHAN mailto:garajamo...@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.kla...@aklaver.com

Re: Symbolic link breaks for postgresql.auto.conf

[Adrian Klaver]

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 > 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



 >
 >
 > On Sun, 6 Jun 2021 at 22:17, Tom Lane mailto:t...@sss.pgh.pa.us>
 > >> wrote:
 >
 >     RAJAMOHAN mailto:garajamo...@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.kla...@aklaver.com 




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: strange behavior of WAL files

[Ravi Krishna]

this is a very interesting case.  Atul keep us posted.

Re: index unique

[Peter J. Holzer]

On 2021-06-03 22:51:55 +0200, Marc Millas wrote:
> postgres 12 with postgis.
> on a table we need a primary key and to get a unique combinaison, we need 3
> columns of that table:
> 1 of type integer,
> 1 of type text,
> 1 of type geometry
> 
> creating the PK constraint doesn work:

I find that if a natural primary key candidate is so complex, it is
usually better to use a surrogate key. 

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature