Tips for PostgreSQL

Contents

1. Accessing the database
2. Exploring database structure
3. Port forwarding
4. Generating ER diagram

Accessing the database

To access the database and run SQL queries, you can use the following command:

kubectl exec -i -t deployment/reana-db -- psql -U reana

This command opens a PostgreSQL command-line interface (CLI) session as the reana user within the reana database. You can now execute SQL queries directly in the terminal.

Exploring database structure

You can use the \dt command within the PostgreSQL CLI to list all tables and their associated schemas. This is helpful for exploring the database's structure and identifying the tables you need to work with.

reana=# \dt

REANA's database tables are not located in the default schema. They are located in the __reana schema. To explore this schema, use the \dt command like this:

reana=# \dt __reana.*

Port forwarding

Port forwarding is not typically required for simple PostgreSQL CLI access, but it can be useful when you want to access the PostgreSQL database using local tools or applications such as DataGrip. Here's how to set up port forwarding:

Forward a local port

Use kubectl port-forward to forward a local port to the database service's port. Replace <local port> with the desired local port:

kubectl port-forward service/reana-db <local port>:5432

Connection details

When connecting to the database via port forwarding, use the following connection details:

Property	Value
Host	localhost
Port	<local port> (the port you specified in the port forwarding command)
Username	reana
Database	reana

You can omit specifying a password when connecting locally through port forwarding.

Generating ER diagram

Reana has a lot of SQL tables whose definitions are located at reana-db/models.py and sometimes it can be challenging to have a general overview of the database. ER diagrams help us visualising database tables and their relationships formally.

There is a nice library called eralchemy2 which helps generating ER diagrams from a given database uri. Follow the steps given below to generate the ER diagram for REANA:

1. Install eralchemy2 library

pip install eralchemy2

2. Generate the ER diagram by specifying the database uri and the output file name: (You can specify png or pdf as an output file extension)

eralchemy2 -i 'postgresql+psycopg2://username:password@hostname:port/databasename' -o <your_file_name>

You can take the following command as reference if you did not update any default db parameters (Note that we also specify the __reana schema in the options parameter)

eralchemy2 -i 'postgresql+psycopg2://reana:reana@localhost:5432/reana?options=-c%20search_path%3D__reana' -o er.png