def init_postgres(db_uri):
"""Initializes PostgreSQL database from provided URI.
New user and database will be created, if needed. It also creates uuid-ossp extension.
"""
hostname, port, db, username, password = data_utils.explode_db_uri(db_uri) # pylint: disable=unused-variable
if hostname not in ['localhost', '127.0.0.1']:
raise Exception('Cannot configure a remote database')
# Checking if user already exists
retv = subprocess.check_output(
'sudo -u postgres psql -t -A -c'
'"SELECT COUNT(*) FROM pg_user WHERE usename = \'%s\';"' % username,
shell=True)
if retv == '0':
exit_code = subprocess.call(
'sudo -u postgres psql -c '
'"CREATE ROLE %s PASSWORD \'%s\' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;"'
% (username, password),
shell=True,
)
if exit_code != 0:
raise Exception('Failed to create PostgreSQL user!')
# Checking if database exists
exit_code = subprocess.call('sudo -u postgres psql -c "\\q" %s' % db,
shell=True)
if exit_code != 0:
exit_code = subprocess.call('sudo -u postgres createdb -O %s %s' %
(username, db),
shell=True)
if exit_code != 0:
raise Exception('Failed to create PostgreSQL database!')
def init_postgres(db_uri):
"""Initializes PostgreSQL database from provided URI.
New user and database will be created, if needed. It also creates uuid-ossp extension.
"""
hostname, db, username, password = data_utils.explode_db_uri(db_uri)
if hostname not in ["localhost", "127.0.0.1"]:
raise Exception("Cannot configure a remote database")
# Checking if user already exists
retv = subprocess.check_output(
"sudo -u postgres psql -t -A -c \"SELECT COUNT(*) FROM pg_user WHERE usename = '%s';\"" % username, shell=True
)
if retv[0] == "0":
exit_code = subprocess.call(
"sudo -u postgres psql -c \"CREATE ROLE %s PASSWORD '%s' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;\""
% (username, password),
shell=True,
)
if exit_code != 0:
raise Exception("Failed to create PostgreSQL user!")
# Checking if database exists
exit_code = subprocess.call('sudo -u postgres psql -c "\q" %s' % db, shell=True)
if exit_code != 0:
exit_code = subprocess.call("sudo -u postgres createdb -O %s %s" % (username, db), shell=True)
if exit_code != 0:
raise Exception("Failed to create PostgreSQL database!")
# Creating database extension
exit_code = subprocess.call(
'sudo -u postgres psql -t -A -c "CREATE EXTENSION IF NOT EXISTS \\"%s\\";" %s' % ("uuid-ossp", db), shell=True
)
if exit_code != 0:
raise Exception("Failed to create PostgreSQL extension!")
def full_db(location, rotate=False):
"""Create complete dump of PostgreSQL database.
This command creates database dump using pg_dump and puts it into specified directory
(default is *backup*). It's also possible to remove all previously created backups
except two most recent ones. If you want to do that, set *rotate* argument to True.
File with a dump will be a tar archive with a timestamp in the name: `%Y%m%d-%H%M%S.tar.bz2`.
"""
create_path(location)
FILE_PREFIX = "cb-backup-"
db_hostname, db_name, db_username, db_password = explode_db_uri(current_app.config['SQLALCHEMY_DATABASE_URI'])
print('Creating database dump in "%s"...' % location)
# Executing pg_dump command
# More info about it is available at http://www.postgresql.org/docs/9.3/static/app-pgdump.html
dump_file = os.path.join(location, FILE_PREFIX + strftime("%Y%m%d-%H%M%S", gmtime()))
if subprocess.call('pg_dump -Ft "%s" > "%s.tar"' % (db_name, dump_file), shell=True) != 0:
raise Exception("Failed to create database dump!")
# Compressing created dump
if subprocess.call('bzip2 "%s.tar"' % dump_file, shell=True) != 0:
raise Exception("Failed to create database dump!")
print('Created %s.tar.bz2' % dump_file)
if rotate:
print("Removing old backups (except two latest)...")
remove_old_archives(location, "%s[0-9]+-[0-9]+.tar" % FILE_PREFIX,
is_dir=False, sort_key=lambda x: os.path.getmtime(x))
print("Done!")
def init_postgres(db_uri):
"""Initializes PostgreSQL database from provided URI.
New user and database will be created, if needed. It also creates uuid-ossp extension.
"""
hostname, port, db, username, password = data_utils.explode_db_uri(db_uri) # pylint: disable=unused-variable
if hostname not in ['localhost', '127.0.0.1']:
raise Exception('Cannot configure a remote database')
# Checking if user already exists
retv = subprocess.check_output('sudo -u postgres psql -t -A -c'
'"SELECT COUNT(*) FROM pg_user WHERE usename = \'%s\';"' %
username, shell=True)
if retv == '0':
exit_code = subprocess.call(
'sudo -u postgres psql -c '
'"CREATE ROLE %s PASSWORD \'%s\' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;"' %
(username, password),
shell=True,
)
if exit_code != 0:
raise Exception('Failed to create PostgreSQL user!')
# Checking if database exists
exit_code = subprocess.call('sudo -u postgres psql -c "\\q" %s' % db, shell=True)
if exit_code != 0:
exit_code = subprocess.call('sudo -u postgres createdb -O %s %s' % (username, db), shell=True)
if exit_code != 0:
raise Exception('Failed to create PostgreSQL database!')
def test_explode_db_uri(self):
uri = "postgresql://*****:*****@localhost:5432/cb"
hostname, db_name, username, password = utils.explode_db_uri(uri)
self.assertEqual(hostname, "localhost")
self.assertEqual(db_name, "cb")
self.assertEqual(username, "cb_user")
self.assertEqual(password, "cb_password")
def create_extension(db_uri):
host, port, db, username, password = data_utils.explode_db_uri(db_uri)
psql_cmd = "psql -h %s -p %s -U %s -W %s %s" % (host, port, username, password, db)
exit_code = subprocess.call(
'%s -t -A -c "CREATE EXTENSION IF NOT EXISTS \\"%s\\";" %s' %
(psql_cmd, 'uuid-ossp', db),
shell=True,
)
if exit_code != 0:
raise Exception('Failed to create PostgreSQL extension!')
def create_extension(db_uri):
host, port, db, username, password = data_utils.explode_db_uri(db_uri)
psql_cmd = "psql -h %s -p %s -U %s -W %s %s" % (host, port, username,
password, db)
exit_code = subprocess.call(
'%s -t -A -c "CREATE EXTENSION IF NOT EXISTS \\"%s\\";" %s' %
(psql_cmd, 'uuid-ossp', db),
shell=True)
if exit_code != 0:
raise Exception('Failed to create PostgreSQL extension!')
def full_db(location, rotate=False):
"""Create complete dump of PostgreSQL database.
This command creates database dump using pg_dump and puts it into specified directory
(default is *backup*). It's also possible to remove all previously created backups
except two most recent ones. If you want to do that, set *rotate* argument to True.
File with a dump will be a tar archive with a timestamp in the name: `%Y%m%d-%H%M%S.tar.bz2`.
"""
create_path(location)
FILE_PREFIX = "cb-backup-"
db_hostname, db_port, db_name, db_username, _ = \
explode_db_uri(current_app.config['SQLALCHEMY_DATABASE_URI'])
print('Creating database dump in "%s"...' % location)
# Executing pg_dump command
# More info about it is available at http://www.postgresql.org/docs/9.3/static/app-pgdump.html
dump_file = os.path.join(location,
FILE_PREFIX + strftime("%Y%m%d-%H%M%S", gmtime()))
print(
'pg_dump -h "%s" -p "%s" -U "%s" -d "%s" -Ft > "%s.tar"' %
(db_hostname, db_port, db_username, db_name, dump_file), )
result = subprocess.call(
'pg_dump -h "%s" -p "%s" -U "%s" -d "%s" -Ft > "%s.tar"' %
(db_hostname, db_port, db_username, db_name, dump_file),
shell=True,
)
if result != 0:
raise Exception("Failed to create database dump!")
# Compressing created dump
result = subprocess.call('bzip2 "%s.tar"' % dump_file, shell=True)
if result != 0:
raise Exception("Failed to create database dump!")
print('Created %s.tar.bz2' % dump_file)
if rotate:
print("Removing old backups (except two latest)...")
remove_old_archives(location,
"%s[0-9]+-[0-9]+.tar" % FILE_PREFIX,
is_dir=False,
sort_key=os.path.getmtime)
print("Done!")
