Connecting to the PostgreSQL Database Instance
These instructions concern the PostgreSQL instance deployed to STFC cloud. For a deployment of a PostgreSQL instance to a local Kubernetes cluster, see the Kubernetes section.
Connect using PGAdmin link
Use the below link to connect with the PostgreSQL database instance via PGadmin:
https://k8s.stfc.skao.int/staging-ska-db-oda/pgadmin4
Once you are able to see the Pgadmin screen, login using the admin credentials given in the project’s CI/CD variables. Below query can be used to get data from query editor:
$ SELECT table_name
FROM information_schema.tables
WHERE table_schema='public';
Connect using PSQL terminal window
The PostgreSQL database instance can also be connected using the psql terminal window. You can use below command once you are on the terminal. The password is available in the projects CI/CD variables.
The Postgres host can be found from the CI/CD pipeline job info-dev-environment
$ PGPASSWORD=******** psql -U postgres -d postgres -h <POSTGRES HOST>
Once you are connected using psql terminal, there are a few basic commands:
$ \du -- to check which user you are connected to
$ \dt -- to get a list of avilable tables
$ select info from tab_oda_sbd; --- to get json column from SBD table
$ \q to quit
Schema created for ODA
An initial schema has been defined for the ODA. Below database tables are identified so far :-
tab_oda_sbd → A table to store SBDs
tab_oda_sbi → A table to store SBIs
tab_oda_prj → A table to store Projects
tab_oda_obs_prj → A table to store Observation Programs
tab_oda_exe_blk → A table to store Execution Blocks
Once you login to the Pgadmin link you should be able to see the above list of tables under DB–>Schema–>Tables. Alternatively, you can also use below command on the query tool to fetch these tables:
$ SELECT table_name
FROM information_schema.tables
WHERE table_schema='public'
AND table_type='BASE TABLE';
Job created for database:
https://gitlab.com/ska-telescope/db/ska-db-oda/-/jobs/2667353723
If there are any changes to the schema, the above job needs to be stopped and re-deployed. With every redeployment the IP address mentioned in the PGAdmin link as well as connection string for psql would change. We should make a note of it.
Useful links for PostgreSQL
Below are a few useful links in order to get used to PostgreSQL and PGadmin: