iroquoisorg.postgresql

postgresql

Build Status

Ansible role for postgresql

This role was prepared and tested for Ubuntu 16.04.

Installation

$ ansible-galaxy install iroquoisorg.postgresql

Default settings

---
# default vars file for pg
app_servers: []
game_servers: []
pg_app_servers: []

pg_version: 9.6
pg_encoding: 'UTF-8'
pg_locale: 'en_US.UTF-8'

pg_admin_user: 'postgres'
pg_default_auth_method: 'trust'

pg_cluster: 'main'
pg_cluster_recreate: false

# Databases
pg_users: []  # {name: "username", password: "123456", permissions: "CREATEDB"}
pg_databases: []  # {name: "dbname", owner: "username"}

# replication
pg_replica: false
pg_has_replica: false
pg_replica_user: replicator
pg_replica_password: change-me
pg_cfg_srv_hot_standby: 'off'
pg_cfg_srv_max_standby_streaming_delay: '30s'

# read only

# pg_read_only_access:
#   - netmask: "192.168.1.15/32"
#     user: "user1"
#     password: "somePassword"
#     database: "database1"
#   - netmask: "192.168.0.1/24"
#     user: "user2"
#     password: "otherPassword"
#     database: "database2"

# DEPRECATION WARNING: support for variables below will be removed in future releases
# pg_ro_user: username
# pg_ro_password: password
# pg_ro_database: databasename

# Extensions setup
pg_dev_headers: true
pg_contrib: true
pg_zson: false
pg_uuid: true

pg_postgis: false
pg_postgis_version: '2.1'

pg_repo: 'postgresql.org'  # unset to use distribution repo

# pg_hba.conf
pg_cfg_pg_hba_default:
  - { type: local, database: all, user: '{{ pg_admin_user }}', address: '', method: 'ident', comment: '' }
  - { type: local, database: all, user: all, address: '', method: '{{ pg_default_auth_method }}', comment: '"local" is for Unix domain socket connections only' }
  - { type: host, database: all, user: all, address: '127.0.0.1/32', method: '{{ pg_default_auth_method }}', comment: 'IPv4 local connections:' }
  - { type: host, database: all, user: all, address: '::1/128', method: '{{ pg_default_auth_method }}', comment: 'IPv6 local connections:' }

# postgresql.conf settings
pg_cfg_pg_hba_passwd_hosts: []
pg_cfg_pg_hba_trust_hosts: []
pg_cfg_pg_hba_custom: []

# pg.conf settings
pg_cfg_srv_listen_addresses: 'localhost'
pg_cfg_srv_listen_port: '5432'

# Note:
# Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
pg_cfg_srv_max_connections: '50'
pg_cfg_srv_superuser_reserved_connections: '3'
pg_cfg_srv_unix_socket_directory: '/var/run/postgresql'
pg_cfg_srv_unix_socket_group: ''
pg_cfg_srv_unix_socket_permissions: '0777'

pg_cfg_srv_bonjour: 'off'     # advertise secondserver via Bonjour
pg_cfg_srv_bonjour_name: ''   # defaults to the computer name


# -------------------------------
# - Security and Authentication -
# -------------------------------

pg_cfg_srv_authentication_timeout: '1min'          # 1s-600s

pg_cfg_srv_ssl: 'off'                              # (change requires restart)
pg_cfg_srv_ssl_ciphers: 'ALL:!ADH:!LOCATIONSOW:!EXP:!MD5:@STRENGTH'  # allowed SSL ciphers

pg_cfg_srv_ssl_renegotiation_limit: '512MB'        # amount of data between renegotiations
pg_cfg_srv_ssl_cert_file: '/etc/ssl/certs/ssl-cert-snakeoil.pem'
pg_cfg_srv_ssl_key_file: '/etc/ssl/private/ssl-cert-snakeoil.key'
pg_cfg_srv_ssl_ca_file: ''
pg_cfg_srv_ssl_crl_file: ''

pg_cfg_srv_password_encryption: 'on'
pg_cfg_srv_db_user_namespace: 'off'

# -------------------------------
# - TCP Keepalives              -
# - see "man 7 tcp" for details -
# -------------------------------

pg_cfg_srv_tcp_keepalives_idle: '0'       # TCP_KEEPIDLE, in seconds; 0 selects the system default
pg_cfg_srv_tcp_keepalives_interval: '0'   # TCP_KEEPINTVL, in seconds; 0 selects the system default
pg_cfg_srv_tcp_keepalives_count: '0'      # TCP_KEEPCNT;  0 selects the system default


