#!/usr/bin/env python3

'''
This should be run inside the new system.
It can be run periodically
'''

import os
import re
import time
import json
import subprocess as sp

from perfact.generic import load_config
import perfact.dbconn

from helpers import run, sudo, run_cmds_from_args


def dump(inp, out):
    '''
    Dump data from one stream to another, until the input no longer delivers
    anything.
    '''
    while True:
        data = inp.read(4096)
        if not data:
            return
        out.write(data)


def create_cluster(version, name, port, main_version=None, copy_config=True):
    '''
    Create a new cluster and copy the config from the main cluster
    '''
    if main_version is None:
        main_version = version
    sudo(f"pg_createcluster {version} source --port={port}")
    main = f'/etc/postgresql/{main_version}/main'
    target = f'/etc/postgresql/{version}/{name}'
    sudo(
        ['cp', f'{main}/pg_hba.conf', f'{main}/pg_ident.conf', target],
    )
    if copy_config:
        sudo(
            ['cp', f'{main}/conf.d/perfact.conf', f'{target}/conf.d/'],
        )


def drop_cluster(version, name):
    '''
    Drop cluster including cleanup of conf.d
    '''
    sudo(f'pg_dropcluster {version} {name} --stop',
         f'rm -rf /etc/postgresql/{version}/{name}',
         )


def set_port(version, cluster, port):
    '''
    Replace port directive in postgresql config.
    '''
    sudo(
        ['sed', '-i', f's:^port = [0-9]*\\(.*\\):port = {port}\\1:',
         f'/etc/postgresql/{version}/{cluster}/postgresql.conf'
         ],
        ['systemctl', 'restart', f'postgresql@{version}-{cluster}'],
    )


def pg_cmd(port, dbname=None, single_transaction=True, user='postgres',
           cmd=None):
    '''
    Construct psql command.
    '''
    result = ['psql', f'--user={user}', '-v', 'ON_ERROR_STOP=1',
              f'--port={port}']
    if dbname:
        result.append(f'--dbname={dbname}')
    if dbname and single_transaction:
        result.append('--single-transaction')
    if cmd is not None:
        result.extend(['-c', cmd])
    return result


