Merge lp:~stub/charms/precise/postgresql/wal-e into lp:charms/postgresql

[Stuart Bishop]

WAL-E support.

WAL-E gives us log shipping and point in time backups to OpenStack Swift, Windows Azure Block Storage, and Amazon S3.

This branch adds EXPERIMENTAL support for all three.

It adds tests for Swift log shipping. S3 and WABS have not been tested (not even manually).

There are no tests yet for PITR backup or restore.

[Whit Morriss (whitmo)]

This is an important feature, thanks Stuart!

My main concern is adding some documentation to the README about wal-e and this implementation: basic overview of the feature, how to use the config values, caveats about it's experimental nature.

With that added, I'd suggest it for merging.

-w

review: Needs Information (charm)

[Stuart Bishop (stub)]

Thanks. I have added some documentation for the experimental wal-e support. I have not bothered documenting SwiftWAL, as I expect my wal-e tests will confirm that wal-e is a suitable and more capable replacement.

[Stuart Bishop (stub)]

Not actually merged.

There is a second MP made on request for people who want to review several branches as a big unrelated mess of changes (https://code.launchpad.net/~stub/charms/precise/postgresql/integration/+merge/233666), but this one should be much easier to review.

[Whit Morriss (whitmo)]

thanks Stuart!

-w

On Sun, Sep 21, 2014 at 1:39 AM, Stuart Bishop <email address hidden>
wrote:

> Not actually merged.
>
> There is a second MP made on request for people who want to review several
> branches as a big unrelated mess of changes (
> https://code.launchpad.net/~stub/charms/precise/postgresql/integration/+merge/233666),
> but this one should be much easier to review.
> --
>
> https://code.launchpad.net/~stub/charms/precise/postgresql/wal-e/+merge/225807
> You are reviewing the proposed merge of
> lp:~stub/charms/precise/postgresql/wal-e into lp:charms/postgresql.
>

--
---------------
D. Whit Morriss
Developer, Juju Ecosystem
Canonical USA

[lp:~stub/charms/precise/postgresql/wal-e]

160. By Stuart Bishop on 2014-09-22

Merged bug-1276024-fix-log-shipping into wal-e.

161. By Stuart Bishop on 2014-10-07

Merged bug-1276024-fix-log-shipping into wal-e.

=== modified file 'README.md'
--- README.md	2014-06-27 05:27:12 +0000
+++ README.md	2014-10-08 12:49:24 +0000
@@ -255,6 +255,44 @@
   6. juju add-relation postgresql storage
 
 
+# Point In Time Recovery
+
+The PostgreSQL charm has experimental support for log shipping and point
+in time recovery. This feature uses the wal-e[2] tool, and requires access
+to Amazon S3, Microsoft Azure Block Storage or Swift. This feature is
+flagged as experimental because it has only been tested with Swift, and
+not yet been tested under load. It also may require some API changes,
+particularly on how authentication credentials are accessed when a standard
+emerges. The charm can be configured to perform regular filesystem backups
+and ship WAL files to the object store. Hot standbys will make use of the
+archived WAL files, allowing them to resync after extended netsplits or
+even let you turn off streaming replication entirely.
+
+With a base backup and the WAL archive you can perform point in time
+recovery, but this is still a manual process and the charm does not
+yet help you do it. The simplest approach would be to create a new
+PostgreSQL service containing a single unit, 'juju ssh' in and use
+wal-e to replace the database after shutting it down, create a
+recovery.conf to replay the archived WAL files using wal-e, restart the
+database and wait for it to recover. Once recovered, new hot standby
+units can be added and client services related to the new database
+service.
+
+To enable the experimental wal-e support with Swift, you will need to
+use Ubuntu 14.04 (Trusty), and set the service configuration settings
+similar to the following::
+
+    postgresql:
+        wal_e_storage_uri: swift://mycontainer
+        os_username: my_swift_username
+        os_password: my_swift_password
+        os_auth_url: https://keystone.auth.url.example.com:8080/v2/
+        os_tenant_name: my_tenant_name
+        install_sources: |
+            - ppa:stub/pgcharm
+            - cloud:icehouse
+
+
 # Contact Information
 
 ## PostgreSQL 
@@ -265,3 +303,4 @@
 - [PostgreSQL Mailing List](http://www.postgresql.org/list/)
 
   [1]: https://bugs.launchpad.net/charms/+source/postgresql/+bug/1258485
+  [2]: https://github.com/wal-e/wal-e

=== modified file 'config.yaml'
--- config.yaml	2014-10-08 12:49:24 +0000
+++ config.yaml	2014-10-08 12:49:24 +0000
@@ -389,7 +389,7 @@
   # Swift backups and PITR via SwiftWAL
   swiftwal_container_prefix:
     type: string
-    default: ""
+    default: null
     description: |
       EXPERIMENTAL.
       Swift container prefix for SwiftWAL to use. Must be set if any
@@ -398,7 +398,7 @@
       implemented in juju.
   swiftwal_backup_schedule:
     type: string
-    default: ""
+    default: null
     description: |
       EXPERIMENTAL.
       Cron-formatted schedule for SwiftWAL database backups.
@@ -416,34 +416,83 @@
     description: |
       EXPERIMENTAL.
       Archive WAL files into Swift. If swiftwal_backup_schedule is set,
-      this allows point-in-time recovery and WAL files are removed
+      allows point-in-time recovery and WAL files are removed
       automatically with old backups. If swiftwal_backup_schedule is not set
       then WAL files are never removed. Enabling this option will override
       the archive_mode and archive_command settings.
+  wal_e_storage_uri:
+    type: string
+    default: null
+    description: |
+      EXPERIMENTAL.
+      Specify storage to be used by WAL-E. Every PostgreSQL service must use
+      a unique URI. Backups will be unrecoverable if it is not unique. The
+      URI's scheme must be one of 'swift' (OpenStack Swift), 's3' (Amazon AWS)
+      or 'wabs' (Windows Azure). For example:
+        'swift://some-container/directory/or/whatever'
+        's3://some-bucket/directory/or/whatever'
+        'wabs://some-bucket/directory/or/whatever'
+      Setting the wal_e_storage_uri enables regular WAL-E filesystem level
+      backups (per wal_e_backup_schedule), and log shipping to the configured
+      storage. Point-in-time recovery becomes possible, as is disabling the
+      streaming_replication configuration item and relying solely on
+      log shipping for replication.
+  wal_e_backup_schedule:
+    type: string
+    default: "13 0 * * *"
+    description: |
+      EXPERIMENTAL.
+      Cron-formatted schedule for WAL-E database backups. If
+      wal_e_backup_schedule is unset, WAL files will never be removed from
+      WAL-E storage.
+  wal_e_backup_retention:
+    type: int
+    default: 2
+    description: |
+      EXPERIMENTAL.
+      Number of recent base backups and WAL files to retain.
+      You need enough space for this many backups plus one more, as
+      an old backup will only be removed after a new one has been
+      successfully made to replace it.
   streaming_replication:
     type: boolean
     default: true
     description: |
-      EXPERIMENTAL.
       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.
   os_username:
     type: string
-    default: ""
+    default: null
     description: EXPERIMENTAL. OpenStack Swift username.
   os_password:
     type: string
-    default: ""
+    default: null
     description: EXPERIMENTAL. OpenStack Swift password.
   os_auth_url:
     type: string
-    default: ""
+    default: null
     description: EXPERIMENTAL. OpenStack Swift authentication URL.
   os_tenant_name:
     type: string
-    default: ""
+    default: null
     description: EXPERIMENTAL. OpenStack Swift tenant name.
+  aws_access_key_id:
+    type: string
+    default: null
+    description: EXPERIMENTAL. Amazon AWS access key id.
+  aws_secret_access_key:
+    type: string
+    default: null
+    description: EXPERIMENTAL. Amazon AWS secret access key.
+  wabs_account_name:
+    type: string
+    default: null
+    description: EXPERIMENTAL. Windows Azure account name.
+  wabs_access_key:
+    type: string
+    default: null
+    description: EXPERIMENTAL. Windows Azure access key.
   package_status:
     default: "install"
     type: string

=== modified file 'hooks/hooks.py'
--- hooks/hooks.py	2014-10-08 12:49:24 +0000
+++ hooks/hooks.py	2014-10-08 12:49:24 +0000
@@ -16,6 +16,7 @@
 from tempfile import NamedTemporaryFile
 from textwrap import dedent
 import time
+import urlparse
 
 from charmhelpers import fetch
 from charmhelpers.core import hookenv, host
@@ -407,11 +408,16 @@
 
     # Log shipping to Swift using SwiftWAL. This could be for
     # non-streaming replication, or for PITR.
-    if config_data['swiftwal_log_shipping']:
+    if config_data.get('swiftwal_log_shipping', None):
         config_data['archive_mode'] = True
         config_data['wal_level'] = 'hot_standby'
         config_data['archive_command'] = swiftwal_archive_command()
 
+    if config_data.get('wal_e_storage_uri', None):
+        config_data['archive_mode'] = True
+        config_data['wal_level'] = 'hot_standby'
+        config_data['archive_command'] = wal_e_archive_command()
+
     # Send config data to the template
     # Return it as pg_config
     charm_dir = hookenv.charm_dir()
@@ -610,6 +616,8 @@
     config_data['scripts_dir'] = postgresql_scripts_dir
     config_data['swiftwal_backup_command'] = swiftwal_backup_command()
     config_data['swiftwal_prune_command'] = swiftwal_prune_command()
+    config_data['wal_e_backup_command'] = wal_e_backup_command()
+    config_data['wal_e_prune_command'] = wal_e_prune_command()
 
     charm_dir = hookenv.charm_dir()
     template_file = "{}/templates/postgres.cron.tmpl".format(charm_dir)
@@ -633,12 +641,15 @@
     charm_dir = hookenv.charm_dir()
     streaming_replication = hookenv.config('streaming_replication')
     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,
-        'restore_command': swiftwal_restore_command()})
+    params = dict(
+        host=master_host, port=master_port,
+        password=local_state['replication_password'],
+        streaming_replication=streaming_replication)
+    if hookenv.config('wal_e_storage_uri'):
+        params['restore_command'] = wal_e_restore_command()
+    elif hookenv.config('swiftwal_log_shipping'):
+        params['restore_command'] = swiftwal_restore_command()
+    recovery_conf = Template(open(template_file).read()).render(params)
     log(recovery_conf, DEBUG)
     host.write_file(
         os.path.join(postgresql_cluster_dir, 'recovery.conf'),
@@ -660,6 +671,108 @@
     if os.path.isfile(config_file):
         return(open(config_file).read())
 =======
+def ensure_swift_container(container):
+    from swiftclient import client as swiftclient
+    config = hookenv.config()
+    con = swiftclient.Connection(
+        authurl=config.get('os_auth_url', ''),
+        user=config.get('os_username', ''),
+        key=config.get('os_password', ''),
+        tenant_name=config.get('os_tenant_name', ''),
+        auth_version='2.0',
+        retries=0)
+    try:
+        con.head_container(container)
+    except swiftclient.ClientException:
+        con.put_container(container)
+
+
+def wal_e_envdir():
+    '''The envdir(1) environment location used to drive WAL-E.'''
+    return os.path.join(_get_postgresql_config_dir(), 'wal-e.env')
+
+
+def create_wal_e_envdir():
+    '''Regenerate the envdir(1) environment used to drive WAL-E.'''
+    config = hookenv.config()
+    env = dict(
+        SWIFT_AUTHURL=config.get('os_auth_url', ''),
+        SWIFT_TENANT=config.get('os_tenant_name', ''),
+        SWIFT_USER=config.get('os_username', ''),
+        SWIFT_PASSWORD=config.get('os_password', ''),
+        AWS_ACCESS_KEY_ID=config.get('aws_access_key_id', ''),
+        AWS_SECRET_ACCESS_KEY=config.get('aws_secret_access_key', ''),
+        WABS_ACCOUNT_NAME=config.get('wabs_account_name', ''),
+        WABS_ACCESS_KEY=config.get('wabs_access_key', ''),
+        WALE_SWIFT_PREFIX='',
+        WALE_S3_PREFIX='',
+        WALE_WABS_PREFIX='')
+
+    uri = config.get('wal_e_storage_uri', None)
+
+    if uri:
+        # Until juju provides us with proper leader election, we have a
+        # state where units do not know if they are alone or part of a
+        # cluster. To avoid units stomping on each others WAL and backups,
+        # we use a unique container for each unit when they are not
+        # part of the peer relation. Once they are part of the peer
+        # relation, they share a container.
+        if local_state.get('state', 'standalone') == 'standalone':
+            if not uri.endswith('/'):
+                uri += '/'
+            uri += hookenv.local_unit().split('/')[-1]
+
+        parsed_uri = urlparse.urlparse(uri)
+
+        required_env = []
+        if parsed_uri.scheme == 'swift':
+            env['WALE_SWIFT_PREFIX'] = uri
+            required_env = ['SWIFT_AUTHURL', 'SWIFT_TENANT',
+                            'SWIFT_USER', 'SWIFT_PASSWORD']
+            ensure_swift_container(parsed_uri.netloc)
+        elif parsed_uri.scheme == 's3':
+            env['WALE_S3_PREFIX'] = uri
+            required_env = ['AWS_ACCESS_KEY_ID', 'AWS_SECRET_ACCESS_KEY']
+        elif parsed_uri.scheme == 'wabs':
+            env['WALE_WABS_PREFIX'] = uri
+            required_env = ['WABS_ACCOUNT_NAME', 'WABS_ACCESS_KEY']
+        else:
+            log('Invalid wal_e_storage_uri {}'.format(uri), ERROR)
+
+        for env_key in required_env:
+            if not env[env_key].strip():
+                log('Missing {}'.format(env_key), ERROR)
+
+    # Regenerate the envdir(1) environment recommended by WAL-E.
+    # All possible keys are rewritten to ensure we remove old secrets.
+    host.mkdir(wal_e_envdir(), 'postgres', 'postgres', 0o750)
+    for k, v in env.items():
+        host.write_file(
+            os.path.join(wal_e_envdir(), k), v.strip(),
+            'postgres', 'postgres', 0o640)
+
+
+def wal_e_archive_command():
+    '''Return the archive_command needed in postgresql.conf.'''
+    return 'envdir {} wal-e wal-push %p'.format(wal_e_envdir())
+
+
+def wal_e_restore_command():
+    return 'envdir {} wal-e wal-fetch "%f" "%p"'.format(wal_e_envdir())
+
+
+def wal_e_backup_command():
+    postgresql_cluster_dir = os.path.join(
+        postgresql_data_dir, pg_version(), hookenv.config('cluster_name'))
+    return 'envdir {} wal-e backup-push {}'.format(
+        wal_e_envdir(), postgresql_cluster_dir)
+
+
+def wal_e_prune_command():
+    return 'envdir {} wal-e delete --confirm retain {}'.format(
+        wal_e_envdir(), hookenv.config('wal_e_backup_retention'))
+
+
 def swiftwal_config():
     postgresql_config_dir = _get_postgresql_config_dir()
     return os.path.join(postgresql_config_dir, "swiftwal.conf")
@@ -1000,6 +1113,7 @@
     create_ssl_cert(os.path.join(
         postgresql_data_dir, pg_version(), config_data['cluster_name']))
     create_swiftwal_config()
+    create_wal_e_envdir()
     update_service_port()
     update_nrpe_checks()
 
@@ -1671,6 +1785,9 @@
     if hookenv.config('swiftwal_container_prefix'):
         packages.append('swiftwal')
 
+    if hookenv.config('wal_e_storage_uri'):
+        packages.extend(['wal-e', 'daemontools'])
+
     packages.extend((hookenv.config('extra-packages') or '').split())
     packages = fetch.filter_installed_packages(packages)
     # Set package state for main postgresql package if installed
@@ -1938,6 +2055,7 @@
     # config. This can go away when we have real leader election and can
     # safely share a single container.
     create_swiftwal_config()
+    create_wal_e_envdir()
 
     local_state.publish()
 

=== modified file 'templates/postgres.cron.tmpl'
--- templates/postgres.cron.tmpl	2014-10-08 12:49:24 +0000
+++ templates/postgres.cron.tmpl	2014-10-08 12:49:24 +0000
@@ -9,3 +9,8 @@
 {{swiftwal_backup_schedule}} postgres \
     {{swiftwal_backup_command}} && {{swiftwal_prune_command}}
 {% endif -%}
+
+{% if wal_e_backup_schedule -%}
+{{wal_e_backup_schedule}} postgres \
+    {{wal_e_backup_command}} && {{wal_e_prune_command}}
+{% endif -%}

=== modified file 'test.py'
--- test.py	2014-10-08 12:49:24 +0000
+++ test.py	2014-10-08 12:49:24 +0000
@@ -25,7 +25,7 @@
 from testing.jujufixture import JujuFixture, run
 
 
-SERIES = os.environ.get('SERIES', 'precise').strip()
+SERIES = os.environ.get('SERIES', 'trusty').strip()
 TEST_CHARM = 'local:{}/postgresql'.format(SERIES)
 PSQL_CHARM = 'local:{}/postgresql-psql'.format(SERIES)
 
@@ -43,6 +43,22 @@
     return lambda x: x
 
 
+def skip_if_s3_is_unavailable():
+    os_keys = set(['AWS_ACCESS_KEY_ID', 'AWS_SECRET_ACCESS_KEY'])
+    for os_key in os_keys:
+        if os_key not in os.environ:
+            return unittest.skip('S3 is unavailable')
+    return lambda x: x
+
+
+def skip_if_wabs_is_unavailable():
+    os_keys = set(['WABS_ACCOUNT_NAME', 'WABS_ACCESS_KEY'])
+    for os_key in os_keys:
+        if os_key not in os.environ:
+            return unittest.skip('WABS is unavailable')
+    return lambda x: x
+
+
 class PostgreSQLCharmBaseTestCase(object):
 
     # Override these in subclasses to run these tests multiple times
@@ -429,6 +445,51 @@
                 table_found = True
         self.assertTrue(table_found, "Replication not replicating")
 
+    @skip_if_swift_is_unavailable()
+    def test_wal_e_swift_logshipping(self):
+        os_keys = set(['OS_TENANT_NAME', 'OS_AUTH_URL',
+                       'OS_USERNAME', 'OS_PASSWORD'])
+        container = '_juju_pg_tests'
+        for os_key in os_keys:
+            self.pg_config[os_key.lower()] = os.environ[os_key]
+        self.pg_config['streaming_replication'] = False
+        self.pg_config['wal_e_storage_uri'] = 'swift://{}/{}'.format(
+            container, datetime.utcnow().strftime('%Y%m%dT%H%M%SZ'))
+        self.pg_config['install_sources'] = 'ppa:stub/pgcharm'
+
+        def swift_cleanup():
+            subprocess.call(['swift', 'delete', container],
+                            stdout=open(os.devnull, 'wb'),
+                            stderr=subprocess.STDOUT)
+        self.addCleanup(swift_cleanup)
+
+        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-admin', 'psql:db-admin'])
+        self.wait_until_ready(['postgresql/0', 'postgresql/1'])
+
+        # Confirm that the slave has not opened a streaming
+        # replication connection.
+        num_slaves = self.sql('SELECT COUNT(*) FROM pg_stat_replication',
+                              'master', dbname='postgres')[0][0]
+        self.assertEqual(num_slaves, 0, 'Streaming connection found')
+
+        # Confirm that replication is actually happening.
+        # Create a table and force a WAL change.
+        self.sql('CREATE TABLE foo AS SELECT generate_series(0,100)',
+                 'master', dbname='postgres')
+        self.sql('SELECT pg_switch_xlog()',
+                 'master', dbname='postgres')
+        timeout = time.time() + 120
+        table_found = False
+        while time.time() < timeout and not table_found:
+            time.sleep(1)
+            if self.sql("SELECT TRUE from pg_class WHERE relname='foo'",
+                        'hot standby', dbname='postgres'):
+                table_found = True
+        self.assertTrue(table_found, "Replication not replicating")
+
     def test_basic_admin(self):
         '''Connect to a single unit service via the db-admin relationship.'''
         self.juju.deploy(TEST_CHARM, 'postgresql', config=self.pg_config)