This guide will walk you through setting up OmniDB, a powerful database management tool, inside a Docker container on your fresh Kubuntu/Ubuntu system. This setup allows you to manage your PostgreSQL and MySQL databases, which are running directly on your Kubuntu/Ubuntu host, through a web browser. We'll also ensure your OmniDB settings persist even if you restart the container.

Prerequisites:

A fresh installation of Kubuntu/Ubuntu.
Docker installed and running on your Kubuntu/Ubuntu host.
PostgreSQL and MySQL installed and running on your Kubuntu/Ubuntu host. For this guide, we'll assume you have a PostgreSQL user named db_user_pg with the password secure_password_pg and a MySQL user named db_user_mysql with the password secure_password_mysql. Remember to replace these with your actual credentials.

Step 1: Creating a Docker Volume for Persistent Data

To ensure your OmniDB configurations (like saved connections) are not lost when the Docker container stops or is removed, we'll create a Docker volume. Think of a volume as a dedicated storage space managed by Docker.

Open your Kubuntu/Ubuntu terminal (you can usually do this by pressing Ctrl+Alt+T) and run the following command:

docker volume create omnidb_data

This command tells Docker to create a named storage area called omnidb_data.

Step 2: Running the OmniDB Docker Container

Now, we'll download and run the official OmniDB Docker image. This image contains all the necessary files to run the OmniDB application. We'll also configure it to use the persistent volume we created and map network ports so you can access it from your web browser.

Run this command in your terminal:

docker run -d --name omnidb_container -p 8081:8000 -p 25482:25482 -v omnidb_data:/home/omnidb/.omnidb/omnidb-server --restart always omnidbteam/omnidb:latest

Let's break down what each part of this command does:

-d: This tells Docker to run the container in the background, so your terminal isn't blocked.
--name omnidb_container: This gives your running container a friendly name, omnidb_container, which makes it easier to manage later.
-p 8081:8000: This maps port 8081 on your Kubuntu computer to port 8000 inside the Docker container. OmniDB's web interface runs on port 8000 inside the container, so this makes it accessible on http://localhost:8081 in your web browser.
-p 25482:25482: This maps port 25482 on your host to the same port in the container. This port is used for communication between the OmniDB web interface and its backend.
-v omnidb_data:/home/omnidb/.omnidb/omnidb-server: This links the omnidb_data volume we created earlier to a specific folder inside the container where OmniDB stores its important data. This ensures your settings are saved.
--restart always: This tells Docker to automatically restart the omnidb_container if it stops for any reason (unless you explicitly stop it).
omnidbteam/omnidb:latest: This specifies the official OmniDB Docker image to download and run. :latest means it will use the newest stable version.

Step 3: Creating an OmniDB Superuser

Before you can log in to OmniDB for the first time, you need to create a superuser account. This account will have full administrative privileges within OmniDB. Run the following command in your terminal:

docker exec -it omnidb_container python omnidb-server.py --createsuperuser=admin_user admin_password

docker exec -it omnidb_container: This command allows you to run commands inside the omnidb_container. The -it flags make it interactive, so you can see the output.
python omnidb-server.py --createsuperuser=admin_user admin_password: This runs a script inside the container that creates a superuser with the username admin_user and the password admin_password. Remember this username and password for logging into OmniDB.

You should see some output in your terminal indicating that the superuser has been created.

Step 4: Accessing OmniDB in Your Web Browser

Now that the container is running and you've created a superuser, you can access the OmniDB web interface. Open your web browser (like Firefox or Chrome) and go to the following address:

http://localhost:8081

You should see the OmniDB login page. Enter the username admin_user and the password admin_password you created in the previous step to log in.

Step 5: Connecting to Your Local PostgreSQL Database (and Understanding Remote Connections)

When you try to connect to PostgreSQL running on your Kubuntu host from within the Docker container, you're essentially making a "remote" connection, even though both are on the same machine. This is because the Docker container has its own isolated network. To allow these connections, PostgreSQL needs to be configured to listen for them and to authorize them.

