Database guide for enterprise administrators

Estimated reading: 8 minutes

The Robility flow database stores data that is essential for more Robility flow operations, including startup, flow execution, user interactions, and administrative tasks. The database supports both frontend (visual editor) and backend (API) operations, making its availability critical to Robility flow’s stability and functionality. For details about the database schema, see Memory management options.

This guide is designed for enterprise database administrators (DBAs) and operators responsible for deploying and managing Robility flow in production environments. It explains how to configure Robility flow to use PostgreSQL, including high availability (HA) and active-active configurations, as well as best practices for monitoring, maintenance, and security.

Configure Robility flow with PostgreSQL

Robility flow’s default database is SQLite. However, PostgreSQL is recommended for production deployments due to its scalability, performance, and robustness.

The following steps explain how to configure Robility flow to use PostgreSQL for a standalone or containerized deployment. For more information, see Configure an external PostgreSQL database.

1. Set up PostgreSQL:

a. Deploy a PostgreSQL instance (version 12 or higher recommended) using a local server, Docker, or a managed cloud service.
b. Create a database for Robility flow.
c. Create a PostgreSQL user with appropriate, minimal permissions to manage and write to the database, such as CREATE, SELECT, INSERT, UPDATE, DELETE on your Robility flow tables.

2. Obtain the connection string in the format postgresql://user:password@host:port/dbname, such aspostgresql://Robility flow: securepassword@postgres:5432/Robility flow

For High Availability, use the virtual IP or proxy hostname instead of the direct database host. For more information, see High Availability for PostgreSQL.

3. Configure Robility flow with the .env or docker-compose.yml files.
.env

i. Create a .env file in the Robility flow directory:

touch .env

ii. Add the connection string to the .env file:

ROBILITY_FLOW_DATABASE_URL="postgresql://Robility_flow:securepassword@postgres:5432/Robility_flow"

For more environment variables, see the .env.example file in the Robility flow repository.

4. Start Robility flow with your PostgreSQL connection:

uv run Robility flow run --env-file .env

5. Optional: Run migrations.

Robility flow uses migrations to manage its database schema. When you first connect to PostgreSQL, Robility flow automatically runs migrations to create the necessary tables.

Direct schema modification can cause conflicts with Robility flow’s built-in schema management. If you need to update the schema, you can manually run migrations with the Robility flow CLI:

i. Run Robility flow migration to preview the changes.
ii. Review the changes to ensure that it’s safe to proceed with the migration.
iii. Run Robility flow migration –fix to run the migration and permanently apply the changes.

This is a destructive operation that can delete data. For more information, see Robility flow migration.

6. To verify the configuration, create any flow using the Robility flow visual editor or API, and then query your database to confirm the tables and activity are recorded there. The content of the flow doesn’t matter; you only need to confirm that the flow is stored in your PostgreSQL database. You can query the database in two ways:

a. Query the database container:

docker exec -it <postgres-container> psql -U robilityflow -d robilityflow

b. Use SQL:

SELECT * FROM pg_stat_activity WHERE datname = 'Robility flow';

High Availability for PostgreSQL

To further improve performance, reliability, and scalability, use a High Availability (HA) or Active-Active HA PostgreSQL configuration. This is recommended for production deployments to minimize downtime and ensure continuous operations if your database server fails, especially when multiple Robility flow instances rely on the same database.

High Availability (HA)

1. Set up streaming replication:

a. Configure one primary database for writes.
b. Configure one or more replicas for reads and failover.

Select either synchronous or asynchronous replication based on your latency and consistency requirements.

2. Implement automatic failover using one of the following options:

a. Use an HA orchestrator, distributed configuration store, and traffic router, such as Patroni, etcd or Consul, and HAProxy.
b. Use Pgpool-II alone or add supporting services for more robust HA support.
c. Use managed services that provide built-in HA with automatic failover, such as AWS RDS or Google Cloud SQL.

3. Update your PostgreSQL connection string to point to the HA setup. If you have a multi-instance deployment, make sure all of your Robility flow instances connect to the same HA PostgreSQL database.

The connection string you use depends on your HA configuration and services.

a. Use a virtual IP or DNS name that resolves to the current primary database, such as postgresql://Robility flow :securepassword@db-proxy:5432/Robility flow ?sslmode=require.
b. Use the provided endpoint for a managed service, such as Robility flow cluster-xyz.us-east-1.rds.amazonaws.com.

4. Optional: Implement load balancing for read-heavy workloads:

a. Use a connection pooler like PgBouncer to distribute read queries across replicas.
b. Configure Robility flow to use a single connection string pointing to the primary PostgreSQL database or proxy.

