def test_setup_barmanserver_config_file(): host = get_barmanserver() ansible_vars = load_ansible_vars() config_file = ansible_vars['barman_configuration_file'] assert host.file(config_file).exists, \ "%s does not exist" % config_file
def test_setup_pgpool_users(): ansible_vars = load_ansible_vars() pgpool2_user = ansible_vars['pgpool2_users'][0]['name'] pgpool2_password = ansible_vars['pgpool2_users'][0]['pass'] pgpool2_port = ansible_vars['pgpool2_port'] pg_user = '******' pg_group = 'postgres' if get_pg_type() == 'EPAS': pg_user = '******' pg_group = 'enterprisedb' pgpool2_address = get_pgpool2()[0] address = str(pgpool2_address).strip("<>").split('//')[1] host = get_primary() with host.sudo(pg_user): query = "Select usename from pg_user where usename = '%s' or usename ='%s'" % ( 'pgpool', 'pgpool2') cmd = host.run( 'PGPASSWORD=%s psql -At -U %s -h %s -p %s -c "%s" postgres' % (pgpool2_password, pgpool2_user, address, pgpool2_port, query)) result = cmd.stdout.strip().split('\n') assert len(result) == 2, \ "pgpool users was not created sucessfully."
def test_manage_pgbouncer_auth_file(): ansible_vars = load_ansible_vars() pgbouncer_user = ansible_vars['pgbouncer_auth_user_list'][0]['username'] pgbouncer_password = ansible_vars['pgbouncer_auth_user_list'][0][ 'password'] pgbouncer_port = ansible_vars['pgbouncer_listen_port'] pgbouncer_auth_file = ansible_vars['pgbouncer_auth_file'] pg_user = '******' pg_group = 'postgres' if get_pg_type() == 'EPAS': pg_user = '******' pg_group = 'enterprisedb' pgbouncer_address = get_pgbouncer()[0] address = str(pgbouncer_address).strip("<>").split('//')[1] host = get_primary() with host.sudo(pg_user): query = "SHOW config" cmd = host.run( 'PGPASSWORD=%s psql -At -U %s -h %s -p %s -c "%s" pgbouncer | grep %s' % (pgbouncer_password, pgbouncer_user, address, pgbouncer_port, query, 'auth_file')) result = cmd.stdout.strip() assert pgbouncer_auth_file in result, \ "pgbouncer auth file was not configured properly."
def test_manage_pgbouncer_users(): ansible_vars = load_ansible_vars() pgbouncer_user = ansible_vars['pgbouncer_auth_user_list'][0]['username'] pgbouncer_password = ansible_vars['pgbouncer_auth_user_list'][0][ 'password'] pgbouncer_port = ansible_vars['pgbouncer_listen_port'] pg_user = '******' pg_group = 'postgres' if get_pg_type() == 'EPAS': pg_user = '******' pg_group = 'enterprisedb' pgbouncer_address = get_pgbouncer()[0] address = str(pgbouncer_address).strip("<>").split('//')[1] host = get_primary() with host.sudo(pg_user): query = "SHOW users" cmd = host.run( 'PGPASSWORD=%s psql -At -U %s -h %s -p %s -c "%s" pgbouncer | grep %s' % (pgbouncer_password, pgbouncer_user, address, pgbouncer_port, query, 'pgbouncer')) result = cmd.stdout.strip() result = result.split('\n') assert len(result) == 2, \ "pgbouncer users have not been successfully created."
def test_setup_pgpool_loadbalance(): ansible_vars = load_ansible_vars() pgpool2_user = ansible_vars['pgpool2_users'][0]['name'] pgpool2_password = ansible_vars['pgpool2_users'][0]['pass'] pgpool2_port = ansible_vars['pgpool2_port'] pg_user = '******' pg_group = 'postgres' if get_pg_type() == 'EPAS': pg_user = '******' pg_group = 'enterprisedb' pgpool2_address = get_pgpool2()[0] address = str(pgpool2_address).strip("<>").split('//')[1] host = get_primary() with host.sudo(pg_user): query = "PGPOOL SHOW load_balance_mode;" cmd = host.run( "PGPASSWORD=%s psql -At -U %s -h %s -p %s -c '%s' postgres" % (pgpool2_password, pgpool2_user, address, pgpool2_port, query)) result = cmd.stdout.strip() assert result == 'on', \ "Load Balance is not enabled."
def test_init_dbserver_files(): ansible_vars = load_ansible_vars() pg_data = ansible_vars['pg_data'] pg_wal = ansible_vars['pg_wal'] pg_user = '******' pg_group = 'postgres' if get_pg_type() == 'EPAS': pg_user = '******' pg_group = 'enterprisedb' host = get_primary() for pg_dir in [pg_data, pg_wal]: assert host.file(pg_dir).exists, \ "%s does not exist" % pg_dir assert host.file(pg_dir).is_directory, \ "%s is not a directory" % pg_dir assert host.file(pg_dir).user == pg_user, \ "%s is not owned by postgres" % pg_dir assert host.file(pg_dir).group == pg_group, \ "%s group is not postgres" % pg_dir # Test PGWAL assert host.file("%s/pg_wal" % pg_data).exists, \ "%s/pg_wal does not exist" % pg_data assert host.file("%s/pg_wal" % pg_data).is_symlink, \ "%s/pg_wal is not a symlink" % pg_data assert host.file("%s/pg_wal" % pg_data).linked_to == pg_wal, \ "%s/pg_wal is not linked to %s" % (pg_data, pg_wal)
def test_manage_dbserver_files(): ansible_vars = load_ansible_vars() pg_user = '******' pg_group = 'postgres' pg_profile_path = get_pg_profile_dir() pg_sql_script = ansible_vars['pg_sql_scripts'][0]['file_path'] profile_prefix = 'pgsql' if get_pg_type() == 'EPAS': pg_user = '******' pg_group = 'enterprisedb' profile_prefix = 'enterprisedb' host = get_primary() #Testing if '.psqlrc' file was created properly assert host.file('%s/.psqlrc' % pg_profile_path).exists, \ ".psqlrc does not exist" assert host.file('%s/.psqlrc' % pg_profile_path).user == pg_user, \ ".psqlrc is not owned by %s" % pg_user assert host.file('%s/.psqlrc' % pg_profile_path).group == pg_group, \ ".psqlrc group is not in %s" % pg_user #Testing if shell profile was created properly assert host.file('%s/.%s_profile' % (pg_profile_path, profile_prefix)).exists, \ "%s_profile does not exist" % pg_user assert host.file('%s/.%s_profile' % (pg_profile_path, profile_prefix)).user == pg_user, \ "%s_profile is not owned by %s" % (pg_user, pg_user) assert host.file('%s/.%s_profile' % (pg_profile_path, profile_prefix)).group == pg_group, \ "%s_profile group is not in %s" % (pg_user, pg_user) #Testing if '.pgpass' file was created properly assert host.file('%s/.pgpass' % pg_profile_path).exists, \ ".pgpass does not exist" assert host.file('%s/.pgpass' % pg_profile_path).user == pg_user, \ ".psqlrc is not owned by %s" % pg_user assert host.file('%s/.pgpass' % pg_profile_path).group == pg_group, \ ".psqlrc group is not in %s" % pg_group assert host.file('%s/.pgpass' % pg_profile_path).exists, \ ".pgpass does not exist" #Testing if files were properly copied over assert host.file('%s' % pg_sql_script).exists, \ "File(s) were not properly copied over"
def test_setup_barmanserver_sudo(): host = get_barmanserver() ansible_vars = load_ansible_vars() barman_user = ansible_vars['barman_user'] with host.sudo(barman_user): cmd = host.run('barman -h') result = cmd.stdout.strip() assert len(result) > 0, \ "%s could not be used as a sudo user" % barman_user
def test_init_dbserver_data_directory(): ansible_vars = load_ansible_vars() pg_data = ansible_vars['pg_data'] pg_user = '******' if get_pg_type() == 'EPAS': pg_user = '******' host = get_primary() socket_dir = get_pg_unix_socket_dir() with host.sudo(pg_user): query = "SELECT setting FROM pg_settings WHERE name = 'data_directory'" cmd = host.run('psql -At -h %s -c "%s" postgres' % (socket_dir, query)) data_directory = cmd.stdout.strip() assert host.file(data_directory).linked_to == pg_data, \ "Postgres data_directory is not linked to '%s'" % pg_data
def test_manage_dbserver_create_user(): ansible_vars = load_ansible_vars() pg_user = '******' pg_group = 'postgres' pg_created_user = ansible_vars['pg_users'][0]['name'] if get_pg_type() == 'EPAS': pg_user = '******' pg_group = 'enterprisedb' host = get_primary() socket_dir = get_pg_unix_socket_dir() with host.sudo(pg_user): query = "Select * from pg_user WHERE usename='%s'" % pg_created_user cmd = host.run('psql -At -h %s -c "%s" postgres' % (socket_dir, query)) result = cmd.stdout.strip() assert len(result) > 0, \ "User was not sucessfully created."
def test_manage_pgpool_pcp_node_count(): ansible_vars = load_ansible_vars() pcp_user = ansible_vars['pcp_users'][0]['name'] pcp_pass = ansible_vars['pcp_users'][0]['pass'] pg_user = '******' pg_group = 'postgres' if get_pg_type() == 'EPAS': pg_user = '******' pg_group = 'enterprisedb' host = get_pgpool2()[0] with host.sudo(pg_user): cmd = host.run("pcp_node_count -U %s -h localhost -w" % pcp_user) result = cmd.stdout.strip() assert result == '1', \ "Database node count is not equal to 1"
def test_manage_dbserver_conf_params(): ansible_vars = load_ansible_vars() pg_conf_param = ansible_vars['pg_postgres_conf_params'][0]['name'] pg_user = '******' pg_group = 'postgres' if get_pg_type() == 'EPAS': pg_user = '******' pg_group = 'enterprisedb' host = get_primary() socket_dir = get_pg_unix_socket_dir() with host.sudo(pg_user): query = "Show %s" % pg_conf_param cmd = host.run('psql -At -h %s -c "%s" postgres' % (socket_dir, query)) result = cmd.stdout.strip() assert len(result) > 0, \ "Database parameter %s does not exist" % pg_conf_param
def test_manage_dbserver_database(): ansible_vars = load_ansible_vars() pg_database = ansible_vars['pg_databases'][0]['name'] pg_user = '******' pg_group = 'postgres' if get_pg_type() == 'EPAS': pg_user = '******' pg_group = 'enterprisedb' host = get_primary() socket_dir = get_pg_unix_socket_dir() with host.sudo(pg_user): query = "Select * from pg_database WHERE datname = '%s'" % pg_database cmd = host.run('psql -At -h %s -c "%s" postgres' % (socket_dir, query)) result = cmd.stdout.strip() assert len(result) > 0, \ "Query was not succesfully executed" % pg_database
def test_manage_dbserver_pg_extension(): ansible_vars = load_ansible_vars() pg_extension = ansible_vars['pg_extensions'][0]['name'] pg_user = '******' pg_group = 'postgres' if get_pg_type() == 'EPAS': pg_user = '******' pg_group = 'enterprisedb' host = get_primary() socket_dir = get_pg_unix_socket_dir() with host.sudo(pg_user): query = "Select * from pg_extension WHERE extname = '%s'" % pg_extension cmd = host.run('psql -At -h %s -c "%s" postgres' % (socket_dir, query)) result = cmd.stdout.strip() assert len(result) > 0, \ "PG extension %s does not exist" % pg_extension
def test_manage_dbserver_pg_grant_roles(): ansible_vars = load_ansible_vars() pg_role = ansible_vars['pg_grant_roles'][0]['role'] pg_user = '******' pg_group = 'postgres' if get_pg_type() == 'EPAS': pg_user = '******' pg_group = 'enterprisedb' host = get_primary() socket_dir = get_pg_unix_socket_dir() with host.sudo(pg_user): query = "Select rolname FROM pg_roles WHERE pg_has_role('%s', oid, 'member') AND rolname = '%s'" % ( pg_user, pg_role) cmd = host.run('psql -At -h %s -c "%s" postgres' % (socket_dir, query)) result = cmd.stdout.strip() assert len(result) > 0, \ "User %s has not been granted the %s role" % (pg_user, pg_role)
def test_manage_pgpool_pcp_user(): ansible_vars = load_ansible_vars() pcp_user = ansible_vars['pcp_users'][0]['name'] pcp_pass = ansible_vars['pcp_users'][0]['pass'] pg_user = '******' pg_group = 'postgres' if get_pg_type() == 'EPAS': pg_user = '******' pg_group = 'enterprisedb' host = get_pgpool2()[0] with host.sudo(pg_user): cmd = host.run("touch ~/.pcppass") cmd = host.run("echo 'localhost:9898:%s:%s' >> ~/.pcppass" % (pcp_user, pcp_pass)) cmd = host.run("chmod 600 ~/.pcppass ") cmd = host.run("pcp_node_info -U %s -h localhost -w" % pcp_user) result = cmd.stdout.strip() assert len(result) > 0, \ "pcp command succesfully works"
def test_manage_dbserver_sql_script(): ansible_vars = load_ansible_vars() pg_user = '******' pg_group = 'postgres' pg_sql_script = ansible_vars['pg_sql_scripts'][0]['file_path'] pg_script_table = ansible_vars['pg_script_table'] if get_pg_type() == 'EPAS': pg_user = '******' pg_group = 'enterprisedb' host = get_primary() socket_dir = get_pg_unix_socket_dir() with host.sudo(pg_user): cmd = host.run('cat %s' % pg_sql_script) query = cmd.stdout.strip() cmd = host.run('psql -At -h %s -c "%s" postgres' % (socket_dir, query)) query = "Select * from pg_tables WHERE tablename = '%s'" % pg_script_table cmd = host.run('psql -At -h %s -c "%s" postgres' % (socket_dir, query)) result = cmd.stdout.strip() assert len(result) > 0, \ "SQL scripts were not succesfully executed"