postgresql.conf

• file contain parameter to help configure and manage performance of the database server 
• when we run initd command to create cluster it create postgresql.conf file default
• the file allow one parameter per the line format 
• parameter that require restart are clearly marked on the file
  many parameter need a server restart to take affect 

the file is located in data directory of the cluster 

default is located in /etc/postgresql/13/main/ 

check parameter of PostgreSQL inside pgSQL console

the parameter you saw in the file can also be viewed in the psql console itself
to check and verified whether they enabled or not , and what is the parameter set for it
for example in the file there parameter called max_connection , i can see the value of it inside psql console by using show max_connection

view parameter using pg_setting

pg_setting is table contain all the infromation about config setup of postgresql.conf
you can query it to get parameters set
for example the below query will show parameter that recently edited and pending restart to apply 

select name ,source,boot_val,pending_restart from pg_settings where name = 'max_connections';

to get the list of column in pg_setting we can use the below option , useful in case you want to build query for yourself 

\d pg_setting 

select name , setting , category , boot_val from pg_settings where sourcefile = '/etc/postgresql/13/main/postgresql.conf';

view history of changes using pg_file_setting

this table contain log of any changes done on postgresql.conf file

postgresql.autoconf

this file have parameter that allow to modify the postgresql.conf parameter from cli rather then editing the file itself 

the editing of postgresql.conf is very critical and better to edit it from psql command line using alter command 
keeping in mind some parameter require restart to reflect in the postgresql

example 1 , edit of the parameter work mem

this parameter we will edit it form pgSQL cli 

to view the current value use the show command and the parameter
syntax

show [paramter in postgresql.conf]; show work_mem ;

to alter command we use alter system set
syntax :
alter system set [parameter] = '[new value ]'; alter system set work_mem='10MB';

check if changes require restart from psql cli

as mentioned before some changes require restart , for instance work_mem dosnt require restart .
to check if the change require restart go to postgresql.conf file and in there you will file comment ‘restart required ‘

or from cli itself you can check it by using the below query .

select * from pg_file_settings;

applying column is showing ‘t’ meaning the changes is applied
there is column called error if its empty means no restart required if there s enters in the column means restart is required .

does change in parameter reflect on postgresql.conf file .

we have changed the parameter fo work_mem from 3 to 10 let’s check if its reflected on postgresql.cnf file

the value hasn’t changed why ?
acuity the changes is not edited in postgresql.conf file , it will be available in postgresql.autoconf
if you checked it you will find the changes you did for work_mem
the file will be located in the data directory of the db-cluster
/var/lib/postgresql/13/main/
if you check the content of the file you will find the new value for work_mem

when postgresql services start it will load config from postgresql.conf

then it will load config in the postgresql.auto.conf 

postgresql will load the value in postgresql.auto.conf and avoid load the value in postgresql.conf 

reset all

if you want to reset all the changes you did in psql cli using alter system
command
you can use the below query 

alter system reset all ;

this means all values in postgresql.auto.onf will be removed
but keep in mind it will not reset values in postgresql.conf
only changes done using alter system command will be reset

---

• in short postgresql.auto.conf is useful to edit config of PostgreSQL without touching the postgresql.conf file 

pg _ident.conf

the file is part of authenticating section file of PostgreSQL and will allow to map OS user with PostgreSQL user
this file allow to match the user in PostgreSQL database with username in OS level 

any changes to the file require a reload only 

the file is located in /etc/postgresql/13/main/

i have PostgreSQL user called ahmed , but the user is not there in OS so i will create it and map it to user called postres in PostgreSQL

in the file you will asked to give friendly name for map , then add the OS username in Identity , then postgresql_username add PostgreSQL username
after that you need to reload PostgreSQL

pg_hba.conf

enables clients authentication between PostgreSQL server and the client application

HBA means host based authenticating .