This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Administration

This section describes how to manage and configure the United Manufacturing Hub cluster.

In this section, you will find information about how to manage and configure the United Manufacturing Hub cluster, from customizing the cluster to access the different services.

1 - Access the Database

This page describes how to access the United Manufacturing Hub database to perform SQL operations using a database client, the CLI or Grafana.

There are multiple ways to access the database. If you want to just visualize data, then using Grafana or a database client is the easiest way. If you need to also perform SQL commands, then using a database client or the CLI are the best options.

Generally, using a database client gives you the most flexibility, since you can both visualize the data and manipulate the database. However, it requires you to install a database client on your machine.

Using the CLI gives you more control over the database, but it requires you to have a good understanding of SQL.

Grafana, on the other hand, is for visualizing data. It is a good option if you just want to see the data in a dashboard and don’t need to manupulate it.

Before you begin

You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.

Get the database credentials

If you are not using the CLI, you need to know the database credentials. You can find them in the timescale-post-init-pw Secret. By default, the username is factoryinsight and the password is changeme.

...
ALTER USER factoryinsight WITH PASSWORD 'changeme';
...

Access the database using a database client

There are many database clients that you can use to access the database. Here’s a list of some of the most popular database clients:

Database clients
NameFree or PaidPlatforms
pgAdminFreeWindows, macOS, Linux
DataGripPaidWindows, macOS, Linux
DBeaverBothWindows, macOS, Linux

For the sake of this tutorial, pgAdmin will be used as an example, but other clients have similar functionality. Refer to the specific client documentation for more information.

Forward the database port to your local machine

  1. From the Pods section in UMHLens / OpenLens, find the united-manufacturing-hub-timescaledb-0 Pod.
  2. In the Pod Details window, click the Forward button next to the postgresql:5432/TCP port.
  3. Enter a port number, such as 5432, and click Start. You can disable the Open in browser option if you don’t want to open the port in your browser.

Using pgAdmin

You can use pgAdmin to access the database. To do so, you need to install the pgAdmin client on your machine. For more information, see the pgAdmin documentation.

  1. Once you have installed the client, you can add a new server from the main window.

    pgAdmin main window
    pgAdmin main window

  2. In the General tab, give the server a meaningful name. In the Connection tab, enter the database credentials:

    • The Host name/address is localhost.
    • The Port is the port you forwarded.
    • The Maintenance database is postgres.
    • The Username and Password are the ones you found in the Secret.
  3. Click Save to save the server.

    pgAdmin connection window
    pgAdmin connection window

You can now connect to the database by double-clicking the server.

Use the side menu to navigate through the server. The tables are listed under the Schemas > public > Tables section of the factoryinsight database.

Refer to the pgAdmin documentation for more information on how to use the client to perform database operations.

Access the database using the command line interface

You can access the database from the command line using the psql command directly from the united-manufacturing-hub-timescaledb-0 Pod.

You will not need credentials to access the database from the Pod’s CLI.

Open a shell in the database Pod

  1. From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.

  2. Click the Pod Shell button to open a shell in the container.

    Lens Pod Shell
    Lens Pod Shell

  3. Enter the postgres shell:

    psql
    
  4. Connect to the database:

    \c factoryinsight
    

Perform SQL commands

Once you have a shell in the database, you can perform SQL commands.

  1. For example, to create an index on the processValueTable:

    CREATE INDEX ON processvaluetable (valuename);
    
  2. When you are done, exit the postgres shell:

     exit
    

Access the database using Grafana

You can use Grafana to visualize data from the database.

Add PostgreSQL as a data source

  1. Open the Grafana dashboard in your browser.

  2. From the Configuration (gear) icon, select Data Sources.

  3. Click Add data source and select PostgreSQL.

  4. Configure the connection to the database:

    • The Host is united-manufacturing-hub.united-manufacturing-hub.svc.cluster.local:5432.
    • The Database is factoryinsight.
    • The User and Password are the ones you found in the Secret.
    • Set TLS/SSL Mode to require.
    • Enable TimescaleDB.

    Everything else can be left as the default.

    Grafana PostgreSQL data source
    Grafana PostgreSQL data source

  5. Click Save & Test to save the data source.

  6. Now click on Explore to start querying the database.

  7. You can also create dashboards using the newly created data source.

What’s next

2 - Access Services From Within the Cluster

This page describes how to access services from within the cluster.

All the services deployed in the cluster are visible to each other. That makes it easy to connect them together.

Before you begin

You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.

Connect to a service from another service

To connect to a service from another service, you can use the service name as the host name.

