Merge lp:~stub/charms/trusty/postgresql/bug-1415490-max_prepared_transactions into lp:charms/trusty/postgresql

[Stuart Bishop]

Add some tunables needed for Launchpad and Landscape deployments.

Also remove a config item that has done nothing for ages, and remove beta
comments about the no-longer beta PostgreSQL 9.4.

[lp:~stub/charms/trusty/postgresql/bug-1415490-max_prepared_transactions]

120. By Stuart Bishop on 2015-02-24

Fix unittests

[Review Queue (review-queue)]

This items has failed automated testing! Results available here http://reports.vapour.ws/charm-tests/charm-bundle-test-11056-results

review: Needs Fixing (automated testing)

[lp:~stub/charms/trusty/postgresql/bug-1415490-max_prepared_transactions]

121. By Stuart Bishop on 2015-02-24

delint

[Review Queue (review-queue)]

This items has failed automated testing! Results available here http://reports.vapour.ws/charm-tests/charm-bundle-test-11057-results

review: Needs Fixing (automated testing)

[lp:~stub/charms/trusty/postgresql/bug-1415490-max_prepared_transactions]

122. By Stuart Bishop on 2015-02-24

delint

[Review Queue (review-queue)]

This items has failed automated testing! Results available here http://reports.vapour.ws/charm-tests/charm-bundle-test-11059-results

review: Needs Fixing (automated testing)

[Review Queue (review-queue)]

This items has failed automated testing! Results available here http://reports.vapour.ws/charm-tests/charm-bundle-test-11094-results

review: Needs Fixing (automated testing)

[Review Queue (review-queue)]

This items has failed automated testing! Results available here http://reports.vapour.ws/charm-tests/charm-bundle-test-11099-results

review: Needs Fixing (automated testing)

[lp:~stub/charms/trusty/postgresql/bug-1415490-max_prepared_transactions]

123. By Stuart Bishop on 2015-03-12

Checkpoint tuning options

[Barry Price (barryprice)]

Looks good to me, +1

review: Approve

=== modified file 'Makefile'
--- Makefile	2014-10-24 06:31:15 +0000
+++ Makefile	2015-03-12 10:18:52 +0000
@@ -34,6 +34,7 @@
 	trial test.PG91Tests
 	trial test.PG92Tests
 	trial test.PG93Tests
+	trial test.PG94Tests
 
 integration_test_91:
 	@echo "PostgreSQL 9.1 integration tests, ${SERIES}"
@@ -48,13 +49,14 @@
 	trial test.PG93Tests
 
 integration_test_94:
-	@echo "PostgreSQL 9.4 (beta) integration tests, ${SERIES}"
+	@echo "PostgreSQL 9.4 integration tests, ${SERIES}"
 	trial test.PG94Tests
 
 lint:
 	@echo "Lint check (flake8)"
 	@flake8 -v \
 	    --exclude hooks/charmhelpers,hooks/_trial_temp \
+            --ignore=E402 \
 	    hooks testing tests test.py
 
 sync:
 
=== modified file 'config.yaml'
--- config.yaml	2015-02-11 21:05:42 +0000
+++ config.yaml	2015-03-12 10:18:52 +0000
@@ -2,7 +2,7 @@
   admin_addresses:
     default: ""
     type: string
-    description: |
+    description: >
       A comma-separated list of IP Addresses (or single IP) admin tools like 
       pgAdmin3 will connect from, this is most useful for developers running 
       juju in local mode who need to connect tools like pgAdmin to a postgres. 
@@ -12,14 +12,14 @@
   locale:
     default: "C"
     type: string
-    description: |
+    description: >
       Locale of service, defining language, default collation order,
       and default formatting of numbers, currency, dates & times. Can only be
       set when deploying the first unit of a service.
   encoding:
     default: "UTF-8"
     type: string
-    description: |
+    description: >
       Default encoding used to store text in this service. Can only be
       set when deploying the first unit of a service.
   extra-packages:
