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."
