/silversupport/service/postgresql.py

  1import os
  2from silversupport.shell import run, overwrite_file
  3from silversupport.abstractservice import AbstractService
  4
  5
  6class Service(AbstractService):
  7
  8    packages = [
  9        'postgresql-8.4',
 10        'postgresql-client',
 11        'postgresql-client-8.4',
 12        'postgresql-client-common',
 13        'postgresql-common',
 14        ]
 15
 16    platform_packages = dict(
 17        python=[
 18            'python-psycopg2',
 19            'python-egenix-mxdatetime',
 20            'python-egenix-mxtools',
 21            ],
 22        php=[
 23            'php5-pgsql',
 24            ])
 25
 26    def install(self):
 27        if not os.path.exists('/usr/bin/psql'):
 28            self.install_packages()
 29            overwrite_file('/etc/postgresql/8.4/main/pg_hba.conf',
 30                           source_file=os.path.join(os.path.dirname(__file__),
 31                                                    'postgresql-pg_hba.conf'))
 32            run(['chown', 'postgres:postgres',
 33                 '/etc/postgresql/8.4/main/pg_hba.conf'])
 34            run(['/etc/init.d/postgresql-8.4', 'restart'])
 35
 36        stdout, stderr, returncode = run(
 37            ['psql', '-U', 'postgres', '--tuples-only'],
 38            capture_stdout=True, capture_stderr=True,
 39            stdin="select r.rolname from pg_catalog.pg_roles as r;")
 40        if 'www-mgr' not in stdout:
 41            run(['createuser', '-U', 'postgres',
 42                 '--no-superuser', '--no-createdb',
 43                 '--no-createrole', 'www-mgr'])
 44
 45        app_name = self.app_config.app_name
 46        stdout, stderr, returncode = run(
 47            ['/usr/bin/psql', '-U', 'postgres', '-l', '-t', '-A'],
 48            capture_stdout=True)
 49        databases = [line.split('|')[0] for line in stdout.splitlines()]
 50        if app_name in databases:
 51            self.output('Database %s already exists' % app_name)
 52        else:
 53            self.output('Database %s does not exist; creating.' % app_name)
 54            run(['createdb', '-U', 'postgres', '-O', 'www-mgr',
 55                 app_name])
 56
 57    def env_setup(self):
 58        environ = {}
 59        app_name = self.app_config.app_name
 60        if not self.devel:
 61            environ['CONFIG_PG_DBNAME'] = app_name
 62            environ['CONFIG_PG_USER'] = 'www-mgr'
 63            environ['CONFIG_PG_PASSWORD'] = ''
 64            environ['CONFIG_PG_HOST'] = ''
 65            environ['CONFIG_PG_PORT'] = ''
 66            environ['CONFIG_PG_SQLALCHEMY'] = 'postgres://postgres@/%s' % app_name
 67        else:
 68            import getpass
 69            environ['CONFIG_PG_DBNAME'] = app_name
 70            environ['CONFIG_PG_USER'] = getpass.getuser()
 71            environ['CONFIG_PG_PASSWORD'] = ''
 72            environ['CONFIG_PG_HOST'] = ''
 73            environ['CONFIG_PG_PORT'] = ''
 74            for name, value in self.devel_config.items():
 75                if name.startswith('postgis.'):
 76                    name = name[len('postgis.'):]
 77                    environ['CONFIG_PG_%s' % name.upper()] = value
 78            sa = 'postgres://'
 79            if environ.get('CONFIG_PG_USER'):
 80                sa += environ['CONFIG_PG_USER']
 81                if environ.get('CONFIG_PG_PASSWORD'):
 82                    sa += ':' + environ['CONFIG_PG_PASSWORD']
 83                sa += '@'
 84            if environ.get('CONFIG_PG_HOST'):
 85                ## FIXME: should this check for 'localhost', which SA actually doesn't like?
 86                sa += environ['CONFIG_PG_HOST']
 87            if environ.get('CONFIG_PG_PORT'):
 88                sa += ':' + environ['CONFIG_PG_PORT']
 89            sa += '/' + environ['CONFIG_PG_DBNAME']
 90            environ['CONFIG_PG_SQLALCHEMY'] = sa
 91        return environ
 92
 93    def cli_options(self, force_user=None):
 94        if not force_user:
 95            force_user = self.env['CONFIG_PG_USER']
 96        options = ['-U', force_user]
 97        if self.env.get('CONFIG_PG_HOST'):
 98            options.extend(['--host', self.env['CONFIG_PG_HOST']])
 99            if self.env.get('CONFIG_PG_PORT'):
100                options.extend(['--port', self.env['CONFIG_PG_PORT']])
101        return options
102
103    def backup(self, output_dir):
104        ## FIXME: this includes a bunch of nonsense
105        run(['pg_dump', '--format=custom', '--no-owner', '--no-acl', '--no-privileges']
106            + self.cli_options('postgres')
107            + [self.env['CONFIG_PG_DBNAME'], '--file', os.path.join(output_dir, 'postgis.pgdump')])
108        fp = open(os.path.join(output_dir, 'postgis.README.txt'), 'w')
109        fp.write(self.BACKUP_README)
110        fp.close()
111
112    BACKUP_README = """\
113    The backup is created by pg_dump -Fc.  To restore it use pg_restore.
114    """
115
116    def restore(self, input_dir):
117        path = os.path.join(input_dir, 'postgis.pgdump')
118        dbname = self.env['CONFIG_PG_DBNAME']
119        run(['pg_restore'] + self.cli_options('postgres') + ['--dbname', dbname, path])
120
121    def clear(self):
122        dbname = self.env['CONFIG_PG_DBNAME']
123        ## FIXME: -U etc?
124        run(['dropdb'] + self.cli_options('postgres') + [dbname])
125        self.install()