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!")