Query Executor Tool for Postgres
Overview:
A query executor tool for PostgreSQL that allows for query execution without a direct database connection enhances security and flexibility while collaborating on query testing without the need for each team member to have direct database access. This comes with a user-friendly interface that simplify query execution and management for users who may not be familiar with direct database connections. Let's dive into the process right away.
Table of content:
A query executor tool for PostgreSQL that allows for query execution without a direct database connection enhances security and flexibility while collaborating on query testing without the need for each team member to have direct database access. This comes with a user-friendly interface that simplify query execution and management for users who may not be familiar with direct database connections. Let's dive into the process right away.
Table of content:
- Objective
- Disclaimer
- Prerequisites
- Procedure
- Illustration
Objective:
This tool is designed to execute update and delete queries in the client environment by connecting to the database through the database configuration file (database_params.conf).
Disclaimer:
- This tool has been tested only in the PostgreSQL environment.
- It is not recommended for executing sensitive queries in the production environment directly. For complex queries, testing should first be performed on a test setup.
- Please ensure to take a backup of the data before running this tool.
Prerequisites:
- Attached QueryExecutor.zip file.
- Edit access to the application folder and files on the application server.
- Administrator privileges for the Command Prompt.
Procedure:
Download and extract the QueryExecutor.zip file from the attachment and extract into the <Drive>:\ManageEngine\ServiceDeskPlus MSP directory. This will create a "QueryExecutor" folder at the specified location.
Start the MSP Application service.
Open the queryToExecute.txt file located in <MSP Home>\QueryExecutor and paste the query which needs to be executed.
Open the Command Prompt as an administrator and execute the run.bat or run.sh command from the same path to apply the changes specified in the query.
Check the queryExecutor.log file, in the <MSP Home>\QueryExecutor folder, for results and analysis.
Illustration:
Below is an illustration of using the QueryExecutor tool to delete a backup approver without manually connecting to the database:
Query: delete from backupapproverdetails where backup_config_id in (1);
queryToExecute.txt file:
queryToExecute.txt file:
Before execution: There are two backup approvers, as shown in the screenshot below.
After execution: The specified approver with user ID '1' has been deleted via the query.
QueryExecutor log:
Therefore, as mentioned, DB queries can now be executed directly from the application folder, without the need for direct database connections.
New to ADSelfService Plus?
Related Articles
Query Executor Tool for PostGres
This tool is designed to execute queries in the customer environment by connecting the database by reading the database configuration file. We need to enter the query that we require to execute in queryToExecute.txt file. We can enter multiple ...Remote read only access to database for Postgres customers
Frequently customers want to connect some reporting / dashboard application like PowerBI or Tableau with our Postgres database server. By default, the bundled Postgres is configured to only listen to the local machine. We can configure to allow ...Migration failure for Postgres Database with a blank space in it
Trace : \ManageEngine\ServiceDesk\Patch\AdventNet_ManageEngine_ServiceDesk_Plus-14.2.0-SP-1.0.0\SERVICEDESK\PreInstall\pg_migrate\pgsql_old\bin\pg_dumpall.exe", -U, postgres, -p, 65433, -h, 127.0.0.1, -r, -w, |, ...How to connect to external postgres(external DB)
1. From the postgres installed path navigate to bin folder and open a command prompt 2. Execute the command --> pg_ctl -D "C:\Program Files\PostgreSQL\10\data" start (Postgres server gets started) 3. Connect to DB using the command-->psql -U postgres ...Delete requests
If you would like to delete the older requests, you could use the below query. Connect to the Database For MSSQL: delete from workorder where dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00') <= ...