Parameters and Values

vim /pg/data/13.6/postgresql.conf

listen_addresses = '192.168.56.61,localhost'

port= 5432

log_connections = yes

log_destination = ‘stderr’

search_path = '"$user", public'

shared_buffers = 128MB

datestyle = 'iso, mdy'

timezone = 'Europe/Istanbul'

#Yapılan değişikliklerin bir kısmı, instance ı kapatıp açmadan reload ile aktif olur.


postgres@srv1:~$ pg_ctl reload

veya

psql

postgres=# select pg_reload_conf();

---

All parameter names are case sensitive.
Below are the data types taken by the parameters:
Boolean: A data type that can take the values on, off, true, false, yes, no, 1, 0.
String: Text data type.
Numeric: Numeric data type that can be defined as digits.
Numeric with Unit: Like B, kB, 32MB, 16GB, TB, us, ms, s, min, h, d.
Enumerated: An array of values, days, months, etc.

postgres=# alter system set shared_buffers = '256MB' ;
postgres=# select sourcefile, name, setting from pg_file_settings;

sourcefile | name | setting
---------------------------+----------------------------+-------
../postgresql.conf |port | 5432
../postgresql.conf |max_connections | 100
../postgresql.conf |shared_buffers | 128MB
../postgresql.conf |dynamic_shared_memory_type | posix
../postgresql.conf |max_wal_size | 1GB
../postgresql.conf |min_wal_size | 80MB
../postgresql.conf |log_timezone | Europe/Istanbul
../postgresql.conf |datestyle | iso, mdy
../postgresql.conf |timezone | Europe/Istanbul
../postgresql.conf |lc_messages | en_US.UTF-8
../postgresql.conf |lc_monetary | en_US.UTF-8
../postgresql.conf |lc_numeric | en_US.UTF-8
../postgresql.conf |lc_time | en_US.UTF-8
../postgresql.conf |default_text_search_config | pg_catalog.english
../postgresql.auto.conf | shared_buffers | 256MB
(15 rows)

---

Changing the postgresql.auto.conf file manually or by other applications is not
recommended. It should only be updated with alter system set command.
Thereby, it can be monitored from the pg_file_settings view when and what has
been changed, and can be quickly intervened in case of a problem.

Changing and Querying Parameters via SQL

ALTER SYSTEM              # It becomes enabled throughout the system.
# Database level settings. It overrides the system settings.

ALTER DATABASE
# Role level settings. It overrides the above two.

ALTER ROLE
# Sets the parameter to default.

ALTER SYSTEM RESET configuration_parameter;

ALTER SYSTEM RESET ALL;  # Reset the whole system to default
 values.

show all ;               # Lists all settings

show shared_buffers ;    # Displays shared buffers only.

More detailed information can be obtained by querying pg_settings with SQL commands.

Example:

postgres=# alter system set work_mem='32MB';

select pending_restart from pg_settings where name = 'work_mem';
pending_restart
-----------------

f -> Restart pending parameters can be seen like this.

postgres=# show work_mem ;
work_mem
----------
4MB

postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t

postgres=# show work_mem ;
work_mem
----------
32MB

Changing Parameters via Shell

postgres -c log_connections=yes -c log_destination= 'syslog'

... [5661] LOG: ending log output to stderr
... [5661] HINT: Future log output will go to log destination "syslog".
………
env PGOPTIONS=”-c work_mem=1GB -c commit_delay=1min” psql

---

When the instance is stopped and the log_connections parameter is off in the
configuration files, the parameters can be changed while the instance is started. Values
given in this way overrides the settings in postgresql.conf file.
Session-specific values can be defined with env PGOPTIONS.

Managing the Contents of the Configuration File

include ‘shared.conf’
include ‘memory.conf’
include ‘server.conf’
include_dir ‘conf.d’

# Örnek klasör içeriği

00shared.conf
01memory.conf
02server.conf

---

If there are many complex parameters, they can be split into different files and
mentioned in postgresql.conf. If the full name of the file is not given in the
postgresql.conf file, it assumes that is located in the same directory.
Likewise, the directory containing the configuration files can also be mentioned.

File Locations

postgres=# select name, setting from pg_settings
where name in
(‘data_directory’,
‘config_file’,
‘hba_file’,
‘ident_file’,
‘external_pid_file’);

name             |  setting
-------------------+-------------------------------
config_file      | /pg/data/13.6/postgresql.conf
data_directory   | /pg/data/13.6
external_pid_file|
hba_file         | /pg/data/13.6/pg_hba.conf
ident_file       | /pg/data/13.6/pg_ident.conf

---

It is also possible to view and change the locations from the postgresql.conf configuration file.

data_directory : Mentions the location of data files. This parameter can only be
set at the startup of the instance.

config_file : Mentions the location of the main configuration file (postgresql.conf). It
can only be used during boot with the postgres command.

hba_file : Mentions the location of the pg_hba.conf configuration file, which is used
for host based authentication. It can only be set during instance startup.

ident_file : Mentions the location of the pg_ident.conf file used to match the
operating system and database users. The location of the pg_ident.conf file can also be
specified only during instance startup.

external_pid_file : It gives the name of the additional process created for
administrative tasks. It can only be set during instance startup.