---
default_postgres_version: 13
# distributions:
#   bionic-pgdg
#   bullseye-pgdg
#   buster-pgdg
#   focal-pgdg
#   groovy-pgdg
#   hirsute-pgdg
#   sid-pgdg
#   stretch-pgdg
#   xenial-pgdg
default_postgres_target_distribution: focal-pgdg
default_max_connections: 1000
default_shared_buffers: 256MB

database_state: present

postgres_homedir: '/var/lib/postgresql'

postgres_listen_addresses: "listen_addresses = 'localhost,{{ stage_private_server_ip }}'"

postgres_base_config:
  -
    regex: "^data_directory"
    line: "data_directory = '/var/lib/postgresql/{{ default_postgres_version }}/main'"
  -
    regex: "^hba_file"
    line: "hba_file = '/etc/postgresql/{{ default_postgres_version }}/main/pg_hba.conf'"
  -
    regex: "^ident_file"
    line: "ident_file = '/etc/postgresql/{{ default_postgres_version }}/main/pg_ident.conf'"
  -
    regex: "^external_pid_file"
    line: "external_pid_file = '/var/run/postgresql/{{ default_postgres_version }}-main.pid'"
  -
    regex: "^port"
    line: "port = 5432"
  -
    regex: "^unix_socket_directories"
    line: "unix_socket_directories = '/var/run/postgresql'"
  -
    regex: "^dynamic_shared_memory_type"
    line: "dynamic_shared_memory_type = posix"
  -
    regex: "^max_wal_size"
    line: "max_wal_size = 1GB"
  -
    regex: "^min_wal_size"
    line: "min_wal_size = 80MB"
  -
    regex: "^log_line_prefix"
    line: "log_line_prefix = '%m [%p] %q%u@%d '"
  -
    regex: "^log_timezone"
    line: "log_timezone = 'Etc/UTC'"
  -
    regex: "^cluster_name"
    line: "cluster_name = '{{ default_postgres_version }}/main'"
  -
    regex: "^stats_temp_directory"
    line: "stats_temp_directory = '/var/run/postgresql/{{ default_postgres_version }}-main.pg_stat_tmp'"
  -
    regex: "^datestyle"
    line: "datestyle = 'iso, mdy'"
  -
    regex: "^timezone"
    line: "timezone = 'Etc/UTC'"
  -
    regex: "^lc_messages"
    line: "lc_messages = 'en_US.UTF-8'"
  -
    regex: "^lc_monetary"
    line: "lc_monetary = 'en_US.UTF-8'"
  -
    regex: "^lc_numeric"
    line: "lc_numeric = 'en_US.UTF-8'"
  -
    regex: "^lc_time"
    line: "lc_time = 'en_US.UTF-8'"
  -
    regex: "^default_text_search_config"
    line: "default_text_search_config = 'pg_catalog.english'"
  -
    regex: "^include_dir"
    line: "include_dir = 'conf.d'"
  -
    regex: "^wal_level"
    line: "wal_level = replica"
  -
    regex: "^max_wal_senders"
    line: "max_wal_senders = 10"
  -
    regex: "^archive_mode"
    line: "archive_mode = on"
  -
    regex: "^archive_command"
    line: "archive_command = 'cp -f %p /postgresql/replication/%f'"
  -
    regex: "^wal_keep_size"
    line: "wal_keep_size = 16"
  -
    regex: "^listen_addresses = "
    line: "{{ postgres_listen_addresses }}"
  -
    regex: "^max_connections"
    line: "max_connections = {{ default_max_connections }}"
  -
    regex: "^shared_buffers"
    line: "shared_buffers = {{ default_shared_buffers }}"
  -
    regex: "^shared_preload_libraries"
    line: "shared_preload_libraries = 'pg_stat_statements'"

postgres_ssl_config:
  -
    regex: "^ssl = on"
    line: "ssl = on"
  -
    regex: "^ssl_key_file"
    line: "ssl_key_file = '{{ cert_private_key }}'"
  -
    regex: "^ssl_cert_file"
    line: "ssl_cert_file = '{{ cert_public_key }}'"
  -
    regex: "^ssl_ca_file"
    line: "ssl_ca_file = '{{ ca_cert }}'"
  -
    regex: "^ssl_ciphers"
    line: "ssl_ciphers = 'HIGH'"

postgres_config : '{{ postgres_base_config + ( postgres_ssl_config | default([]) ) }}'

postgres_exporter_user: postgres
postgres_exporter_group: "{{ postgres_exporter_user }}"
postgres_exporter_dir: "/opt/"

postgres_exporter_version: "0.10.1"
postgres_exporter_checksum: "sha256:5344afe06a90c3cbd52803d56031bfcbcff78b56448e16c9228697ea0a2577b7"

postgres_exporter_dist: "postgres_exporter-{{ postgres_exporter_version }}.linux-amd64"
postgres_exporter_download_url: "https://github.com/prometheus-community/postgres_exporter/releases/download/v{{ postgres_exporter_version }}/{{ postgres_exporter_dist }}.tar.gz"

