# data and conf directory, following CIS to relocate
data_directory  = '/pgdata'
config_file = '/pgdata/postgresql.conf'
hba_file = '/pgdata/pg_hba.conf'
ident_file = '/pgdata/pg_ident.conf'
unix_socket_directories = '/var/run/postgresql, /tmp'
unix_socket_group='dbmon'
unix_socket_permissions = 0760

# max allowed connections
max_connections = 5000
superuser_reserved_connections = 3
max_worker_processes = 8
max_parallel_maintenance_workers = 2
max_parallel_workers = 2
max_parallel_workers_per_gather = 0

#enable hugepage shared buffer
huge_pages = on
# shared buffer size managed by PG   Physical RAM * 0.35 , MB
shared_buffers = 45875MB
# effective_cache_size Physical RAC * 0.70 ,used by optimizer
effective_cache_size = 91750MB

# memory per session/process worker
work_mem = 8MB
maintenance_work_mem = 2048MB

# wal settings
wal_level = 'logical'
wal_buffers = 16384
wal_compression = on
#wal_keep_size = 2048

#checkpoint
checkpoint_timeout = 63min
checkpoint_completion_target = 0.9
max_wal_size = 102400
min_wal_size = 20480

checkpoint_warning = 300s

#archive command
archive_mode = on
archive_command='pgbackrest --stanza=meetings-PostgreSQL-taurus-c-1 --pg1-path=/pgdata archive-push %p'
#archive_timeout = 300

#autovaccum
autovacuum = on
autovacuum_max_workers = 3
autovacuum_analyze_scale_factor = 0.05
autovacuum_naptime = 15
autovacuum_vacuum_cost_limit = 2000
autovacuum_vacuum_scale_factor = 0.1
autovacuum_vacuum_insert_scale_factor = 0.2
log_autovacuum_min_duration = 10000

# Terminate any session with an open transaction that has been idle for longer than the specified amount of time
idle_in_transaction_session_timeout=300000
#below about client session ,it's from STIG
#statement_timeout = 10000 #milliseconds
#default 0 selects the operating system's default 7200,75,9
tcp_keepalives_idle = 0 # seconds
tcp_keepalives_interval = 0 # seconds
tcp_keepalives_count = 0

#list address
port = 5432
listen_addresses = '*'

#password encryption
password_encryption = 'scram-sha-256'

#ssl = on
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
#ssl_ca_file = '/pgarchive/ssl/root.crt'
#ssl_cert_file = '/pgarchive/ssl/server.crt'
#ssl_key_file = '/pgarchive/ssl/server.key'
#ssl_min_protocol_version = 'TLSv1.2'

# replication related
max_replication_slots = 32
max_wal_senders = 32
wal_receiver_timeout = '6000s'
wal_sender_timeout = '6000s'

#error reporting and logging
log_checkpoints = on
log_destination = stderr
log_directory = '/pgarchive/pglog'
log_file_mode = 0600
log_filename = 'postgresql.log.%Y-%m-%d'
logging_collector = on
log_line_prefix  = '%m:%r:%u@%d:[%p]:[%l-1]:%a'
log_timezone = 'UTC'
log_truncate_on_rotation = on
log_rotation_age = '1d'
log_rotation_size = 0
client_min_messages = error

log_connections = on
log_disconnections = on
#log_error_verbosity = 'verbose'
#we use pgaudit to logging ddl.
log_statement = 'ddl'

# statistics sample
track_activity_query_size = 4096
default_statistics_target = 100

#JIT
jit = off

# concurrency IO bitmap heap scan
effective_io_concurrency = 200
# random page cost ,default 4, lower it to 1.1 for ssd
random_page_cost = 1.1

max_stack_depth = 6144

#extensions
shared_preload_libraries = 'pg_stat_statements'

#pg_cron
cron.database_name = 'postgres'
cron.log_run = on
cron.log_statement = on
cron.max_running_jobs = 7
cron.use_background_workers = on

#STIG,All errors, denials and unsuccessful requests are logged if logging is enabled.
#pgaudit.log_catalog=on
#pgaudit.log_level='log'
#pgaudit.log_parameter=on
#pgaudit.log_statement_once=off
#pgaudit.log='all, -misc'
#pgaudit.log='role'