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 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 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 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'
