# file: postgresql/defaults/main.yml # Basic settings postgresql_version: 9.3 postgresql_encoding: 'UTF-8' postgresql_locale: 'en_US.UTF-8' postgresql_admin_user: "postgres" postgresql_default_auth_method: "trust" # The user/group that will run postgresql process or service postgresql_service_user: "{{ postgresql_admin_user }}" postgresql_service_group: "{{ postgresql_admin_user }}" postgresql_cluster_name: "main" postgresql_cluster_reset: false postgresql_database_owner: "{{ postgresql_admin_user }}" # Extensions postgresql_ext_install_contrib: no postgresql_ext_install_dev_headers: no postgresql_ext_install_postgis: no postgresql_ext_postgis_version: "2.1" # be careful: check whether the postgresql/postgis versions work together # List of databases to be created (optional) postgresql_databases: [] # List of users to be created (optional) postgresql_users: [] # List of user privileges to be applied (optional) postgresql_user_privileges: [] # pg_hba.conf postgresql_pg_hba_default: - { type: local, database: all, user: '{{ postgresql_admin_user }}', address: '', method: '{{ postgresql_default_auth_method }}', comment: '' } - { type: local, database: all, user: all, address: '', method: '{{ postgresql_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: '{{ postgresql_default_auth_method }}', comment: 'IPv4 local connections:' } - { type: host, database: all, user: all, address: '::1/128', method: '{{ postgresql_default_auth_method }}', comment: 'IPv6 local connections:' } postgresql_pg_hba_passwd_hosts: [] postgresql_pg_hba_trust_hosts: [] postgresql_pg_hba_custom: [] # postgresql.conf #------------------------------------------------------------------------------ # FILE LOCATIONS #------------------------------------------------------------------------------ # Location of postgres configuration files here postgresql_conf_directory: "/etc/postgresql/{{postgresql_version}}/{{postgresql_cluster_name}}" # HBA (Host Based Authentication) file postgresql_hba_file: "{{postgresql_conf_directory}}/pg_hba.conf" # Ident configuration file postgresql_ident_file: "{{postgresql_conf_directory}}/pg_ident.conf" # Use data in another directory postgresql_data_directory: "/var/lib/postgresql/{{postgresql_version}}/{{postgresql_cluster_name}}" # If external_pid_file is not explicitly set, on extra PID file is written postgresql_external_pid_file: "/var/run/postgresql/{{postgresql_version}}-{{postgresql_cluster_name}}.pid" #------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ postgresql_listen_addresses: - localhost postgresql_port: 5432 postgresql_max_connections: 100 postgresql_superuser_reserved_connections: 3 postgresql_unix_socket_directories: - /var/run/postgresql postgresql_unix_socket_group: '' postgresql_unix_socket_permissions: '0777' # begin with 0 to use octal notation # Automatic pg_ctl configuration. Specify a list of options containing # cluster specific options to be passed to pg_ctl(1). postgresql_pg_ctl_options: [] postgresql_bonjour: off # advertise server via Bonjour postgresql_bonjour_name: '' # defaults to the computer name # - Security and Authentication - postgresql_authentication_timeout: 60s postgresql_ssl: off postgresql_ssl_ciphers: - 'DEFAULT' - '!LOW' - '!EXP' - '!MD5' - '@STRENGTH' postgresql_ssl_prefer_server_ciphers: on postgresql_ssl_ecdh_curve: 'prime256v1' postgresql_ssl_renegotiation_limit: 512MB # amount of data between renegotiations postgresql_ssl_cert_file: /etc/ssl/certs/ssl-cert-snakeoil.pem postgresql_ssl_key_file: /etc/ssl/private/ssl-cert-snakeoil.key postgresql_ssl_ca_file: '' postgresql_ssl_crl_file: '' postgresql_password_encryption: on postgresql_db_user_namespace: off # Kerberos and GSSAPI postgresql_krb_server_keyfile: '' postgresql_krb_srvname: postgres postgresql_krb_caseins_users: off # TCP Keepalives, 0 selects the system default (in seconds) postgresql_tcp_keepalives_idle: 0 postgresql_tcp_keepalives_interval: 0 postgresql_tcp_keepalives_count: 0 #------------------------------------------------------------------------------ # RESOURCE USAGE (except WAL) #------------------------------------------------------------------------------ # - Memory - postgresql_shared_buffers: 128MB # min 128kB postgresql_huge_pages: try # on, off, or try postgresql_temp_buffers: 8MB # min 800kB # 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. postgresql_max_prepared_transactions: 0 # zero disables the feature postgresql_work_mem: 1MB # min 64kB postgresql_maintenance_work_mem: 16MB # min 1MB postgresql_autovacuum_work_mem: -1 # min 1MB, or -1 to use maintenance_work_mem postgresql_max_stack_depth: 2MB # min 100kB postgresql_dynamic_shared_memory_type: posix # the default is the first option # supported by the operating system: # posix # sysv # windows # mmap # use none to disable dynamic shared memory # - Disk - # limits per-session temp file space in kB, or -1 for no limit postgresql_temp_file_limit: -1 # - Kernel Resource Usage - postgresql_max_files_per_process: 1000 # min 25 postgresql_shared_preload_libraries: [] # - Cost-Based Vacuum Delay - postgresql_vacuum_cost_delay: 0 # 0-100 milliseconds postgresql_vacuum_cost_page_hit: 1 # 0-10000 credits postgresql_vacuum_cost_page_miss: 10 # 0-10000 credits postgresql_vacuum_cost_page_dirty: 20 # 0-10000 credits postgresql_vacuum_cost_limit: 200 # 1-10000 credits # - Background Writer - postgresql_bgwriter_delay: 200ms # 10-10000ms between rounds postgresql_bgwriter_lru_maxpages: 100 # 0-1000 max buffers written/round postgresql_bgwriter_lru_multiplier: 2.0 # 0-10.0 multipler on buffers scanned/round # - Asynchronous Behavior - postgresql_effective_io_concurrency: 1 # 1-1000; 0 disables prefetching postgresql_max_worker_processes: 8 #------------------------------------------------------------------------------ # WRITE AHEAD LOG #------------------------------------------------------------------------------ # - Settings - postgresql_wal_level: minimal # minimal, archive, hot_standby, or logical postgresql_fsync: on # turns forced synchronization on or off # Synchronization level: # - off # - local # - remote_write # - on postgresql_synchronous_commit: on # The default is the first option supported by the operating system: # - open_datasync # - fdatasync (default on Linux) # - fsync # - fsync_writethrough # - open_sync postgresql_wal_sync_method: fsync # recover from partial page writes postgresql_full_page_writes: on postgresql_wal_log_hints: off # also do full page writes of non-critical updates postgresql_wal_buffers: -1 # min 32kB, -1 sets based on shared_buffers postgresql_wal_writer_delay: 200ms # 1-10000 milliseconds postgresql_commit_delay: 0 # range 0-100000, in microseconds postgresql_commit_siblings: 5 # range 1-1000 # - Checkpoints - postgresql_checkpoint_segments: 3 # in logfile segments, min 1, 16MB each postgresql_checkpoint_timeout: 5min # range 30s-1h postgresql_checkpoint_completion_target: 0.5 # checkpoint target duration, 0.0 - 1.0 postgresql_checkpoint_warning: 30s # 0 disables # - Archiving - # allows archiving to be done postgresql_archive_mode: off # 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' postgresql_archive_command: '' # force a logfile segment switch after this postgresql_archive_timeout: 0 #------------------------------------------------------------------------------ # REPLICATION #------------------------------------------------------------------------------ # - Sending Server(s) - # Set these on the master and on any standby that will send replication data. # max number of walsender processes postgresql_max_wal_senders: 0 postgresql_wal_keep_segments: 0 # in logfile segments, 16MB each; 0 disables postgresql_wal_sender_timeout: 60s # in milliseconds; 0 disables postgresql_max_replication_slots: 0 # max number of replication slots # - Master Server - # These settings are ignored on a standby server. # Standby servers that provide sync rep. # Comma-separated list of application_name from standby(s) postgresql_synchronous_standby_names: [] # '*' means 'all' # number of xacts by which cleanup is delayed postgresql_vacuum_defer_cleanup_age: 0 # - Standby Servers - # "on" allows queries during recovery postgresql_hot_standby: off # max delay before canceling queries when reading WAL from archive postgresql_max_standby_archive_delay: 30s # -1 allows indefinite delay # max delay before canceling queries when reading streaming WAL; postgresql_max_standby_streaming_delay: 30s # -1 allows indefinite delay # send replies at least this often postgresql_wal_receiver_status_interval: 10s # 0 disables # send info from standby to prevent query conflicts postgresql_hot_standby_feedback: off #time that receiver waits for communication from master in milliseconds postgresql_wal_receiver_timeout: 60s #------------------------------------------------------------------------------ # QUERY TUNING #------------------------------------------------------------------------------ # - Planner Method Configuration - postgresql_enable_bitmapscan: on postgresql_enable_hashagg: on postgresql_enable_hashjoin: on postgresql_enable_indexscan: on postgresql_enable_indexonlyscan: on postgresql_enable_material: on postgresql_enable_mergejoin: on postgresql_enable_nestloop: on postgresql_enable_seqscan: on postgresql_enable_sort: on postgresql_enable_tidscan: on # - Planner Cost Constants - postgresql_seq_page_cost: 1.0 # measured on an arbitrary scale postgresql_random_page_cost: 4.0 # same scale as above postgresql_cpu_tuple_cost: 0.01 # same scale as above postgresql_cpu_index_tuple_cost: 0.005 # same scale as above postgresql_cpu_operator_cost: 0.0025 # same scale as above postgresql_effective_cache_size: 128MB # - Genetic Query Optimizer - postgresql_geqo: on postgresql_geqo_threshold: 12 postgresql_geqo_effort: 5 # range 1-10 postgresql_geqo_pool_size: 0 # selects default based on effort postgresql_geqo_generations: 0 # selects default based on effort postgresql_geqo_selection_bias: 2.0 # range 1.5-2.0 postgresql_geqo_seed: 0.0 # range 0.0-1.0 # - Other Planner Options - postgresql_default_statistics_target: 100 # range 1-10000 postgresql_constraint_exclusion: partition # on, off, or partition postgresql_cursor_tuple_fraction: 0.1 # range 0.0-1.0 postgresql_from_collapse_limit: 8 postgresql_join_collapse_limit: 8 # 1 disables collapsing of explicit #------------------------------------------------------------------------------ # ERROR REPORTING AND LOGGING #------------------------------------------------------------------------------ # - Where to Log - # Valid values are combinations of stderr, csvlog, syslog, and eventlog. # depending on platform. Csvlog requires logging_collector to be on. postgresql_log_destination: stderr # Enable capturing of stderr and csvlog into log files. # Required to be on for csvlogs. postgresql_logging_collector: off # These are only used if logging_collector is on: # Directory where log files are written, can be absolute or relative to PGDATA postgresql_log_directory: pg_log # Log file name pattern, can include strftime() escapes postgresql_log_filename: postgresql-%Y-%m-%d_%H%M%S.log postgresql_log_file_mode: '0600' # begin with 0 to use octal notation # If on, an existing log file with the same name as the new log file will be # truncated rather than appended to. But such truncation only occurs on # time-driven rotation, not on restarts or size-driven rotation. Default is # off, meaning append to existing files in all cases. postgresql_log_truncate_on_rotation: off # Automatic rotation of logfiles will happen after that time. postgresql_log_rotation_age: 1d # Automatic rotation of logfiles will happen after that much log output. postgresql_log_rotation_size: 10MB # These are relevant when logging to syslog: postgresql_syslog_facility: LOCAL0 postgresql_syslog_ident: postgres # This is only relevant when logging to eventlog (win32): postgresql_event_source: PostgreSQL # - When to Log - # Values in order of decreasing detail: # - debug5 # - debug4 # - debug3 # - debug2 # - debug1 # - log # - notice # - warning # - error postgresql_client_min_messages: notice # Values in order of decreasing detail: # - debug5 # - debug4 # - debug3 # - debug2 # - debug1 # - info # - notice # - warning # - error # - log # - fatal # - panic postgresql_log_min_messages: warning # Values in order of decreasing detail: # - debug5 # - debug4 # - debug3 # - debug2 # - debug1 # - info # - notice # - warning # - error # - log # - fatal # - panic (effectively off) postgresql_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 postgresql_log_min_duration_statement: -1 # - What to Log - postgresql_debug_print_parse: off postgresql_debug_print_rewritten: off postgresql_debug_print_plan: off postgresql_debug_pretty_print: on postgresql_log_checkpoints: off postgresql_log_connections: off postgresql_log_disconnections: off postgresql_log_duration: off postgresql_log_error_verbosity: default # terse, default, or verbose messages postgresql_log_hostname: off # Special values: # %a = application name # %u = user name # %d = database 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 number # %s = sessioan start timestamp # %v = virtual transaction ID # %x = transaction ID (0 if none) # %q = stop here in non-session # processes # %% = '%' postgresql_log_line_prefix: '%t ' # log lock waits >= deadlock_timeout postgresql_log_lock_waits: off postgresql_log_statement: none # none, ddl, mod, all # log temporary files equal or larger postgresql_log_temp_files: -1 postgresql_log_timezone: UTC #------------------------------------------------------------------------------ # RUNTIME STATISTICS #------------------------------------------------------------------------------ # - Query/Index Statistics Collector - postgresql_track_activities: on postgresql_track_counts: on postgresql_track_io_timing: off postgresql_track_functions: none # none, pl, all postgresql_track_activity_query_size: 1024 postgresql_update_process_title: on postgresql_stats_temp_directory: pg_stat_tmp # - Statistics Monitoring - postgresql_log_parser_stats: off postgresql_log_planner_stats: off postgresql_log_executor_stats: off postgresql_log_statement_stats: off #------------------------------------------------------------------------------ # AUTOVACUUM PARAMETERS #------------------------------------------------------------------------------ # Enable autovacuum subprocess? 'on' requires track_counts to also be on. postgresql_autovacuum: on # -1 disables, 0 logs all actions and their durations, > 0 logs only # actions running at least this number of milliseconds. postgresql_log_autovacuum_min_duration: -1 # max number of autovacuum subprocesses postgresql_autovacuum_max_workers: 3 # time between autovacuum runs postgresql_autovacuum_naptime: 1min # min number of row updates before vacuum postgresql_autovacuum_vacuum_threshold: 50 # min number of row updates before analyze postgresql_autovacuum_analyze_threshold: 50 # fraction of table size before vacuum postgresql_autovacuum_vacuum_scale_factor: 0.2 # fraction of table size before analyze postgresql_autovacuum_analyze_scale_factor: 0.1 # maximum XID age before forced vacuum postgresql_autovacuum_freeze_max_age: 200000000 # maximum Multixact age before forced vacuum postgresql_autovacuum_multixact_freeze_max_age: 400000000 # default vacuum cost delay for autovacuum, in milliseconds postgresql_autovacuum_vacuum_cost_delay: 20ms # default vacuum cost limit for autovacuum, postgresql_autovacuum_vacuum_cost_limit: -1 #------------------------------------------------------------------------------ # CLIENT CONNECTION DEFAULTS #------------------------------------------------------------------------------ # - Statement Behavior - postgresql_search_path: # schema names - '"$user"' - public postgresql_default_tablespace: '' # a tablespace name, '' uses the default postgresql_temp_tablespaces: [] # a list of tablespace names postgresql_check_function_bodies: on postgresql_default_transaction_isolation: read committed postgresql_default_transaction_read_only: off postgresql_default_transaction_deferrable: off postgresql_session_replication_role: origin postgresql_statement_timeout: 0 # in milliseconds, 0 is disabled postgresql_lock_timeout: 0 # in milliseconds, 0 is disabled postgresql_vacuum_freeze_min_age: 50000000 postgresql_vacuum_freeze_table_age: 150000000 postgresql_vacuum_multixact_freeze_min_age: 5000000 postgresql_vacuum_multixact_freeze_table_age: 150000000 postgresql_bytea_output: hex # hex, escape postgresql_xmlbinary: base64 postgresql_xmloption: content # - Locale and Formatting - postgresql_datestyle: - iso - mdy postgresql_intervalstyle: postgres postgresql_timezone: UTC # Select the set of available time zone abbreviations. Currently, there are: # Default # Australia # India # You can create your own file in `share/timezonesets/`. postgresql_timezone_abbreviations: Default postgresql_extra_float_digits: 0 # min -15, max 3 postgresql_client_encoding: sql_ascii # 'sql_ascii' actually defaults to database encoding # These settings are initialized by initdb, but they can be changed. # locale for system error message postgresql_lc_messages: en_US.UTF-8 # locale for monetary formatting postgresql_lc_monetary: en_US.UTF-8 # locale for number formatting postgresql_lc_numeric: en_US.UTF-8 # locale for time formatting postgresql_lc_time: en_US.UTF-8 postgresql_default_text_search_config: pg_catalog.english postgresql_dynamic_library_path: '$libdir' postgresql_local_preload_libraries: [] postgresql_session_preload_libraries: [] #------------------------------------------------------------------------------ # LOCK MANAGEMENT #------------------------------------------------------------------------------ postgresql_deadlock_timeout: 1s postgresql_max_locks_per_transaction: 64 # min 10 # 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. postgresql_max_pred_locks_per_transaction: 64 # min 10 #------------------------------------------------------------------------------ # VERSION/PLATFORM COMPATIBILITY #------------------------------------------------------------------------------ # - Previous PostgreSQL Versions - postgresql_array_nulls: on postgresql_backslash_quote: safe_encoding # on, off, or safe_encoding postgresql_default_with_oids: off postgresql_escape_string_warning: on postgresql_lo_compat_privileges: off postgresql_quote_all_identifiers: off postgresql_sql_inheritance: on postgresql_standard_conforming_strings: on postgresql_synchronize_seqscans: on # - Other Platforms and Clients - postgresql_transform_null_equals: off #------------------------------------------------------------------------------ # ERROR HANDLING #------------------------------------------------------------------------------ # Terminate session on any error? postgresql_exit_on_error: off # Reinitialize after backend crash? postgresql_restart_after_crash: on #------------------------------------------------------------------------------ # PGTUNE #------------------------------------------------------------------------------ # Should we run pgtune and accept the changes it proposes? postgresql_pgtune: no # Total system memory in bytes, will attempt to detect if set to "no" postgresql_pgtune_memory: no # Database type, valid options are DW, OLTP, Web, Mixed, Desktop postgresql_pgtune_type: Mixed # Maximum number of expected connections, if "no", default based on db type postgresql_pgtune_connections: no postgresql_env: LC_ALL: "{{ postgresql_locale }}" LC_LCTYPE: "{{ postgresql_locale }}" # APT settings postgresql_apt_key_id: ACCC4CF8 postgresql_apt_key_url: "https://www.postgresql.org/media/keys/ACCC4CF8.asc" postgresql_apt_repository: 'deb http://apt.postgresql.org/pub/repos/apt/ {{ansible_distribution_release}}-pgdg main' # Pin-Priority of PGDG repository postgresql_apt_pin_priority: 500