Merge lp:~stub/charms/precise/postgresql/pg93 into lp:charms/postgresql

[Stuart Bishop]

Run tests for all supported versions of PostgreSQL.

This involved several drive-bys, including:

- Allowing specifying a non-standard port to actually work.
- Realizing that the mechanism for specifying external sources was broken,
  and replacing it with the charm-helpers tool. I was using a space separated
  list, but unless you are using a PPA your source probably contains a space.
- Realizing that the current mechanism for charm-helpers to retrieve keys
  is insecure, so implement a shortcut to add the official PG backports
  securely that also makes it easier to use.
- Using null instead of empty string to indicate default/magic, probably
  for personal and unjustifiable aethetic reasons. Empty string still works.
- Config validation, to catch dangerously bad config changes before code
  that doesn't expect them destroys data. We cannot support version
  downgrades, and we don't yet support version upgrades.

[lp:~stub/charms/precise/postgresql/pg93]

148. By Stuart Bishop on 2014-01-27

9.2 tests need PGDG archive or packages might not be found

149. By Stuart Bishop on 2014-01-27

Replication fixes

150. By Stuart Bishop on 2014-01-29

Fix admin test to work on non-local providers

151. By Stuart Bishop on 2014-01-29

charm-helpers for lsb_release

152. By Stuart Bishop on 2014-01-29

distro_release -> distro_codename

153. By Stuart Bishop on 2014-01-29

Merged tests into pg93.

154. By Stuart Bishop on 2014-01-30

Refactoring startup, shutdown & reload to better avoid config failures and race conditions like lp:1273636, and some fixes

155. By Stuart Bishop on 2014-01-30

Fixes

156. By Stuart Bishop on 2014-01-31

Fix PG 9.2 & 9.3 replication

157. By Stuart Bishop on 2014-01-31

Improve config validation

158. By Stuart Bishop on 2014-01-31

Make basic replication test more robust

159. By Stuart Bishop on 2014-01-31

Bump timeout to decrease flakeyness due to LP:1200267

160. By Stuart Bishop on 2014-02-04

Resolve conflicts

161. By Stuart Bishop on 2014-02-05

Merged tests into pg93.

162. By Stuart Bishop on 2014-02-05

Merged tests into pg93.

[Marco Ceppi (marcoceppi)]

Fantastic work, thank you for submitting this.

make test fails however with the following:

Lint check (flake8)
directory hooks
checking hooks/helpers.py
checking hooks/hooks.py
hooks/hooks.py:2109:5: E125 continuation line does not distinguish itself from next logical line
checking hooks/test_hooks.py
directory testing
checking testing/__init__.py
checking testing/jujufixture.py
directory tests
checking test.py
make: *** [test] Error 1

Seems simple enough, otherwise LGTM

[lp:~stub/charms/precise/postgresql/pg93]

163. By Stuart Bishop on 2014-02-07

Better fit with juju test

164. By Stuart Bishop on 2014-02-07

delint

[Stuart Bishop (stub)]

Thanks. I've delinted, and will land it once the dependent MP https://code.launchpad.net/~stub/charms/precise/postgresql/cleanups/+merge/203701 is ready.

[Marco Ceppi (marcoceppi)]

LGTM +1

review: Approve

=== modified file '.bzrignore'
--- .bzrignore	2012-10-30 16:41:12 +0000
+++ .bzrignore	2014-02-07 12:21:04 +0000
@@ -0,0 +1,3 @@
+_trial_temp
+hooks/_trial_temp
+hooks/local_state.pickle

=== modified file 'Makefile'
--- Makefile	2014-01-09 09:08:11 +0000
+++ Makefile	2014-02-07 12:21:04 +0000
@@ -1,13 +1,30 @@
 CHARM_DIR := $(shell pwd)
+TEST_TIMEOUT := 900
 
 test: lint unit_test integration_test
 
 unit_test:
+	@echo "Unit tests of hooks"
 	cd hooks && trial test_hooks.py
 
 integration_test:
-	echo "Integration tests using Juju deployed units"
-	TEST_TIMEOUT=900 ./test.py -v
+	@echo "PostgreSQL integration tests, all versions"
+	trial test
+
+integration_test_91:
+	@echo "PostgreSQL 9.1 integration tests"
+	trial test.PG91Tests
+
+integration_test_92:
+	@echo "PostgreSQL 9.2 integration tests"
+	trial test.PG92Tests
+
+integration_test_93:
+	@echo "PostgreSQL 9.3 integration tests"
+	trial test.PG93Tests
 
 lint:
-	@flake8 --exclude hooks/charmhelpers hooks # requires python-flakes8
+	@echo "Lint check (flake8)"
+	@flake8 -v \
+	    --exclude hooks/charmhelpers,hooks/_trial_temp \
+	    hooks testing tests test.py

=== modified file 'README.md'
--- README.md	2014-02-07 12:21:03 +0000
+++ README.md	2014-02-07 12:21:04 +0000
@@ -62,9 +62,12 @@
 To setup a client using a PostgreSQL database, in this case a vanilla Django
 installation listening on port 8080::
 
-    juju deploy postgresql juju deploy python-django juju deploy gunicorn juju
-add-relation python-django postgresql:db juju add-relation python-django
-gunicorn juju expose python-django
+    juju deploy postgresql
+    juju deploy python-django
+    juju deploy gunicorn
+    juju add-relation python-django postgresql:db
+    juju add-relation python-django gunicorn
+    juju expose python-django
 
 
 ## Known Limitations and Issues

=== modified file 'config.yaml'
--- config.yaml	2014-01-22 07:09:38 +0000
+++ config.yaml	2014-02-07 12:21:04 +0000
@@ -35,17 +35,20 @@
     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.
+        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: ""
+    default: null
     type: string
-    description: Version of PostgreSQL that we want to install
+    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.
   cluster_name:
     default: "main"
     type: string
@@ -55,9 +58,9 @@
     type: string
     description: IP to listen on
   listen_port:
-    default: 5432
+    default: null
     type: int
-    description: Port to listen on
+    description: Port to listen on. Default is automatically assigned.
   max_connections:
     default: 100
     type: int
@@ -120,7 +123,9 @@
   autovacuum:
     default: True
     type: boolean
-    description: Autovacuum should almost always be running.
+    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
@@ -329,13 +334,31 @@
             juju-postgresql-0
         If you're running multiple environments with the same services in them
         this allows you to differentiate between them.
+  pgdg:
+    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
+        provided by the main Ubuntu archive.
+    type: boolean
+    default: false
+  install_sources:
+    description: |
+        List of extra package sources, per charm-helpers standard.
+        YAML format.
+    type: string
+    default: ""
+  install_keys:
+    description: |
+        List of signing keys for install_sources package sources, per
+        charmhelpers standard. YAML format.
+    type: string
+    default: ""
   extra_archives:
     default: ""
     type: string
     description: |
-        Extra archives to add, space separated. Supports ppa:, http:, cloud:
-        URIs, as well as other schemes and keywords supported by
-        charmhelpers.fetch.add_source() such as "proposed".
+        DEPRECATED & IGNORED. Use install_sources and install_keys. 
   advisory_lock_restart_key:
     default: 765
     type: int