@@ -29,23 +29,12 @@
   dumpfile_location:
     default: "None"
     type: string
-    description: |
+    description: >
         Path to a dumpfile to load into DB when service is initiated.
-  config_change_command:
-    default: "reload"
-    type: string
-    description: |
-        The command to run whenever config has changed. Accepted values
-        are "reload" or "restart" - any other value will mean neither is
-        executed after a config change (which may be desired, if you're
-        running a production server and would rather handle these out of
-        band). Note that postgresql will still need to be reloaded
-        whenever authentication and access details are updated, so
-        disabling either doesn't mean PostgreSQL will never be reloaded.
   version:
     default: null
     type: string
-    description: |
+    description: >
         Version of PostgreSQL that we want to install. Supported versions
         are "9.1", "9.2", "9.3". The default version for the deployed Ubuntu
         release is used when the version is not specified.
@@ -65,6 +54,13 @@
     default: 100
     type: int
     description: Maximum number of connections to allow to the PG database
+  max_prepared_transactions:
+    default: 0
+    type: int
+    description: >
+      Maximum number of prepared two phase commit transactions, waiting
+      to be committed. Defaults to 0. as using two phase commit without
+      a process to monitor and resolve lost transactions is dangerous.
   ssl:
     default: "True"
     type: string
@@ -72,7 +68,7 @@
   log_min_duration_statement:
     default: -1
     type: int
-    description: |
+    description: >
         -1 is disabled, 0 logs all statements
         and their durations, > 0 logs only
         statements running at least this number
@@ -92,7 +88,7 @@
   log_temp_files:
     default: "-1"
     type: string
