def database(name, owner, template='template0', encoding='UTF8', locale='en_US.UTF-8'): """ Require a PostgreSQL database. :: from fabtools import require require.postgres.database('myapp', owner='dbuser') """ if not database_exists(name): with watch('/etc/locale.gen') as locales: require_locale(locale) if locales.changed: restarted(_service_name()) create_database(name, owner, template=template, encoding=encoding, locale=locale)
def setup_postgres(): require.postgres.server() # NOTE: fabtools.require.postgres.user did not allow me to create a user with no pw if not postgres.user_exists(SITE_USER): su('createuser -S -D -R -w %s' % SITE_USER, 'postgres') if not postgres.database_exists(SITE_USER): require.postgres.database(SITE_USER, SITE_USER, encoding='UTF8', locale='en_US.UTF-8')
def setup_database(): require.postgres.server() # NOTE: fabtools.require.postgres.user did not allow me to create a user with no pw prompt? if not postgres.user_exists(SITE_USER): su('createuser -S -D -R -w %s' % SITE_USER, 'postgres') if not postgres.database_exists(SITE_USER): require.postgres.database(SITE_USER, SITE_USER, encoding='UTF8', locale='en_US.UTF-8')
def upload_sqldump(app): if app.dbdump: if re.match('http(s)?://', app.dbdump): fname = 'dump.sql.gz' url = app.dbdump auth = '' else: latest = cdstar.get_latest_bitstream(app.dbdump) fname, url = latest.name, latest.url auth = '-u"{0}:{1}" '.format(os.environ['CDSTAR_USER_BACKUP'], os.environ['CDSTAR_PWD_BACKUP']) target = pathlib.PurePosixPath('/tmp') / fname run('curl -s -o {0} {1} {2}'.format(target, auth, url)) else: db_name = prompt('Replace with dump of local database:', default=app.name) sqldump = pathlib.Path(tempfile.mktemp(suffix='.sql.gz', prefix='%s-' % db_name)) target = pathlib.PurePosixPath('/tmp') / sqldump.name db_user = '******' if PLATFORM == 'windows' else '' local('pg_dump %s--no-owner --no-acl -Z 9 -f %s %s' % (db_user, sqldump, db_name)) require.file(str(target), source=str(sqldump)) sqldump.unlink() if app.stack == 'soundcomparisons': sudo('echo "drop database {0};" | mysql'.format(app.name)) require.mysql.database(app.name, owner=app.name) sudo('gunzip -c {0} | mysql -u {1} --password={1} -D {1}'.format(target, app.name), user=app.name) else: # TODO: assert supervisor.process_status(app.name) != 'RUNNING' if postgres.database_exists(app.name): require_postgres(app, drop=True) sudo('gunzip -c %s | psql -d %s' % (target, app.name), user=app.name) sudo('vacuumdb -zf %s' % app.name, user='******') files.remove(str(target))
def database(name, owner, template='template0', encoding='UTF8', locale='en_US.UTF-8'): """ Require a PostgreSQL database. :: from fabtools import require require.postgres.database('myapp', owner='dbuser') """ if not database_exists(name): if locale not in run('locale -a').split(): require_locale(locale) restarted(_service_name()) create_database(name, owner, template=template, encoding=encoding, locale=locale)
def database(name, owner, template='template0', encoding='UTF8', locale='en_US.UTF-8', allow_restart=False): """ Require a PostgreSQL database. :: from fabtools import require require.postgres.database('myapp', owner='dbuser') """ locale_transform = lambda l: l.strip().lower().replace('-', '') if not database_exists(name): locales = map( locale_transform, run('locale -a').split() ) if locale_transform(locale) not in locales: if not allow_restart: abort( 'New locale "{}" must be installed and ' 'postgres must be restarted after that'.format( locale ) ) require_locale(locale) restarted(_service_name()) create_database(name, owner, template=template, encoding=encoding, locale=locale)
def setup_database(): user = SITE_SETTINGS["user"] require.postgres.server() # TODO: fabtools.require.postgres.user did not allow a user with no pw prompt? see if there is a better way if not postgres.user_exists(user): su("createuser -S -D -R -w %s" % user, "postgres") if not postgres.database_exists(user): require.postgres.database(user, user, encoding="UTF8", locale="en_US.UTF-8")
def setup_postgres(): """Initial postgres setup.""" if not db.user_exists(env.database_user): if 'database_pw' not in env: prompt('PostgreSQL database password:'******'database_pw') create_user(env.database_user, password=env.database_pw, encrypted_password=True) if not database_exists(env.database_name): create_database(env.database_name, env.database_user)
def postgres_create(): """ Create postgres objects, including owner, databases, and schemas. """ if not pg_user_exists(env.db_user): create_pg_user(env.db_user, password=env.db_password) if not database_exists(env.db_name): create_database(env.db_name, owner=env.db_user)
def test_require_database(postgres_server, postgres_user): from fabtools.postgres import database_exists, drop_database from fabtools.require.postgres import database database('pgdb', postgres_user) assert database_exists('pgdb') drop_database('pgdb')
def setup_database(): user = SITE_SETTINGS['user'] require.postgres.server() # TODO: fabtools.require.postgres.user did not allow a user with no pw prompt? see if there is a better way if not postgres.user_exists(user): su('createuser -S -D -R -w %s' % user, 'postgres') if not postgres.database_exists(user): require.postgres.database(user, user, encoding='UTF8', locale='en_US.UTF-8')
def run(self): user = env['user'] if self.user: user = self.user if not funcs.user_exists(user): funcs.create_user(user, self.password) if not funcs.database_exists(self.database): funcs.create_database( self.database, user, locale='ja_JP.utf8')
def create_database(postgis=False): """ postgis=false if creating a fresh DB to pg_restore a DB which already has postgis installed - spheroid already exists error """ print(green("Creating PG database")) if not postgres.database_exists(settings.DB_NAME): require.postgres.database(settings.DB_NAME, settings.DB_USER, #locale='en_GB.utf8', locale='en_GB.UTF-8', template="template_postgis" if postgis else 'template0' )
def server_prepare(): """ installing all necessary packages """ apt_packages = ('libpq-dev', 'python3-dev', 'libssl-dev', 'lib32ncurses5-dev', 'python3', 'postgresql', 'postgis',) sudo('apt-get install %s' % " ".join(apt_packages)) sudo('easy_install pip') """ db routines """ db_env = DB[env.environment] if not postgres.user_exists(db_env["user"]): postgres.create_user(db_env["user"], db_env["password"], createdb=True, createrole=True) if not postgres.database_exists(db_env['db']): postgres.create_database(db_env['db'], owner=db_env["user"])
def set_db_permissions(): """Set the db so user wsgi has all permissions. """ user = "******" dbname = "changelog" if not user_exists(user): create_user(user, password="******") if not database_exists(dbname): create_database(dbname, user) grant_sql = "GRANT ALL ON schema public to %s;" % user # assumption is env.repo_alias is also database name run('psql %s -c "%s"' % (dbname, grant_sql)) grant_sql = "GRANT ALL ON ALL TABLES IN schema public to %s;" % user # assumption is env.repo_alias is also database name run('psql %s -c "%s"' % (dbname, grant_sql)) grant_sql = "GRANT ALL ON ALL SEQUENCES IN schema public to %s;" % user run('psql %s -c "%s"' % (dbname, grant_sql))
def database(name, owner, template="template0", encoding="UTF8", locale="en_US.UTF-8"): """ Require a PostgreSQL database. :: from fabtools import require require.postgres.database('myapp', owner='dbuser') """ if not database_exists(name): if locale not in run("locale -a").split(): require_locale(locale) restarted(_service_name()) create_database(name, owner, template=template, encoding=encoding, locale=locale)
def set_db_permissions(): """Set the db so user wsgi has all permissions. """ user = '******' dbname = 'changelog' if not user_exists(user): create_user(user, password='******') if not database_exists(dbname): create_database(dbname, user) grant_sql = 'GRANT ALL ON schema public to %s;' % user # assumption is env.repo_alias is also database name run('psql %s -c "%s"' % (dbname, grant_sql)) grant_sql = ( 'GRANT ALL ON ALL TABLES IN schema public to %s;' % user) # assumption is env.repo_alias is also database name run('psql %s -c "%s"' % (dbname, grant_sql)) grant_sql = ( 'GRANT ALL ON ALL SEQUENCES IN schema public to %s;' % user) run('psql %s -c "%s"' % (dbname, grant_sql))
def create_pgsql_user(username, password=None): if postgres.user_exists(username): raise KeyError("Username {} is taken".format(username)) if password is None: password = gen_pass(10) postgres.create_user(username, password=password, createdb=True) db_not_created = True db_name = "" while db_not_created: db_name = gen_pass(12) + "_DB" if not postgres.database_exists(db_name): postgres.create_database(db_name, owner=username) db_not_created = False print c.blue("Username="******"Password="******"DB Name =", bold=True),\ c.green(db_name, bold=True) return username, password, db_name
def deploy(app, environment, with_alembic=False): template_variables = get_template_variables( app, 'true' if environment == 'production' else 'false') require.users.user(app.name, shell='/bin/bash') require.postfix.server(env['host']) require.postgres.server() for pkg in [ 'libpq-dev', 'git', 'nginx', 'supervisor', 'openjdk-6-jre', 'make', 'sqlite3', 'curl', ]: require.deb.package(pkg) require.postgres.user(app.name, app.name) require.postgres.database(app.name, app.name) require.files.directory(app.venv, use_sudo=True) require.python.virtualenv(app.venv, use_sudo=True) require.files.directory(app.logs, use_sudo=True) with virtualenv(app.venv): sudo('pip install -U pip') require.python.package('gunicorn', use_sudo=True) install_repos('clld') install_repos(app.name) sudo('webassets -m %s.assets build' % app.name) require_bibutils(app) # # configure nginx: # require.files.directory(str(app.nginx_location.dirname()), owner='root', group='root', use_sudo=True) template_variables['auth'] = http_auth(app) if environment == 'test': create_file_as_root('/etc/nginx/sites-available/default', DEFAULT_SITE) create_file_as_root(app.nginx_location, LOCATION_TEMPLATE.format(**template_variables)) elif environment == 'production': create_file_as_root(app.nginx_site, SITE_TEMPLATE.format(**template_variables)) create_file_as_root('/etc/logrotate.d/{0}'.format(app.name), LOGROTATE_TEMPLATE.format(**template_variables)) maintenance(app, hours=app.deploy_duration, template_variables=template_variables) service.reload('nginx') # # TODO: replace with initialization of db from data repos! # if confirm('Copy files?', default=False): execute(copy_files, app) if not with_alembic and confirm('Recreate database?', default=False): supervisor(app, 'pause', template_variables) local('pg_dump -f /tmp/{0.name}.sql {0.name}'.format(app)) local('gzip -f /tmp/{0.name}.sql'.format(app)) require.files.file('/tmp/{0.name}.sql.gz'.format(app), source="/tmp/{0.name}.sql.gz".format(app)) sudo('gunzip -f /tmp/{0.name}.sql.gz'.format(app)) if postgres.database_exists(app.name): with cd('/var/lib/postgresql'): sudo('sudo -u postgres dropdb %s' % app.name) require.postgres.database(app.name, app.name) sudo('sudo -u {0.name} psql -f /tmp/{0.name}.sql -d {0.name}'.format( app)) else: if exists(app.src.joinpath('alembic.ini')): if confirm('Upgrade database?', default=False): supervisor(app, 'pause', template_variables) with virtualenv(app.venv): with cd(app.src): sudo('sudo -u {0.name} {1} -n production upgrade head'. format(app, app.bin('alembic'))) create_file_as_root( app.config, CONFIG_TEMPLATES[environment].format(**template_variables)) create_file_as_root(app.newrelic_config, NEWRELIC_TEMPLATE.format(**template_variables)) supervisor(app, 'run', template_variables) time.sleep(5) res = run('curl http://localhost:%s/_ping' % app.port) assert json.loads(res)['status'] == 'ok'
def deploy(app, environment, with_alembic=False): template_variables = get_template_variables( app, 'true' if environment == 'production' else 'false') require.users.user(app.name, shell='/bin/bash') require.postfix.server(env['host']) require.postgres.server() for pkg in [ 'libpq-dev', 'git', 'nginx', 'supervisor', 'openjdk-6-jre', 'make', 'sqlite3', 'curl', ]: require.deb.package(pkg) require.postgres.user(app.name, app.name) require.postgres.database(app.name, app.name) require.files.directory(app.venv, use_sudo=True) require.python.virtualenv(app.venv, use_sudo=True) require.files.directory(app.logs, use_sudo=True) with virtualenv(app.venv): sudo('pip install -U pip') require.python.package('gunicorn', use_sudo=True) install_repos('clld') install_repos(app.name) sudo('webassets -m %s.assets build' % app.name) require_bibutils(app) # # configure nginx: # require.files.directory( str(app.nginx_location.dirname()), owner='root', group='root', use_sudo=True) template_variables['auth'] = http_auth(app) if environment == 'test': create_file_as_root('/etc/nginx/sites-available/default', DEFAULT_SITE) create_file_as_root( app.nginx_location, LOCATION_TEMPLATE.format(**template_variables)) elif environment == 'production': create_file_as_root(app.nginx_site, SITE_TEMPLATE.format(**template_variables)) create_file_as_root( '/etc/logrotate.d/{0}'.format(app.name), LOGROTATE_TEMPLATE.format(**template_variables)) maintenance(app, hours=app.deploy_duration, template_variables=template_variables) service.reload('nginx') # # TODO: replace with initialization of db from data repos! # if confirm('Copy files?', default=False): execute(copy_files, app) if not with_alembic and confirm('Recreate database?', default=False): supervisor(app, 'pause', template_variables) local('pg_dump -f /tmp/{0.name}.sql {0.name}'.format(app)) local('gzip -f /tmp/{0.name}.sql'.format(app)) require.files.file( '/tmp/{0.name}.sql.gz'.format(app), source="/tmp/{0.name}.sql.gz".format(app)) sudo('gunzip -f /tmp/{0.name}.sql.gz'.format(app)) if postgres.database_exists(app.name): with cd('/var/lib/postgresql'): sudo('sudo -u postgres dropdb %s' % app.name) require.postgres.database(app.name, app.name) sudo('sudo -u {0.name} psql -f /tmp/{0.name}.sql -d {0.name}'.format(app)) else: if exists(app.src.joinpath('alembic.ini')): if confirm('Upgrade database?', default=False): supervisor(app, 'pause', template_variables) with virtualenv(app.venv): with cd(app.src): sudo('sudo -u {0.name} {1} -n production upgrade head'.format( app, app.bin('alembic'))) create_file_as_root( app.config, CONFIG_TEMPLATES[environment].format(**template_variables)) create_file_as_root( app.newrelic_config, NEWRELIC_TEMPLATE.format(**template_variables)) supervisor(app, 'run', template_variables) time.sleep(5) res = run('curl http://localhost:%s/_ping' % app.port) assert json.loads(res)['status'] == 'ok'
def deploy(app, environment, with_alembic=False, with_blog=False, with_files=True): with settings(warn_only=True): lsb_release = run('lsb_release -a') for codename in ['trusty', 'precise']: if codename in lsb_release: lsb_release = codename break else: if lsb_release != '{"status": "ok"}': # if this were the case, we'd be in a test! raise ValueError('unsupported platform: %s' % lsb_release) if environment == 'test' and app.workers > 3: app.workers = 3 template_variables = get_template_variables( app, monitor_mode='true' if environment == 'production' else 'false', with_blog=with_blog) require.users.user(app.name, shell='/bin/bash') require.postfix.server(env['host']) require.postgres.server() require.deb.packages(app.require_deb) require.postgres.user(app.name, app.name) require.postgres.database(app.name, app.name) require.files.directory(str(app.venv), use_sudo=True) with_pg_collkey = getattr(app, 'pg_collkey', False) if with_pg_collkey: pg_version = '9.1' if lsb_release == 'precise' else '9.3' if not exists('/usr/lib/postgresql/%s/lib/collkey_icu.so' % pg_version): require.deb.packages(['postgresql-server-dev-%s' % pg_version, 'libicu-dev']) upload_template_as_root( '/tmp/Makefile', 'pg_collkey_Makefile', dict(pg_version=pg_version)) require.files.file( '/tmp/collkey_icu.c', source=os.path.join( os.path.dirname(__file__), 'pg_collkey-v0.5', 'collkey_icu.c')) with cd('/tmp'): sudo('make') sudo('make install') init_pg_collkey(app) if lsb_release == 'precise': require.deb.package('python-dev') require.python.virtualenv(str(app.venv), use_sudo=True) else: require.deb.package('python3-dev') require.deb.package('python-virtualenv') if not exists(str(app.venv.joinpath('bin'))): sudo('virtualenv -q --python=python3 %s' % app.venv) require.files.directory(str(app.logs), use_sudo=True) if app.pages and not exists(str(app.pages)): with cd(str(app.home)): sudo('sudo -u {0} git clone https://github.com/clld/{0}-pages.git'.format(app.name)) with virtualenv(str(app.venv)): require.python.pip('6.0.6') sp = env['sudo_prefix'] env['sudo_prefix'] += ' -H' # set HOME for pip log/cache require.python.packages(app.require_pip, use_sudo=True) for name in [app.name] + getattr(app, 'dependencies', []): pkg = '-e git+git://github.com/clld/%s.git#egg=%s' % (name, name) require.python.package(pkg, use_sudo=True) env['sudo_prefix'] = sp sudo('webassets -m %s.assets build' % app.name) require_bibutils(app) # # configure nginx: # require.files.directory( os.path.dirname(str(app.nginx_location)), owner='root', group='root', use_sudo=True) restricted, auth = http_auth(app) if restricted: template_variables['auth'] = auth template_variables['admin_auth'] = auth if environment == 'test': upload_template_as_root('/etc/nginx/sites-available/default', 'nginx-default.conf') template_variables['SITE'] = False upload_template_as_root( app.nginx_location, 'nginx-app.conf', template_variables) elif environment == 'production': template_variables['SITE'] = True upload_template_as_root(app.nginx_site, 'nginx-app.conf', template_variables) upload_template_as_root( '/etc/logrotate.d/{0}'.format(app.name), 'logrotate.conf', template_variables) maintenance(app, hours=app.deploy_duration, template_variables=template_variables) service.reload('nginx') # # TODO: replace with initialization of db from data repos! # if with_files: if confirm('Copy files?', default=False): execute(copy_files, app) if not with_alembic and confirm('Recreate database?', default=False): local('pg_dump -x -O -f /tmp/{0.name}.sql {0.name}'.format(app)) local('gzip -f /tmp/{0.name}.sql'.format(app)) require.files.file( '/tmp/{0.name}.sql.gz'.format(app), source="/tmp/{0.name}.sql.gz".format(app)) sudo('gunzip -f /tmp/{0.name}.sql.gz'.format(app)) supervisor(app, 'pause', template_variables) if postgres.database_exists(app.name): with cd('/var/lib/postgresql'): sudo('sudo -u postgres dropdb %s' % app.name) require.postgres.database(app.name, app.name) if with_pg_collkey: init_pg_collkey(app) sudo('sudo -u {0.name} psql -f /tmp/{0.name}.sql -d {0.name}'.format(app)) else: if exists(app.src.joinpath('alembic.ini')): if confirm('Upgrade database?', default=False): # Note: stopping the app is not strictly necessary, because the alembic # revisions run in separate transactions! supervisor(app, 'pause', template_variables) with virtualenv(str(app.venv)): with cd(str(app.src)): sudo('sudo -u {0.name} {1} -n production upgrade head'.format( app, app.bin('alembic'))) if confirm('Vacuum database?', default=False): if confirm('VACUUM FULL?', default=False): sudo('sudo -u postgres vacuumdb -f -z -d %s' % app.name) else: sudo('sudo -u postgres vacuumdb -z -d %s' % app.name) template_variables['TEST'] = {'test': True, 'production': False}[environment] upload_template_as_root(app.config, 'config.ini', template_variables) upload_template_as_root(app.newrelic_config, 'newrelic.ini', template_variables) supervisor(app, 'run', template_variables) time.sleep(5) res = run('curl http://localhost:%s/_ping' % app.port) assert json.loads(res)['status'] == 'ok'