# Configure PostgreSQL as the Database Backend Squirro supports PostgreSQL as an alternative SQL database backend to MariaDB. This page describes how to configure PostgreSQL for Ansible-based and on-premises Linux deployments. > **Important** > > PostgreSQL is supported for new Squirro installations only. Migrating data from an existing MariaDB deployment to PostgreSQL is not supported through this procedure. To migrate an existing instance, visit the [Squirro Support website](https://go.squirro.com/support) and submit a technical support request. > > > > PostgreSQL is supported on currently supported Squirro releases. Each Squirro service creates its database schema automatically the first time it starts against an empty database. > **Note** > > For containerized deployments, currently available as a technical preview only, PostgreSQL is the default database. No additional configuration is required for a fresh installation. For guidance specific to containerized setups, visit the [Squirro Support website](https://go.squirro.com/support) and submit a technical support request. ## Ansible-Based Deployment By default, the Squirro Ansible role uses MariaDB as the database. To use PostgreSQL instead, set the [`database_type`](ansible/variable-reference.md#confval-database_type) variable to `postgres` in your playbook. > **Important** > > The Ansible role supports PostgreSQL only as an external (remote) database. The role does not install or manage a local PostgreSQL server, and it does not create the required databases and roles. Provision the PostgreSQL server and create the databases and roles in advance, then set [`remote_postgres_server`](ansible/variable-reference.md#confval-remote_postgres_server) to `true` so that the role configures the Squirro services to connect to it. To create the databases and roles, follow the SQL steps in the [On-Premises Linux Deployment](#postgresql-on-premises) section below. When `database_type` is set to `postgres`, the role installs the `psycopg2` PostgreSQL driver and writes the PostgreSQL connection strings into the Squirro service configuration files. ### Prerequisites Before running the playbook, provision an external PostgreSQL server and create one role and one database for each of the following Squirro services: - `configuration` - `datasource` - `emailsender` - `filtering` - `machinelearning` - `notes` - `plumber` - `scheduler` - `studio` - `topic` - `user` The GenAI service also uses a database, which is configured separately. See the GenAI configuration step in the on-premises section for details. Each database uses `UTF8` encoding with `en_US.UTF-8` collation and character type. The Create Database Users and Databases steps in the on-premises section provide the exact SQL commands. ### Configuration To connect to an external PostgreSQL server such as AWS RDS, set the following variables in your playbook: ```yaml database_type: postgres remote_postgres_server: true postgres_host: postgres.example.com postgres_port: 5432 postgres_shared_service_password: ``` The role uses a single shared password for all services by default. To assign a dedicated password to a service, set the corresponding per-service variable. To use an SSL/TLS connection to the PostgreSQL server, set the following variables: ```yaml postgres_ssl: true postgres_ssl_ca_certs: /etc/ssl/certs/ca-bundle.trust.crt ``` ### Per-Service Passwords Each Squirro service can use a dedicated database password. Each variable defaults to the value of `postgres_shared_service_password`. Set any of the following variables to override the shared password for a specific service: - `postgres_configuration_password` - `postgres_datasource_password` - `postgres_emailsender_password` - `postgres_filtering_password` - `postgres_machinelearning_password` - `postgres_notes_password` - `postgres_plumber_password` - `postgres_scheduler_password` - `postgres_studio_password` - `postgres_topic_password` - `postgres_user_password` For more granular control, each service also accepts a full connection string through a `postgres__uri` variable, which overrides the host, port, and password variables for that service. For a description of each variable, see the [Ansible Role Variables Reference](ansible/variable-reference.md#ansible-variable-reference) page. ## On-Premises Linux Deployment > **Note for administrators** > > End of support for installing Squirro on Linux manually. This section remains available for reference only and is no longer maintained. Use Ansible for all installations and upgrades. See the [Install and Manage Squirro with Ansible](ansible/index.md#ansible) page. This section describes how to install and configure PostgreSQL as the metadata storage on a single-node, on-premises Linux deployment. The procedure targets PostgreSQL 16 on Rocky Linux 8 and Red Hat Enterprise Linux 8. A standard Squirro installation provisions MariaDB by default. This procedure configures PostgreSQL as the backend instead, before the instance holds any data, and turns off the MariaDB service at the end. > **Important** > > This procedure configures PostgreSQL manually and is intended for nodes that are not managed by the Squirro Ansible role. On an Ansible-managed node, the role reasserts the Squirro service configuration files on every run and can overwrite these manual changes. For Ansible-managed deployments, use the Ansible-Based Deployment section above instead. > **Note** > > The default password `squirro/4u` is used throughout this procedure for consistency with the Squirro service defaults. Replace it with a secure password in production deployments. > > > > For supported PostgreSQL version compatibility, visit the [Squirro Support website](https://go.squirro.com/support) and submit a technical support request. ### Install PostgreSQL Install the PostgreSQL server on the Squirro node. On Rocky Linux 8 and Red Hat Enterprise Linux 8, install PostgreSQL with the `dnf` package manager: ```console # Install the repository RPM sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm # Turn off the built-in PostgreSQL module sudo dnf -qy module disable postgresql # Install PostgreSQL sudo dnf install -y postgresql16-server ``` After installation, initialize the database, then start and turn on the service: ```console # Initialize the database sudo /usr/pgsql-16/bin/postgresql-16-setup initdb # Turn on and start the systemd service sudo systemctl enable postgresql-16 sudo systemctl start postgresql-16 ``` To verify that the PostgreSQL server is running: ```console # Check the service status systemctl status postgresql-16 # Become the OS user that runs PostgreSQL sudo su - postgres # Open psql as the postgres user psql # List the existing databases \l ``` For more information, see the [PostgreSQL installation documentation](https://www.postgresql.org/download/linux/redhat/) website. ### Create Database Users and Databases Each Squirro service uses a dedicated database and role. Before creating the roles, confirm that PostgreSQL uses `scram-sha-256` as the password encryption method, so that the passwords created in the next step are hashed correctly. PostgreSQL 16 uses `scram-sha-256` by default. To set it explicitly, edit `/var/lib/pgsql/16/data/postgresql.conf`: ```ini password_encryption = scram-sha-256 ``` Reload the configuration, then create the roles and databases: ```console # Become the OS postgres user sudo su - postgres # Reload the settings /usr/pgsql-16/bin/pg_ctl reload # Create the Squirro roles and the per-service databases sq_users_dbs=('configuration' 'datasource' 'emailsender' 'filtering' 'genai' 'machinelearning' 'notes' 'plumber' 'scheduler' 'studio' 'topic' 'user') for sq_user_db in "${sq_users_dbs[@]}"; do # Escaped quotes are required because 'user' is a reserved word. # The encoding and locale are set explicitly so the result does not depend on the template database defaults. psql -c "create role \"${sq_user_db}\" with login password 'squirro/4u';" -c "create database \"${sq_user_db}\" with owner \"${sq_user_db}\" encoding 'UTF8' lc_collate 'en_US.UTF-8' lc_ctype 'en_US.UTF-8' template template0;" done ``` > **Note** > > The `create role` and `create database` commands fail if the role or database already exists. Run this step only once against a fresh PostgreSQL server. To rerun it, drop the existing roles and databases first. Verify that every Squirro database uses `UTF8` encoding with `en_US.UTF-8` collation and character type: ```console # List the databases and check their owners and encoding psql -c "\l" # Confirm that passwords were hashed using SCRAM-SHA-256 psql -c "select * from pg_authid;" ``` ### Configure Authentication Client authentication is controlled by the `pg_hba.conf` file. In a single-node deployment, the Squirro services and the PostgreSQL server run on the same node, so the relevant connection type is `host` on the loopback address. Squirro recommends the `scram-sha-256` authentication method. Edit `/var/lib/pgsql/16/data/pg_hba.conf` to use `scram-sha-256` for IPv4 and IPv6 loopback connections. PostgreSQL 16 uses this method by default: ```text # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 scram-sha-256 # IPv6 local connections: host all all ::1/128 scram-sha-256 ``` After changing the file, reload the settings: ```console sudo su - postgres /usr/pgsql-16/bin/pg_ctl reload ``` ### Configure Squirro Services Point each Squirro service at PostgreSQL by editing the `db` option in the configuration files in `/etc/squirro/`. The PostgreSQL connection string uses the format `postgresql://:@localhost/`, for example `postgresql://configuration:squirro/4u@localhost/configuration`. The following snippet updates the `db` option in every affected configuration file: ```console for config_file in /etc/squirro/*.ini; do sed -i "s/^db = mysql+mariadbconnector\(.*\)?charset=utf8$/db = postgresql\1/" "$config_file" done ``` > **Note** > > This command only rewrites `db` lines that use the `mysql+mariadbconnector` driver and end in `?charset=utf8`. Lines in any other format are left unchanged, so confirm the result with the verification step below. The command also preserves the existing user name, password, and host from the MariaDB connection string. Adjust them manually if your PostgreSQL roles use different credentials. To confirm that every service now points at PostgreSQL: ```console grep -E '^db =' /etc/squirro/*.ini ``` The GenAI service stores the database configuration in the `/etc/squirro/genai.d/run.sh` script rather than in an `.ini` file. Change the line that sets the `SQ_DATABASE_URL` environment variable from: ```shell echo "SQ_DATABASE_URL=mysql+pymysql://genai:squirro/4u@sql/genai?charset=utf8mb4" ``` to: ```shell echo "SQ_DATABASE_URL=postgresql+psycopg2://genai:squirro/4u@localhost/genai" ``` > **Important** > > The GenAI database configuration reverts to the MariaDB default on every instance upgrade. To prevent the upgrade from overwriting the configuration, turn off the instance upgrade. Go to Server → Upgrade Instance and set the `enable_upgrade_instance` option to `false` in `/etc/squirro/topic.ini`: > > > > ```ini > [studio] > # Set to true to turn on the upgrade_instance plugin > enable_upgrade_instance = false > ``` > > > > Restart the `topic` and `topicproxy` services after the change: > > > > ```console > systemctl restart sqtopicd > systemctl restart sqtopicproxyd > ``` Finally, switch the deployment over to PostgreSQL: ```console # Stop the Squirro services squirro_stop # Stop and turn off MariaDB systemctl stop mariadb systemctl disable mariadb # Restart the Squirro services squirro_restart ``` If the `mariadb` service keeps restarting after it is turned off, mask it: ```console systemctl mask mariadb ``` For setup guidance specific to your environment, visit the [Squirro Support website](https://go.squirro.com/support) and submit a technical support request.