Active-Active HA

To implement Active-Active HA, you must deploy multiple Robility flow flow instances, use load balancing to distribute traffic across the instances, and ensure all instances connect to the same HA PostgreSQL database:

1. Deploy multiple Robility flow flow instances using Kubernetes or Docker Swarm.

You must configure your instances to use a shared PostgreSQL database. For more information, see Best practices for Robility flow flow on Kubernetes.

2. Set up streaming replication:

a. Configure one primary database for writes.
b. Configure one or more replicas for reads and failover.

Select either synchronous or asynchronous replication based on your latency and consistency requirements.

3. Implement automatic failover using one of the following options:

4. Update your PostgreSQL connection string to point to the HA setup. Make sure all of your Robility flow flow instances connect to the same HA PostgreSQL database.

The connection string you use depends on your HA configuration and services:

a. Use a virtual IP or DNS name that resolves to the current primary database, such as postgresql://Robility flow flow:securepassword@db-proxy:5432/Robility flow flow?sslmode=require.
b. Use the provided endpoint for a managed service, such as Robility flow flow.cluster-xyz.us-east-1.rds.amazonaws.com.

5. Use a load balancer to distribute requests across your instances.

The following example configuration is for a production deployment that has three Robility flow flow-runtime replicas, uses the Kubernetes load balancer service to distribute traffic to healthy pods, and uses the HA PostgreSQL database connection string.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: Robility flow flow-runtime
spec:
  replicas: 3
  selector:
    matchLabels:
      app: Robility flow flow-runtime
  template:
    metadata:
      labels:
        app: Robility flow flow-runtime
    spec:
      containers:
      - name: Robility flow flow
        image: Robility flow flowai/Robility flow flow:latest
        ports:
        - containerPort: 7860
        env:
        - name: ROBILITY_FLOW_FLOW_DATABASE_URL
          value: "postgresql://Robility flow flow:securepassword@db-proxy:5432/Robility flow flow?sslmode=require"
---
apiVersion: v1
kind: Service
metadata:
  name: Robility flow flow-runtime
spec:
  selector:
    app: Robility flow flow-runtime
  ports:
  - port: 80
    targetPort: 7860

After implementing HA or Active-Active HA, monitor failover events and ensure replicas are in sync. Robility flow , through SQLAlchemy, supports reconnection attempts if ROBILITY FLOW _DATABASE_CONNECTION_RETRY=True, ensures recovery after failover, and reduces disruption once the database is back online.

Although PostgreSQL handles concurrent connections well, you must still monitor contention, deadlocks, or other performance degradation during high load.

Impact of database failure

If the PostgreSQL database becomes unavailable, the following Robility flow functions will fail:

a. Flow Retrieval: Cannot load new or existing flows from the database.
b. Flow Saving: Unable to save new flows or updates to existing flows.
c. User Authentication: Login and user management functions fail.
d. Project Collection Access: Cannot access or share community/custom project collections.
e. Configuration Retrieval: Unable to load application settings.
f. Configuration Updates: Changes to settings cannot be saved.
g. Execution Log Access: Cannot retrieve historical flow execution logs.
h. Log Writing: New execution or system activity logs cannot be recorded.
i. Multi-User Collaboration: Sharing flows or projects across users fails.
j. API Flow Loading: API requests to load new flows (non-cached) fail.

Flows already loaded in memory may continue to function with cached configurations. However, any operation requiring database access fails until the database is restored. For example, a cached flow might run, but it won’t record logs or message history to the database.

To minimize the possibility and impact of database failure, use HA configurations and record backups regularly. For example, you can use pg_dump to create logical backups or set up continuous archiving with write-ahead logs (WAL) for point-in-time recovery. Test restoration procedures regularly to ensure your team understands how to execute them in a disaster recovery scenario.

Database monitoring

Monitor your PostgreSQL database to ensure optimal performance and reliability:

a. Use tools like pgAdmin, Prometheus with PostgreSQL exporter, or cloud-based monitoring for PostgreSQL.
b. Track performance metrics such as CPU, memory, and disk I/O usage.
c. Monitor replica health, availability, lag, and synchronization. For example, use pg_stat_activity to monitor connection counts and contention.
d. Set up alerts and notifications for high latency, failover events, or replication issues.
e. Enable PostgreSQL logging, such as log_connections and log_statements, to track access and changes.

Database guide for enterprise administrators

Configure Robility flow with PostgreSQL

High Availability for PostgreSQL

Impact of database failure

Database monitoring

Database guide for enterprise administrators

CONTENTS