Overview

The goal for this process is to split complex reporting queries from the production database. 

Prerequisite

• Your qTest Manager and qTest Sessions database are on the same Postgres database server.
• This article assumes you have the knowledge to create a postgres read-replica server.

Note: These instructions are verified on qtestctl 6.2.

Stop Insights

• Linux (non-Docker)- Access the current directory "qtestctl" where you've installed your latest version of Insights.

  $ ./qtestctl stop

• Windows

  C:\[path_to_qtestctl]>net stop qtest

• Docker- Access the current directory "qtestctl" where you've installed your latest version of Insights.

  $ ./shutdown
  

Modify qtestctl/insights/build.gradle

1. Line 215, add a new line: auto.refresh.dblink=false
   

   build.gradle

   212 #--------SCHEDULER--------- 213 unscheduler-batch-size=500 214 scheduler-thread-pool=10  215 scheduler-result-file-dir=  216 auto.refresh.dblink=false

   
   
2. Line 186, replace: ${config['insights.postgres.host']} with the endpoint of the RR RDS instance.
   

   build.gradle | Ex

   184 #--------INFO QTEST DATABASE READ ----------- 185 qtestread.poolName=Qtest Read DB Pool 186 qtestread.url=jdbc:postgresql://qtestctl-rr.rds.amazonaws.com:${config['insights.postgres.port']}/${config['insights.postgres.db']} 187 qtestread.user=${config['insights.postgres.auth.user']}

3. Line 462, replace: ${config['insights.postgres.host']} with the endpoint of the RR RDS instance.
   

   build.gradle | Ex

   459 <Connection 460        CommandTimeOut="600" 461        ID="qTestMySQL" 462        PostgreSQLServer="qtestctl-rr.rds.amazonaws.com"

4. Line 482, replace: ${config['sessions.postgres.host']} with the endpoint of the RR RDS instance.
   

   build.gradle | Ex

   479 <Connection 480 CommandTimeOut="600" 481 ID="sessionDB" 482 PostgreSQLServer="qtestctl-rr.rds.amazonaws.com

Re-create Database Link

IMPORTANT: Only a Postgres Superuser can run these functions.

1. From the qTest database on the master Postgres:

   -- Format: select 
   refresh_foreign_schema_session('RR_DB_URL','DB_PORT','SESSIONS_DB_NAME','DB_USERNAME','PASSWORD','DB_USERNAME');
   select refresh_foreign_schema_session('qtestctl-rr.rds.amazonaws.com','5432','sessions','postgres','pass@word','postgres');

2. From the Sessions database on the master Postgres:

   -- Format: select 
   refresh_foreign_schema_qtest('RR_DB_URL','DB_PORT','QTEST_DB_NAME','DB_USERNAME','PASSWORD','DB_USERNAME');
   select refresh_foreign_schema_qtest('qtestctl-rr.rds.amazonaws.com','5432','qtest','postgres','pass@word','postgres');

Restart qTest Service and Verify 

• Linux (non-Docker)

  $ systemctl start qtest

• Windows

  C:\[path_to_qtestctl]>net start qtest

• Docker

  $ ./startup

Access Insights from the 9-box in qTest Manager and verify connectivity.