Overview: 

This article provides step-by-step instructions to connect to the Analytics Plus PostgreSQL database from a Windows machine.

Prerequisites:  

• Access to the Analytics Plus server machine

• Login in to Analytics Plus UI as Admin 

• Access the Command Prompt as Adminis 

Run the following command: psql.exe -U postgres -p <port_number> -h 127.0.0.1  zreportsdb

Example:

psql.exe -U postgres -p 33366 -h 127.0.0.1

Note: Default port: 33366, if unknown then find it under: <AnalyticsPlus_Home>\conf\port.properties

Log in to the Analytics Plus application as admin.

Go to: Settings → Troubleshoot → DBQuery → Execute the below query

select propvalue from zdbsysprop where propkey like '%zrop.postgres.user.password%'

• The output will display the encrypted password as shown in the below image.

Open a new browser tab and navigate to:

https://<aplusurl>:<portnumber>/zrop/jsp/Encrypt.jsp

• Paste the encrypted password into: "Enter the Value to EAR Decrypt" field

• Click Submit
  

• 
  The decrypted password will be displayed below the input field as shown in the below image.
  

• Enter the decrypted password in the command prompt.

If successful, you will see:

zreportsdb=#

Expected Outcome  

You will now be connected to the Analytics Plus PostgreSQL database (zreportsdb).

Procedure for Linux

Log in to the Analytics Plus server and open a terminal session.

Run: cd <AnalyticsPlus_Home>/pgsql/bin

Run the following command:

./psql -U postgres -p <port_number> -h 127.0.0.1 zreportsdb

Example: ./psql -U postgres -p 33366 -h 127.0.0.1 zreportsdb

'U' represents the Username of DB (default username of the Aplus DB is postgres)
'p' represents the port number of the DB (by default Aplus DB is 33366)  
'h' represents the hostname of the DB (default Aplus DB is 127.0.0.1)

Note:

• Default port: 33366

• If unknown, find it in: <AnalyticsPlus_Home>/pgsql/conf/port.properties

Step 4: Enter Database Password  

• You will be prompted to enter the password.

• The password will not be visible while typing (expected behavior).

• Press Enter after typing the password.

If Password is Unknown  

Step 5: Access Analytics Plus Web Console  

Log in to the Analytics Plus application from a browser. 

Note: If the Linux server machine doesn't support accessing the Application UI, you may access it from a client machine.

Step 6: Navigate to DBQuery  

Go to: Settings → Troubleshoot → DBQuery

Step 7: Retrieve Encrypted Password  

Execute the below query:

select propvalue from zdbsysprop where propkey like '%zrop.postgres.user.password%';

• The output will display the encrypted password as shown in the below image.

Step 8: Decrypt the Password  

Open a new browser tab and navigate to:

https://:/zrop/jsp/Encrypt.jsp

• Paste the encrypted password into:
  "Enter the Value to EAR Decrypt"

• Click Submit

Step 9: Copy Decrypted Password  

• The decrypted password will be displayed below the input field.

Step 10: Authenticate in Terminal  

• Enter the decrypted password in the terminal.

Step 11: Verify Connection  

If successful, you will see:

postgres=#

Step 12: Connect to Analytics Plus Database  

Input 'zreportsdb' and press enter

Expected Outcome  

You will now be connected to the Analytics Plus PostgreSQL database (zreportsdb).