postgres_exporter_binary: "{{ postgres_exporter_dir }}{{ postgres_exporter_dist }}/postgres_exporter"

postgres_exporter_datasource_uri: "postgres@:5432/postgres?host=/var/run/postgresql"
postgres_exporter_home: "{{ '/var/lib/pgsql' if ansible_os_family ==  'RedHat' else '/var/lib/postgresql' }}"
postgres_exporter_flags:
  - "--web.listen-address='{{ stage_private_server_ip }}:{{ monitor_port_postgres }}'"
  - '--auto-discover-databases'
  - '--extend.query-path={{ postgres_exporter_home }}/queries.yml'

postgres_exporter_config_file: /etc/default/postgres_exporter

# got several queries from here:
# https://raw.githubusercontent.com/bdellegrazie/ansible-role-postgres_exporter/b01ae2aae53e02a0778ce6c06361cfb6af2a50c2/files/queries.yml
postgres_exporter_additional_queries:
  pg_replication:
    query: "SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) as lag"
    master: true
    metrics:
      - lag:
          usage: "GAUGE"
          description: "Replication lag behind master in seconds"
  pg_replication_wal_files:
    query: "SELECT COUNT(*) FROM pg_ls_dir('pg_wal') as count"
    master: true
    metrics:
      - wal_files_count:
          usage: "COUNTER"
          description: "Number of WAL files"
  pg_postmaster:
    query: "SELECT pg_postmaster_start_time as start_time_seconds from pg_postmaster_start_time()"
    master: true
    metrics:
      - start_time_seconds:
          usage: "GAUGE"
          description: "Time at which postmaster started"

  pg_stat_user_tables:
    query: "SELECT current_database() datname, schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup, n_mod_since_analyze, COALESCE(last_vacuum, '1970-01-01Z'), COALESCE(last_vacuum, '1970-01-01Z') as last_vacuum, COALESCE(last_autovacuum, '1970-01-01Z') as last_autovacuum, COALESCE(last_analyze, '1970-01-01Z') as last_analyze, COALESCE(last_autoanalyze, '1970-01-01Z') as last_autoanalyze, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count FROM pg_stat_user_tables"
    metrics:
      - datname:
          usage: "LABEL"
          description: "Name of current database"
      - schemaname:
          usage: "LABEL"
          description: "Name of the schema that this table is in"
      - relname:
          usage: "LABEL"
          description: "Name of this table"
      - seq_scan:
          usage: "COUNTER"
          description: "Number of sequential scans initiated on this table"
      - seq_tup_read:
          usage: "COUNTER"
          description: "Number of live rows fetched by sequential scans"
      - idx_scan:
          usage: "COUNTER"
          description: "Number of index scans initiated on this table"
      - idx_tup_fetch:
          usage: "COUNTER"
          description: "Number of live rows fetched by index scans"
      - n_tup_ins:
          usage: "COUNTER"
          description: "Number of rows inserted"
      - n_tup_upd:
          usage: "COUNTER"
          description: "Number of rows updated"
      - n_tup_del:
          usage: "COUNTER"
          description: "Number of rows deleted"
      - n_tup_hot_upd:
          usage: "COUNTER"
          description: "Number of rows HOT updated (i.e., with no separate index update required)"
      - n_live_tup:
          usage: "GAUGE"
          description: "Estimated number of live rows"
      - n_dead_tup:
          usage: "GAUGE"
          description: "Estimated number of dead rows"
      - n_mod_since_analyze:
          usage: "GAUGE"
          description: "Estimated number of rows changed since last analyze"
      - last_vacuum:
          usage: "GAUGE"
          description: "Last time at which this table was manually vacuumed (not counting VACUUM FULL)"
      - last_autovacuum:
          usage: "GAUGE"
          description: "Last time at which this table was vacuumed by the autovacuum daemon"
      - last_analyze:
          usage: "GAUGE"
          description: "Last time at which this table was manually analyzed"
      - last_autoanalyze:
          usage: "GAUGE"
          description: "Last time at which this table was analyzed by the autovacuum daemon"
      - vacuum_count:
          usage: "COUNTER"
          description: "Number of times this table has been manually vacuumed (not counting VACUUM FULL)"
      - autovacuum_count:
          usage: "COUNTER"
          description: "Number of times this table has been vacuumed by the autovacuum daemon"
      - analyze_count:
          usage: "COUNTER"
          description: "Number of times this table has been manually analyzed"
      - autoanalyze_count:
          usage: "COUNTER"
          description: "Number of times this table has been analyzed by the autovacuum daemon"

  pg_statio_user_tables:
    query: "SELECT current_database() datname, schemaname, relname, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit, toast_blks_read, toast_blks_hit, tidx_blks_read, tidx_blks_hit FROM pg_statio_user_tables"
    metrics:
      - datname:
          usage: "LABEL"
          description: "Name of current database"
      - schemaname:
          usage: "LABEL"
          description: "Name of the schema that this table is in"
      - relname:
          usage: "LABEL"
          description: "Name of this table"
      - heap_blks_read:
          usage: "COUNTER"
          description: "Number of disk blocks read from this table"
      - heap_blks_hit:
          usage: "COUNTER"
          description: "Number of buffer hits in this table"
      - idx_blks_read:
          usage: "COUNTER"
          description: "Number of disk blocks read from all indexes on this table"
      - idx_blks_hit:
          usage: "COUNTER"
          description: "Number of buffer hits in all indexes on this table"
      - toast_blks_read:
          usage: "COUNTER"
          description: "Number of disk blocks read from this table's TOAST table (if any)"
      - toast_blks_hit:
          usage: "COUNTER"
          description: "Number of buffer hits in this table's TOAST table (if any)"
      - tidx_blks_read:
          usage: "COUNTER"
          description: "Number of disk blocks read from this table's TOAST table indexes (if any)"
      - tidx_blks_hit:
          usage: "COUNTER"
          description: "Number of buffer hits in this table's TOAST table indexes (if any)"

  pg_database:
    query: "SELECT pg_database.datname, pg_database_size(pg_database.datname) as size_bytes FROM pg_database"
    master: true
    cache_seconds: 30
    metrics:
      - datname:
          usage: "LABEL"
          description: "Name of the database"
      - size_bytes:
          usage: "GAUGE"
          description: "Disk space used by the database"

  pg_stat_statements:
    query: "SELECT t2.rolname, t3.datname, queryid, calls, ( total_plan_time + total_exec_time ) / 1000 as total_time_seconds, ( min_plan_time + min_exec_time ) / 1000 as min_time_seconds, ( max_plan_time + max_exec_time ) / 1000 as max_time_seconds, ( mean_plan_time + mean_exec_time ) / 1000 as mean_time_seconds, ( stddev_plan_time + stddev_exec_time ) / 1000 as stddev_time_seconds, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time / 1000 as blk_read_time_seconds, blk_write_time / 1000 as blk_write_time_seconds FROM pg_stat_statements t1 JOIN pg_roles t2 ON (t1.userid=t2.oid) JOIN pg_database t3 ON (t1.dbid=t3.oid) WHERE t2.rolname != 'rdsadmin'"
    master: true
    metrics:
      - rolname:
          usage: "LABEL"
          description: "Name of user"
      - datname:
          usage: "LABEL"
          description: "Name of database"
      - queryid:
          usage: "LABEL"
          description: "Query ID"
      - calls:
          usage: "COUNTER"
          description: "Number of times executed"
      - total_time_seconds:
          usage: "COUNTER"
          description: "Total time spent in the statement, in milliseconds"
      - min_time_seconds:
          usage: "GAUGE"
          description: "Minimum time spent in the statement, in milliseconds"
      - max_time_seconds:
          usage: "GAUGE"
          description: "Maximum time spent in the statement, in milliseconds"
      - mean_time_seconds:
          usage: "GAUGE"
          description: "Mean time spent in the statement, in milliseconds"
      - stddev_time_seconds:
          usage: "GAUGE"
          description: "Population standard deviation of time spent in the statement, in milliseconds"
      - rows:
          usage: "COUNTER"
          description: "Total number of rows retrieved or affected by the statement"
      - shared_blks_hit:
          usage: "COUNTER"
          description: "Total number of shared block cache hits by the statement"
      - shared_blks_read:
          usage: "COUNTER"
          description: "Total number of shared blocks read by the statement"
      - shared_blks_dirtied:
          usage: "COUNTER"
          description: "Total number of shared blocks dirtied by the statement"
      - shared_blks_written:
          usage: "COUNTER"
          description: "Total number of shared blocks written by the statement"
      - local_blks_hit:
          usage: "COUNTER"
          description: "Total number of local block cache hits by the statement"
      - local_blks_read:
          usage: "COUNTER"
          description: "Total number of local blocks read by the statement"
      - local_blks_dirtied:
          usage: "COUNTER"
          description: "Total number of local blocks dirtied by the statement"
      - local_blks_written:
          usage: "COUNTER"
          description: "Total number of local blocks written by the statement"
      - temp_blks_read:
          usage: "COUNTER"
          description: "Total number of temp blocks read by the statement"
      - temp_blks_written:
          usage: "COUNTER"
          description: "Total number of temp blocks written by the statement"
      - blk_read_time_seconds:
          usage: "COUNTER"
          description: "Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)"
      - blk_write_time_seconds:
          usage: "COUNTER"
          description: "Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)"

database_engine: postgres
backup_dest_dir: "{{ backup_directory }}/{{ database_engine }}/{{ get_current_date }}"
backup_status_file: '{{ backup_dest_dir }}/backup_finished'
