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_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_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_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_setup_efm_redhat(): if not get_os().startswith('centos') and not get_os().startswith('rocky'): pytest.skip() host = get_primary() packages = ['java-1.8.0-openjdk', 'mailx', 'edb-efm44'] for package in packages: assert host.package(package).is_installed, \ "Package %s not installed" % packages
def test_setup_efm_debian(): if not (get_os().startswith('debian') or get_os().startswith('ubuntu')): pytest.skip() host = get_primary() packages = ['openjdk-8-jdk', 'bsd-mailx', 'edb-efm44'] for package in packages: assert host.package(package).is_installed, \ "Package %s not installed" % packages
def test_setup_pemagent_service(): host = get_primary() pg_version = get_pg_version() service = 'pemagent' assert host.service(service).is_running, \ "Pemagent service not running" assert host.service(service).is_enabled, \ "Pemagent service not enabled"
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_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_repo_edb_centos(): if not EDB_ENABLE_REPO: pytest.skip() if not get_os().startswith('centos') and not get_os().startswith('rocky'): pytest.skip() host = get_primary() cmd = host.run("yum repolist") assert cmd.succeeded, \ "Unable to list the package repository list" assert "EnterpriseDB RPMs" in cmd.stdout, \ "Access to the EDB package repository not configured"
def test_setup_repo_edb_debian(): if not EDB_ENABLE_REPO: pytest.skip() if not (get_os().startswith('debian') or get_os().startswith('ubuntu')): 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.enterprisedb.com" in cmd.stdout, \ "Access to the EDB package repository not configured"
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_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_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_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_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_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_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_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_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_install_dbserver_pg_debian(): if not (get_os().startswith('debian') or get_os().startswith('ubuntu')): pytest.skip() if get_pg_type() != 'PG': pytest.skip() host = get_primary() pg_version = get_pg_version() packages = [ 'ca-certificates', 'python3-pycurl', 'python3-psycopg2', 'postgresql-%s' % pg_version, 'postgresql-server-dev-%s' % pg_version, 'postgresql-%s-sslutils' % pg_version, ] if get_os() in ['debian9', 'debian10']: packages += ['python-psycopg2', 'python-ipaddress'] for package in packages: assert host.package(package).is_installed, \ "Package %s not installed" % packages
def test_manage_dbserver_hba_file(): 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 hba_file" cmd = host.run('psql -At -h %s -c "%s" postgres' % (socket_dir, query)) result = cmd.stdout.strip() cmd = host.run('grep postgres %s' % result) result = cmd.stdout.strip() assert len(result) > 0, \ "pg_hba.conf file was not sucessfully modified"
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)