Working with the application database

Tonic Structural uses a PostgreSQL database to store its internal state. This database does not store customer data. It only stores data that Structural needs to operate.

Selecting the schema for the Structural application database

By default, Structural creates the application database tables in the public schema.

To use a different schema, set the environment setting TONIC_DB_SCHEMA to the schema name.

Note that you cannot set this setting from the Environment Settings tab on Structural Settings. You must set it from a .yaml file and then restart Structural.

Also note that if you change the schema, you should do it when you first install Structural.

If you change the setting on a running instance, it deploys the application schema objects into the new schema beside the existing public schema, but it does not migrate the data from public.

When the application launches after the schema change, it looks like a new instance of Structural. For example, it has none of your user accounts or workspaces. To restore the data, you must manually move it from the public schema to the new schema.

Connecting to the Structural application database

In general, you do not need to connect directly to the Structural application database. These details are provided if needed for advanced troubleshooting.

When you initially set up Structural, you provide Structural with connection details to a PostgreSQL database. How to connect depends on where you set up your PostgreSQL database.

Inside a Docker container

Some customers set up a PostgreSQL database inside the same Docker network as the other Structural containers. In that case, you should ensure that:

• Port 5432 is exposed on the PostgreSQL Docker container.

• Port 5432 is properly mapped.

To do this, add the following section to the PostgreSQL service section of your docker-compose file:

#This will expose Port 5432 on the docker container and map it to
#Port 5432 on the host machine

ports:
    - 5432:5432

Standalone PostgreSQL or cloud installation

If you installed PostgreSQL in a standalone fashion, or you use a cloud service such as Amazon RDS, then make sure that the firewall settings and security groups allow a connection on the appropriate port.

Making the connection

You can use any PostgreSQL client. The following example uses psql, the PostgreSQL command line client.

#Format
#psql -h <HOST> -p <PORT> -U <user> -d <Tonic DB name>

#Example
psql -h localhost -p 5432 -U pguser -d allos

Other Structural application database tips and troubleshooting

How can I reduce the size of the Structural application database?

The Structural application database includes a ColumnSchema table that contains the column schema information for your workspace source databases.

By default, when you delete a workspace, Structural does a soft delete. It marks the workspace as deleted, but does not remove the associated rows from the ColumnSchema table.

To help prevent the table from growing too large, you can configure Structural to instead do a hard delete, and remove the ColumnSchema rows that are associated with a deleted workspace.

To have Structural remove the ColumnSchema rows for deleted workspaces, set the environment setting TONIC_DELETE_COLUMN_SCHEMA_ON_WORKSPACE_DELETE to true. You can add this setting manually to the Environment Settings tab on Structural Settings.