Executing Queries
You can execute queries in any number of ways, below are some suggestions
Small queries
Connect to Postgres by following commands outlined in cnp-module-postgres
Once in, you can execute provided query
cmd psql> paste your query here;
Note: The prompt would most likely be the DB_NAME you provided in steps you followed when connecting to the DB
Copy the output result and send it to the authorised recipient, this is usually mentioned in the ticket. If not mentioned, then confirm with team members before sending data off
For small queries with large results, you could do the following to output to file
\o <filename>.csv
<your query here>
\q
This should create a file called <filename>.csv with the output in it.
Note: the extension could be whatever you choose, using csv in the example below
Example:
Larger, lengthier queries
- Connect to Postgres as described in above step
- Create a new file, for example
cmd vi <ticket-number>-<any-extras-labeling>.sqlYou could usenanoas well. Just replaceviwithnano - Copy/paste query and save it
cmd :wq!If usingnanothen use it’s equivalent - Once file is saved you could run the command passing the file as input to Postgres.
cmd psql "sslmode=require host=${POSTGRES_HOST} dbname=${DB_NAME} user=${DB_USER}" -o DTSPO-2766-result.csv < DTSPO-2766-get-case-data.sqlHereDTSPO-2766-result.csvis where I want the results saved andDTSPO-2766-get-case-data.sqlis the file with provided sql query - If no errors, you can
catthe output file for a quick eye-balling 👀cmd cat <output-file>.csv
Bringing query file home
You can use any sftp tool of your choice to connect to bastion or follow below steps
- Log out of your ssh terminal by exiting
- Copy the file from the bastion server right from your terminal
cmd scp user.name@hmcts.net@bastion-devops-prod.platform.hmcts.net:/home/user.name/DTSPO-2766-result.csv /Users/<location-of-choice>/Users/<location-of-choice>being a path on your file system, here is an example using a MAC - You can now send the file(s) to authorised recipient(s) or share as instructed in the ticket.
If unclear, confirm with team members.
If you run into any other issues please feel free to reach out to team members.
Notes
In the in cnp-module-postgres documentation
cmd
export PGPASSWORD=<result-from-earlier>
Assign to the variable PGPASSWORD the token returned from the az account get-access-token ... command you ran earlier
Example:
Some external reference
You can have a quick peak for some psql info that may be useful
- Outputting Query Results to Files with \o
- How to run an SQL file in Postgres
- Run a SQL file in Postgres using the ‘psql’ command
- Connect to PostgreSQL and then run a SQL file using ‘psql’