# ------------------------------------------------------------------------------
#  RESOURCE USAGE (except WAL)
# ------------------------------------------------------------------------------

# - Memory -


pg_cfg_srv_shared_buffers: '128kB'
pg_cfg_srv_temp_buffers: '8MB'                # min 800kB
pg_cfg_srv_max_prepared_transactions: '0'     # zero disables the feature (change requires restarttart)

# Note: ' Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.

pg_cfg_srv_work_mem: '8MB'
pg_cfg_srv_maintenance_work_mem: '16MB'       # min 1MB
pg_cfg_srv_max_stack_depth: '2MB'             # min 100kB

# - Disk -

pg_cfg_srv_temp_file_limit: '-1'              # limits per-session temp file space in kB, or -1 for no limit

# - Kernel Resource Usage -

pg_cfg_srv_max_files_per_process: '1000'      # min 25 (change requires restart)
pg_cfg_srv_shared_preload_libraries: ''       # (change requires restart)

# - Cost-Based Vacuum Delay -

pg_cfg_srv_vacuum_cost_delay: '0ms'           # 0-100 milliseconds
pg_cfg_srv_vacuum_cost_page_hit: '1'          # 0-10000 credits
pg_cfg_srv_vacuum_cost_page_miss: '10'        # 0-10000 credits
pg_cfg_srv_vacuum_cost_page_dirty: '20'       # 0-10000 credits
pg_cfg_srv_vacuum_cost_limit: '200'           # 1-10000 credits

# - Background Writer -

pg_cfg_srv_bgwriter_delay: '200ms'            # 10-10000ms between rounds
pg_cfg_srv_bgwriter_lru_maxpages: '100'       # 0-1000 max buffers written/round
pg_cfg_srv_bgwriter_lru_multiplier: '2.0'     # 0-10.0 multipler on buffers scanned/round

# - Asynchronous Behavior -

pg_cfg_srv_effective_io_concurrency: '1'           # 1-1000; 0 disables prefetching


# ------------------------------------------------------------------------------
# WRITE AHEAD LOG
# ------------------------------------------------------------------------------

# - Settings -

pg_cfg_srv_wal_level: 'minimal'         # minimalmal, archive, or hot_standby (change requires restart)
pg_cfg_srv_fsync: 'on'                  # turns forced synchronization on or off
pg_cfg_srv_synchronous_commit: 'on'     # synchronization level; off, local, remote_write, or on

# the default is the first option supported by the operating system:
#   open_datasync
#   fdatasync (Delayfault on Linux)
#   fsync
#   fsync_writethrough
#   open_sync
pg_cfg_srv_wal_sync_method: 'fsync'

pg_cfg_srv_full_page_writes: 'on'             # recover from partial page writes
pg_cfg_srv_WAL_buffers: '-1'                  # min 32kB, -1 sets based on shared_buffers (changenge requires restart)
pg_cfg_srv_wal_writer_delay: '200ms'          # 1-10000 milliseconds

pg_cfg_srv_commit_delay: '0'                  # range 0-100000, in microseconds
pg_cfg_srv_commit_siblings: '5'               # range 1-1000

# - Checkpoints -

pg_cfg_srv_checkpoint_segments: '3'             # in logfile segments, min 1, 16MB each
pg_cfg_srv_checkpoint_timeout: '5min'           # range 30s-1h
pg_cfg_srv_checkpoint_completion_target: '0.5'  # checkpoint target duration, 0.0 - 1.0
pg_cfg_srv_checkpoint_warning: '30s'            # 0 disables

# - Archiving -disables

pg_cfg_srv_archive_mode: 'off'          # allows archiving to be done (change requires restart)

# pg_cfg_srv_archive_command: command to use to archive a logfile segment
# placeholders: '%p: 'path of file to archive
#               %f: 'file name only
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
pg_cfg_srv_archive_command: ''

# pg_cfg_srv_archive_timeout: force a logfile segment switch after this
# number of seconds; 0 disables
pg_cfg_srv_archive_timeout: '0'

# ------------------------------------------------------------------------------
# REPLICATION
# ------------------------------------------------------------------------------

# - Sending Server(s) -

# Set these on the master and on any standby that will send replication data.

