On Tue, Jan 02, 2018 at 03:55:14PM +0000, Robertson, Alan L wrote:
>
> This query seems to be pretty cool for looking at configuration changes:
>
> SELECT pg_settings.setting INTO TEMPORARY config_file FROM pg_settings WHERE name = 'config_file';
> SELECT * FROM config_file INNER JOIN pg_file_settings ON config_file.setting!=pg_file_settings.sourcefile;
>
> The intent of this query is to return the values of all settings which have been modified by SQL from the
installationdefaults.
>
> I'm an SQL newbie, so I'm sure this august body can improve it. But it does appear to work ;-)
It does indeed.
> The output of the join looks something like this - and it has the _correct_ version of the TCP variables, even when
runlocally.
>
>
> setting | sourcefile | sourceline | seqno |
name | setting | applied | error
>
------------------------------------------+---------------------------------------------------+------------+-------+---------------------+---------+---------+-------
> /etc/postgresql/9.5/main/postgresql.conf | /var/lib/postgresql/9.5/main/postgresql.auto.conf | 3 | 23 |
tcp_keepalives_idle| 60 | t |
> (1 row)
You don't actually need to create a temporary table to get that. You
could use something like:
SELECT
s.setting, f.*
FROM
pg_settings s
JOIN
pg_file_settings f
ON (
s.name = 'config_file' AND
s.setting <> f.sourcefile
);
Best,
David.
P.S. In future, please don't top post.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate