Merge lp:~stub/charms/precise/postgresql/bug-1276024-fix-log-shipping into lp:charms/postgresql

[Stuart Bishop]

Wire up the STILL EXPERIMENTAL Swift log shipping, per Bug #1276024.

SwiftWAL is a tool I wrote to allow PostgreSQL to push filesystem level
backups and log ship WAL files to Swift. It is similar to WAL-E. In fact,
it is so similar that I will probably scrap it in favor of WAL-E since
WAL-E now supports Swift in addition to Amazon S3. Switching to or adding
support for WAL-E should be mostly mechanical work, although IIRC it
still needed bidirectional SSH setup too.

Anyway, back to the branch at hand, this branch is concerned with log
shipping to Swift. Log shipping is one component of configuring point
in time recovery, and allows us to replicate without a streaming
replication connection.

This branch also fixes up filesystem backups to Swift, but finalizing
backups, recovery and tests is for a later branch and may require juju
actions to be implemented first.

I'm keeping the EXPERIMENTAL status since I'm expecting changes to the
design. For instance, should all the OpenStack configuration be in the
config, or should be be creating a relation to some service to retrieve
the credentials? Or perhaps we need a generic object storage service that
proxies to the provider's object storage system? It would be great if I
could write this once and have it work on all providers.

The Swift tests are skipped unless a valid set of OpenStack credentials
are found in the environment (OS_TENANT_NAME, OS_AUTH_URL etc.).

[Charles Butler (lazypower)]

Stub,

I've taken a look through this, and with you openly stating its a stop gap prior to Wal-E, do we want to pursue this branch in leu of that integration? Or would it be prudent to pin this and review when WAL-E lands?

I'm not seeing anything terribly heinous in this, but I also haven't run deployment tests on it as of yet.

[Stuart Bishop (stub)]

@lazypower The next branch in the pipeline builds on this one and adds wal-e support. I then get to test both under real conditions, and will probably drop SwiftWAL when this stops being experimental.

[Stuart Bishop (stub)]

btw. if you test https://code.launchpad.net/~stub/charms/precise/postgresql/test-upgrade-charm/+merge/229914 you will be testing all 4 mps in the queue (they form a development pipeline, each dependant on the previous to avoid merge conflicts)

[lp:~stub/charms/precise/postgresql/bug-1276024-fix-log-shipping]

150. By Stuart Bishop on 2014-08-27

Resolve conflicts

151. By Stuart Bishop on 2014-08-27

Merged bug-1281600-log_temp_files into bug-1276024-fix-log-shipping.

152. By Stuart Bishop on 2014-08-27

Merged bug-1281600-log_temp_files into bug-1276024-fix-log-shipping.

153. By Stuart Bishop on 2014-08-27

Merged bug-1281600-log_temp_files into bug-1276024-fix-log-shipping.

[Whit Morriss (whitmo)]

merged in https://code.launchpad.net/~stub/charms/precise/postgresql/integration/+merge/233666

=== modified file 'config.yaml'
--- config.yaml	2014-08-27 12:56:33 +0000
+++ config.yaml	2014-08-27 12:56:33 +0000
@@ -172,9 +172,10 @@
     default: False
     type: boolean
     description: |
+        DEPRECATED.
         Hot standby or warm standby. When True, queries can be run against
         the database when in recovery or standby mode (ie. replicated).
-        Overridden by juju when master/slave relations are used.
+        Overridden when service contains multiple units.
   hot_standby_feedback:
     default: False
     type: boolean
@@ -189,7 +190,7 @@
         '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
-        replication s used.
+        replication is used.
   max_wal_senders:
     default: 0
     type: int
@@ -392,7 +393,9 @@
     description: |
       EXPERIMENTAL.
       Swift container prefix for SwiftWAL to use. Must be set if any
-      SwiftWAL features are enabled.
+      SwiftWAL features are enabled. This will become a simple
+      swiftwal_container config item when proper leader election is
+      implemented in juju.
   swiftwal_backup_schedule:
     type: string
     default: ""

=== modified file 'hooks/hooks.py'
--- hooks/hooks.py	2014-08-27 12:56:33 +0000
+++ hooks/hooks.py	2014-08-27 12:56:33 +0000
@@ -395,11 +395,21 @@
         log('Ensuring minimal replication settings')
         config_data['hot_standby'] = True
         config_data['wal_level'] = 'hot_standby'
-        config_data['max_wal_senders'] = max(
-            num_slaves, config_data['max_wal_senders'])
         config_data['wal_keep_segments'] = max(
             config_data['wal_keep_segments'],
             config_data['replicated_wal_keep_segments'])
+        # We need this set even if config_data['streaming_replication']
+        # is False, because the replication connection is still needed
+        # by pg_basebackup to build a hot standby.
+        config_data['max_wal_senders'] = max(
+            num_slaves, config_data['max_wal_senders'])
+
+    # Log shipping to Swift using SwiftWAL. This could be for
+    # non-streaming replication, or for PITR.
+    if config_data['swiftwal_log_shipping']:
+        config_data['archive_mode'] = True
+        config_data['wal_level'] = 'hot_standby'
+        config_data['archive_command'] = swiftwal_archive_command()
 
     # Send config data to the template
     # Return it as pg_config
@@ -596,15 +606,14 @@
 def install_postgresql_crontab(output_file):
     '''Create the postgres user's crontab'''
     config_data = hookenv.config()
-    crontab_data = {
-        'backup_schedule': config_data["backup_schedule"],
-        'scripts_dir': postgresql_scripts_dir,
-        'backup_days': config_data["backup_retention_count"],
-    }
+    config_data['scripts_dir'] = postgresql_scripts_dir
+    config_data['swiftwal_backup_command'] = swiftwal_backup_command()
+    config_data['swiftwal_prune_command'] = swiftwal_prune_command()
+
     charm_dir = hookenv.charm_dir()
     template_file = "{}/templates/postgres.cron.tmpl".format(charm_dir)
     crontab_template = Template(
-        open(template_file).read()).render(crontab_data)
+        open(template_file).read()).render(config_data)
     host.write_file(output_file, crontab_template, perms=0600)
 
 
@@ -627,7 +636,8 @@
         'host': master_host,
         'port': master_port,
         'password': local_state['replication_password'],
-        'streaming_replication': streaming_replication})
+        'streaming_replication': streaming_replication,
+        'restore_command': swiftwal_restore_command()})
     log(recovery_conf, DEBUG)
     host.write_file(
         os.path.join(postgresql_cluster_dir, 'recovery.conf'),
@@ -638,6 +648,64 @@
         postgresql_restart()
 
 
+def swiftwal_config():
+    postgresql_config_dir = _get_postgresql_config_dir()
+    return os.path.join(postgresql_config_dir, "swiftwal.conf")
+
+
+def create_swiftwal_config():
+    if not hookenv.config('swiftwal_container_prefix'):
+        return
+
+    # 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 Swift 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':
+        container = '{}_{}'.format(hookenv.config('swiftwal_container_prefix'),
+                                   hookenv.local_unit().split('/')[-1])
+    else:
+        container = hookenv.config('swiftwal_container_prefix')
+
+    template_file = os.path.join(hookenv.charm_dir(),
+                                 'templates', 'swiftwal.conf.tmpl')
+    params = dict(hookenv.config())
+    params['swiftwal_container'] = container
+    content = Template(open(template_file).read()).render(params)
+    host.write_file(swiftwal_config(), content, "postgres", "postgres", 0o600)
+
+
+def swiftwal_archive_command():
+    '''Return the archive_command needed in postgresql.conf'''
+    return 'swiftwal --config={} archive-wal %p'.format(swiftwal_config())
+
+
+def swiftwal_restore_command():
+    '''Return the restore_command needed in recovery.conf'''
+    return 'swiftwal --config={} restore-wal %f %p'.format(swiftwal_config())
+
+
+def swiftwal_backup_command():
+    '''Return the backup command needed in postgres' crontab'''
+    cmd = 'swiftwal --config={} backup --port={}'.format(swiftwal_config(),
+                                                         get_service_port())
+    if not hookenv.config('swiftwal_log_shipping'):
+        cmd += ' --xlog'
+    return cmd
+
+
+def swiftwal_prune_command():
+    '''Return the backup & wal pruning command needed in postgres' crontab'''
+    config = hookenv.config()
+    args = '--keep-backups={} --keep-wals={}'.format(
+        config.get('swiftwal_backup_retention', 0),
+        max(config['wal_keep_segments'],
+            config['replicated_wal_keep_segments']))
+    return 'swiftwal --config={} prune {}'.format(swiftwal_config(), args)
+
+
 def update_service_port():
     old_port = local_state.get('listen_port', None)
     new_port = get_service_port()
@@ -918,6 +986,7 @@
     generate_postgresql_hba(postgresql_hba)
     create_ssl_cert(os.path.join(
         postgresql_data_dir, pg_version(), config_data['cluster_name']))
+    create_swiftwal_config()
     update_service_port()
     update_nrpe_checks()
     if force_restart:
@@ -1556,12 +1625,18 @@
                 "postgresql-contrib-{}".format(version),
                 "postgresql-plpython-{}".format(version),
                 "python-jinja2", "python-psycopg2"]
+
     # PGDG currently doesn't have debversion for 9.3 & 9.4. Put this back
     # when it does.
     if not (hookenv.config('pgdg') and version in ('9.3', '9.4')):
         packages.append("postgresql-{}-debversion".format(version))
+
     if hookenv.config('performance_tuning').lower() != 'manual':
         packages.append('pgtune')