To get a list of available services and related ports you can open UMHLens / OpenLens and go to Network > Services.

All of them are available from within the cluster. The ones of type LoadBalancer are also available from outside the cluster using the node IP.

Example

The most common use case is to connect to the MQTT Broker from Node-RED.

To do that, when you create the MQTT node, you can use the service name united-manufacturing-hub-mqtt as the host name and one the ports listed in the Ports column.

The MQTT service name has changed since version 0.9.10. If you are using an older version, use united-manufacturing-hub-vernemq instead of united-manufacturing-hub-mqtt.

What’s next

3 - Access Services Outside the Cluster

This page describe how to access services from outside the cluster.

Some of the microservices in the United Manufacturing Hub are exposed outside the cluster with a LoadBalancer service. A LoadBalancer is a service that exposes a set of Pods on the same network as the cluster, but not necessarily to the entire internet. The LoadBalancer service provides a single IP address that can be used to access the Pods.

Before you begin

You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.

Accessing the services

The LoadBalancer service provides a single IP address that can be used to access the Pods. To find the IP address, open UMHLens / OpenLens and navigate to Network > Services. The IP address is listed in the External IP column.

To access the services, use the IP address and the port number of the service, e.g. http://192.168.1.100:8080.

If you installed the United Manufacturing Hub on your local machine, either using the Management Console or the command line, the services are accessible at localhost:<port-number>.

Services with LoadBalancer by default

The following services are exposed outside the cluster with a LoadBalancer service by default:

To access Node-RED, you need to use the /node-red path, e.g. http://192.168.1.100:1880/node-red.

Services without a LoadBalancer

Some of the microservices in the United Manufacturing Hub are exposed via a ClusterIP service. That means that they are only accessible from within the cluster itself. To access them from outside the cluster, you need to create a LoadBalancer service.

Create a LoadBalancer service

If you are looking to expose th Kafka broker, follow the instructions in the Access Kafka outside the cluster page.

For any other microservice, follow these steps to enable the LoadBalancer service:

  1. Open UMHLens / OpenLens and navigate to Network > Services.

  2. Select the service and click the Edit button.

  3. Scroll down to the status.loadBalancer section and change it to the following:

    status:
      loadBalancer:
        ingress:
        - ip: <external-ip>
    

    Replace <external-ip> with the external IP address of the node.

  4. Scroll to the spec.type section and change the value from ClusterIP to LoadBalancer.

  5. Click Save to apply the changes.

If you installed the United Manufacturing Hub on your local machine, either using the Management Console or the command line, you also need to map the port exposed by the k3d cluster to a port on your local machine. To do that, run the following command:

k3d cluster edit united-manufacturing-hub --port-add "<local-port>:<cluster-port>@server:0"

Replace <local-port> with a free port number on your local machine, and <cluster-port> with the port number of the service.

Port forwarding in UMHLens / OpenLens

If you don’t want to create a LoadBalancer service, effectively exposing the microservice to anyone that has access to the host IP address, you can use UMHLens / OpenLens to forward the port to your local machine.

  1. Open UMHLens / OpenLens and navigate to Network > Services.
  2. Select the service that you want to access.
  3. Scroll down to the Connection section and click the Forward… button.
  4. From the dialog, you can choose a port on your local machine to forward the cluster port from, or you can leave it empty to use a random port.
  5. Click Forward to apply the changes.
  6. If you left the checkbox Open in browser checked, then the service will open in your default browser.

You can see and manage the forwarded ports of your cluster in the Network > Port Forwarding section.

Port forwarding can be unstable, especially if the connection to the cluster is slow. If you are experiencing issues, try to create a LoadBalancer service instead.

Security considerations

MQTT broker

There are some security considerations to keep in mind when exposing the MQTT broker.

By default, the MQTT broker is configured to allow anonymous connections. This means that anyone can connect to the broker without providing any credentials. This is not recommended for production environments.

To secure the MQTT broker, you can configure it to require authentication. For that, you can either enable RBAC or set up HiveMQ PKI (recommended for production environments).

If you are using a version of the United Manufacturing Hub older than 0.9.10, then you need to change the ACL configuration to allow your MQTT client to connect to the broker.

Troubleshooting

LoadBalancer service stuck in Pending state

If the LoadBalancer service is stuck in the Pending state, it probably means that the host port is already in use. To fix this, edit the service and change the section spec.ports.port to a different port number.

What’s next

4 - Access Kafka Outside the Cluster

This page describes how to access Kafka from outside the cluster.

By default the Kafka broker is only available from within the cluster, therefore you cannot access it from external applications.

You can enable external access from the Kafka configuration.

