Module5_Configuration

Question 1

how to change parameter for a single session

Answer 1

using the SET command

Question 2

how to show all parameter in DB

Answer 2

show all;

Question 3

how to see what parameter are set in which file and if they need restart to become active.

Answer 3

select * from pg_file_settings;

Question 4

how to reload cluster config from psql?

Answer 4

SELECT pg_reload_config();

Question 5

how to reset parameter to initial value?

Answer 5

alter system RESET parameter_name;

Question 6

how can multiple cluster on 1 machine use 1 postgresql.conf

Answer 6

by using INCLUDE or INCLUDE_DIR to point to that central postgresql.conf

Question 7

how to alter parameter on DATABASE level and NOT on whole cluster

Answer 7

alter database DATABASE_NAME set PARAMETER_NAME=1 ;

Question 8

how to change parameter for 1 USER not whole cluster.

Answer 8

alter user USER_NAME set parameter_name=1;

Question 9

what overwrites the parameter cluster level, database level and user level.

Answer 9

database level overwrites cluster level parameter
user level overwrites database level parameters

Question 10

what parameter specified from which hosts PG can accept connections

Answer 10

LISTEN_ADDRESSES

Question 11

what parameter define how many connections are allowd on the cluster and what is the default?

Answer 11

parameter name is max_connections and is default 100

Question 12

how do always reserve connection for superuser in PG

Answer 12

superuser_reserved_connections (default 3 sessions)

Question 13

what do the parameters unix_socket_directory unix_socket_permissions do?

Answer 13

when local user is usings socket on linux and the permission on that directory (default /tmp)

Question 14

what is autentication_timeout parameter?

Answer 14

a timeframe where a client need to perform the authentication (default 1 min) longer than that and connection will be terminated.

Question 15

what does parameter row_security do?

Answer 15

this parameter controls row security policy behavior (default is on)

Question 16

what is parameter password_encryption

Answer 16

what is the encryprion method to store password. Default is scram_sha_256

Question 17

how to enable SSL on PG?

Answer 17

enable parameter SSL (default off)

Question 18

what are these SSL parameter:
ssl_ca_file
ssl_cert_file
ssl_key_file
ssl_ciphers
ssl_dh_params_file

Answer 18

ssl_ca_file: specifies name of file containing ssl CERTIFICATE AUTHORITY (CA)
ssl_cert_file: specifies name of file containing ssl CERTIFICATES
ssl_key_file: specifies name of file containing ssl server private key
ssl_ciphers: list of ssl ciphers that may be used for secure connections
ssl_dh_params_file: specifies name of file for custom OPENSSL DH parameters

Question 19

what are these MEMORY SETTINGS:
shared_buffers
temp_buffers
work_mem
maintenance_work_mem
autovacuum_work_mem
temp_file_limit

Answer 19

shared_buffers: size of shared buffel pool for a cluster (server side parameter)
temp_buffers: amount of mem used for caching temp tables (session side parameter)
work_mem: amount of mem used for sorting and hashing operations (session side parameter)
maintenance_work_mem: amount of mem used for maintenance tasks like reindexing (session side parameter)
autovacuum_work_mem: amount of mem used for autovacume worker (session side parameter)
temp_file_limit: amount of mem used for temporary files (session side parameter)

Question 20

what are the background writer settings:
bgwriter_delay
bgwriter_lru_maxpages
bgwriter_lru_multiplier

Answer 20

bgwriter_delay: specfies time between activity rounds for the background writer (default 200ms)
bgwriter_lru_maxpages: max numbers of pages that background writer may client per activity round
bgwriter_lru_multiplier: multiplier on buffers scanned per round. by default if system thinks 10 pages will be needed it cleans 10 * bgwriter_lru_multiplier (lets say 2.0) = 20

Question 21

what are these query planning settings:
- random_page_cost
- seq_page_cost
- effective_cache_size
- enable_hints
- plan_cache_mode

Answer 21

• random_page_cost: estimate cost of random page fetch (default 4.0)
• seq_page_cost: estimate cost of sequential page fetch (default 1.0) must be >=random_page_cost
• effective_cache_size (default 4gb): used to estimate cost of index scan (best practice 75% of system memory)
• enable_hints(default true): uses hints in query. for ADVANCED SERVER ONLY NOT PSQL COMMUNITY EDITION
• plan_cache_mode (default auto): controls custom of generic plan exectutions for prepared statements. can be set to auto, force_custom_plan and force_generic_plan

Question 22

what are these statement behavior parameters?
- search_path
- default_tablespace
- temp_tablespace
- statement_timeout
- idle_in_transaction_session_timeout