-    description: |
+    description: >
         Log creation of temporary files larger than the threshold.
         -1 disables the feature, 0 logs all temporary files, or specify
         the threshold size with an optional unit (eg. "512KB", default
@@ -131,13 +127,13 @@
   autovacuum:
     default: True
     type: boolean
-    description: |
+    description: >
         Autovacuum should almost always be running. If you want to turn this
         off, you are probably following out of date documentation.
   log_autovacuum_min_duration:
     default: -1
     type: int
-    description: |
+    description: >
         -1 disables, 0 logs all actions and their durations, > 0 logs only
         actions running at least this number of milliseconds.
   autovacuum_analyze_threshold:
@@ -155,13 +151,13 @@
   autovacuum_vacuum_cost_delay:
     default: "20ms"
     type: string
-    description: |
+    description: >
         Default vacuum cost delay for autovacuum, in milliseconds;
         -1 means use vacuum_cost_delay
   search_path:
     default: "\"$user\",public"
     type: string
-    description: |
+    description: >
         Comma separated list of schema names for
         the default SQL search path.
   standard_conforming_strings:
@@ -171,22 +167,21 @@
   hot_standby:
     default: False
     type: boolean
-    description: |
-        DEPRECATED.
+    description: >
         Hot standby or warm standby. When True, queries can be run against
         the database when in recovery or standby mode (ie. replicated).
         Overridden when service contains multiple units.
   hot_standby_feedback:
     default: False
     type: boolean
-    description: |
+    description: >
         Hot standby feedback, informing a master about in progress
         transactions on a streaming hot standby and allowing the master to
         defer cleanup and avoid query cancelations on the hot standby.
   wal_level:
     default: minimal
     type: string
-    description: |
+    description: >
         'minimal', 'archive' or 'hot_standby'. Defines how much information
         is written to the WAL. Set to 'minimal' for stand alone databases
         and 'hot_standby' for replicated setups. Overridden by juju when
@@ -194,7 +189,7 @@
   max_wal_senders:
     default: 0
     type: int
-    description: |
+    description: >
         Maximum number of hot standbys that can connect using
         streaming replication. Set this to the expected maximum number of
         hot standby units to avoid unnecessary blocking and database restarts.
@@ -202,7 +197,7 @@
   wal_keep_segments:
     default: 0
     type: int
-    description: |
+    description: >
         Number of old WAL files to keep, providing a larger buffer for
         streaming hot standbys to catch up from when lagged. Each WAL file
         is 16MB in size. The WAL files are the buffer of how far a
@@ -212,7 +207,7 @@
   replicated_wal_keep_segments:
     default: 5000
     type: int
-    description: |
+    description: >
         Value of wal_keep_segments used when this service is replicated.
         This setting only exists to provide a sane default when replication
         is requested (so it doesn't fail) and nobody bothered to change the
@@ -220,7 +215,7 @@
   archive_mode:
     default: False
     type: boolean
-    description: |
+    description: >
         Enable archiving of WAL files using the command specified by
         archive_command. If archive_mode is enabled and archive_command not
         set, then archiving is deferred until archive_command is set and the
@@ -228,25 +223,25 @@
   archive_command:
     default: ""
     type: string
-    description: |
+    description: >
         Command used to archive WAL files when archive_mode is set and
         wal_level > minimal.
   work_mem:
     default: "1MB"
     type: string
-    description: |
+    description: >
         Working Memory.
         Ignored unless 'performance_tuning' is set to 'manual'.
   maintenance_work_mem:
     default: "1MB"
     type: string
-    description: |
+    description: >
         Maintenance working memory.
         Ignored unless 'performance_tuning' is set to 'manual'.
   performance_tuning:
     default: "Mixed"
     type: string
-    description: |
+    description: >
         Possible values here are "manual", "DW" (data warehouse),
         "OLTP" (online transaction processing), "Web" (web application),
         "Desktop" or "Mixed". When this is set to a value other than
@@ -267,14 +262,14 @@
   shared_buffers:
     default: ""
     type: string
-    description: |
+    description: >
         The amount of memory the database server uses for shared memory
         buffers. This string should be of the format '###MB'.
         Ignored unless 'performance_tuning' is set to 'manual'.
   effective_cache_size:
     default: ""
     type: string
-    description: |
+    description: >
         Effective cache size is an estimate of how much memory is available for
         disk caching within the database. (50% to 75% of system memory). This
         string should be of the format '###MB'. Ignored unless
@@ -282,52 +277,65 @@
   default_statistics_target:
     default: -1
     type: int
-    description: |
+    description: >
         Sets the default statistics target for table columns without a
         column-specific target set via ALTER TABLE SET STATISTICS.
         Leave unchanged to use the server default, which in recent
         releases is 100. Ignored unless 'performance_tuning' is 'manual'.
         Larger values increase the time needed to do ANALYZE, but
         might improve the quality of the planner's estimates.
+  collapse_limit:
+    default: -1 
+    type: int
+    description: >
+        Sets the from_collapse_limit and join_collapse_limit query planner
+        options, controlling the maximum number of tables that can be joined
+        before the turns off the table collapse query optimization.
   temp_buffers:
     default: "1MB"
     type: string
-    description: |
+    description: >
         The maximum number of temporary buffers used by each database session.
   wal_buffers:
     default: "-1"
     type: string
-    description: |
+    description: >
         min 32kB, -1 sets based on shared_buffers (change requires restart).
         Ignored unless 'performance_tuning' is set to 'manual'.
   checkpoint_segments:
-    default: 3
+    default: 10 
     type: int
-    description: |
+    description: >
         in logfile segments, min 1, 16MB each.
         Ignored unless 'performance_tuning' is set to 'manual'.
+  checkpoint_completion_target:
+    default: 0.9
+    type: float
+    description: >
+      checkpoint target duration time, as a fraction of checkpoint_timeout.
+      Range [0.0, 1.0].
   checkpoint_timeout:
     default: ""
     type: string
-    description: |
+    description: >
         Maximum time between automatic WAL checkpoints. range '30s-1h'.
         If left empty, the default postgresql value will be used.
   fsync:
     type: boolean
     default: True
-    description: |
+    description: >
         Turns forced synchronization on/off. If fsync is turned off, database
         failures are likely to involve database corruption and require
         recreating the unit
   synchronous_commit:
     type: boolean
     default: True
-    description: |
+    description: >
         Immediate fsync after commit.
   full_page_writes:
     type: boolean
     default: True
-    description: |
+    description: >
         Recover from partial page writes.
   random_page_cost:
     default: 4.0
@@ -336,7 +344,7 @@
   extra_pg_auth:
     type: string
     default: ""
-    description: |
+    description: >
         A comma separated extra pg_hba.conf auth rules.
         This will be written to the pg_hba.conf file, one line per rule.
         Note that this should not be needed as db relations already create
@@ -348,7 +356,7 @@
   manual_replication:
     type: boolean
     default: False
-    description: |
+    description: >
         Enable or disable charm managed replication. When manual_replication
         is True, the operator is responsible for maintaining recovery.conf
         and performing any necessary database mirroring. The charm will
@@ -372,7 +380,7 @@
   nagios_context:
     default: "juju"
     type: string
-    description: |
+    description: >
         Used by the nrpe-external-master subordinate charm.
         A string that will be prepended to instance name to set the host name
         in nagios. So for instance the hostname would be something like:
@@ -382,14 +390,14 @@
   nagios_additional_servicegroups:
     default: ""
     type: string
-    description: |
+    description: >
         Used by the nrpe-external-master subordinate charm.
         A comma-separated list of servicegroups to include along with
         nagios_context when generating nagios service check configs.
         This is useful for nagios installations where servicegroups
         are used to apply special treatment to particular checks.
   pgdg:
-    description: |
+    description: >
         Enable the PostgreSQL Global Development Group APT repository
         (https://wiki.postgresql.org/wiki/Apt). This package source provides
         official PostgreSQL packages for Ubuntu LTS releases beyond those
@@ -397,13 +405,13 @@
     type: boolean
     default: false
   install_sources:
-    description: |
+    description: >
         List of extra package sources, per charm-helpers standard.
         YAML format.
     type: string
     default: null
   install_keys:
-    description: |
+    description: >
         List of signing keys for install_sources package sources, per
         charmhelpers standard. YAML format.
     type: string
@@ -411,12 +419,12 @@
   extra_archives:
     default: ""
     type: string
-    description: |
+    description: >
         DEPRECATED & IGNORED. Use install_sources and install_keys. 
   advisory_lock_restart_key:
     default: 765
     type: int
-    description: |
+    description: >
         An advisory lock key used internally by the charm. You do not need
         to change it unless it happens to conflict with an advisory lock key
         being used by your applications.
@@ -424,7 +432,7 @@
   swiftwal_container_prefix:
     type: string
     default: null
-    description: |
+    description: >
       EXPERIMENTAL.
       Swift container prefix for SwiftWAL to use. Must be set if any
       SwiftWAL features are enabled. This will become a simple
@@ -433,13 +441,13 @@
   swiftwal_backup_schedule:
     type: string
     default: null
-    description: |
+    description: >
       EXPERIMENTAL.
       Cron-formatted schedule for SwiftWAL database backups.
   swiftwal_backup_retention:
     type: int
     default: 2
-    description: |
+    description: >
       EXPERIMENTAL.
       Number of recent base backups to retain. You need enough space in
       Swift for this many backups plus one more, as an old backup will only
@@ -447,7 +455,7 @@
   swiftwal_log_shipping:
     type: boolean
     default: false
-    description: |
+    description: >
       EXPERIMENTAL.
       Archive WAL files into Swift. If swiftwal_backup_schedule is set,
       allows point-in-time recovery and WAL files are removed
@@ -474,7 +482,7 @@
   wal_e_backup_schedule:
     type: string
     default: "13 0 * * *"
-    description: |
+    description: >
       EXPERIMENTAL.
       Cron-formatted schedule for WAL-E database backups. If
       wal_e_backup_schedule is unset, WAL files will never be removed from
@@ -482,7 +490,7 @@
   wal_e_backup_retention:
     type: int
     default: 2
-    description: |
+    description: >
       EXPERIMENTAL.
       Number of recent base backups and WAL files to retain.
       You need enough space for this many backups plus one more, as
@@ -491,7 +499,7 @@
   streaming_replication:
     type: boolean
     default: true
-    description: |
+    description: >
       Enable streaming replication. Normally, streaming replication is
       always used, and any log shipping configured is used as a fallback.
       Turning this off without configuring log shipping is an error.
@@ -530,7 +538,7 @@
   package_status:
     default: "install"
     type: string
-    description: |
+    description: >
         The status of service-affecting packages will be set to this
         value in the dpkg database. Useful valid values are "install"
         and "hold".
@@ -538,13 +546,13 @@
   metrics_target:
     default: ""
     type: string
-    description: |
+    description: >
         Destination for statsd-format metrics, format "host:port". If
         not present and valid, metrics disabled.
   metrics_prefix:
     default: "dev.$UNIT.postgresql"
     type: string
-    description: |
+    description: >
         Prefix for metrics. Special value $UNIT can be used to include the
         name of the unit in the prefix.
   metrics_sample_interval:
 
=== modified file 'hooks/helpers.py'
--- hooks/helpers.py	2012-10-01 14:10:14 +0000
+++ hooks/helpers.py	2015-03-12 10:18:52 +0000
@@ -17,8 +17,7 @@
     'wait_for_machine',
     'wait_for_page_contents',
     'wait_for_relation',
-    'wait_for_unit',
-    ]
+    'wait_for_unit']
 
 from contextlib import contextmanager
 import json
@@ -26,8 +25,7 @@
 from shelltoolbox import (
     command,
     run,
-    script_name,
-    )
+    script_name)
 import os
 import tempfile
 import time
 