+
+    if hookenv.config('swiftwal_container_prefix'):
+        packages.append('swiftwal')
+
     packages.extend((hookenv.config('extra-packages') or '').split())
     packages = fetch.filter_installed_packages(packages)
     # Set package state for main postgresql package if installed
@@ -1825,6 +1900,11 @@
             _get_postgresql_config_dir(), "pg_hba.conf")
         generate_postgresql_hba(postgresql_hba)
 
+    # Swift container name make have changed, so regenerate the SwiftWAL
+    # config. This can go away when we have real leader election and can
+    # safely share a single container.
+    create_swiftwal_config()
+
     local_state.publish()
 
 

=== modified file 'templates/postgres.cron.tmpl'
--- templates/postgres.cron.tmpl	2013-11-19 11:38:45 +0000
+++ templates/postgres.cron.tmpl	2014-08-27 12:56:33 +0000
@@ -1,14 +1,11 @@
-{{backup_schedule}} postgres {{scripts_dir}}/pg_backup_job {{backup_days}}
-{% if swiftwal_container -%}
+# Maintained by juju
+#
+{% if backup_schedule -%}
+{{backup_schedule}} postgres \
+    {{scripts_dir}}/pg_backup_job {{backup_retention_count}}
+{% endif -%}
+
 {% if swiftwal_backup_schedule -%}
-{% if swiftwal_log_shipping -%}
-{{swiftwal_backup_schedule}} postgres \
-  swiftwal --config={{swiftwal_config}} backup && \
-  swiftwal --config={{swiftwal_config}} prune -n {{swiftwal_backup_retention}}
-{% else -%}
-{{swiftwal_backup_schedule}} postgres \
-  swiftwal --config={{swiftwal_config}} backup --xlog && \
-  swiftwal --config={{swiftwal_config}} prune -n {{swiftwal_backup_retention}}
-{% endif -%}
-{% endif -%}
+{{swiftwal_backup_schedule}} postgres \
+    {{swiftwal_backup_command}} && {{swiftwal_prune_command}}
 {% endif -%}

=== modified file 'templates/swiftwal.conf.tmpl'
--- templates/swiftwal.conf.tmpl	2013-11-22 13:41:01 +0000
+++ templates/swiftwal.conf.tmpl	2014-08-27 12:56:33 +0000
@@ -1,6 +1,6 @@
 # Generated and maintained by juju
-OS_USERNAME: {{config.os_username}}
-OS_TENANT_NAME: {{config.os_tenant_name}}
-OS_PASSWORD: {{config.os_password}}
-OS_AUTH_URL: {{config.os_auth_url}}
-CONTAINER: {{vars.container}}
+OS_USERNAME: {{os_username}}
+OS_TENANT_NAME: {{os_tenant_name}}
+OS_PASSWORD: {{os_password}}
+OS_AUTH_URL: {{os_auth_url}}
+CONTAINER: {{swiftwal_container}}

=== modified file 'test.py'
--- test.py	2014-06-10 11:36:17 +0000
+++ test.py	2014-08-27 12:56:33 +0000
@@ -9,6 +9,7 @@
     juju destroy-environment
 """
 
+from datetime import datetime
 import os.path
 import signal
 import socket
@@ -33,6 +34,15 @@
     pass
 
 
+def skip_if_swift_is_unavailable():
+    os_keys = set(['OS_TENANT_NAME', 'OS_AUTH_URL',
+                   'OS_USERNAME', 'OS_PASSWORD'])
+    for os_key in os_keys:
+        if os_key not in os.environ:
+            return unittest.skip('Swift is unavailable')
+    return lambda x: x
+
+
 class PostgreSQLCharmBaseTestCase(object):
 
     # Override these in subclasses to run these tests multiple times
@@ -371,6 +381,54 @@
 
         self.assertEqual(num_slaves, 1, 'Slave not connected')
 
+    @skip_if_swift_is_unavailable()
+    def test_swiftwal_logshipping_replication(self):
+        os_keys = set(['OS_TENANT_NAME', 'OS_AUTH_URL',
+                       'OS_USERNAME', 'OS_PASSWORD'])
+        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['swiftwal_log_shipping'] = True
+        self.pg_config['swiftwal_container_prefix'] = '{}_{}'.format(
+            '_juju_pg_tests', datetime.utcnow().strftime('%Y%m%dT%H%M%SZ'))
+        self.pg_config['install_sources'] = 'ppa:stub/pgcharm'
+
+        def swift_cleanup():
+            prefix = self.pg_config['swiftwal_container_prefix']
+            for container in [prefix, prefix + '_1', prefix + '_2']:
+                # Ignore errors and output
+                subprocess.call(['swift', 'delete', container],
+                                stdout=subprocess.PIPE,
+                                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)
@@ -546,7 +604,6 @@
         self.assertIs(False, self.is_master(standby_unit_1, 'postgres'))
 
     def test_admin_addresses(self):
-
         # 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