Before you begin

You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.

Enable external access from Kafka configuration

  1. From UMHLens / OpenLens, go to Helm > Releases.

  2. Click on the Upgrade button.

  3. Search for the kafka section and edit the following values:

    ...
    kafka:
    ...
      externalAccess:
        autoDiscovery:
          enabled: true
        ...
        enabled: true
      ...
      rbac:
        create: true
    ...
    
  4. Click Upgrade.

To verify that the LoadBalancer service is created, go to Network > Services and search for united-manufacturing-hub-kafka-external.

Now you can connect to Kafka from external applications using the node IP and the port 9094.

What’s next

5 - Expose Grafana to the Internet

This page describes how to expose Grafana to the Internet.

This page describes how to expose Grafana to the Internet so that you can access it from outside the Kubernetes cluster.

Before you begin

You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.

Enable the ingress

To expose Grafana to the Internet, you need to enable the ingress.

  1. Open UMHLens / OpenLens and go to the Helm > Releases page.
  2. Click the Upgrade button and search for Grafana.
  3. Scroll down to the ingress section
  4. Set the enabled field to true.
  5. Add you domain name to the hosts field.
  6. Click Upgrade to apply the changes.

Remember to add a DNS record for your domain name that points to the external IP address of the Kubernetes host. You can find the external IP address of the Kubernetes host on the Nodes page in UMHLens / OpenLens.

What’s next

6 - Install Custom Drivers in NodeRed

This page describes how to install custom drivers in NodeRed.

NodeRed is running on Alpine Linux as non-root user. This means that you can’t install packages with apk. This tutorial shows you how to install packages with proper security measures.

Before you begin

You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.

Change the security context

  1. From the StatefulSets section in UMHLens / OpenLens, click on united-manufacturing-hub-nodered to open the details page.

  2. Click the Edit button to open the StatefulSet’s configuration.

    Lens StatefulSet Edit
    Lens StatefulSet Edit

  3. Press Ctrl+F and search for securityContext.

  4. Set the values of the runAsUser field to 0, of fsGroup to 0, and of runAsNonRoot to false.

    ...
           securityContext:
             runAsUser: 0
       restartPolicy: Always
       terminationGracePeriodSeconds: 30
       dnsPolicy: ClusterFirst
       securityContext:
         runAsUser: 0
         runAsNonRoot: false
         fsGroup: 0
    ...
    
  5. Click Save.

Install the packages

  1. From the Pods section in UMHLens / OpenLens, click on united-manufacturing-hub-nodered-0 to open the details page.

  2. Click the Pod Shell button to open a shell in the container.

    Lens Pod Shell
    Lens Pod Shell

  3. Install the packages with apk:

    apk add <package>
    

    For example, to install unixodbc:

    apk add unixodbc
    

    You can find the list of available packages here.

  4. Exit the shell.

Revert the security context

For security reasons, you should revert the security context after you install the packages.

  1. From the StatefulSets section in UMHLens / OpenLens, click on united-manufacturing-hub-nodered to open the details page.

  2. Click the Edit button to open the StatefulSet’s configuration.

    Lens StatefulSet Edit
    Lens StatefulSet Edit

  3. Set the values of the runAsUser field to 1000, of fsGroup to 1000, and of runAsNonRoot to true.

    ...
           securityContext:
             runAsUser: 1000
       restartPolicy: Always
       terminationGracePeriodSeconds: 30
       dnsPolicy: ClusterFirst
       securityContext:
         runAsUser: 1000
         runAsNonRoot: true
         fsGroup: 1000
    ...
    
  4. Click Save.

What’s next

7 - Execute Kafka Shell Scripts

This page describes how to execute Kafka shell scripts.

When working with Kafka, you may need to execute shell scripts to perform administrative tasks. This page describes how to execute Kafka shell scripts.

Before you begin

You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.

Open a shell in the Kafka container

  1. From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-kafka-0 to open the details page.

  2. Click the Pod Shell button to open a shell in the container.

    Lens Pod Shell
    Lens Pod Shell

  3. Navigate to the Kafka bin directory:

    cd /opt/bitnami/kafka/bin
    
  4. Execute any Kafka shell scripts. For example, to list all topics:

    ./kafka-topics.sh --list --zookeeper zookeeper:2181
    
  5. To exit the shell:

    exit
    

What’s next

8 - Reduce database size

This page describes how to reduce the size of the United Manufacturing Hub database.

