Module5_Configuration Flashcards
how to change parameter for a single session
using the SET command
how to show all parameter in DB
show all;
how to see what parameter are set in which file and if they need restart to become active.
select * from pg_file_settings;
how to reload cluster config from psql?
SELECT pg_reload_config();
how to reset parameter to initial value?
alter system RESET parameter_name;
how can multiple cluster on 1 machine use 1 postgresql.conf
by using INCLUDE or INCLUDE_DIR to point to that central postgresql.conf
how to alter parameter on DATABASE level and NOT on whole cluster
alter database DATABASE_NAME set PARAMETER_NAME=1 ;
how to change parameter for 1 USER not whole cluster.
alter user USER_NAME set parameter_name=1;
what overwrites the parameter cluster level, database level and user level.
database level overwrites cluster level parameter
user level overwrites database level parameters
what parameter specified from which hosts PG can accept connections
LISTEN_ADDRESSES
what parameter define how many connections are allowd on the cluster and what is the default?
parameter name is max_connections and is default 100
how do always reserve connection for superuser in PG
superuser_reserved_connections (default 3 sessions)
what do the parameters unix_socket_directory unix_socket_permissions do?
when local user is usings socket on linux and the permission on that directory (default /tmp)
what is autentication_timeout parameter?
a timeframe where a client need to perform the authentication (default 1 min) longer than that and connection will be terminated.
what does parameter row_security do?
this parameter controls row security policy behavior (default is on)
what is parameter password_encryption
what is the encryprion method to store password. Default is scram_sha_256
how to enable SSL on PG?
enable parameter SSL (default off)
what are these SSL parameter:
ssl_ca_file
ssl_cert_file
ssl_key_file
ssl_ciphers
ssl_dh_params_file
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
what are these MEMORY SETTINGS:
shared_buffers
temp_buffers
work_mem
maintenance_work_mem
autovacuum_work_mem
temp_file_limit
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)
what are the background writer settings:
bgwriter_delay
bgwriter_lru_maxpages
bgwriter_lru_multiplier
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
what are these query planning settings:
- random_page_cost
- seq_page_cost
- effective_cache_size
- enable_hints
- plan_cache_mode
- 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
what are these statement behavior parameters?
- search_path
- default_tablespace
- temp_tablespace
- statement_timeout
- idle_in_transaction_session_timeout
- 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.
what are these write ahead log settings:
- wal_level
- fsync
- wal_buffers
- min_wal_size
- max_wal_size
- checkpoint_timeout
- wal_compression
- 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.
what does de log_destination parameter do?
controls logging type for database cluster. can be set to csvlog, syslog (linux) and evenlog (windows)
