PostreSQL

---
kind: configuration/postgresql
title: PostgreSQL
name: default
specification:
  config_file:
    parameter_groups:
      - name: CONNECTIONS AND AUTHENTICATION
        subgroups:
          - name: Connection Settings
            parameters:
              - name: listen_addresses
                value: "'*'"
                comment: listen on all addresses
          - name: Security and Authentication
            parameters:
              - name: ssl
                value: 'off'
                comment: to have the default value also on Ubuntu
      - name: RESOURCE USAGE (except WAL)
        subgroups:
          - name: Kernel Resource Usage
            parameters:
              - name: shared_preload_libraries
                value: AUTOCONFIGURED
                comment: set by automation
      - name: ERROR REPORTING AND LOGGING
        subgroups:
          - name: Where to Log
            parameters:
              - name: log_directory
                value: "'/var/log/postgresql'"
                comment: to have standard location for Filebeat and logrotate
              - name: log_filename
                value: "'postgresql.log'"
                comment: to use logrotate with common configuration
      - name: WRITE AHEAD LOG
        subgroups:
          - name: Settings
            parameters:
              - name: wal_level
                value: replica
                when: replication
          # Changes to archive_mode require a full PostgreSQL server restart,
          # while archive_command changes can be applied via a normal configuration reload.
          # See https://repmgr.org/docs/repmgr.html#CONFIGURATION-POSTGRESQL
          - name: Archiving
            parameters:
              - name: archive_mode
                value: 'on'
                when: replication
              - name: archive_command
                value: "'/bin/true'"
                when: replication
      - name: REPLICATION
        subgroups:
          - name: Sending Server(s)
            parameters:
              - name: max_wal_senders
                value: 10
                comment: maximum number of simultaneously running WAL sender processes
                when: replication
              - name: wal_keep_size
                value: 500
                comment: the size of WAL files held for standby servers (MB)
                when: replication
          - name: Standby Servers # ignored on master server
            parameters:
              - name: hot_standby
                value: 'on'
                comment: must be 'on' for repmgr needs, ignored on primary but recommended in case primary becomes standby
                when: replication
  extensions:
    pgaudit:
      enabled: false
      shared_preload_libraries:
        - pgaudit
      config_file_parameters:
        log_connections: 'off'
        log_disconnections: 'off'
        log_statement: 'none'
        log_line_prefix: "'%m [%p] %q%u@%d,host=%h '"
        # pgaudit specific, see https://github.com/pgaudit/pgaudit/tree/REL_13_STABLE#settings
        pgaudit.log: "'write, function, role, ddl, misc_set'"
        pgaudit.log_catalog: 'off # to reduce overhead of logging' # default is 'on'
        # the following first 2 parameters are set to values that make it easier to access audit log per table
        # change their values to the opposite if you need to reduce overhead of logging
        pgaudit.log_relation: 'on # separate log entry for each relation' # default is 'off'
        pgaudit.log_statement_once: 'off' # same as default
        pgaudit.log_parameter: 'on' # default is 'off'
    pgbouncer:
      enabled: false
    replication:
      enabled: false
      replication_user_name: ls_repmgr
      replication_user_password: PASSWORD_TO_CHANGE
      privileged_user_name: ls_repmgr_admin
      privileged_user_password: PASSWORD_TO_CHANGE
      repmgr_database: ls_repmgr
      shared_preload_libraries:
        - repmgr
  logrotate:
    pgbouncer:
      period: weekly
      rotations: 5
    # Configuration partly based on /etc/logrotate.d/postgresql-common provided by 'postgresql-common' package from Ubuntu repo.
      # PostgreSQL from Ubuntu repo:
        # By default 'logging_collector' is disabled, so 'log_directory' parameter is ignored.
        # Default log path is /var/log/postgresql/postgresql-$version-$cluster.log.
      # PostgreSQL from SCL repo (RHEL):
        # By default 'logging_collector' is enabled and there is up to 7 files named 'daily' (e.g. postgresql-Wed.log)
        # and thus they can be overwritten by built-in log facility to provide rotation.
    # To have similar configuration for both distros (with logrotate), 'log_filename' parameter is modified.
    postgresql: |-
      /var/log/postgresql/postgresql*.log {
          maxsize 10M
          daily
          rotate 6
          copytruncate
          # delaycompress is for Filebeat
          delaycompress
          compress
          notifempty
          missingok
          su root root
          nomail
          # to have multiple unique filenames per day when dateext option is set
          dateformat -%Y%m%dH%H
      }