How to Set Up the Database for Oxygen Text Search Service

To connect Oxygen Text Search Service to the PostgreSQL database, follow these steps:

1. Create a database user that will be used by Oxygen Text Search Service to connect to the database. For example, feedback_db_user:

   CREATE USER feedback_db_user WITH PASSWORD 'password';

   For more details, see: https://www.postgresql.org/docs/12/sql-createuser.html.

2. Create the database where Oxygen Text Search Service will store its data. For example, otss_db:

   CREATE DATABASE otss_db ENCODING='UTF8' LC_COLLATE='C' LC_CTYPE='C' TEMPLATE='template0';

   The database must have the encoding set of UTF8. For more details, see: https://www.postgresql.org/docs/12/sql-createdatabase.html.

3. The user needs to have permission to connect to the database, create and populate the tables, etc. You can provide these permissions with the following command:

   GRANT ALL PRIVILEGES ON DATABASE otss_db TO feedback_db_user;

   For more details, see: https://www.postgresql.org/docs/12/sql-grant.html.

4. Configure Oxygen Text Search Service to connect to the database:
   1. Edit the $OXYGEN_FEEDBACK_INSTALL_DIR/docker-compose.yml file.
   2. Configure Oxygen Text Search Service with the following environment variables:

        text-search-service:
          # [...]
          environment:
            - spring.datasource.url=jdbc:postgresql://db_server_host:5432/otss_db
            - spring.datasource.username=feedback_db_user
            - spring.datasource.password=password

      In this example, replace db_server_host with the actual IP address or hostname of the machine running the PostgreSQL server (it can also be an internal network name if both services run on the same Docker network).

      Important:

      Since Oxygen Text Search Service always runs inside a Docker container, do not use localhost in the database server URL configuration.

      Inside the container, localhost refers to the container itself, not the host machine. To connect to a MySQL database running on the host machine or another server, use the host machine's IP address instead of localhost. You can determine the host's IP by running ip a on the host machine.

      If the database is hosted on the same machine, ensure it is configured to accept connections from external sources (e.g. Docker containers).