Over time, time-series data can consume a large amount of disk space. To reduce the amount of disk space used by time-series data, there are three options:

  • Enable data compression. This reduces the required disk space by applying mathematical compression to the data. This compression is lossless, so the data is not changed in any way. However, it will take more time to compress and decompress the data. For more information, see how TimescaleDB compression works.
  • Enable data retention. This deletes old data that is no longer needed, by setting policies that automatically delete data older than a specified time. This can be beneficial for managing the size of the database, as well as adhering to data retention regulations. However, by definition, data loss will occur. For more information, see how TimescaleDB data retention works.
  • Downsampling. This is a method of reducing the amount of data stored by aggregating data points over a period of time. For example, you can aggregate data points over a 30-minute period, instead of storing each data point. If exact data is not required, downsampling can be useful to reduce database size. However, data may be less accurate.

Before you begin

You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.

Open the database shell

  1. From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.

  2. Click the Pod Shell button to open a shell in the container.

    Lens Pod Shell
    Lens Pod Shell

  3. Enter the postgres shell:

    psql
    
  4. Connect to the database:

    \c factoryinsight
    

Enable data compression

To enable data compression, you need to execute the following SQL command from the database shell:

SELECT add_retention_policy('processvaluetable', INTERVAL '7 days');

This command will set a retention policy on the processvaluetable table, which will delete data older than 7 days.

Enable data retention

To enable data retention, you need to execute the following SQL command from the database shell:

SELECT add_compression_policy('processvaluetable', INTERVAL '7 days');

This command will set a compression policy on the processvaluetable table, which will compress data older than 7 days.

What’s next

9 - Delete Assets from the Database

This task shows you how to delete assets from the database.

This is useful if you have created assets by mistake, or to delete the ones that are no longer needed.

This task deletes data from the database. Make sure you have a backup of the database before you proceed.

Before you begin

You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.

Also, make sure to backup the database before you proceed. For more information, see Backing Up and Restoring the Database.

Open the database shell

  1. From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.

  2. Click the Pod Shell button to open a shell in the container.

    Lens Pod Shell
    Lens Pod Shell

  3. Enter the postgres shell:

    psql
    
  4. Connect to the database:

    \c factoryinsight
    

Choose the assets to delete

You have multiple choices to delete assets, like deleting a single asset, or deleting all assets in a location, or deleting all assets with a specific name.

To do so, you can customize the SQL command using different filters. Specifically, a combination of the following filters:

  • assetname
  • location
  • customer

To filter an SQL command, you can use the WHERE clause. For example, using all of the filters:

WHERE assetname = <my-asset> AND location = <my-location> AND customer = <my-customer>;

You can use any combination of the filters, even just one of them.

Here are some examples:

  • Delete all assets with the same name from any location and any customer:

    WHERE assetname = '<asset-name>'
    
  • Delete all assets in a specific location:

     WHERE location = '<location-name>'
    
  • Delete all assets with the same name in a specific location:

    WHERE assetname = '<asset-name>' AND location = '<location-name>'
    
  • Delete all assets with the same name in a specific location for a single customer:

    WHERE assetname = 'my-asset' AND location = 'my-location' AND customer = 'customer'
    

Delete the assets

Once you know the filters you want to use, you can use the following SQL commands to delete assets:

BEGIN;

WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM shifttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);

WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM counttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);

WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM ordertable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);

WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM processvaluestringtable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);

WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM processvaluetable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);

WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM producttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);

WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM shifttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);

WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM statetable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);

WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM assettable WHERE id IN (SELECT id FROM assets_to_be_deleted);

COMMIT;

Optionally, you can add the following code before the last WITH statement if you used the track&trace feature:

WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>), uniqueproducts_to_be_deleted AS (SELECT uniqueproductid FROM uniqueproducttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted))
DELETE FROM producttagtable WHERE product_uid IN (SELECT uniqueproductid FROM uniqueproducts_to_be_deleted);

WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>), uniqueproducts_to_be_deleted AS (SELECT uniqueproductid FROM uniqueproducttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted))
DELETE FROM producttagstringtable WHERE product_uid IN (SELECT uniqueproductid FROM uniqueproducts_to_be_deleted);

WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>), uniqueproducts_to_be_deleted AS (SELECT uniqueproductid FROM uniqueproducttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted))
DELETE FROM productinheritancetable WHERE parent_uid IN (SELECT uniqueproductid FROM uniqueproducts_to_be_deleted) OR child_uid IN (SELECT uniqueproductid FROM uniqueproducts_to_be_deleted);

WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM uniqueproducttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);

What’s next

10 - Change the Language in Factoryinsight

This page describes how to change the language in Factoryinsight, in order to display the returned text in a different language.