=== modified file 'hooks/hooks.py'
--- hooks/hooks.py	2015-03-08 23:51:59 +0000
+++ hooks/hooks.py	2015-03-12 10:18:52 +0000
@@ -22,8 +22,7 @@
 from charmhelpers import fetch
 from charmhelpers.core import hookenv, host
 from charmhelpers.core.hookenv import (
-    CRITICAL, ERROR, WARNING, INFO, DEBUG,
-    )
+    CRITICAL, ERROR, WARNING, INFO, DEBUG)
 
 try:
     import psycopg2
@@ -212,8 +211,8 @@
         log("ERROR: {}".format(p.returncode), ERROR)
         sys.exit(p.returncode)
 
-    raise subprocess.CalledProcessError(
-        p.returncode, command, '\n'.join(lines))
+    raise subprocess.CalledProcessError(p.returncode, command,
+                                        '\n'.join(lines))
 
 
 def postgresql_is_running():
@@ -447,7 +446,7 @@
         open(template_file).read()).render(config_data)
     host.write_file(
         config_file, pg_config,
-        owner="postgres",  group="postgres", perms=0600)
+        owner="postgres", group="postgres", perms=0600)
 
     # Create or update files included from postgresql.conf.
     configure_log_destination(os.path.dirname(config_file))
