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_install_dbserver_pg_centos(): if not get_os().startswith('centos') and not get_os().startswith('rocky'): pytest.skip() if get_pg_type() != 'PG': pytest.skip() host = get_primary() pg_version = get_pg_version() packages = [ 'glibc-common', 'ca-certificates', 'postgresql%s' % pg_version, 'postgresql%s-server' % pg_version, 'postgresql%s-contrib' % pg_version, 'sslutils_%s' % pg_version, ] if get_os() == 'centos7': packages += [ 'python-pycurl', 'libselinux-python', 'python-psycopg2', 'python-ipaddress' ] elif get_os() == 'rocky8': packages += [ 'python3-pycurl', 'python3-libselinux', 'python3-psycopg2' ] for package in packages: assert host.package(package).is_installed, \ "Package %s not installed" % packages
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_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_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_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_init_dbserver_socket(): host = get_primary() if get_pg_type() == 'PG': sockets = ["tcp://5432", "unix:///var/run/postgresql/.s.PGSQL.5432"] elif get_pg_type() == 'EPAS': sockets = [ "tcp://5444", ] if get_os().startswith('centos') or get_os().startswith('rocky'): sockets.append("unix:///var/run/edb/as%s/.s.PGSQL.5444" % get_pg_version()) elif get_os().startswith('debian') or get_os().startswith('ubuntu'): sockets.append("unix:///var/run/edb-as/.s.PGSQL.5444") for socket in sockets: assert host.socket(socket).is_listening, \ "Postgres is not listening on %s" % socket
def test_setup_pgpool_PG_packages(): if get_pg_type() != 'PG': pytest.skip() host = get_pgpool2()[0] packages = ['pgpool-II', 'openssl'] for package in packages: assert host.package(package).is_installed, \ "Package %s not installed" % packages
def test_setup_pgpool_EPAS_packages(): if get_pg_type() != 'EPAS': pytest.skip() host = get_pgpool2()[0] packages = ['edb-pgpool43', 'openssl'] for package in packages: assert host.package(package).is_installed, \ "Package %s not installed" % packages
def test_setup_pemserver_epas(): if get_pg_type() != 'EPAS': pytest.skip() host = get_pemserver() packages = ['edb-pem-server'] for package in packages: assert host.package(package).is_installed, \ "Package %s not installed" % packages
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_pgpool2_PG(): if get_pg_type() != 'PG': pytest.skip() host = get_pgpool2()[0] service = 'pgpool-II' assert host.service(service).is_running, \ "pgpool2 service not running" assert host.service(service).is_enabled, \ "pgpool2 service not enabled"
def test_setup_pgpool2_EPAS(): if get_pg_type() != 'EPAS': pytest.skip() host = get_pgpool2()[0] service = 'edb-pgpool-4.3' assert host.service(service).is_running, \ "pgpool2 service not running" assert host.service(service).is_enabled, \ "pgpool2 service not enabled"
def test_init_dbserver_service(): host = get_primary() pg_version = get_pg_version() if get_os().startswith('centos') or get_os().startswith('rocky'): if get_pg_type() == 'PG': service = 'postgresql-%s' % pg_version elif get_pg_type() == 'EPAS': service = 'edb-as-%s' % pg_version elif get_os().startswith('debian') or get_os().startswith('ubuntu'): if get_pg_type() == 'PG': service = 'postgresql@%s-main' % pg_version elif get_pg_type() == 'EPAS': service = 'edb-as@%s-main' % pg_version assert host.service(service).is_running, \ "Postgres service not running" assert host.service(service).is_enabled, \ "Postgres service not enabled"
def test_setup_repo_pgdg_debian(): if not (get_os().startswith('debian') or get_os().startswith('ubuntu')): pytest.skip() if get_pg_type() != 'PG': pytest.skip() host = get_primary() cmd = host.run("grep -rhE ^deb /etc/apt/sources.list*") assert cmd.succeeded, \ "Unable to list the package repository list" assert "apt.postgresql.org" in cmd.stdout, \ "Access to the PGDG package repository not configured"
def test_setup_repo_pgdg_centos(): if not get_os().startswith('centos') and not get_os().startswith('rocky'): pytest.skip() if get_pg_type() != 'PG': pytest.skip() host = get_primary() cmd = host.run("yum repolist") assert cmd.succeeded, \ "Unable to list the package repository list on %s" % host assert "PostgreSQL common RPMs for RHEL / Rocky 8" in cmd.stdout, \ "Access to the PGDG package repository not configured"
def test_setup_pemserver_pg(): if get_pg_type() != 'PG': pytest.skip() host = get_pemserver() pg_version = get_pg_version() packages = [ 'edb-pem-server', 'sslutils_%s' % pg_version, 'postgresql%s-contrib' % pg_version, ] for package in packages: assert host.package(package).is_installed, \ "Package %s not installed" % packages
def test_setup_efm_service(): pg_user = '******' if get_pg_type() == 'EPAS': pg_user = '******' host = get_primary() nodes = [node for node in get_pg_cluster_nodes()] with host.sudo(pg_user): cmd = host.run( '/usr/edb/efm-4.4/bin/efm cluster-status main | grep UP') result = cmd.stdout.strip().split('\n') assert len(result) == len(nodes), \ "EFM service has not started on all the nodes"
def test_setup_efm_user(): 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 * from pg_user where usename = 'efm' and userepl = 't'" cmd = host.run('psql -At -h %s -c "%s" postgres' % (socket_dir, query)) result = cmd.stdout.strip() assert len(result) > 0, \ "efm user was not succesfully created"
def test_setup_replication_stat_replication(): pg_user = '******' if get_pg_type() == 'EPAS': pg_user = '******' host = get_primary() rep_count = len(get_standbys()) socket_dir = get_pg_unix_socket_dir() with host.sudo(pg_user): query = "Select application_name from pg_stat_replication" cmd = host.run('psql -At -h %s -c "%s" postgres' % (socket_dir, query)) result = cmd.stdout.strip().split('\n') assert len(result) == rep_count, \ "Replication was not successful on master"
def test_install_dbserver_epas_centos(): if not get_os().startswith('centos') and not get_os().startswith('rocky'): pytest.skip() if get_pg_type() != 'EPAS': pytest.skip() host = get_primary() pg_version = int(get_pg_version()) packages = [ 'edb-as%s-server' % pg_version, 'edb-as%s-server-core' % pg_version, 'edb-as%s-server-contrib' % pg_version, 'edb-as%s-server-libs' % pg_version, 'edb-as%s-server-client' % pg_version, 'edb-as%s-server-sslutils' % pg_version, 'edb-as%s-server-indexadvisor' % pg_version, 'edb-as%s-server-sqlprofiler' % pg_version, 'edb-as%s-server-sqlprotect' % pg_version, 'edb-as%s-server-sslutils' % pg_version, ] if get_os() == 'centos7': packages += [ 'python2-pip', 'python-psycopg2', 'python-ipaddress', ] elif get_os() == 'rocky8': packages += [ 'python3-pip', 'python3-psycopg2', ] if pg_version > 10: packages += [ 'edb-as%s-server-llvmjit' % pg_version, ] if pg_version > 10 and pg_version < 14: packages += [ 'edb-as%s-server-edb-modules' % pg_version, ] elif pg_version >= 14: packages += [ 'edb-as%s-server-edb_wait_states' % pg_version, ] for package in packages: assert host.package(package).is_installed, \ "Package %s not installed" % packages
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_setup_replication_slots(): 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_replication_slots" cmd = host.run('psql -At -h %s -c "%s" postgres' % (socket_dir, query)) result = cmd.stdout.strip().split('\n') assert len(result) > 0, \ "Replication did not create replication slots"
def test_setup_efm_pg_read_all_settings(): 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 = r"\du" cmd = host.run('psql -At -h %s -c "%s" postgres' % (socket_dir, query)) result = cmd.stdout.strip().replace('\nProfile', "").split('\n') efm_index = [idx for idx, role in enumerate(result) if 'efm' in role][0] assert 'pg_read_all_settings' in result[efm_index], \ "EFM role is not a pg_read_all_settings"
def test_setup_pemserver_pemagent(): pg_user = '******' pg_group = 'postgres' if get_pg_type() == 'EPAS': pg_user = '******' pg_group = 'enterprisedb' host = get_pemserver() socket_dir = get_pg_unix_socket_dir() with host.sudo(pg_user): query = "Select * from pg_user where usename = 'agent1'" cmd = host.run('psql -At -h %s -c "%s" postgres' % (socket_dir, query)) result = cmd.stdout.strip() assert len(result) > 0, \ "pemagent user was not succesfully created"
def test_setup_pemserver_creation(): pg_user = '******' pg_group = 'postgres' if get_pg_type() == 'EPAS': pg_user = '******' pg_group = 'enterprisedb' host = get_pemserver() socket_dir = get_pg_unix_socket_dir() with host.sudo(pg_user): query = r"\list" cmd = host.run('psql -At -h %s -c "%s" postgres | grep pem' % (socket_dir, query)) result = cmd.stdout.strip() assert len(result) > 0, \ "PEM database was not succesfully 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_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_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_setup_pemagent_agents(): pg_user = '******' pg_group = 'postgres' nodes = [node for node in get_pg_nodes()] if get_pg_type() == 'EPAS': pg_user = '******' pg_group = 'enterprisedb' host = get_pemserver() socket_dir = get_pg_unix_socket_dir() with host.sudo(pg_user): query = "Select id from pem.agent" cmd = host.run('psql -At -h %s -c "%s" pem' % (socket_dir, query)) result = cmd.stdout.strip().split('\n') # Seeing if number of agents is equal to the number of nodes assert len(result) == len(nodes), \ "Pem agents were created sucessfuly."