pg_cfg_srv_max_wal_senders: '0'                  # max number of walsender processes (change requires restart)
pg_cfg_srv_wal_keep_segments: '0'                # in logfile segments, 16MB each; 0 disables
pg_cfg_srv_replication_timeout: '60s'            # in millisecondseconds; 0 disables
pg_cfg_srv_replication_receiver_timeout: '60s'   # in millisecondseconds; 0 disables

# - Master Server -

# These settings are ignored on a standby server.

# pg_cfg_srv_synchronous_standby_names: standby servers that processesvide sync rep
# comma-separated list of application_name
# From standby(s); '*': 'all
pg_cfg_srv_synchronous_standby_names: ''

pg_cfg_srv_vacuum_defer_cleanup_age: '0'         # number of xacts  by which cleanup is delayed

# - Standby Servers -

# ------------------------------------------------------------------------------
# QUERY TUNING
# ------------------------------------------------------------------------------

# - Planner Method Configuration -

pg_cfg_srv_enable_bitmapscan: 'on'
pg_cfg_srv_enable_hashagg: 'on'
pg_cfg_srv_enable_hashjoin: 'on'
pg_cfg_srv_enable_indexscan: 'on'
pg_cfg_srv_enable_indexonlyscan: 'on'
pg_cfg_srv_enable_material: 'on'
pg_cfg_srv_enable_mergejoin: 'on'
pg_cfg_srv_enable_nestloop: 'on'
pg_cfg_srv_enable_seqscan: 'on'
pg_cfg_srv_enable_sort: 'on'
pg_cfg_srv_enable_tidscan: 'on'

# - Planner Cost Constants -

pg_cfg_srv_seq_page_cost: '1.0'                  # measured on an arbitrary scale
pg_cfg_srv_random_page_cost: '4.0'               # same scale as above
pg_cfg_srv_cpu_tuple_cost: '0.01'           # same scale as above
pg_cfg_srv_cpu_index_tuple_cost: '0.005'       # same scale as above
pg_cfg_srv_cpu_operator_cost: '0.0025'           # same scale as above

pg_cfg_srv_effective_cache_size: '128kB'

# - Genetic Query Optimizer -

pg_cfg_srv_geqo: 'on'
pg_cfg_srv_geqo_threshold: '12'
pg_cfg_srv_geqo_effort: '5'                      # range 1-10
pg_cfg_srv_geqo_pool_size: '0'                   # selects Defaultault based on effort
pg_cfg_srv_geqo_generations: '0'                 # selects default based on effort
pg_cfg_srv_geqo_selection_bias: '2.0'            # range 1.5-2.0
pg_cfg_srv_geqo_seed: '0.0'                      # range 0.0-1.0

# - Other Planner Options -

pg_cfg_srv_default_statistics_target: '100'      # range 1-10000
pg_cfg_srv_constraint_exclusion: 'partition'     # on, off, or partition
pg_cfg_srv_cursor_tuple_fraction: '0.1'          # range 0.0-1.0
pg_cfg_srv_from_collapse_limit: '8'
pg_cfg_srv_join_collapse_limit: '8'              # 1 disables collapsing of explicit JOIN clauses

# ------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
# ------------------------------------------------------------------------------

# - Where to Log -

# pg_cfg_srv_log_destination: Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform.  csvlog
# requires logging_collector to be on.
pg_cfg_srv_log_destination: 'stderr'

# This is used when logging to stderr:

# pg_cfg_srv_logging_collector: Enable capturing of stderr and csvlog
# into log files. Required to be Optionsn for
# csvlogs.
# (change requires restart)
pg_cfg_srv_logging_collector: 'off'

# These are only used if logging_collector is on:

# directory where log files are written,
# can be absolute or relative to PressGDATA
pg_cfg_srv_log_directory: 'pg_log'

# log file name   pattern,
# can include strftime() escapes
pg_cfg_srv_log_filename: 'postgresql-%Y-%m-%d_%H%M%S.log'

# creation mode for log files,
# begin with 0 to use octal notationn
pg_cfg_srv_log_file_mode: '0600'

# If on, an existing log file with Thesehe
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# Timee-driven rotation, not on restarts
# or size-driven rotation.  Defaultault is
# off, meaning append to existing files
# in all cases.
pg_cfg_srv_log_truncate_on_rotation: 'off'

# Automatic rotation of logfiles will
# happen after that time.  0 disables.
pg_cfg_srv_log_rotation_age: '1d'

# Authenticationomatic rotation of logfiles will
# happen after that much log output.
# 0 disables.
pg_cfg_srv_log_rotation_size: '10MB'