class Migrate():
    '''
    Class that keeps track of what clusters in what versions are available and
    provides methods for the different maniplations. Every method not starting
    with an underscore is automatically offered as command that can be run
    directly from the command line.
    '''

    def __init__(self):
        '''
        Find out version information about postgresql clusters
        '''

        self._read_clusters()

        here = os.path.dirname(__file__)
        self.config = load_config(f'{here}/../config/migration_conf.py')
        self.ports = self.config['ports']
        fname = f'{here}/../passwords.py'
        if os.path.isfile(fname):
            self.pg_passwords = load_config(fname)['passwords']

    def _read_clusters(self):
        '''
        Update information about clusters from pg_lsclusters
        '''
        self.clusters = {
            entry['cluster']: entry
            for entry in json.loads(run(
                ['pg_lsclusters', '--json'],
                stdout=sp.PIPE, stderr=sp.PIPE,
                universal_newlines=True,
            ).stdout)
        }

        self.template = ('template' if 'template' in self.clusters else 'main')

    def _pg_ctl(self, action, name):
        '''
        Start or stop a cluster, enabling or disabling it at the same time.
        '''
        assert action in ['start', 'stop'], 'Invalid action'
        # make permanent
        perm_action = 'enable' if (action == 'start') else 'disable'
        version = self.clusters[name]['version']
        sudo(
            f'systemctl {action} postgresql@{version}-{name}',
            f'systemctl {perm_action} postgresql@{version}-{name}',
        )
        self.clusters[name]['running'] = (action == 'start')

    def _ctl_services(self, action='start'):
        ''' Start or stop processes that access the main database. '''
        assert action in ['start', 'stop'], "Invalid action"
        # Services in the order that they should be stopped
        services = [
            'perfact-measure',
            'monit',
            'perfact-assignd',
            'perfact-dbcached',
            'perfact-dbbookd',
            'perfact-mqttsubd@local',
            'perfact-i18nd',
        ]
        if self.config.get('zope_version', '2') == '2':
            servicename = 'zope@2.13-instance-ema{}'
        else:
            servicename = 'zope4@ema{}'
        services.extend([
            servicename.format(inst) for inst in ['', '01', '02']
        ])
        enabled_services = []
        for service in services:
            enabled = sp.run(
                ['systemctl', 'is-enabled', service],
                universal_newlines=True,
                stdout=sp.PIPE, stderr=sp.PIPE,
            ).stdout.strip() == 'enabled'
            if enabled:
                enabled_services.append(service)

        if action == 'start':
            enabled_services = reversed(enabled_services)

        for service in enabled_services:
            print(f'{action} {service}')
            sudo(f'systemctl {action} {service}')

    def _rename(self, src, tgt):
        '''
        Rename a cluster and set the port accordingly
        '''
        version = self.clusters[self.template]['version']
        assert tgt not in self.clusters, \
            "Cluster {tgt} already exists, cannot rename"
        port = self.ports[tgt]
        sudo(
            f'pg_renamecluster {version} {src} {tgt}',
            ['sed', '-i', f's:^port = [0-9]*\\(.*\\):port = {port}\\1:',
             f'/etc/postgresql/{version}/{tgt}/postgresql.conf'
             ],
        )
        self.clusters[tgt] = self.clusters[src]
        del self.clusters[src]

        if self.clusters[tgt]['running']:
            sudo(['systemctl', 'restart', f'postgresql@{version}-{tgt}'])

    def _fetch_via_basebackup(self):
        '''
        Fetch new source database via pg_basebackup
        '''

        print("Fetching source DB via pg_basebackup")

        source = '/var/lib/postgresql/pg_work/transfer'
        target = '/var/lib/postgresql/source_backup/'
        cmds = f'''
            set -e
            mkdir {source}
            chown postgres {source}
            sudo --login -u postgres pg_basebackup --progress \\
                --checkpoint=fast --format=plain -X stream -D \\
                {source}
            chown -R perfact {source}
        '''
        run(['ssh', 'source-system', 'sudo', 'bash'],
            input=cmds,
            universal_newlines=True,
            )

        sudo(f'chown -R perfact {target}')
        run(['rsync', '-aP', '--delete-during', f'source-system:{source}/',
             target])
        sudo(f'chown -R postgres {target}')

        run(['ssh', 'source-system', 'sudo', f'rm -rf {source}'])

    def _fetch_via_rsync(self):

        print("Fetching source DB via rsync")

        fetch_rsync_source = self.config.get('fetch_rsync_source')
        fetch_rsync_target = self.config.get('fetch_rsync_target')

        assert fetch_rsync_source and fetch_rsync_target

        remote_cmd = (
            f'rsync -aP --delete-during {fetch_rsync_source}'
            f' synctarget:{fetch_rsync_target}'
        )

        # Since we are fetching from a running system, it is quite possible
        # that source files vanish while we are fetching them. We therefore
        # fetch twice, first without check=True, allowing for a failure return
        # code.
        for check in [False, True]:
            run(['ssh', 'source-system', 'sudo', 'bash'],
                input=remote_cmd,
                universal_newlines=True,
                check=check,
                )

    def _tuples(self, query, **args):
        """
        Open a connection to the target database if it is not open yet, send
        query and return tuples.
        """
        if not hasattr(self, 'dbconn'):
            self.dbconn = perfact.dbconn.DBConn(
                f"dbname=perfactema user=zope port={self.ports['target']}"
            )
            self.dbconn.connect()
        self.dbconn.execute(query, args)
        result = self.dbconn.tuples()
        self.dbconn.commit()
        return result

    def fetch(self):
        '''
        Fetch new source backup
        Check configured fetch mode and call corresponding method
        '''

        mode_funcs = {
            'basebackup': self._fetch_via_basebackup,
            'rsync': self._fetch_via_rsync,
        }

        fetch_mode = self.config.get('fetch_mode')

        assert fetch_mode in mode_funcs, 'Inavlid fetch_mode! Check config!'
        mode_funcs[fetch_mode]()

    def _init_source_cluster(self):
        '''
        Create a new source cluster and configure it.
        '''
        # TODO: Switch to copying the main postgresql.conf and adjusting it
        # instead of starting from a new one.
        # TODO: Maybe use pg_dumpall
        version = self.clusters['main']['version']
        for name in ['source', 'target']:
            if name in self.clusters:
                print(f"Dropping existing {name} cluster")
                drop_cluster(version, name)

        print("Creating source cluster")
        create_cluster(version, 'source', self.ports['source'])
        self._read_clusters()
        self._pg_ctl('start', 'source')
        roles = ['zope'] + self.config.get('additional_roles', [])
        passwords = getattr(self, 'pg_passwords', {})
        for role in roles:
            cmd = f'create role {role} with login'
            if role in passwords:
                cmd += f" password '{passwords[role]}'"
            run(pg_cmd(port=self.ports['source'], cmd=cmd))

    def create_from_dump(self):
        '''
        Create a source cluster from a database dump.
        '''
        assert 'database_dumps' in self.config, (
            "Parameter 'database_dumps' not found in config"
        )
        self._init_source_cluster()
        port = self.ports['source']
        # This is a new cluster. If the following crashes, we throw it away
        # anyway, so we can take the speedup of fsync=off
        run(pg_cmd(port=port, cmd='alter system set fsync=off;'))
        for dbname, filename in self.config['database_dumps'].items():
            run(pg_cmd(port=port, cmd=f'create database {dbname}'))

            # Process pipeline - This contains one or two processes, depending
            # on if additional filters are to be applied on the output of cat
            # or zcat. The output of the last process is used as input for the
            # psql command, although additional lines are inserted before that.
            pipeline = []

            # cat or zcat to dump output
            pipeline.append(sp.Popen(
                ['zcat' if filename.endswith('.gz') else 'cat', filename],
                stdout=sp.PIPE,
                universal_newlines=False,
            ))

            # include sed command to filter out lines
            filter_rules = self.config.get('filter_rules', [])
            if filter_rules:
                cmd = ['sed']
                for rule in filter_rules:
                    cmd.extend(['-e', rule])
                pipeline.append(sp.Popen(
                    cmd, stdin=pipeline[-1].stdout,
                    stdout=sp.PIPE,
                    universal_newlines=False
                ))

            consumer = sp.Popen(
                pg_cmd(port=port, dbname=dbname),
                stdin=sp.PIPE,
                universal_newlines=False,
            )
            query = self.config.get('pre_query', {}).get(dbname, None)
            if query:
                consumer.stdin.write(query.encode('utf-8'))

            dump(pipeline[-1].stdout, consumer.stdin)
            consumer.stdin.close()

            for proc in pipeline:
                assert proc.wait() == 0, "Error extracting dump"
            assert consumer.wait() == 0, "Error creating database"

        run(pg_cmd(port=port, cmd='alter system reset fsync;'))
        # manually force sync of cached data to disk because we had turned off
        # fsync
        self._pg_ctl('stop', 'source')
        os.sync()
        self._pg_ctl('start', 'source')

    def upgrade(self):
        '''
        Upgrade the source database if it not the newest version yet
        '''
        srcversion = self.clusters['source']['version']
        tmplversion = self.clusters[self.template]['version']
        port = self.clusters['source']['port']
        if srcversion == tmplversion:
            return
        srcmajor = int(srcversion.split('.')[0])
        tmplmajor = int(tmplversion.split('.')[0])
        # pf_measure_activity is incompatible with new postgres
        self._pg_ctl('start', 'source')
        drop_views = [
            'porting_functions',
        ]
        if srcmajor < 10:
            drop_views.append('pf_measure_activity')
        run(pg_cmd(
            port=port,
            dbname='perfactema',
            cmd='\n'.join([
                f'drop view if exists {view}; drop view if exists hist.{view};'
                for view in drop_views
            ])
        ))

        if srcmajor < 14 and tmplmajor >= 14:
            run(pg_cmd(
                port=port,
                dbname='perfactema',
                cmd="""
                    create or replace function
                      array_append_compat(anyarray, anyelement)
                      returns anyarray
                      immutable
                      parallel safe
                      language sql
                    as $$
                      -- compatibility wrapper since psql 14 changed argument
                      -- types of array_append
                      select array_append($1, $2);
                    $$;

                    CREATE OR REPLACE AGGREGATE public.char_list(anyelement)
                    (
                      SFUNC = array_append_compat,
                      STYPE = anyarray,
                      INITCOND = '{}',
                      FINALFUNC = public.char_list_final
                    );

                    CREATE OR REPLACE AGGREGATE public.array_accum(anyelement)
                    (
                        SFUNC = array_append_compat,
                        STYPE = anyarray,
                        INITCOND = '{}'
                    );
                    CREATE OR REPLACE AGGREGATE repack.array_accum(anyelement)
                    (
                        SFUNC = array_append_compat,
                        STYPE = anyarray,
                        INITCOND = '{}'
                    );
                """,
            ))

        sudo(f'pg_upgradecluster -m link {srcversion} source')
        drop_cluster(srcversion, 'source')
        self.clusters['source']['version'] = tmplversion
        main = f'/etc/postgresql/{tmplversion}/main'
        target = f'/etc/postgresql/{tmplversion}/source'
        sudo(
            ['cp', f'{main}/conf.d/perfact.conf', f'{target}/conf.d/'],
        )
        self._read_clusters()
        sudo(f'vacuumdb -U postgres -v -z --port {port} perfactema')

    def backup(self):
        '''
        Create a backup of the source cluster
        '''
        port = self.ports['source']
        self._pg_ctl('start', 'source')
        sudo([
            'su', '--login', 'postgres', '-c',
            'pg_basebackup --checkpoint=fast --format=plain '
            '--wal-method=stream --pgdata=/var/lib/postgresql/source_backup '
            '--progress ' + f'--port={port}'
        ])

    def restore(self):
        '''
        Restore the source database from the backup.
        '''
        backup = '/var/lib/postgresql/source_backup'
        if self.config.get('fetch_mode') == 'rsync':
            backup = self.config.get('fetch_rsync_target')
        backup_version = run(
            f'sudo cat {backup}/PG_VERSION'.split(),
            stdout=sp.PIPE,
            universal_newlines=True,
        ).stdout.strip()

        if 'target' in self.clusters:
            self._rename('target', 'source')
        cl = self.clusters.get('source')
        rsync_seed = False
        if cl and cl['version'] != backup_version:
            # We need to drop the cluster, but we move the data directory to
            # the side before and use it as rsync seed to hopefully speed the
            # rsync up
            self._pg_ctl('stop', 'source')
            src = f'/var/lib/postgresql/{cl["version"]}/source'
            sudo(
                f'mv {src} /var/lib/postgresql/restore',
                f'mkdir {src}',
                f'chown postgres:postgres {src}',
            )
            rsync_seed = True
            drop_cluster(cl['version'], 'source')
            cl = None

        if cl is None:
            main_version = self.clusters['main']['version']
            create_cluster(
                backup_version,
                'source',
                self.ports['source'],
                main_version,
                copy_config=False
            )
            if rsync_seed:
                src = f'/var/lib/postgresql/{backup_version}/source'
                sudo(
                    f'rm -rf {src}',
                    f'mv /var/lib/postgresql/restore {src}',
                )
        else:
            self._pg_ctl('stop', 'source')

        sudo(
            ('rsync --archive --delete-during --progress '
             f'{backup}/ '
             f'/var/lib/postgresql/{backup_version}/source/'
             ),
            f'chmod 0700 /var/lib/postgresql/{backup_version}/source',
        )
        self._read_clusters()
        # Make sure the database is started - if the fetch used rsync, it might
        # take a few seconds until the database is in a state so it accepts
        # connections. Running pg_upgradecluster before that might fail.
        self._pg_ctl('start', 'source')
        for i in range(20):
            print(f"Checking if database started ({i+1}/20).")
            try:
                run(pg_cmd(port=self.ports['source'], cmd='\\q'))
            except sp.CalledProcessError:
                time.sleep(1)
                continue
            break
        else:  # no break occured
            raise TimeoutError("Database not starting")

    def rename(self):
        ''' Rename source cluster to target. '''
        self._rename('source', 'target')

    def apply_patches(self, folder='upgrade-db'):
        '''
        Apply patches found in upgrade repository
        '''
        folder = f'/opt/perfact/migration/{folder}'
        if not os.path.isdir(folder):
            return
        print('Applying patches.')

        self._pg_ctl('start', 'target')
        port = self.ports['target']
        for patch in sorted(os.listdir(folder)):
            if patch.startswith('.') or not patch.endswith('.sql'):
                continue
            print(f'Applying {patch}')
            with open(f'{folder}/{patch}', 'rb') as f:
                proc = sp.Popen(
                    pg_cmd(port=port, dbname='perfactema'),
                    stdin=f,
                    stdout=sp.DEVNULL,
                    stderr=sp.DEVNULL,
                )

            assert proc.wait() == 0, "Error applying patches"

    def zope_exec(self):
        '''
        Execute scripts inside Zope
        '''
        version = self.config.get('zope_version', '4')
        bindir = f'/usr/share/perfact/zope{version}/bin'
        inst = '/opt/perfact/custom/migration/tempinst'
        if not os.path.isdir(inst):
            # Create instance that listens on port 9091
            run(['mkdir', '-p', f'{inst}/var'])
            if version == '4':
                cmd = 'mkwsgiinstance'
                repl = 's/port = 8080/port = 9091/'
                conf = f'{inst}/etc/zope.ini'
            else:
                cmd = 'mkzopeinstance'
                repl = 's/address 8080/address 9091/'
                conf = f'{inst}/etc/zope.conf'

            run([f'{bindir}/{cmd}', '-d', inst, '-u', 'dummy:dummy'])
            run(['sed', '-i', repl, conf])

        # Copy over Data.fs
        if version == '4':
            src = '/var/lib/zope4/zeo/var/Data.fs'
            cmd = [f'{bindir}/runwsgi', f'{inst}/etc/zope.ini']
        else:
            src = '/var/lib/zope2.13/zeo/emazeo/var/Data.fs'
            cmd = [f'{inst}/bin/runzope']

        run(['rsync', '-aP', src, inst + '/var/Data.fs'])

        # Change connection string for dbconn* to point to target DB
        run([f'{bindir}/python',
             '/opt/perfact/migration/src/inchroot/zope-chconn',
             '--port', self.ports['target']])

        # Run instance and call curl
        proc = sp.Popen(cmd)
        time.sleep(5)
        try:
            for path in self.config['zope_urls']:
                print("Calling", path)
                run(('sudo curl --fail --netrc --netrc-file'
                     ' /root/.netrc-assign --dump-header -').split()
                    + ['http://localhost:9091/PerFact/DB_Utils/'
                      f'zDB/zLayout/zI18N/zMod/{self.config["site"]}/'
                      + path]
                    )
        finally:
            proc.terminate()
            proc.wait()

    def replace_tables(self):
        '''
        Replace configured table contents by their template values
        '''
        self._pg_ctl('start', 'target')
        self._pg_ctl('start', self.template)
        # Start psql consumer process
        proc = sp.Popen(
            pg_cmd(port=self.clusters['target']['port'], dbname='perfactema'),
            stdin=sp.PIPE,
        )
        proc.stdin.write(b'SET session_replication_role = replica;\n')
        tmplport = self.ports[self.template]
        # Read data from producers
        for table in self.config['replace_tables']:
            print(f'Replacing data for {table}.')
            proc.stdin.write(f'delete from {table};\n'.encode('ascii'))
            inp = sp.Popen(
                ('pg_dump --data-only --dbname perfactema --username postgres '
                 '--column-inserts '
                 f'--table {table} --port {tmplport}'
                 ).split(),
                stdout=sp.PIPE,
            )
            dump(inp.stdout, proc.stdin)
            assert inp.wait() == 0, "Error dumping table"
        proc.stdin.close()
        assert proc.wait() == 0, 'Unable to replace tables'

    def merge_tables(self):
        '''
        Merge configured tables with data from template
        '''
        self._pg_ctl('start', 'target')
        self._pg_ctl('start', self.template)
        # Read config and compute merge key if not given
        tables = []
        for table in self.config['merge_tables']:
            schema, name = table.split('.')
            mergekey = self.config['merge_tables_keys'].get(name)
            if mergekey is None:
                # Compute mergekey from unique columns
                uniques = self._tuples('''
                    select
                      a.attname as colname
                    from
                      pg_class as t,
                      pg_class as i,
                      pg_index as ix,
                      pg_attribute as a
                    where
                      t.oid = ix.indrelid
                      and i.oid = ix.indexrelid
                      and a.attrelid = t.oid
                      and a.attnum = ANY(ix.indkey)
                      and t.relkind = 'r'
                      and ix.indisunique
                      and t.relname = %(name)s
                      and a.attname != %(name)s || '_id'
                    order by
                      t.relname,
                      i.relname
                ''', name=name)
                if len(uniques):
                    uniques = ','.join([row[0] for row in uniques])
                    mergekey = f'({uniques})'
                else:
                    mergekey = f'{name}_id'

            # prefix each occurence of 'table_' in the key by either
            # 'tmpl_table.' or 'table.' for the join condition
            tmpl_key = re.sub(f'\\b{name}_', f'tmpl_{name}.{name}_', mergekey)
            orig_key = re.sub(f'\\b{name}_', f'{name}.{name}_', mergekey)

            tables.append({
                'fullname': table,
                'schema': schema,
                'name': name,
                'fks': self.config['merge_tables_fks'].get(name) or [],
                'mergekey': mergekey,
                'tmpl_key': tmpl_key,
                'orig_key': orig_key,
            })

        # Start psql consumer process
        proc = sp.Popen(
            pg_cmd(port=self.clusters['target']['port'], dbname='perfactema'),
            stdin=sp.PIPE,
        )

        def pg_exec(query):
            proc.stdin.write(query.encode('utf-8'))

        pre_query = self.config.get('pre_query', None)
        if pre_query:
            pg_exec(pre_query)

        tmplport = self.ports[self.template]

        # create placeholder tables and insert data from producers
        for table in tables:
            name = table['name']
            fullname = table['fullname']
            print(f'Fetching data for {name}.')

            pg_exec(f'''
                drop table if exists tmpl_{name} cascade;
                create table tmpl_{name} (
                  like {fullname}
                  including defaults
                  including indexes
                );''')

            # use column-inserts because the columns might not be the same or
            # in the same order
            inp = sp.Popen(
                ('pg_dump --data-only --dbname perfactema --username postgres '
                 f'--table {fullname} --port {tmplport} --column-inserts'
                 ).split(),
                stdout=sp.PIPE,
                universal_newlines=True,
            )

            while True:
                line = inp.stdout.readline()
                if not line:
                    break
                if line.startswith('SELECT pg_catalog.set'):
                    continue
                if line.startswith('INSERT INTO'):
                    line = re.sub(f'INSERT INTO [a-z]*.{name} ',
                                  f'INSERT INTO tmpl_{name} ',
                                  line)
                pg_exec(line)

            assert inp.wait() == 0, 'Error dumping table'

        # add foreign keys
        for table in tables:
            name = table['name']
            fks = table['fks']
            if not fks:
                continue
            for fk in fks:
                column = f'{name}_{fk}_id'
                ftable = fk.rsplit('_', 1)[-1]

                pg_exec(f'''
                    alter table tmpl_{name}
                    add constraint {fk}_id foreign key ({column})
                    references tmpl_{ftable} ({ftable}_id)
                    on update cascade on delete cascade;
                ''')

        # Move IDs so they do not collide with the source, but if they have a
        # matching data set in the source database, use that column
        for table in tables:
            name = table['name']
            pg_exec(f"""
                select setval('{name}_s', greatest(
                  (select max({name}_id) from {name}),
                  (select max({name}_id) from tmpl_{name})
                ));
            """)
            if table['mergekey'] != f'{name}_id':
                pg_exec(f"""
                    update tmpl_{name} set {name}_id = nextval('{name}_s');
                """)
            pg_exec(
                f'''
                commit; begin;
                update tmpl_{name} set
                  {name}_id = {name}.{name}_id
                from {name}
                where {table['tmpl_key']} = {table['orig_key']};
                '''
            )

        # Move data into target tables
        # TODO: what if we want the new content for a table where the key was
        # already found?
        for table in tables:
            name = table['name']
            pg_exec(f'''
                insert into {name}
                select tmpl_{name}.*
                from tmpl_{name}
                left join {name}
                  on tmpl_{name}.{name}_id = {name}.{name}_id
                where {name}.{name}_id is null;
            ''')

        for table in reversed(tables):
            pg_exec(f'''drop table tmpl_{table['name']};''')

        post_query = self.config.get('post_query', None)
        if post_query:
            pg_exec(post_query)

        proc.stdin.close()
        assert proc.wait() == 0, "Copying tables failed"

    def apply_data_patches(self):
        '''
        Apply patches after data has been copied.
        '''
        self.apply_patches(folder='after-copy-data')

    def copy_data(self):
        '''
        Copy data from template to target (replace tables, merge tables and
        apply patches after.
        '''
        print('Copying data')

        self.replace_tables()
        self.merge_tables()
        self.apply_data_patches()

        # The template cluster does not need to be kept running
        if 'template' in self.clusters:
            self._pg_ctl('stop', 'template')

    def fix_appassign(self):
        '''Fixes appassign entries so they become Python 3 compatible.'''
        repl = "regexp_replace(appassign_session, '(: [0-9]+)L', '\\1', 'g')"
        run(
            pg_cmd(port=self.ports['target'], dbname='perfactema'),
            text=True,
            input=f'''
              update appassign set
                appassign_modtime = now(),
                appassign_author = 'fix_appassign_for_py3',
                appassign_session = {repl}
              where appassign_session != {repl}
            ''',
        )

    def move_into_place(self):
        '''
        If the template cluster is not yet backed up, rename the main cluster
        to template. Else, simply drop it.
        Afterwards, move target to main.
        '''
        print('Moving database into place')

        # Stop depending services
        self._ctl_services('stop')

        version = self.clusters[self.template]['version']
        pgwal = f'/var/lib/postgresql/{version}/main/pg_wal'
        pgwaldir = f'/vol/pg_wal/{version}'
        pgwaltgt = f'{pgwaldir}/main'

        if self.template == 'template':
            drop_cluster(version, 'main')
            del self.clusters['main']
        else:
            self._pg_ctl('stop', 'main')
            islink = run(['sudo', 'test', '-L', pgwal], check=False)
            if islink.returncode == 0:
                # Only the main database should use a symlink
                sudo(
                    ['rm', pgwal],
                    ['mv', pgwaltgt, pgwal],
                )
            self._rename('main', 'template')
            self.template = 'template'

        self._rename('target', 'main')
        self._pg_ctl('stop', 'main')
        sudo(
            ['mkdir', '-p', pgwaldir],
            ['test', '!', '-d', pgwaltgt],  # error if already exists
            ['mv', pgwal, pgwaltgt],
            ['ln', '-s', pgwaltgt, pgwal],
        )
        self._pg_ctl('start', 'main')
        # Start depending services
        self._ctl_services('start')

    def restore_template(self):
        '''
        Move main to target and template to main
        '''
        print('Restoring template DB')
        self._ctl_services('stop')
        if 'target' in self.clusters or 'source' in self.clusters:
            drop_cluster(self.clusters['main']['version'], 'main')
            del self.clusters['main']
        else:
            self._rename('main', 'target')
        self._rename('template', 'main')
        self._pg_ctl('start', 'main')
        self._ctl_services('start')
        self.template = 'main'

    def cleanup_template(self):
        '''
        Remove the template cluster
        '''
        drop_cluster(
                self.clusters['template']['version'], 'template')

    def migrate_certificates(self):
        '''
        Copy old certificates and private keys to a new path
        and read the contents into the appropriate columns of
        the appcert table.
        '''
        old_path = self.config['certificate_migration_path']
        appca_id = self._tuples('''
        select appca_id
          from appca
         where appca_isloginca is true
        ''')[0][0]

        base = '/home/zope/CA'
        ca = f'{base}/{appca_id}'
        old_certs_path = f'{old_path}/certs'
        certs_path = f'{ca}/certs'
        private_path = f'{ca}/private'
        old_keys_path = f'{old_path}/private/keys'
        keys_path = f'{private_path}/keys'

        # Open up permissions for the directories
        sudo(
            f'chmod o+rwx {base} {ca} {private_path} {certs_path}'
            f'            {old_path} {old_certs_path} {old_keys_path}'
        )

        sudo(
            f'cp {old_path}/index.txt {ca}/index.txt',
            f'cp {old_path}/serial {ca}/serial',
        )

        # read out old and new filenames from database
        for line in self._tuples('''
            select
               appuser_certid as old_file,
               appcert_id as new_file
            from appuser
            join appcert
              on appcert_appuser_id = appuser_id;
        '''):
            old = line[0]
            appcert_id = line[1]
            new_cert_path = f'{certs_path}/{appcert_id}.pem'
            new_key_path = f'{keys_path}/{appcert_id}.pem'

            # move certificates and keys around, giving them new names
            sudo(
                f'cp {old_certs_path}/{old}.pem {new_cert_path}',
                f'cp {old_keys_path}/{old}.pem {new_key_path}',
                f'chown zope:zope {new_cert_path} {new_key_path}',
            )

            # read in certificate and private key and store in DB
            with open(new_cert_path) as fd:
                cert = fd.read()

            with open(new_key_path) as fd:
                key = fd.read()

            self._tuples(
                '''
                update appcert set
                  appcert_author = 'migration 2021',
                  appcert_cert = %(cert)s,
                  appcert_key = %(key)s
                where appcert_id = %(appcert_id)s;
                ''',
                cert=cert, key=key, appcert_id=appcert_id
            )

        # reset permissions
        sudo(
            f'chmod o-rwx {base} {ca} {private_path} {certs_path}'
            f'            {old_path} {old_certs_path} {old_keys_path}'
        )

    def init(self):
        '''
        Run initialization steps after first boot
        '''
        self.upgrade()
        self.backup()

    def main(self):
        '''
        Main sequence: Fetch new source, restore it, upgrade and rename it,
        apply patches, copy data and move into place.
        '''
        for function in [
            'fetch',
            'restore',
            'upgrade',
            'rename',
            'apply_patches',
            'copy_data',
            'move_into_place',
        ]:
            getattr(self, function)()


if __name__ == '__main__':
    mig = Migrate()
    cmds = {
        name: getattr(mig, name)
        for name in dir(mig)
    }
    run_cmds_from_args(avail=cmds, default='main')