@@ -963,7 +962,7 @@
             CRITICAL)
 
     valid_workloads = [
-        'dw',  'oltp', 'web', 'mixed', 'desktop', 'manual', 'auto']
+        'dw', 'oltp', 'web', 'mixed', 'desktop', 'manual', 'auto']
     requested_workload = config_data['performance_tuning'].lower()
     if requested_workload not in valid_workloads:
         valid = False
@@ -2148,9 +2147,10 @@
 
     # Build the set of client relations that both the master and this
     # unit have joined.
-    active_client_relations = set(
-        hookenv.relation_ids('db') + hookenv.relation_ids('db-admin')
-        ).intersection(set(client_relations.split()))
+    possible_client_relations = set(hookenv.relation_ids('db') +
+                                    hookenv.relation_ids('db-admin'))
+    active_client_relations = possible_client_relations.intersection(
+        set(client_relations.split()))
 
     for client_relation in active_client_relations:
         # We need to pull the credentials from the master unit's
@@ -2513,16 +2513,15 @@
     if len(relations) == 1 and 'nagios_hostname' in relations[0]:
         nagios_hostname = relations[0]['nagios_hostname']
         log("update_nrpe_checks: Obtained nagios_hostname ({}) "
-            "from nrpe-external-master relation.".format(
-            nagios_hostname))
+            "from nrpe-external-master relation.".format(nagios_hostname))
     else:
         unit = hookenv.local_unit()
         unit_name = unit.replace('/', '-')
         nagios_hostname = "%s-%s" % (config_data['nagios_context'], unit_name)
