PostgreSQL on ctrlX CORE

rcruzoliver · ‎01-10-2024

PostgreSQL is a powerful, open-source relational database management system (RDBMS) known for its extensibility, robustness, and adherence to SQL standards. It provides advanced features such as support for complex data types, indexing, transactions, and concurrency control.

PostgreSQL is designed to handle large of data and is often chosen for mission-critical applications, offering a high level of data integrity and reliability. It supports various programming languages and has a thriving community that contributes to its ongoing development and enhancement. Additionally, PostgreSQL is known for its extensibility, allowing users to define custom data types, operators, and functions.

For these reasons, its use in the ctrlX Ecosystem can really power up our applications.

In the following picture you can see what the system structure looks like:

System overviewThis project targets the current generation ctrlX CORE^virtual, ctrlX CORE X5 and ctrlX CORE X7 running ctrlX OS 2.xx (base apps running an ubuntu core 22). This project is currently supported for amd64, you are free to explore the possibilities for arm64.

The functionalities showed in this How-To have been tested in ctrlX CORE^virtual and ctrlX CORE X7. This guide is simply a demo example, feel free to keep playing with it!

1. Installation

In your Engineering PC you need to download PgAdmin4, you can do it from the official PgAdmin website. Choose your operating system. The functionality showed in this How-To has been tested in Windows 10 and Ubuntu 22.04 as host system in the Engineering PC.

Install the PostgreSQL runtime in your ctrlX CORE. You need to compile it yourself following the instructions that you can find in the attached zip.file (ctrlx_postgresql-main.zip).

2. Configuration

The PostgreSQL runtime uses the files found in the attached zip.file under ctrlx_postgresql/shcripts/data :

configuration/ : it contains all the neccesary configuration files for PostgreSQL to work.
data_postgresql/ : it contains the information data.

3. Connect PgAdmin4 with ctrlX OS

Connect the Engineering PC and the CORE in the same network. If you are using ctrlX CORE^virtual, do not forget to fordward the port in which the runtime is accesible (5432 by default).

Open PgAdmin4 and add a new server.

Add new serverA window will pop up. Fill in the fields as indicated in the image. The server is running in ctrlX CORE^virtual, hence localhost (127.0.0.1) must be indicated, if it is a real CORE, simply indicate the corresponding IP address. The user postgres is by default granted to access without password. This can be later edited.

Setting up a serverSetting up a serverIf the connection does not work, in some cases the connection to that IP is protected. You can try with SSH connection with password, you must fill in as follows with your SSH credentials.

SSH connectionAnd then you can click save, and you will be logged in.

Server connected

4. Test to publish something to PostgreSQL using Node-RED

There exists a Node-RED palette to communicate with PostgreSQL. You can download it as seen in the following images. The CORE must be connected to the internet.

Add new palettesPostgreSQL palette
We have prepared some examples to publish to the data base from Node-RED. Find them attached to this How-to (flows_PostgreSQL.zip). To import it, open the "Flow Editor" from Node-RED and proceed as shown in the following pictures:

Import example
Select the .json file
Example json loaded
You must import both flows (.json) in that .zip:

Products: Working in the background, you do not need to interactuate with this. It simulates a product object that would be later publish to the database.

Product object generationPostgreSQL: It allows you create a table, and then publish cyclicly a product object. Likewise, we have setup a functionality to read objects from the database.

Node-RED exampleOnce you have published something, you can go to PgAdmin4 and visualize what has been published to the Data Base.

After creating the table from Node-RED, you need to refresh the table list as shown in the following picture.

Right click on tables and refresh the list
After that you can visualize the rows in such table as follows:

Right click "products" and visualize all rows
To refresh the visualization you can query again by clicking on the play button on the top. You can also export the data in a csv.file by clicking in the download arrow on top of the table.

Table visualization

Have fun!