Install PostgreSQL on Ubuntu 20.04 and connect Ignition to it

The basics of connecting Ignition 8 to PostgreSQL

I used this page: https://www.postgresql.org/download/linux/ubuntu/ and ran the script to add the PostgreSQL apt repository so that I would be getting the latest version of PostgreSQL.

# Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
sudo apt-get update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get -y install postgresql

Next, edit the config file to allow postgres to listen on all network interfaces:

sudo nano /etc/postgresql/13/main/postgresql.conf

Under CONNECTIONS AND AUTHENTICATION make sure listen_addresses = ‘*’ and the line is uncommented.

CTRL X to save and then restart postgres.

sudo systemctl restart postgresql

Check that postgres is bound to all interfaces:

ss -nlt

It should show 0.0.0.0:5432, which means port 5432 is bound to all interfaces.

Now we need to enable remote logins, add the following line to the bottom of /etc/postgresql/13/main/pg_hba.conf

host    all          all            0.0.0.0/0  md5

Use nano:

sudo nano /etc/postgresql/13/main/pg_hba.conf

CTRL – X to save.

Restart postgres:

sudo systemctl restart postgresql

Login as the postgres user:

sudo -i -u postgres

Create a user ‘ignition’:

createuser --interactive

Create a database called ‘ignition’:

createdb ignition

Run psql

psql

Now you should have a prompt: postgres=# you can use use \q to quit

Give the ignition user a password:

alter user ignition with password 'ignition';

Now you can add the PostgreSQL db connection in the ignition web interface:

This gives you a basic connection, plenty more to learn and postgres out of the box needs extra configuration before going into production, please read more about that. If anyone wants more details on more advanced config with Ignition let us know in the comments and we may publish a guide.

By Roy Westwood

I've been an Industrial Automation professional for over 20 years. I currently lead a team of Systems Engineers creating OEE and Data Management solutions for customers all over the world.

Leave a comment

Your email address will not be published. Required fields are marked *