Answer 22

• search_path: order of schema’s where objects are searched (default “user”, public)
• default_tablespace: name of TS objects are default created
• temp_tablespaces: name of TS which TEMPORARY objects are created
• statement_timeout: timeout to abort query if reaches that time. default is OFF/0 value is in MS.
• idle_in_transaction_session_timeout: terminates when sessions idle IN TRANSACTION reach timeout (time in MS). not just an idle connection but must be in a transaction.

Question 23

what are these write ahead log settings:
- wal_level
- fsync
- wal_buffers
- min_wal_size
- max_wal_size
- checkpoint_timeout
- wal_compression

Answer 23

• wal_level (default replica): determines how much information written to wall parameter can also be minimal,logical (logical streaming).
• fsync (default on): force WAL buffer flush at each commit. Turning off can lead to corruptions.
• wal_buffers (default -1, autotune): the amount memory used in shared memory for WAL data. default is 1.32 about 3% of shared buffers.
• min_wal_size (default 80mb): the WAL size to start recycling wal files.
• max_wal_size (default 1GB): the WALL size to start checkpoint. Controls the number of wal segments (16 mb each) after which checkpoint is forces.
• checkpoint_timeout (default 5 min). Maximum time between checkpoints.
• wal_compression (default off). The WALL of full pages will be compressed and written.

Question 24

what does de log_destination parameter do?

Answer 24

controls logging type for database cluster. can be set to csvlog, syslog (linux) and evenlog (windows)

Question 25

what does logging_collector parameter do?

Answer 25

enables logger process to capture stderr and csv logging messages. then be redirecten to location defined in config (log_directory)
default on.

Question 26

what do these parameters do:
log_min_messages
log_min_error_statments
log_min_duration_statement
log_autovacuum_min_duration
log_statement_sample_rate
log_transaction_sample_rate

Answer 26

log_min_messages: messages of this severity or higher will be sent to the server log
log_min_error_statments: when a message of this severity or hight will be sent to loggen together with the query that caused it
log_min_duration_statement: when query exceeds this parameter it will be logged.
log_autovacuum_min_duration: logs any autovacuum operation running for at least this long
log_statement_sample_rate: percentage of queries (above log_autovacuum_min_duration) to be logged.
log_transaction_sample_rate: samples a percentage of transactions by logging statement.

Question 27

where are these parameters used for:
log_connection:
log_disconnections:
log_temp_files:
log_checkpoints:
log_lock_waits:
log_error_verbosity:
log_line_prefix:
log_statment:

Answer 27

log_connection: log succesfull connections to the server log
log_disconnections: log some information when sessions are DISconnected including duration of the session
log_temp_files: logs temp files of this size of bigger to the server log (kilobytes)
log_checkpoints: log when checkpoints happen
log_lock_waits: log lock waits >= deadlock_timeout
log_error_verbosity: how detailed the logges messages are (terse, default, or verbose messages) default=default
log_line_prefix: additional details to log with each line. default timestamp and process ID
log_statment: log sql statments like DDL, DML, ALL or none

Question 28

what does this parameters do:
max_parallel_workers_per_gather
parallel_tuple_cost
parallel_setup_cost
min_parallel_table_scan_size
min_parallel_index_scan_size
force_parallel_mode
max_parallel_maintenance_workers

Answer 28

max_parallel_workers_per_gather (default 2): how many parallel workers can be used for a read_only quey
parallel_tuple_cost (default 0.1): estimate cost of tranferring one tuple from a parallel worker process to another
parallel_setup_cost (default 1000): estimate cost of launching parallel worker process
min_parallel_table_scan_size (default 8mb): set MIN amount of table data that ust be scanned on order or parallel scan.
min_parallel_index_scan_size (default 512kb): set MIN amount of index data that ust be scanned on order or parallel scan.
force_parallel_mode: usefull when testing parallel query scan even when there is no perfromance benefit
max_parallel_maintenance_workers (default 2): enables parallel BTREE index creation

Question 29

what are these parameters for:
- autovacuum
- log_autovacuum_min_duration
- autovacuum_max_workers
- autovacuum_max_mem

Answer 29

autovacuum (default on): controls when the autovacuum launcher runs and starts worker processes to vacuum and analyse tables
log_autovacuum_min_duration (default -1): autovacuum tasks running longer than this duration are logged
autovacuum_max_workers (defaul 3): max numbers of autovacuum worker processes which may be running at one time.
autovacuum_max_mem (default -1, to use maintenace_work_mem): max amount of memory used by EACH autovacuum worker