You can change the language in Factoryinsight if you want to localize the returned text, like stop codes, to a different language.

Before you begin

You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.

Access the database shell

  1. From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.

  2. Click the Pod Shell button to open a shell in the container.

    Lens Pod Shell
    Lens Pod Shell

  3. Enter the postgres shell:

    psql
    
  4. Connect to the database:

    \c factoryinsight
    

Change the language

Execute the following command to change the language:

INSERT INTO configurationtable (customer, languagecode) VALUES ('factoryinsight', <code>) ON CONFLICT(customer) DO UPDATE SET languagecode=<code>;

where <code> is the language code. For example, to change the language to German, use 0.

Supported languages

Factoryinsight supports the following languages:

Supported languages
LanguageCode
German0
English1
Turkish2

What’s next

11 - Explore Cached Data

This page shows how to explore cached data in the United Manufacturing Hub.

When working with the United Manufacturing Hub, you might want to visualize information about the cached data. This page shows how you can access the cache and explore the data.

Before you begin

You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.

Open a shell in the cache Pod

  1. Open UMHLens / OpenLens and navigate to the Config > Secrets page.

  2. Get the cache password from the Secret redis-secret.

  3. From the Pods section click on united-manufacturing-hub-redis-master-0 to open the details page.

    If you have multiple cache Pods, you can select any of them.
  4. Click the Pod Shell button to open a shell in the container.

    Lens Pod Shell
    Lens Pod Shell

  5. Enter the shell:

    redis-cli -a <cache-password>
    
  6. Now you can execute any command. For example, to get the number of keys in the cache, run:

    KEYS *
    

    Or, to get the cache size, run:

    DBSIZE
    

For more information about Redis commands, see the Redis documentation.

What’s next

12 - Optimize Time Consuming Queries

This page shows how to optimize the database in order to reduce the time needed to execute queries.

When you have a large database, it is possible that some queries take a long time to execute. This especially shows when you are using Grafana and the dropdown menu in the datasource takes a long time to load or does not load at all.

Before you begin

You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.

Your United Manufacturing Hub must be at or later than version 0.9.4. To check the United Manufacturing Hub version, open UMHLens / OpenLens and go to Helm > Releases. The version is listed in the Version column.

Open a shell in the database container

  1. From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.

  2. Click the Pod Shell button to open a shell in the container.

    Lens Pod Shell
    Lens Pod Shell

  3. Enter the postgres shell:

    psql
    
  4. Connect to the database:

    \c factoryinsight
    

Create an index

Indexes are used to speed up queries. Run this query to create an index on the processvaluetable table:

CREATE INDEX ON processvaluetable(valuename, asset_id) WITH (timescaledb.transaction_per_chunk);

Rollback factoryinsight

If you have already created an index, you can rollback the factoryinsight deployment to version 0.9.4. This way it will use a less optimized but faster query, significantly reducing the execution time.

  1. From the Deployments section in UMHLens / OpenLens, click on united-manufacturing-hub-factoryinsight-deployment to open the details page.
  2. Click the Edit button to open the deployment’s configuration.

    Lens deployment Edit
    Lens deployment Edit

  3. Scroll down to the spec.containers section and change the image value to unitedmanufacturinghub/factoryinsight:0.9.4.
  4. Click Save.

What’s next

13 - Optimize Database Datatypes

This page describes how to change the datatype of some columns in the database in order to optimize the performance.

In version 0.9.5 and prior, some tables in the database were created with the varchar data type. This data type is not optimal for storing large amounts of data. In version 0.9.6, the data type of some columns was changed from varchar to text. This migration optimizes the database, by changing the data type of some columns from varchar to text.

Before you begin

You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.

  1. From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.

  2. Click the Pod Shell button to open a shell in the container.

    Lens Pod Shell
    Lens Pod Shell

  3. Enter the postgres shell:

    psql
    
  4. Connect to the database:

    \c factoryinsight
    

Alter the tables

Execute the following SQL statements:

ALTER TABLE assettable ALTER COLUMN assetid TYPE text;
ALTER TABLE assettable ALTER COLUMN location TYPE text;
ALTER TABLE assettable ALTER COLUMN customer TYPE text;
ALTER TABLE producttable ALTER COLUMN product_name TYPE text;
ALTER TABLE ordertable ALTER COLUMN order_name TYPE text;
ALTER TABLE configurationtable ALTER COLUMN customer TYPE text;
ALTER TABLE componenttable ALTER COLUMN componentname TYPE text;

Then confirm the changes by using the following SQL statements:

SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'assettable';
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'producttable';
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'ordertable';
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'configurationtable';
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'componenttable';