Menu

qTest Controller - Setting up qTest Insights with Postgres Read Replica

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

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

Powered by Zendesk