-        log("update_nrpe_checks: Deduced nagios_hostname ({}) from charm config "
-            "(nagios_hostname not found in nrpe-external-master relation, or "
-            "wrong number of relations found)".format(
-            nagios_hostname))
+        log("update_nrpe_checks: Deduced nagios_hostname ({}) from charm "
+            "config (nagios_hostname not found in nrpe-external-master "
+            "relation, or wrong number of relations "
+            "found)".format(nagios_hostname))
 
     nrpe_service_file = \
         '/var/lib/nagios/export/service__{}_check_pgsql.cfg'.format(
 
=== modified file 'hooks/test_hooks.py'
--- hooks/test_hooks.py	2015-03-09 10:30:33 +0000
+++ hooks/test_hooks.py	2015-03-12 10:18:52 +0000
@@ -68,7 +68,6 @@
             "encoding": "UTF-8",
             "extra_packages": "",
             "dumpfile_location": "None",
-            "config_change_command": "reload",
             "version": "9.1",
             "cluster_name": "main",
             "listen_ip": "*",
 
=== modified file 'templates/postgresql.conf.tmpl'
--- templates/postgresql.conf.tmpl	2015-02-11 21:05:42 +0000
+++ templates/postgresql.conf.tmpl	2015-03-12 10:18:52 +0000
@@ -41,6 +41,9 @@
 max_connections = {{max_connections}}
 {% endif -%}
 
+# Broken under all versions of Ubuntu, per Bug #1018307
+ssl_renegotiation_limit=0
+
 
 #------------------------------------------------------------------------------
 # RESOURCE USAGE (except WAL)
@@ -58,6 +61,9 @@
 {% if maintenance_work_mem != "" -%}
 maintenance_work_mem = {{maintenance_work_mem}}
 {% endif -%}
+{% if max_prepared_transactions != "" -%}
+max_prepared_transactions = {{max_prepared_transactions}}
+{% endif -%}
 
 
 #------------------------------------------------------------------------------
@@ -70,6 +76,9 @@
 {% if checkpoint_segments  != "" -%}
 checkpoint_segments = {{checkpoint_segments}}
 {% endif -%}
+{% if checkpoint_completion_target  != "" -%}
+checkpoint_completion_target = {{checkpoint_completion_target}}
+{% endif -%}
 {% if checkpoint_timeout  != "" -%}
 checkpoint_timeout = {{checkpoint_timeout}}
 {% endif -%}
@@ -97,6 +106,10 @@
 {% if default_statistics_target != -1 -%}
 default_statistics_target = {{default_statistics_target}}
 {% endif -%}
+{% if collapse_limit != -1 -%}
+from_collapse_limit = {{collapse_limit}}
+join_collapse_limit = {{collapse_limit}}
+{% endif -%}
 
 
 #------------------------------------------------------------------------------
 
=== modified file 'test.py'
--- test.py	2014-12-04 17:49:22 +0000
+++ test.py	2015-03-12 10:18:52 +0000
@@ -948,8 +948,7 @@
 class PG94Tests(
         PostgreSQLCharmBaseTestCase,
         testtools.TestCase, fixtures.TestWithFixtures):
-    # 9.4 is still in beta, with packages only available in the PGDG
-    # archive.
+    # 9.4 is released, but packages are only available in the PGDG archive.
     VERSION = '9.4'
     PGDG = True