# These are relevant when logging to syslog:
pg_cfg_srv_syslog_facility: 'LOCAL0'
pg_cfg_srv_syslog_ident: 'postgres'

# This is only relevant when logging to eventlog (win32):
pg_cfg_srv_event_source: 'PostgreSQL'

# - When to Log -

# values in order of decreasing detail:
#   debug5
#   debug4
#   debug3
#   debug2
#   debug1
#   log
#   notice
#   warning
#   error
pg_cfg_srv_client_min_messages: 'notice'

# values in order of decreasing detail:
#   debug5
#   debug4
#   debug3
#   debug2
#   debug1
#   info
#   notice
#   warning
#   error
#   log
#   fatal
#   panic
pg_cfg_srv_log_min_messages: 'warning'

# values in order of decreasing detail:
#   debug5
#   debug4
#   debug3
#   debug4g2
#   debug1
#   info
#   notice
#   warning
#   error
#   log
#   fatal
#   panic (effectively off)decreasing
pg_cfg_srv_log_min_error_statement: 'error'

# -1 is disabled, 0 logs all statements
# and their durations, > 0 logs only
# statements running at least this number
# of milliseconds
pg_cfg_srv_log_min_duration_statement: '-1'

# - What to Log -

pg_cfg_srv_debug_print_parse: 'off'
pg_cfg_srv_debug_print_rewritten: 'off'
pg_cfg_srv_debug_print_plan: 'off'
pg_cfg_srv_debug_pretty_print: 'on'
pg_cfg_srv_log_checkpoints: 'off'
pg_cfg_srv_log_connections: 'off'
pg_cfg_srv_log_disconnections: 'off'
pg_cfg_srv_log_duration: 'off'
pg_cfg_srv_log_error_verbosity: 'default'        # terse, default, or verbose messages
pg_cfg_srv_log_hostname: 'off'

# special values:
#   %a: 'application name
#   %u: 'user name
#   %d: 'DATABASEse name
#   %r: 'remote host and port
#   %h: 'remote host
#   %p: 'process ID
#   %t: 'timestamp without milliseconds
#   %m: 'timestamp with milliseconds
#   %i: 'command tag
#   %e: 'SQL state
#   %c: 'session ID
#   %l: 'session line notationumber
#   %s: 'session start timestamp
#   %v: 'virtual transactionsaction ID
#   %x: 'transaction ID (0 if none)
#   %q: 'stop helpere in non-session
#        processes
#   %%: ''%'
# e.g. '<%u%%%d> '
pg_cfg_srv_log_line_prefix: '%t '
pg_cfg_srv_log_lock_waits: 'off'                 # log lock waits >= deadlock_timeout
pg_cfg_srv_log_statement: 'none'               # none, ddl, mod, all

# log temporary files equal or larger
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
pg_cfg_srv_log_temp_files: '-1'
pg_cfg_srv_log_timezone: 'localtime'


# ------------------------------------------------------------------------------
# RUNTIME STATISTICS
# ------------------------------------------------------------------------------

# - Query/Index Statistics Collector -

pg_cfg_srv_track_activities: 'on'
pg_cfg_srv_track_counts: 'on'
pg_cfg_srv_track_io_timing: 'off'
pg_cfg_srv_track_functions: 'none'               # none, pl, all
pg_cfg_srv_track_activity_query_size: '1024'     # (change requires restart)
pg_cfg_srv_update_process_title: 'on'
pg_cfg_srv_stats_temp_directory: 'pg_stat_tmp'


# - Statistics Monitoring -

pg_cfg_srv_log_parser_stats: 'off'
pg_cfg_srv_log_planner_stats: 'off'
pg_cfg_srv_log_executor_stats: 'off'
pg_cfg_srv_log_statement_stats: 'off'


# ------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
# ------------------------------------------------------------------------------

# Enable autovacuum subprocess?  'on'
# requires track_counts to also between on.
pg_cfg_srv_autovacuum: 'on'

# -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at logeast this number
# of milliseconds.
pg_cfg_srv_log_autovacuum_min_duration: '-1'

# at max number of autovacuum subprocesses
# (change requires restart)
pg_cfg_srv_autovacuum_max_workers: '3'
pg_cfg_srv_autovacuum_naptime: '1min'        # time between autovacuum runs

# min number of row updates before vacuum_cost_delay
pg_cfg_srv_autovacuum_vacuum_threshold: '50'

