iroquoisorg.postgresql
postgresql
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.
Install
ansible-galaxy install iroquoisorg.postgresqlLicense
Unknown
Downloads
3.1k
Owner