Look for a "+" icon or a button that says "Add New Connection" and click on it.
In the "Connection Type" dropdown, select "PostgreSQL".
You'll now see fields to enter the connection details.
- Server: Use the IP address of your Kubuntu host (found using hostname -I | awk '{print $1}').
- Port: 5432.
- Database: postgres (or your desired database).
- User: Your PostgreSQL username (e.g., db_user_pg).
- Password: Your PostgreSQL password (e.g., secure_password_pg).
If you encounter a "Connection refused" error when you click "Test," it's likely that PostgreSQL on your host isn't configured to accept connections from the IP address of your Docker container (which will be the IP address of your host). Here's how to address this:

Configuring PostgreSQL for Remote Connections:

PostgreSQL has two main configuration files that control network connections: postgresql.conf (which tells PostgreSQL which network addresses to listen on) and pg_hba.conf (which controls which hosts are allowed to connect, which users they can connect as, and how they are authenticated).

a) Editing postgresql.conf (Listening Address):
- Open your terminal and find the postgresql.conf file. Its location can vary, but it's often in a directory like /etc/postgresql/<your_version>/main/. You can try this command to find it:
```
  cd /etc/postgresql && sudo find . -iname postgresql.conf
```
  Replace <your_version> with the version of PostgreSQL you have installed (e.g., 17).
- Once you find the file, open it with a text editor using sudo:
```
  sudo nano <path_to_postgresql.conf>
```
- Look for the line that starts with #listen_addresses.
- Development/Local Use (Easier but Less Secure for Production): Uncomment this line (remove the # at the beginning) and change its value to '*':
```
  listen_addresses = '*'
```
  Setting it to '*' tells PostgreSQL to listen for connections on all available network interfaces on your computer. This is generally fine for local development but can be a security risk in a production environment as it allows connections from any IP address that can reach your machine.
- More Restrictive (Recommended for Production or if you understand networking): You could also set it to your host's specific IP address (the one you used in the OmniDB connection) or a specific network range if you know the IP range of your Docker network. For example:
```
  listen_addresses = '127.0.0.1,10.29.164.194'
```
  This would tell PostgreSQL to listen only on the local loopback address and your host's specific IP.
- Save the file (Ctrl+O in nano) and exit (Ctrl+X in nano).

b) Editing pg_hba.conf (Host-Based Authentication):

The pg_hba.conf file is usually in the same directory as postgresql.conf. Open it with sudo nano <path_to_pg_hba.conf>.
This file contains rules that determine who can connect to which databases and how they are authenticated. You need to add a rule that allows connections from your host's IP address (or from any IP address for initial testing).
Development/Local Use (Easier but Less Secure for Production): Add the following line at the end of the file (or after the existing host lines):
```
  host all all 0.0.0.0/0 md5
```
- host: Specifies a TCP/IP connection.
- all: Matches all databases.
- all: Matches all users.
- 0.0.0.0/0: Matches all IPv4 addresses. This allows connections from any IP address that can reach your PostgreSQL server. This is convenient for local development but should be restricted in a production environment.
- md5: Specifies the authentication method (using an MD5-hashed password).
More Restrictive (Recommended for Production): To allow only connections from your host's specific IP address, replace 0.0.0.0/0 with your host's IP address followed by /32 (which means a single IP address):
```
  host all all 10.29.164.194/32 md5
```
Save the pg_hba.conf file and exit.

c) Restart PostgreSQL:

After making changes to either postgresql.conf or pg_hba.conf, you need to restart the PostgreSQL service for the changes to take effect:

    sudo systemctl restart postgresql

Now, go back to OmniDB and try clicking the "Test" button for your PostgreSQL connection again. It should hopefully connect successfully. Remember to click "Save" afterward.

Step 6: Connecting to Your Local MySQL Database (and Understanding Remote Connections)

The concept for MySQL is similar to PostgreSQL, but the configuration files are different.

Add a new connection in OmniDB and select "MySQL".
Enter your host's IP address, port 3306, username, and password.
If you encounter a connection error, you need to configure MySQL to accept remote connections.

Configuring MySQL for Remote Connections:

MySQL's network settings are primarily controlled in its configuration file, usually named my.cnf or my.ini or mysqld.cnf. Its location can vary (often in /etc/mysql/ or /etc/mysql/mysql.conf.d/).
- Find the configuration file:
```
  cd /etc/mysql && sudo find . -iname my.cnf
  cd /etc/mysql && sudo find . -iname my.ini
  cd /etc/mysql && sudo find . -iname mysqld.cnf
```
- Open the file with sudo nano <path_to_my.cnf> (or my.ini).
- Look for the section [mysqld].
- Find the line bind-address. By default, it might be set to 127.0.0.1, which means MySQL only listens for connections from the local machine.
- Development/Local Use (Easier but Less Secure for Production): Comment out this line by adding a # at the beginning:
```
  #bind-address = 127.0.0.1
```
  Or, change it to 0.0.0.0 to listen on all interfaces:
```
  bind-address = 0.0.0.0
```
  Warning: Setting bind-address to 0.0.0.0 makes your MySQL server accessible from any IP address that can reach your machine, which can be a security risk in production.
- More Restrictive (Recommended for Production): Change bind-address to your host's specific IP address:
```
  bind-address = 10.29.164.194
```
- Save the file and exit.
- Granting User Access from Remote Hosts: You also need to grant your MySQL user permission to connect from your host's IP address (or % for any host, for testing). Log in to MySQL as the root user (you might need to use sudo mysql -u root -p and enter your root password):
```
  mysql -u root -p
```
  Then, grant the user access:
```
  GRANT ALL PRIVILEGES ON *.* TO 'db_user_mysql'@'%' IDENTIFIED BY 'secure_password_mysql';
  FLUSH PRIVILEGES;
  EXIT;
```
  Replace 'db_user_mysql' and 'secure_password_mysql' with your actual username and password. Using '%' here means "from any host." For more security in production, you would replace '%' with your host's IP address (e.g., 'db_user_mysql'@'10.29.164.194').
- Restart the MySQL service:
```
  sudo systemctl restart mysql
```
Try testing the MySQL connection in OmniDB again.

Important Considerations for Production vs. Development:

Development: For local development, using '*' for listen_addresses and 0.0.0.0/0 in pg_hba.conf (for PostgreSQL) or bind-address = 0.0.0.0 and % for host in GRANT (for MySQL) can be convenient to avoid network configuration hassles. However, be aware of the security implications if your development machine is exposed to a public network.
Production: In a production environment, it is crucial to restrict network access as much as possible. You should:
- Set listen_addresses and bind-address to only the specific IP addresses that need to connect to the database server.
- Use specific IP addresses or network ranges in pg_hba.conf (PostgreSQL) and in the GRANT statement (MySQL) to allow connections only from trusted hosts.
- Ensure you have strong passwords and consider using more secure authentication methods.
- Implement firewall rules at the operating system and network levels to further restrict access to the database ports.

By understanding these configuration aspects, you can correctly set up your database servers to accept connections from your Dockerized OmniDB while also being aware of the security implications for different environments.

Common Mistakes to Watch Out For:

Forgetting to use the host IP address: As mentioned before, localhost inside the container is not your host. Always use the IP address you get from hostname -I | awk '{print $1}' when connecting to databases on your host.
Firewall issues: Your Kubuntu firewall might prevent the Docker container from reaching the PostgreSQL (port 5432) or MySQL (port 3306) servers on your host. If you encounter connection errors, you might need to allow these ports in your firewall using sudo ufw allow <port>/tcp.
Database server not configured for remote connections: By default, PostgreSQL and MySQL might be set up to only accept connections from localhost. You might need to adjust their configuration files (postgresql.conf and my.cnf) to listen on other interfaces (like 0.0.0.0 for all) and potentially adjust their host-based authentication files (pg_hba.conf for PostgreSQL) to allow connections from your host's IP address.
Incorrect credentials: Double-check the usernames and passwords for your PostgreSQL and MySQL users.
Docker container not running: If you can't access http://localhost:8081, make sure your Docker container is running using docker ps. If it's not, start it with docker start omnidb_container.

By carefully following these steps and being mindful of these common pitfalls, you should have a smooth experience setting up OmniDB in Docker to manage your local databases.

How to Manage Local Databases in Dockerized OmniDB: PostgreSQL and MySQL Basics

Table of contents