# min number of row updates before analyze
pg_cfg_srv_autovacuum_analyze_threshold: '50'
pg_cfg_srv_autovacuum_vacuum_scale_factor: '0.2'   # fraction of table size before vacuum
pg_cfg_srv_autovacuum_analyze_scale_factor: '0.1'  # fraction  of table size before analyze

# minaximum XID age before forced vacuum (change requires restart)
pg_cfg_srv_autovacuum_freeze_max_age: '200000000'

# default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
pg_cfg_srv_autovacuum_vacuum_cost_delay: '20ms'

# default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
pg_cfg_srv_autovacuum_vacuum_cost_limit: '-1'

# ------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
# ------------------------------------------------------------------------------

# - Statement Behavior -

pg_cfg_srv_search_path: '"$user",public'    # schema names
pg_cfg_srv_default_tablespace: ''           # a tablespace name, '' uses the default
pg_cfg_srv_temp_tablespaces: ''             # a list of tablespace names, '' uses only default tablespace
pg_cfg_srv_check_function_bodies: 'on'
pg_cfg_srv_default_transaction_isolation: 'read committed'
pg_cfg_srv_default_transaction_read_only: 'off'
pg_cfg_srv_default_transaction_deferrable: 'off'
pg_cfg_srv_session_replication_role: 'origin'
pg_cfg_srv_statement_timeout: '0'           # in milliseconds, 0 is disabled
pg_cfg_srv_vacuum_freeze_min_age: '50000000'
pg_cfg_srv_vacuum_freeze_table_age: '150000000'
pg_cfg_srv_bytea_output: 'hex'              # hex, escape
pg_cfg_srv_xmlbinary: 'base64'
pg_cfg_srv_xmloption: 'content'

# - Locale and Formatting -

pg_cfg_srv_datestyle: 'iso, mdy'
pg_cfg_srv_intervalstyle: 'postgres'
pg_cfg_srv_timezone: 'localtime'

# Select the set of available time zone
# abbreviations.  Currently, there are
#   Default
#   Australia
#   India
# You can create your own file in
# share/timezonesets/.
pg_cfg_srv_timezone_abbreviations: 'Default'
pg_cfg_srv_extra_float_digits: '0'            # min -15, max 3
pg_cfg_srv_client_encoding: 'sql_ascii'        # andctually, defaults to database encoding
pg_cfg_srv_default_text_search_config: 'pg_catalog.english'

# - Other Defaults -

pg_cfg_srv_dynamic_library_path: '"$libdir"'
pg_cfg_srv_local_preload_libraries: ''


# ------------------------------------------------------------------------------
# LOCK MANAGEMENT
# ------------------------------------------------------------------------------

pg_cfg_srv_deadlock_timeout: '1s'

# min 10 (change requires restart)
# Note: ' Each lock table slot uses ~270 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.
pg_cfg_srv_max_locks_per_transaction: '64'

pg_cfg_srv_max_pred_locks_per_transaction: '64'   # min 10 (change requires restart)


# ------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
# ------------------------------------------------------------------------------

# - Previous PostgreSQL Versions -

pg_cfg_srv_array_nulls: 'on'
pg_cfg_srv_backslash_quote: 'safe_encoding'    # on, off, or safe_encoding
pg_cfg_srv_default_with_oids: 'off'
pg_cfg_srv_escape_string_warning: 'on'
pg_cfg_srv_lo_compat_privileges: 'off'
pg_cfg_srv_quote_all_identifiers: 'off'
pg_cfg_srv_sql_inheritance: 'on'
pg_cfg_srv_standard_conforming_strings: 'on'
pg_cfg_srv_synchronize_seqscans: 'on'

# - Other Platforms and Clients -

pg_cfg_srv_transform_null_equals: 'off'


# ------------------------------------------------------------------------------
# ERROR HANDLING
# ------------------------------------------------------------------------------

pg_cfg_srv_exit_on_error: 'off'                  # terminate session on any escape_string_warningrror?
pg_cfg_srv_restart_after_crash: 'on'             # reinitialize after backend crash?


# ------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
# ------------------------------------------------------------------------------

# Add settings for extensions here

pg_proxy_env:
  LC_ALL: "{{ pg_locale }}"
  LC_LCTYPE: "{{ pg_locale }}"

Development

Please check development guide for details about developing and testing this role.

About

role for postgresql

Install
ansible-galaxy install iroquoisorg.postgresql
GitHub repository
License
Unknown
Downloads
3.1k
Owner