=== modified file 'hooks/charmhelpers/fetch/__init__.py'
--- hooks/charmhelpers/fetch/__init__.py	2013-09-24 11:09:35 +0000
+++ hooks/charmhelpers/fetch/__init__.py	2014-02-07 12:21:04 +0000
@@ -117,8 +117,8 @@
 
     Note that 'null' (a.k.a. None) should not be quoted.
     """
-    sources = safe_load(config(sources_var))
-    keys = safe_load(config(keys_var))
+    sources = safe_load(config(sources_var)) or []
+    keys = safe_load(config(keys_var)) or []
     if isinstance(sources, basestring) and isinstance(keys, basestring):
         add_source(sources, keys)
     else:

=== modified file 'hooks/hooks.py'
--- hooks/hooks.py	2014-02-07 12:21:03 +0000
+++ hooks/hooks.py	2014-02-07 12:21:04 +0000
@@ -60,24 +60,22 @@
     package candidate, saving it in local_state for later.
     '''
     config_data = hookenv.config()
-    if config_data['version']:
+    if 'pg_version' in local_state:
+        version = local_state['pg_version']
+    elif 'version' in config_data:
         version = config_data['version']
-    elif 'pg_version' in local_state:
-        version = local_state['pg_version']
     else:
         log("map version from distro release ...")
-        distro_release = run("lsb_release -sc")
-        distro_release = distro_release.rstrip()
         version_map = {'precise': '9.1',
                        'trusty': '9.3'}
-        version = version_map.get(distro_release)
+        version = version_map.get(distro_codename())
         if not version:
-            log("No PG version map for distro_release={}, "
-                "you'll need to explicitly set it".format(distro_release),
+            log("No PG version map for distro_codename={}, "
+                "you'll need to explicitly set it".format(distro_codename()),
                 CRITICAL)
             sys.exit(1)
-        log("version={} from distro_release='{}'".format(
-            version, distro_release))
+        log("version={} from distro_codename='{}'".format(
+            version, distro_codename()))
         # save it for later
         local_state.setdefault('pg_version', version)
         local_state.save()
@@ -86,6 +84,11 @@
     return version
 
 
+def distro_codename():
+    """Return the distro release code name, eg. 'precise' or 'trusty'."""
+    return host.lsb_release()['DISTRIB_CODENAME']
+
+
 class State(dict):
     """Encapsulate state common to the unit for republishing to relations."""
     def __init__(self, state_file):
@@ -128,6 +131,7 @@
         replication_state = dict(client_state)
 
         add(replication_state, 'replication_password')
+        add(replication_state, 'port')
         add(replication_state, 'wal_received_offset')
         add(replication_state, 'following')
         add(replication_state, 'client_relations')
@@ -242,73 +246,101 @@
         startup_file, contents, 'postgres', 'postgres', perms=0o644)
 
 
-def run(command, exit_on_error=True):
+def run(command, exit_on_error=True, quiet=False):
     '''Run a command and return the output.'''
-    try:
-        log(command, DEBUG)
-        return subprocess.check_output(
-            command, stderr=subprocess.STDOUT, shell=True)
-    except subprocess.CalledProcessError, e:
-        log("status=%d, output=%s" % (e.returncode, e.output), ERROR)
-        if exit_on_error:
-            sys.exit(e.returncode)
-        else:
-            raise
+    log("Running {!r}".format(command), DEBUG)
+    p = subprocess.Popen(
+        command, stdin=subprocess.PIPE, stdout=subprocess.PIPE,
+        shell=isinstance(command, basestring))
+    p.stdin.close()
+    lines = []
+    for line in p.stdout:
+        if line:
+            # LP:1274460 & LP:1259490 mean juju-log is no where near as
+            # useful as we would like, so just shove a copy of the
+            # output to stdout for logging.
+            # log("> {}".format(line), DEBUG)
+            if not quiet:
+                print line
+            lines.append(line)
+        elif p.poll() is not None:
+            break
+
+    p.wait()
+
+    if p.returncode == 0:
+        return '\n'.join(lines)
+
+    if p.returncode != 0 and exit_on_error:
+        log("ERROR: {}".format(p.returncode), ERROR)
+        sys.exit(p.returncode)
+
+    raise subprocess.CalledProcessError(
+        p.returncode, command, '\n'.join(lines))
 
 
 def postgresql_is_running():
     '''Return true if PostgreSQL is running.'''
-    # init script always return true (9.1), add extra check to make it useful
-    status, output = commands.getstatusoutput("invoke-rc.d postgresql status")
-    if status != 0:
-        return False
-    # e.g. output: "Running clusters: 9.1/main"
-    vc = "%s/%s" % (pg_version(), hookenv.config("cluster_name"))
-    return vc in output.decode('utf8').split()
+    for version, name, _, status in lsclusters(slice(4)):
+        if (version, name) == (pg_version(), hookenv.config('cluster_name')):
+            if 'online' in status.split(','):
+                log('PostgreSQL is running', DEBUG)
+                return True
+            else:
+                log('PostgreSQL is not running', DEBUG)
+                return False
+    assert False, 'Cluster {} {} not found'.format(
+        pg_version(), hookenv.config('cluster_name'))
 
 
 def postgresql_stop():
     '''Shutdown PostgreSQL.'''
-    success = host.service_stop('postgresql')
-    return not (success and postgresql_is_running())
+    if postgresql_is_running():
+        run([
+            'pg_ctlcluster', '--force',
+            pg_version(), hookenv.config('cluster_name'), 'stop'])
+        log('PostgreSQL shut down')
 
 
 def postgresql_start():
     '''Start PostgreSQL if it is not already running.'''
-    success = host.service_start('postgresql')
-    return success and postgresql_is_running()
+    if not postgresql_is_running():
+        run([
+            'pg_ctlcluster', pg_version(),
+            hookenv.config('cluster_name'), 'start'])
+        log('PostgreSQL started')
 
 
 def postgresql_restart():
     '''Restart PostgreSQL, or start it if it is not already running.'''
     if postgresql_is_running():
         with restart_lock(hookenv.local_unit(), True):
-            # 'service postgresql restart' fails; it only does a reload.
-            # success = host.service_restart('postgresql')
-            try:
-                run('pg_ctlcluster -force {} {} '
-                    'restart'.format(pg_version(),
-                                     hookenv.config('cluster_name')))
-                success = True
-            except subprocess.CalledProcessError:
-                success = False
+            run([
+                'pg_ctlcluster', '--force',
+                pg_version(), hookenv.config('cluster_name'), 'restart'])
+            log('PostgreSQL restarted')
     else:
-        success = host.service_start('postgresql')
+        postgresql_start()
+
+    assert postgresql_is_running()
 
     # Store a copy of our known live configuration so
     # postgresql_reload_or_restart() can make good choices.
-    if success and 'saved_config' in local_state:
+    if 'saved_config' in local_state:
         local_state['live_config'] = local_state['saved_config']
         local_state.save()
 
-    return success and postgresql_is_running()
-
 
 def postgresql_reload():
     '''Make PostgreSQL reload its configuration.'''
     # reload returns a reliable exit status
-    status, output = commands.getstatusoutput("invoke-rc.d postgresql reload")
-    return (status == 0)
+    if postgresql_is_running():
+        # I'm using the PostgreSQL function to avoid as much indirection
+        # as possible.
+        success = run_select_as_postgres('SELECT pg_reload_conf()')[1][0][0]
+        assert success, 'Failed to reload PostgreSQL configuration'
+        log('PostgreSQL configuration reloaded')
+    return postgresql_start()
 
 
 def requires_restart():
@@ -340,38 +372,38 @@
                 # A setting has changed that requires PostgreSQL to be
                 # restarted before it will take effect.
                 restart = True
+                log('{} changed from {} to {}. Restart required.'.format(
+                    name, live_value, new_value), DEBUG)
     return restart
 
 
 def postgresql_reload_or_restart():
     """Reload PostgreSQL configuration, restarting if necessary."""
     if requires_restart():
-        log("Configuration change requires PostgreSQL restart. Restarting.",
-            WARNING)
-        success = postgresql_restart()
-        if not success or requires_restart():
-            log("Configuration changes failed to apply", WARNING)
-            success = False
+        log("Configuration change requires PostgreSQL restart", WARNING)
+        postgresql_restart()
+        assert not requires_restart(), "Configuration changes failed to apply"
     else:
-        success = host.service_reload('postgresql')
-
-    if success:
-        local_state['saved_config'] = local_state['live_config']
-        local_state.save()
-
-    return success
-
-
-def get_service_port(config_file):
+        postgresql_reload()
+
+    local_state['saved_config'] = local_state['live_config']
+    local_state.save()
+
+
+def get_service_port():
     '''Return the port PostgreSQL is listening on.'''
-    if not os.path.exists(config_file):
-        return None
-    postgresql_config = open(config_file, 'r').read()
-    port = re.search("port.*=(.*)", postgresql_config).group(1).strip()
-    try:
-        return int(port)
-    except (ValueError, TypeError):
-        return None
+    for version, name, port in lsclusters(slice(3)):
+        if (version, name) == (pg_version(), hookenv.config('cluster_name')):
+            return int(port)
+
+    assert False, 'No port found for {!r} {!r}'.format(
+        pg_version(), hookenv.config['cluster_name'])
+
+
+def lsclusters(s=slice(0, -1)):
+    for line in run('pg_lsclusters', quiet=True).splitlines()[1:]:
+        if line:
+            yield line.split()[s]
 
 
 def _get_system_ram():
@@ -388,6 +420,8 @@
 def create_postgresql_config(config_file):
     '''Create the postgresql.conf file'''
     config_data = hookenv.config()
+    if not config_data.get('listen_port', None):
+        config_data['listen_port'] = get_service_port()
     if config_data["performance_tuning"] == "auto":
         # Taken from:
         # http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
@@ -439,7 +473,7 @@
     # Return it as pg_config
     charm_dir = hookenv.charm_dir()
     template_file = "{}/templates/postgresql.conf.tmpl".format(charm_dir)
-    if not config_data['version']:
+    if not config_data.get('version', None):
         config_data['version'] = pg_version()
     pg_config = Template(
         open(template_file).read()).render(config_data)
@@ -615,7 +649,7 @@
     host.write_file(output_file, crontab_template, perms=0600)
 
 
-def create_recovery_conf(master_host, restart_on_change=False):
+def create_recovery_conf(master_host, master_port, restart_on_change=False):
     version = pg_version()
     cluster_name = hookenv.config('cluster_name')
     postgresql_cluster_dir = os.path.join(
@@ -632,6 +666,7 @@
     template_file = "{}/templates/recovery.conf.tmpl".format(charm_dir)
     recovery_conf = Template(open(template_file).read()).render({
         'host': master_host,
+        'port': master_port,
         'password': local_state['replication_password'],
         'streaming_replication': streaming_replication})
     log(recovery_conf, DEBUG)
@@ -657,17 +692,16 @@
         return(None)
 
 
-#------------------------------------------------------------------------------
-# update_service_ports:  Convenience function that evaluate the old and new
-#                        service ports to decide which ports need to be
-#                        opened and which to close
-#------------------------------------------------------------------------------
-def update_service_port(old_service_port=None, new_service_port=None):
-    if old_service_port is None or new_service_port is None:
-        return(None)
-    if new_service_port != old_service_port:
-        hookenv.close_port(old_service_port)
-        hookenv.open_port(new_service_port)
+def update_service_port():
+    old_port = local_state.get('listen_port', None)
+    new_port = get_service_port()
+    if old_port != new_port:
+        if new_port:
+            hookenv.open_port(new_port)
+        if old_port:
+            hookenv.close_port(old_port)
+        local_state['listen_port'] = new_port
+        local_state.save()
 
 
 def create_ssl_cert(cluster_dir):
@@ -702,12 +736,15 @@
 
 
 def db_cursor(autocommit=False, db='postgres', user='postgres',
-              host=None, timeout=30):
+              host=None, port=None, timeout=30):
     import psycopg2
+    if port is None:
+        port = get_service_port()
     if host:
-        conn_str = "dbname={} host={} user={}".format(db, host, user)
+        conn_str = "dbname={} host={} port={} user={}".format(
+            db, host, port, user)
     else:
-        conn_str = "dbname={} user={}".format(db, user)
+        conn_str = "dbname={} port={} user={}".format(db, port, user)
     # There are often race conditions in opening database connections,
     # such as a reload having just happened to change pg_hba.conf
     # settings or a hot standby being restarted and needing to catch up
@@ -750,6 +787,47 @@
     return (cur.rowcount, results)
 
 
+def validate_config():
+    """
+    Sanity check charm configuration, aborting the script if
+    we have bogus config values or config changes the charm does not yet
+    (or cannot) support.
+    """
+    valid = True
+    config_data = hookenv.config()
+
+    version = config_data.get('version', None)
+    if version:
+        if version not in ('9.1', '9.2', '9.3'):
+            valid = False
+            log("Invalid or unsupported version {!r} requested".format(
+                version), CRITICAL)
+
+    if config_data['cluster_name'] != 'main':
+        valid = False
+        log("Cluster names other than 'main' do not work per LP:1271835",
+            CRITICAL)
+
+    if config_data['listen_ip'] != '*':
+        valid = False
+        log("listen_ip values other than '*' do not work per LP:1271837",
+            CRITICAL)
+
+    unchangeable_config = [
+        'locale', 'encoding', 'version', 'cluster_name', 'pgdg']
+
+    for name in unchangeable_config:
+        if (name in local_state
+                and local_state[name] != config_data.get(name, None)):
+            valid = False
+            log("Cannot change {!r} setting after install.".format(name))
+        local_state[name] = config_data.get(name, None)
+    local_state.save()
+
+    if not valid:
+        sys.exit(99)
+
+
 #------------------------------------------------------------------------------
 # Core logic for permanent storage changes:
 # NOTE the only 2 "True" return points:
@@ -869,6 +947,7 @@
 
 @hooks.hook()
 def config_changed(force_restart=False):
+    validate_config()
     config_data = hookenv.config()
     update_repos_and_packages()
 
@@ -908,19 +987,16 @@
     postgresql_hba = os.path.join(postgresql_config_dir, "pg_hba.conf")
     postgresql_ident = os.path.join(postgresql_config_dir, "pg_ident.conf")
 
-    current_service_port = get_service_port(postgresql_config)
     create_postgresql_config(postgresql_config)
+    create_postgresql_ident(postgresql_ident)  # Do this before pg_hba.conf.
     generate_postgresql_hba(postgresql_hba)
-    create_postgresql_ident(postgresql_ident)
     create_ssl_cert(os.path.join(
         postgresql_data_dir, pg_version(), config_data['cluster_name']))
-
-    updated_service_port = config_data["listen_port"]
-    update_service_port(current_service_port, updated_service_port)
+    update_service_port()
     update_nrpe_checks()
     if force_restart:
-        return postgresql_restart()
-    return postgresql_reload_or_restart()
+        postgresql_restart()
+    postgresql_reload_or_restart()
 
 
 @hooks.hook()
@@ -930,6 +1006,8 @@
             if os.path.isfile(f) and os.access(f, os.X_OK):
                 subprocess.check_call(['sh', '-c', f])
 
+    validate_config()
+
     config_data = hookenv.config()
     update_repos_and_packages()
     if not 'state' in local_state:
@@ -938,14 +1016,35 @@
         # any non-idempotent setup. We should probably fix this; it
         # seems rather fragile.
         local_state.setdefault('state', 'standalone')
-        local_state.publish()
 
         # Drop the cluster created when the postgresql package was
         # installed, and rebuild it with the requested locale and encoding.
         version = pg_version()
-        run("pg_dropcluster --stop {} main".format(version))
-        run("pg_createcluster --locale='{}' --encoding='{}' {} main".format(
-            config_data['locale'], config_data['encoding'], version))
+        for ver, name in lsclusters(slice(2)):
+            if version == ver and name == 'main':
+                run("pg_dropcluster --stop {} main".format(version))
+        listen_port = config_data.get('listen_port', None)
+        if listen_port:
+            port_opt = "--port={}".format(config_data['listen_port'])
+        else:
+            port_opt = ''
+        with switch_cwd('/tmp'):
+            create_cmd = [
+                "pg_createcluster",
+                "--locale", config_data['locale'],
+                "-e", config_data['encoding']]
+            if listen_port:
+                create_cmd.extend(["-p", str(config_data['listen_port'])])
+            create_cmd.append(pg_version())
+            create_cmd.append(config_data['cluster_name'])
+            run(create_cmd)
+        assert (
+            not port_opt
+            or get_service_port() == config_data['listen_port']), (
+            'allocated port {!r} != {!r}'.format(
+                get_service_port(), config_data['listen_port']))
+        local_state['port'] = get_service_port()
+        local_state.publish()
 
     postgresql_backups_dir = (
         config_data['backup_dir'].strip() or
@@ -972,7 +1071,7 @@
         '{}/pg_backup_job'.format(postgresql_scripts_dir),
         backup_job, perms=0755)
     install_postgresql_crontab(postgresql_crontab)
-    hookenv.open_port(5432)
+    hookenv.open_port(get_service_port())
 
     # Ensure at least minimal access granted for hooks to run.
     # Reload because we are using the default cluster setup and started
@@ -990,16 +1089,14 @@
 
 @hooks.hook()
 def start():
-    if not postgresql_reload_or_restart():
-        raise SystemExit(1)
+    postgresql_reload_or_restart()
 
 
 @hooks.hook()
 def stop():
     if postgresql_is_running():
         with restart_lock(hookenv.local_unit(), True):
-            if not postgresql_stop():
-                raise SystemExit(1)
+            postgresql_stop()
 
 
 def quote_identifier(identifier):
@@ -1262,7 +1359,7 @@
     schema_password = create_user(schema_user)
     ensure_database(user, schema_user, database)
     host = hookenv.unit_private_ip()
-    port = hookenv.config('listen_port')
+    port = get_service_port()
     state = local_state['state']  # master, hot standby, standalone
 
     # Publish connection details.
@@ -1301,7 +1398,7 @@
 
     password = create_user(user, admin=True)
     host = hookenv.unit_private_ip()
-    port = hookenv.config('listen_port')
+    port = get_service_port()
     state = local_state['state']  # master, hot standby, standalone
 
     # Publish connection details.
@@ -1386,18 +1483,62 @@
 
 
 def update_repos_and_packages():
-    extra_repos = hookenv.config('extra_archives')
-    extra_repos_added = local_state.setdefault('extra_repos_added', set())
-    if extra_repos:
-        repos_added = False
-        for repo in extra_repos.split():
-            if repo not in extra_repos_added:
-                fetch.add_source(repo)
-                extra_repos_added.add(repo)
-                repos_added = True
-        if repos_added:
-            fetch.apt_update(fatal=True)
-            local_state.save()
+    need_upgrade = False
+
+    # Add the PGDG APT repository if it is enabled. Setting this boolean
+    # is simpler than requiring the magic URL and key be added to
+    # install_sources and install_keys. In addition, per Bug #1271148,
+    # install_keys is likely a security hole for this sort of remote
+    # archive. Instead, we keep a copy of the signing key in the charm
+    # and can add it securely.
+    pgdg_list = '/etc/apt/sources.list.d/pgdg_{}.list'.format(
+        sanitize(hookenv.local_unit()))
+    pgdg_key = 'ACCC4CF8'
+
+    if hookenv.config('pgdg'):
+        if not os.path.exists(pgdg_list):
+            # We need to upgrade, as if we have Ubuntu main packages
+            # installed they may be incompatible with the PGDG ones.
+            # This is unlikely to ever happen outside of the test suite,
+            # and never if you don't reuse machines.
+            need_upgrade = True
+            run("apt-key add lib/{}.asc".format(pgdg_key))
+            open(pgdg_list, 'w').write('deb {} {}-pgdg main'.format(
+                'http://apt.postgresql.org/pub/repos/apt/', distro_codename()))
+    elif os.path.exists(pgdg_list):
+        log(
+            "PGDG apt source not requested, but already in place in this "
+            "container", WARNING)
+        # We can't just remove a source, as we may have packages
+        # installed that conflict with ones from the other configured
+        # sources. In particular, if we have postgresql-common installed
+        # from the PGDG Apt source, PostgreSQL packages from Ubuntu main
+        # will fail to install.
+        # os.unlink(pgdg_list)
+
+    # Try to optimize our calls to fetch.configure_sources(), as it
+    # cannot do this itself due to lack of state.
+    if (need_upgrade
+        or local_state.get('install_sources', None)
+            != hookenv.config('install_sources')
+        or local_state.get('install_keys', None)
+            != hookenv.config('install_keys')):
+        # Support the standard mechanism implemented by charm-helpers. Pulls
+        # from the default 'install_sources' and 'install_keys' config
+        # options. This also does 'apt-get update', pulling in the PGDG data
+        # if we just configured it.
+        fetch.configure_sources(True)
+        local_state['install_sources'] = hookenv.config('install_sources')
+        local_state['install_keys'] = hookenv.config('install_keys')
+        local_state.save()
+
+    # Ensure that the desired database locale is possible.
+    if hookenv.config('locale') != 'C':
+        run(["locale-gen", "{}.{}".format(
+            hookenv.config('locale'), hookenv.config('encoding'))])
+
+    if need_upgrade:
+        run("apt-get -y upgrade")
 
     version = pg_version()
     # It might have been better for debversion and plpython to only get
@@ -1405,11 +1546,14 @@
     # but they predate this feature.
     packages = ["python-psutil",  # to obtain system RAM from python
                 "libc-bin",       # for getconf
-                "postgresql-%s" % version,
-                "postgresql-contrib-%s" % version,
-                "postgresql-plpython-%s" % version,
-                "postgresql-%s-debversion" % version,
+                "postgresql-{}".format(version),
+                "postgresql-contrib-{}".format(version),
+                "postgresql-plpython-{}".format(version),
                 "python-jinja2", "syslinux", "python-psycopg2"]
+    # PGDG currently doesn't have debversion for 9.3. Put this back when
+    # it does.
+    if not (hookenv.config('pgdg') and version == '9.3'):
+        "postgresql-{}-debversion".format(version)
     packages.extend((hookenv.config('extra-packages') or '').split())
     packages = fetch.filter_installed_packages(packages)
     fetch.apt_install(packages, fatal=True)
@@ -1472,7 +1616,8 @@
     '''Connect the database as a streaming replica of the master.'''
     master_relation = hookenv.relation_get(unit=master)
     create_recovery_conf(
-        master_relation['private-address'], restart_on_change=True)
+        master_relation['private-address'],
+        master_relation['port'], restart_on_change=True)
 
 
 def elected_master():
@@ -1618,6 +1763,7 @@
             local_state['replication_password'] = replication_password
             local_state['client_relations'] = ' '.join(
                 hookenv.relation_ids('db') + hookenv.relation_ids('db-admin'))
+            local_state.publish()
 
         else:
             log("I am master and remain master")
@@ -1635,8 +1781,10 @@
                 master))
 
             master_ip = hookenv.relation_get('private-address', master)
+            master_port = hookenv.relation_get('port', master)
+            assert master_port is not None, 'No master port set'
 
-            clone_database(master, master_ip)
+            clone_database(master, master_ip, master_port)
 
             local_state['state'] = 'hot standby'
             local_state['following'] = master
@@ -1711,7 +1859,7 @@
 
         # Override unit specific connection details
         connection_settings['host'] = hookenv.unit_private_ip()
-        connection_settings['port'] = hookenv.config('listen_port')
+        connection_settings['port'] = get_service_port()
         connection_settings['state'] = local_state['state']
 
         # Block until users and database has replicated, so we know the
@@ -1833,9 +1981,10 @@
                 cur = db_cursor(autocommit=True)
             else:
                 host = hookenv.relation_get('private-address', unit)
+                port = hookenv.relation_get('port', unit)
                 cur = db_cursor(
-                    autocommit=True, db='postgres',
-                    user='juju_replication', host=host)
+                    autocommit=True, db='postgres', user='juju_replication',
+                    host=host, port=port)
             cur.execute(q)
             break
         except psycopg2.Error:
@@ -1853,7 +2002,7 @@
             pass
 
 
-def clone_database(master_unit, master_host):
+def clone_database(master_unit, master_host, master_port):
     with restart_lock(master_unit, False):
         postgresql_stop()
         log("Cloning master {}".format(master_unit))
@@ -1868,7 +2017,8 @@
             'sudo', '-E',  # -E needed to locate pgpass file.
             '-u', 'postgres', 'pg_basebackup', '-D', postgresql_cluster_dir,
             '--xlog', '--checkpoint=fast', '--no-password',
-            '-h', master_host, '-p', '5432', '--username=juju_replication']
+            '-h', master_host, '-p', master_port,
+            '--username=juju_replication']
         log(' '.join(cmd), DEBUG)
 
         if os.path.isdir(postgresql_cluster_dir):
@@ -1883,7 +2033,7 @@
             log(output, DEBUG)
             # Debian by default expects SSL certificates in the datadir.
             create_ssl_cert(postgresql_cluster_dir)
-            create_recovery_conf(master_host)
+            create_recovery_conf(master_host, master_port)
         except subprocess.CalledProcessError as x:
             # We failed, and this cluster is broken. Rebuild a
             # working cluster so start/stop etc. works and we
@@ -1955,9 +2105,10 @@
     return int(logid, 16) * 16 * 1024 * 1024 * 255 + int(offset, 16)
 
 
-def wait_for_db(timeout=120, db='postgres', user='postgres', host=None):
+def wait_for_db(
+        timeout=120, db='postgres', user='postgres', host=None, port=None):
     '''Wait until the db is fully up.'''
-    db_cursor(db=db, user=user, host=host, timeout=timeout)
+    db_cursor(db=db, user=user, host=host, port=port, timeout=timeout)
 
 
 def unit_sorted(units):
@@ -2016,7 +2167,7 @@
         nrpe_check_config.write("# check pgsql\n")
         nrpe_check_config.write(
             "command[check_pgsql]=/usr/lib/nagios/plugins/check_pgsql -P {}"
-            .format(config_data['listen_port']))
+            .format(get_service_port()))
     # pgsql backups
     nrpe_check_file = '/etc/nagios/nrpe.d/check_pgsql_backups.cfg'
     backup_log = "{}/backups.log".format(postgresql_logs_dir)

=== added directory 'lib'
=== added file 'lib/ACCC4CF8.asc'
--- lib/ACCC4CF8.asc	1970-01-01 00:00:00 +0000
+++ lib/ACCC4CF8.asc	2014-02-07 12:21:04 +0000
@@ -0,0 +1,45 @@
+This is the signing key for the PostgreSQL Global Development Group
+APT repository. See https://wiki.postgresql.org/wiki/Apt for details.
+
+-----BEGIN PGP PUBLIC KEY BLOCK-----
+Version: GnuPG v1.4.12 (GNU/Linux)
+
+mQINBE6XR8IBEACVdDKT2HEH1IyHzXkb4nIWAY7echjRxo7MTcj4vbXAyBKOfjja
+UrBEJWHN6fjKJXOYWXHLIYg0hOGeW9qcSiaa1/rYIbOzjfGfhE4x0Y+NJHS1db0V
+G6GUj3qXaeyqIJGS2z7m0Thy4Lgr/LpZlZ78Nf1fliSzBlMo1sV7PpP/7zUO+aA4
+bKa8Rio3weMXQOZgclzgeSdqtwKnyKTQdXY5MkH1QXyFIk1nTfWwyqpJjHlgtwMi
+c2cxjqG5nnV9rIYlTTjYG6RBglq0SmzF/raBnF4Lwjxq4qRqvRllBXdFu5+2pMfC
+IZ10HPRdqDCTN60DUix+BTzBUT30NzaLhZbOMT5RvQtvTVgWpeIn20i2NrPWNCUh
+hj490dKDLpK/v+A5/i8zPvN4c6MkDHi1FZfaoz3863dylUBR3Ip26oM0hHXf4/2U
+A/oA4pCl2W0hc4aNtozjKHkVjRx5Q8/hVYu+39csFWxo6YSB/KgIEw+0W8DiTII3
+RQj/OlD68ZDmGLyQPiJvaEtY9fDrcSpI0Esm0i4sjkNbuuh0Cvwwwqo5EF1zfkVj
+Tqz2REYQGMJGc5LUbIpk5sMHo1HWV038TWxlDRwtOdzw08zQA6BeWe9FOokRPeR2
+AqhyaJJwOZJodKZ76S+LDwFkTLzEKnYPCzkoRwLrEdNt1M7wQBThnC5z6wARAQAB
+tBxQb3N0Z3JlU1FMIERlYmlhbiBSZXBvc2l0b3J5iQI9BBMBCAAnAhsDBQsJCAcD
+BRUKCQgLBRYCAwEAAh4BAheABQJRKm2VBQkINsBBAAoJEH/MfUaszEz4RTEP/1sQ
+HyjHaUiAPaCAv8jw/3SaWP/g8qLjpY6ROjLnDMvwKwRAoxUwcIv4/TWDOMpwJN+C
+JIbjXsXNYvf9OX+UTOvq4iwi4ADrAAw2xw+Jomc6EsYla+hkN2FzGzhpXfZFfUsu
+phjY3FKL+4hXH+R8ucNwIz3yrkfc17MMn8yFNWFzm4omU9/JeeaafwUoLxlULL2z
+Y7H3+QmxCl0u6t8VvlszdEFhemLHzVYRY0Ro/ISrR78CnANNsMIy3i11U5uvdeWV
+CoWV1BXNLzOD4+BIDbMB/Do8PQCWiliSGZi8lvmj/sKbumMFQonMQWOfQswTtqTy
+Q3yhUM1LaxK5PYq13rggi3rA8oq8SYb/KNCQL5pzACji4TRVK0kNpvtxJxe84X8+
+9IB1vhBvF/Ji/xDd/3VDNPY+k1a47cON0S8Qc8DA3mq4hRfcgvuWy7ZxoMY7AfSJ
+Ohleb9+PzRBBn9agYgMxZg1RUWZazQ5KuoJqbxpwOYVFja/stItNS4xsmi0lh2I4
+MNlBEDqnFLUxSvTDc22c3uJlWhzBM/f2jH19uUeqm4jaggob3iJvJmK+Q7Ns3Wcf
+huWwCnc1+58diFAMRUCRBPeFS0qd56QGk1r97B6+3UfLUslCfaaA8IMOFvQSHJwD
+O87xWGyxeRTYIIP9up4xwgje9LB7fMxsSkCDTHOkiEYEEBEIAAYFAk6XSO4ACgkQ
+xa93SlhRC1qmjwCg9U7U+XN7Gc/dhY/eymJqmzUGT/gAn0guvoX75Y+BsZlI6dWn
+qaFU6N8HiQIcBBABCAAGBQJOl0kLAAoJEExaa6sS0qeuBfEP/3AnLrcKx+dFKERX
+o4NBCGWr+i1CnowupKS3rm2xLbmiB969szG5TxnOIvnjECqPz6skK3HkV3jTZaju
+v3sR6M2ItpnrncWuiLnYcCSDp9TEMpCWzTEgtrBlKdVuTNTeRGILeIcvqoZX5w+u
+i0eBvvbeRbHEyUsvOEnYjrqoAjqUJj5FUZtR1+V9fnZp8zDgpOSxx0LomnFdKnhj
+uyXAQlRCA6/roVNR9ruRjxTR5ubteZ9ubTsVYr2/eMYOjQ46LhAgR+3Alblu/WHB
+MR/9F9//RuOa43R5Sjx9TiFCYol+Ozk8XRt3QGweEH51YkSYY3oRbHBb2Fkql6N6
+YFqlLBL7/aiWnNmRDEs/cdpo9HpFsbjOv4RlsSXQfvvfOayHpT5nO1UQFzoyMVpJ
+615zwmQDJT5Qy7uvr2eQYRV9AXt8t/H+xjQsRZCc5YVmeAo91qIzI/tA2gtXik49
+6yeziZbfUvcZzuzjjxFExss4DSAwMgorvBeIbiz2k2qXukbqcTjB2XqAlZasd6Ll
+nLXpQdqDV3McYkP/MvttWh3w+J/woiBcA7yEI5e3YJk97uS6+ssbqLEd0CcdT+qz
++Waw0z/ZIU99Lfh2Qm77OT6vr//Zulw5ovjZVO2boRIcve7S97gQ4KC+G/+QaRS+
+VPZ67j5UMxqtT/Y4+NHcQGgwF/1i
+=Iugu
+-----END PGP PUBLIC KEY BLOCK-----

=== modified file 'templates/pg_hba.conf.tmpl'
--- templates/pg_hba.conf.tmpl	2013-11-19 12:51:43 +0000
+++ templates/pg_hba.conf.tmpl	2014-02-07 12:21:04 +0000
@@ -3,7 +3,7 @@
 #------------------------------------------------------------------------------
 
 # Database administrative login by UNIX sockets
-local   all         postgres                          ident map=superusers
+local   all         root,postgres                     ident map=superusers
 local   replication root,postgres                     ident map=superusers
 local   all         nagios                            md5
 

=== modified file 'templates/pg_ident.conf.tmpl'
--- templates/pg_ident.conf.tmpl	2012-10-05 13:21:43 +0000
+++ templates/pg_ident.conf.tmpl	2014-02-07 12:21:04 +0000
@@ -1,2 +1,3 @@
+# This file is managed by juju
 superusers      root        postgres
 superusers      postgres    postgres

=== modified file 'templates/postgresql.conf.tmpl'
--- templates/postgresql.conf.tmpl	2014-01-17 03:18:52 +0000
+++ templates/postgresql.conf.tmpl	2014-02-07 12:21:04 +0000
@@ -23,6 +23,11 @@
 unix_socket_directory = '/var/run/postgresql'
 {% endif -%}
 
+{% if version >= "9.2" -%}
+ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
+ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
+{% endif -%}
+
 {% if listen_ip != "" -%}
 listen_addresses = '{{listen_ip}}'
 {% endif -%}

=== modified file 'templates/recovery.conf.tmpl'
--- templates/recovery.conf.tmpl	2013-11-28 08:43:35 +0000
+++ templates/recovery.conf.tmpl	2014-02-07 12:21:04 +0000
@@ -4,7 +4,7 @@
 standby_mode = on
 recovery_target_timeline = latest
 {% if streaming_replication %}
-primary_conninfo = 'host={{host}} user=juju_replication password={{password}} requirepeer=postgres'
+primary_conninfo = 'host={{host}} port={{port}} user=juju_replication password={{password}} requirepeer=postgres'
 {% endif %}
 {% if restore_command %}
 restore_command = '{{restore_command}}'

=== modified file 'test.py'
--- test.py	2013-12-23 10:38:13 +0000
+++ test.py	2014-02-07 12:21:04 +0000
@@ -25,13 +25,30 @@
 
 SERIES = 'precise'
 TEST_CHARM = 'local:postgresql'
-PSQL_CHARM = 'local:postgresql-psql'
-
-
-class PostgreSQLCharmTestCase(testtools.TestCase, fixtures.TestWithFixtures):
+PSQL_CHARM = 'cs:postgresql-psql'
+
+
+class PostgreSQLCharmBaseTestCase(object):
+
+    # Override these in subclasses to run these tests multiple times
+    # for different PostgreSQL versions.
+
+    # PostgreSQL version for tests. One of the subclasses leaves the
+    # VERSION as None to test automatic version selection.
+    VERSION = None
+
+    # Use the PGDG Apt archive or not. One of the subclasses sets this
+    # to False to test the Ubuntu main packages. The rest set this to
+    # True to pull packages from the PGDG (only one PostgreSQL version
+    # exists in main).
+    PGDG = None
 
     def setUp(self):
-        super(PostgreSQLCharmTestCase, self).setUp()
+        super(PostgreSQLCharmBaseTestCase, self).setUp()
+
+        # Generate a basic config for all PostgreSQL charm deploys.
+        # Tests may add or change options.
+        self.pg_config = dict(version=self.VERSION, pgdg=self.PGDG)
 
         self.juju = self.useFixture(JujuFixture(
             reuse_machines=True,
@@ -99,7 +116,7 @@
 
     def test_basic(self):
         '''Connect to a a single unit service via the db relationship.'''
-        self.juju.deploy(TEST_CHARM, 'postgresql')
+        self.juju.deploy(TEST_CHARM, 'postgresql', config=self.pg_config)
         self.juju.deploy(PSQL_CHARM, 'psql')
         self.juju.do(['add-relation', 'postgresql:db', 'psql:db'])
         self.juju.wait_until_ready()
@@ -107,11 +124,27 @@
         result = self.sql('SELECT TRUE')
         self.assertEqual(result, [['t']])
 
+    def test_streaming_replication(self):
+        self.juju.deploy(
+            TEST_CHARM, 'postgresql', num_units=2, config=self.pg_config)
+        self.juju.deploy(PSQL_CHARM, 'psql')
+        self.juju.do(['add-relation', 'postgresql:db', 'psql:db'])
+        self.juju.wait_until_ready()
+
+        # Confirm that the slave has successfully opened a streaming
+        # replication connection.
+        num_slaves = self.sql(
+            'SELECT COUNT(*) FROM pg_stat_replication',
+            postgres_unit='master')[0][0]
+
+        self.assertEqual(num_slaves, '1', 'Slave not connected')
+
     def test_basic_admin(self):
         '''Connect to a single unit service via the db-admin relationship.'''
-        self.juju.deploy(TEST_CHARM, 'postgresql')
+        self.juju.deploy(TEST_CHARM, 'postgresql', config=self.pg_config)
         self.juju.deploy(PSQL_CHARM, 'psql')
         self.juju.do(['add-relation', 'postgresql:db-admin', 'psql:db-admin'])
+        self.juju.do(['expose', 'postgresql'])
         self.juju.wait_until_ready()
 
         result = self.sql('SELECT TRUE', dbname='postgres')
@@ -125,7 +158,8 @@
 
     def test_failover(self):
         """Set up a multi-unit service and perform failovers."""
-        self.juju.deploy(TEST_CHARM, 'postgresql', num_units=3)
+        self.juju.deploy(
+            TEST_CHARM, 'postgresql', num_units=3, config=self.pg_config)
         self.juju.deploy(PSQL_CHARM, 'psql')
         self.juju.do(['add-relation', 'postgresql:db', 'psql:db'])
         self.juju.wait_until_ready()
@@ -213,7 +247,8 @@
 
     def test_failover_election(self):
         """Ensure master elected in a failover is the best choice"""
-        self.juju.deploy(TEST_CHARM, 'postgresql', num_units=3)
+        self.juju.deploy(
+            TEST_CHARM, 'postgresql', num_units=3, config=self.pg_config)
         self.juju.deploy(PSQL_CHARM, 'psql')
         self.juju.do(['add-relation', 'postgresql:db-admin', 'psql:db-admin'])
         self.juju.wait_until_ready()
@@ -261,7 +296,15 @@
         self.assertIs(False, self.is_master(standby_unit_1, 'postgres'))
 
     def test_admin_addresses(self):
-        self.juju.deploy(TEST_CHARM, 'postgresql')
+
+        # This test also tests explicit port assignment. We need
+        # a different port for each PostgreSQL version we might be
+        # testing, because clusters from previous tests of different
+        # versions may be hanging around.
+        port = 7400 + int((self.VERSION or '66').replace('.', ''))
+        self.pg_config['listen_port'] = port
+
+        self.juju.deploy(TEST_CHARM, 'postgresql', config=self.pg_config)
         self.juju.deploy(PSQL_CHARM, 'psql')
         self.juju.do(['add-relation', 'postgresql:db-admin', 'psql:db-admin'])
         self.juju.wait_until_ready()
@@ -271,7 +314,7 @@
         unit_ip = self.juju.status['services']['postgresql']['units'][
             unit]['public-address']
         s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
-        s.connect((unit_ip, 5432))
+        s.connect((unit_ip, port))
         my_ip = s.getsockname()[0]
         del s
 
@@ -280,8 +323,9 @@
             "ALTER USER postgres ENCRYPTED PASSWORD 'foo'", dbname='postgres')
 
         # Direct connection string to the unit's database.
-        conn_str = 'dbname=postgres user=postgres password=foo host={}'.format(
-            unit_ip)
+        conn_str = (
+            'dbname=postgres user=postgres password=foo '
+            'host={} port={}'.format(unit_ip, port))
 
         # Direct database connections should fail at the moment.
         self.assertRaises(
@@ -297,7 +341,7 @@
         self.assertEquals(1, cur.fetchone()[0])
 
     def test_explicit_database(self):
-        self.juju.deploy(TEST_CHARM, 'postgresql')
+        self.juju.deploy(TEST_CHARM, 'postgresql', config=self.pg_config)
         self.juju.deploy(PSQL_CHARM, 'psql')
         self.juju.do(['set', 'psql', 'database=explicit'])
         self.juju.do(['add-relation', 'postgresql:db', 'psql:db'])
@@ -306,11 +350,12 @@
         result = self.sql('SELECT current_database()')
         self.assertEqual(result, [['explicit']])
 
-
     def test_roles_granted(self):
-        self.juju.deploy(TEST_CHARM, 'postgresql')
-        self.juju.deploy(PSQL_CHARM, 'psql')
-        self.juju.do(['set', 'psql', 'roles=role_a'])
+        # We use two units to confirm that there is no attempts to
+        # grant roles on the hot standby.
+        self.juju.deploy(
+            TEST_CHARM, 'postgresql', num_units=2, config=self.pg_config)
+        self.juju.deploy(PSQL_CHARM, 'psql', config={'roles': 'role_a'})
         self.juju.do(['add-relation', 'postgresql:db', 'psql:db'])
         self.juju.wait_until_ready()
 
@@ -330,9 +375,11 @@
         self.assertEqual(result, [['t', 't']])
 
     def test_roles_revoked(self):
-        self.juju.deploy(TEST_CHARM, 'postgresql')
-        self.juju.deploy(PSQL_CHARM, 'psql')
-        self.juju.do(['set', 'psql', 'roles=role_a,role_b'])
+        # We use two units to confirm that there is no attempts to
+        # grant roles on the hot standby.
+        self.juju.deploy(
+            TEST_CHARM, 'postgresql', num_units=2, config=self.pg_config)
+        self.juju.deploy(PSQL_CHARM, 'psql', config={'roles': 'role_a,role_b'})
         self.juju.do(['add-relation', 'postgresql:db', 'psql:db'])
         self.juju.wait_until_ready()
 
@@ -366,6 +413,29 @@
         self.assertEqual(result, [['f', 'f', 'f']])
 
 
+class PG91Tests(
+        PostgreSQLCharmBaseTestCase,
+        testtools.TestCase, fixtures.TestWithFixtures):
+    # Test automatic version selection under precise.
+    VERSION = None if SERIES == 'precise' else '9.1'
+    PGDG = False if SERIES == 'precise' else True
+
+
+class PG92Tests(
+        PostgreSQLCharmBaseTestCase,
+        testtools.TestCase, fixtures.TestWithFixtures):
+    VERSION = '9.2'
+    PGDG = True
+
+
+class PG93Tests(
+        PostgreSQLCharmBaseTestCase,
+        testtools.TestCase, fixtures.TestWithFixtures):
+    # Test automatic version selection under trusty.
+    VERSION = None if SERIES == 'trusty' else '9.3'
+    PGDG = False if SERIES == 'trusty' else True
+
+
 def unit_sorted(units):
     """Return a correctly sorted list of unit names."""
     return sorted(

=== modified file 'testing/jujufixture.py'
--- testing/jujufixture.py	2013-12-23 10:38:13 +0000
+++ testing/jujufixture.py	2014-02-07 12:21:04 +0000
@@ -1,9 +1,11 @@
 import json
+import os.path
 import subprocess
 import time
 
 import fixtures
 from testtools.content import text_content
+import yaml
 
 
 __all__ = ['JujuFixture', 'run']
@@ -42,7 +44,7 @@
             return json.loads(out)
         return None
 
-    def deploy(self, charm, name=None, num_units=1):
+    def deploy(self, charm, name=None, num_units=1, config=None):
         # The first time we deploy a local: charm in the test run, it
         # needs to deploy with --update to ensure we are testing the
         # desired revision of the charm. Subsequent deploys we do not
@@ -54,6 +56,14 @@
             cmd = ['deploy', '-u']
             self._deployed_charms.add(charm)
 
+        if config:
+            config_path = os.path.join(
+                self.useFixture(fixtures.TempDir()).path, 'config.yaml')
+            cmd.append('--config={}'.format(config_path))
+            config = yaml.safe_dump({name: config}, default_flow_style=False)
+            open(config_path, 'w').write(config)
+            self.addDetail('pgconfig', text_content(config))
+
         cmd.append(charm)
 
         if name is None:
@@ -93,10 +103,10 @@
         self.status = self.get_result(['status'])
 
         self._free_machines = set(
-            int(k) for k, m in self.status['machines'].items() if
-                k != '0'
-                and m.get('life', None) not in ('dead', 'dying')
-                and m.get('agent-state', 'pending') in ('started', 'ready'))
+            int(k) for k, m in self.status['machines'].items()
+            if k != '0'
+            and m.get('life', None) not in ('dead', 'dying')
+            and m.get('agent-state', 'pending') in ('started', 'ready'))
         for service in self.status.get('services', {}).values():
             for unit in service.get('units', []):
                 if 'machine' in unit:
@@ -201,6 +211,8 @@
         raise
 
     (out, err) = proc.communicate(input)
+    detail_collector.addDetail(
+        'cmd', text_content('{}: {}'.format(proc.returncode, ' '.join(cmd))))
     if out:
         detail_collector.addDetail('stdout', text_content(out))
     if err:

=== added file 'tests/00_setup.test'
--- tests/00_setup.test	1970-01-01 00:00:00 +0000
+++ tests/00_setup.test	2014-02-07 12:21:04 +0000
@@ -0,0 +1,12 @@
+#!/bin/sh
+
+sudo apt-get install -y \
+    python-flake8 \
+    python-fixtures \
+    python-jinja2 \
+    python-mocker \
+    python-psycopg2 \
+    python-testtools \
+    python-twisted-core \
+    python-yaml
+

=== added file 'tests/01_lint.test'
--- tests/01_lint.test	1970-01-01 00:00:00 +0000
+++ tests/01_lint.test	2014-02-07 12:21:04 +0000
@@ -0,0 +1,3 @@
+#!/bin/bash
+
+make -C $(dirname $0)/.. lint

=== renamed file 'tests/01_unittests.test' => 'tests/02_unit_test.test'
--- tests/01_unittests.test	2014-01-15 10:18:42 +0000
+++ tests/02_unit_test.test	2014-02-07 12:21:04 +0000
@@ -1,3 +1,3 @@
 #!/bin/bash
 
-cd $(dirname $0)/../hooks && trial test_hooks.py
+make -C $(dirname $0)/.. unit_test

=== renamed symlink 'tests/10_juju_integration.test' => 'tests/91_integration_test_91.test' (properties changed: -x to +x)
=== target was u'../test.py'
--- tests/10_juju_integration.test	1970-01-01 00:00:00 +0000
+++ tests/91_integration_test_91.test	2014-02-07 12:21:04 +0000
@@ -0,0 +1,3 @@
+#!/bin/bash
+
+make -C $(dirname $0)/.. integration_test_91

=== added file 'tests/92_integration_test_92.test'
--- tests/92_integration_test_92.test	1970-01-01 00:00:00 +0000
+++ tests/92_integration_test_92.test	2014-02-07 12:21:04 +0000
@@ -0,0 +1,3 @@
+#!/bin/bash
+
+make -C $(dirname $0)/.. integration_test_92

=== added file 'tests/93_integration_test_93.test'
--- tests/93_integration_test_93.test	1970-01-01 00:00:00 +0000
+++ tests/93_integration_test_93.test	2014-02-07 12:21:04 +0000
@@ -0,0 +1,3 @@
+#!/bin/bash
+
+make -C $(dirname $0)/.. integration_test_93