FME Flow: 2025.2

Configure the FME Flow Database on a Separate Database Server

Note  Complete this step only if you are configuring the FME Flow Database on your own database server, and did not install the PostgreSQL database that is included with the FME Flow installer. For more information, see Provide a Database Server.

To configure FME Flow to use a separate database server, follow the steps below. You can configure FME Flow with a PostgreSQL (recommended), Microsoft SQL Server, or Oracle database, running on either Windows or Linux.

Note  PostgreSQL is the recommended database server with FME Flow, offering enhanced performance and stability through targeted optimizations.

One notation used is <FMEFlowDir>, which is the installation directory of FME Flow. This is typically C:\Program Files\FMEFlow on Windows and /opt/fmeflow on Linux.

1. Stop All FME Flow Services on All Machines

When changing database providers or when initially setting up FME Flow to use a separate database server, stop all FME Flow services first.

Windows only: With all FME Flow services stopped, ensure memurai.exe (or redis-server on older FME Flow installations) is not present. Open Task Manager and click on the Details tab to view all running processes. Review the list for memurai.exe and end the task if it is still running.

2. Configure the Database

In this section, you will set up FME Flow Database objects and users with permissions to access the database. To ensure a successful database configuration, this procedure is best performed by a database administrator or in consultation with one.

During the installation of FME Flow, you were prompted to enter a database username (default fmeflow) and password. The installer uses these values to populate the database scripts and the JDBC connection string found in fmeDatabaseConfig.txt. It is expected that you will be using these values. If you want to change them, see Providing Your Own FME Flow Database Schema, Name, or User.

If you are upgrading, you should back up any FME Job Logs you want to keep, as these logs are not part of the FME Flow backup.

Note  The specific way to set up database objects and user permissions on various production databases may differ depending on the available database tools and intended target platform.

PostgreSQL is an open source database that can be downloaded from the Internet. For more information, see https://www.postgresql.com.

FME Flow comes shipped with the necessary PostgreSQL JDBC driver, which is located in <FMEFlowDir>Utilities\jdbc.

Before proceeding, make sure that:

  • PostgreSQL is installed
  • You know the credentials for the PostgreSQL superuser or equivalent.
  • You have access to pgAdmin or a similar database client to connect to the PostgreSQL database server to run the necessary scripts.

To create the fmeflow user and database objects, three SQL scripts are provided. Additional scripts are provided to drop the configured user and database. These scripts are located in the following directory:

<FMEFlowDir>\Server\database\postgresql

Note  This directory refers to the machine on which the FME Flow Core is installed (not the machine on which the database server is installed). To run these scripts, you must transfer this directory to the machine from where you can run the scripts, or reference it through a UNC path.

The scripts are:

  • postgresql_createUser.sql: Creates the FME Flow database user with the name you specified during installation, and grants it all required permissions. The password appears as <<DATABASE_PASSWORD>>. Before running this script, replace <<DATABASE_PASSWORD>> with the password that was specified for the database during the FME Flow installation, in single quotes.
  • postgresql_createDB.sql: Creates the FME Flow Database.
  • postgresql_createSchema.sql: Creates all FME Flow-related tables, indexes, views, and triggers.
  • postgresql_dropUser.sql : Drops the FME Flow Database user.
  • postgresql_dropDB.sql: Drops the FME Flow Database.

Instructions

Using your preferred PostgreSQL database client, follow these instructions to configure the FME Flow database:

  1. Connect to the PostgreSQL database server as the postgres user or a user with similar administrative privileges.

  2. Create an FME Flow database user:
    1. Open postgresql_createUser.sql in a text editor.
    2. Replace <<DATABASE_PASSWORD>> with the password specified during the FME Flow installation, in single quotes.

    3. Using your database client, run the postgresql_createUser.sql script. The script creates the FME Flow database user and password that you specified during installation.

  3. Create the FME Flow Database:

    4. Open and run the postgresql_createDB.sql script.

    Note  This script will work through PostgreSQL SQL Shell, but cannot be run as-is through pgAdmin, as it requires switching databases using “\c”. If using pgAdmin, you will need to execute the first two commands in the sql script:

    • CREATE DATABASE fmeflow;

    • GRANT ALL PRIVILEGES ON DATABASE fmeflow TO "fmeflow";

    Then, connect to the new fmeflow database as the postgres admin user and run the last command:

    • GRANT CREATE ON SCHEMA public TO "fmeflow";

    The script creates a new, empty FME Flow database named fmeflow and grants all privileges on the database to the user.

  4. Create the FME Flow Database schema:

    1. Using your preferred database client, connect to the fmeflow database as the fmeflow user.

    2. Open and run the postgresql_createSchema.sql script. The script creates all FME Flow related tables, indexes, views, and triggers.

If you need to drop the FME Flow user, run the postgresql_dropUser.sql script as the postgres user or equivalent. If you need to drop the FME Flow Database, run the postgresql_dropDB.sql script as the postgres user or equivalent.

FME Flow ships with the necessary SQL Server JDBC driver, located in <FMEFlowDir>Utilities\jdbc.

Before proceeding, make sure that:

Before proceeding, make sure that:

  • SQL Server is installed
  • You know the credentials for the sa superuser or equivalent.
  • You have access to SQL Server Management Studio or a similar database client to connect to the SQL Server database server to run the scripts.

To create the fmeflow user, login, and database objects, two SQL scripts are provided. Additional scripts are provided to drop the configured user and database. These scripts are in the following directory:

<FMEFlowDir>\Server\database\sqlserver\.

Note  This directory refers to the machine on which the FME Flow Core is installed (not the machine on which the database server is installed). To run these scripts, you must transfer this directory to the machine from where you can run the scripts, or reference it through a UNC path.

The scripts are:

  • sqlserver_createUser.sql: Creates the FME Flow login and user with the name you specified during installation, and grants all required permissions. The password appears as <<DATABASE_PASSWORD>>. Before running this script, replace <<DATABASE_PASSWORD>> with the password that was specified for the database during the FME Flow installation, in single quotes.
  • sqlserver_createDB.sql: Creates the FME Flow Database, including tables, indexes, views, and triggers..
  • sqlserver_dropUser.sql : Drops the FME Flow Database user.
  • sqlserver_dropDB.sql: Drops the FME Flow Database.

Instructions

Using your preferred SQL Server database client, follow these instructions to configure the FME Flow Database:

  1. Connect to the SQL Server as the sa user or a user with similar administrative privileges.

  2. Create the FME Flow Database:

    3. Open and run the sqlserver_createDB.sql script.

  3. Create the FME Flow user and login:
    1. Open sqlserver_createUser.sql.
    2. Replace <<DATABASE_PASSWORD>> with the password specified during the FME Flow installation, in single quotes.

    3. Using your database client, run the sqlserver_createUser.sql script. The script creates the login and user, and grants the user access to the newly created database and makes it the owner.

If you need to drop the FME Flow user, run the sqlserver_dropUser.sql script as the sa user or equivalent. If you need to drop the FME Flow Database, run the sqlserver_dropDB.sql script as the sa user or equivalent.

Before proceeding, make sure that:

  • Oracle database server is installed.
  • You know the credentials for the SYS superuser or equivalent.

  • You have access to a database client to connect to the Oracle database server and run the necessary scripts. Examples include Oracle SQL Developer, DBeaver, and SQLPlus command line.

  • If using an Oracle database server, you must obtain the Oracle Database JDBC driver and place it in the following directory:

    • Windows:
      • <FME FlowDir>\FMEFlow\Utilities\jdbc
    • Linux:
      • /opt/fmeflow/Utilities/jdbc

    Driver versions recommended: FME Flow uses Java SE Development Kit (JDK) 17, so the latest versions of ojdbc11.jar and ojdbc17.jar are recommended.

    Driver versions not recommended: All ojdbc10.jar versions.

    For more information, see https://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html.

    • If you encounter an issue with a newer version of the driver, try an older version.

    Consider the version of the database server when selecting the appropriate driver version.

To create the fmeflow user and database objects, two SQL scripts are provided. Additional scripts are provided to drop the configured user and database. These scripts are in the following directory:

<FMEFlowDir>\Server\database\oracle\.

Note  This directory refers to the machine on which the FME Flow Core is installed (not the machine on which the database server is installed). To run these scripts, you must transfer this directory to the machine from where you can run the scripts, or reference it through a UNC path.

The scripts are:

  • oracle_createUser.sql: Creates the FME Flow database user with the name you specified during installation, and grants it all required permissions. The password appears as <<DATABASE_PASSWORD>>. Before running this script, replace <<DATABASE_PASSWORD>> with the password that was specified for the database during the FME Flow installation.

    Considerations for TABLESPACE: Discuss with your Oracle DBA what tablespace the new user will use. You can alter the user with an additional statement to ensure proper write permissions to the tablespace.

    • Example: ALTER USER fmeflow QUOTA UNLIMITED ON users.

  • oracle_createDB.sql: Creates all FME Flow-related database packages, tables, indexes, views, and triggers.
  • oracle_dropUser.sql : Drops the FME Flow Database user and the database packages, tables, indexes, views, and triggers associated with the FME Flow database user.

Instructions

Using your preferred Oracle database client, follow these instructions to configure the FME Flow Database:

  1. Connect to the Oracle database server as the SYS user or a user with similar administrative privileges.

  2. Create the FME Flow Database user:
    1. Open oracle_createUser.sql.
    2. Replace <<DATABASE_PASSWORD>> with the password specified during the FME Flow installation.

    3. Consider the tablespace the user will use. Check with your database administrator if unsure.

    4. You may be required to create a new tablespace and alter the script accordingly.

    5. Using your database client, run the oracle_createUser.sql script.

  3. Create the FME Flow Database:

    1. Once the FME Flow database user has been created, log on again as the new user.
    2. Open and run the oracle_createDB.sql script.

If you need to drop the FME Flow user and database objects, run the oracle_dropUser.sql script as the SYS user or equivalent.

3. Enable Connections

FME Flow connects to the database when it starts. Therefore, ensure that your database server is running and configured to accept incoming connections before FME Flow is started. The database must allow connections over TCP/IP with all machines on which the FME Flow Application Server, FME Flow Core and FME Engines are installed.

4. Configure the Database Connection

The connection to the FME Flow Database is specified in configuration file fmeDatabaseConfig.txt. The FME Flow installer adds your connection parameters based on what was specified during installation.

To view the connection, open fmeDatabaseConfig.txt. Under FME SERVER SETTINGS START, find the section titled Database Connection. The parameters are below for reference:

  • DB_TYPE - Identifies the database server: postgresql, sqlserver, or oracle.
  • DB_DRIVER - The JDBC driver name used for connecting to the database.
  • DB_JDBC_URL - The JDBC URL used for connecting to the database.
  • DB_USERNAME - The database user name.
  • DB_PASSWORD - The database user password.
  • DB_CONNECT_EXPIRY - The database connection expiry time, in seconds.
  • DB_SQLSTMTS_PATH - The path to the SQL statement resource bundle.

An example is provided for each type of system database supported by FME Flow: PostgreSQL, SQL Server, and Oracle.

You may need to change these parameters, especially DB_JDBC_URL, depending on how your database server or database has been configured outside of FME Flow. For scenarios and guidance, see Configuring the FME Flow Database Connection.

After changing fmeDatabaseConfig.txt, FME Flow must be restarted.

Note that the database password in the connection parameters is encrypted during installation. If you are changing the FME Flow database after installation, the new database password must be manually encrypted. Follow the procedure in Encrypting the FME Flow Database Password. Alternatively, the database password can be provided as plain text in the connection parameters.

5. Start the FME Flow Services on All Machines

For more information, see Working with the FME Flow System Services.

What's Next?

Troubleshooting

If the FME Flow Web User Interface fails to present the login window, review the fmeserver.log, fmeprocessmonitorcore.log, and fmeconnection.log located in the FME Flow System Share, which is specified during installation: <fileserverUNCPath>/resources/logs/core/current

If you see errors about a failed database login in the logs and are unsure of the database password, you can provide it in plain text in the fmeDatabaseConfig.txt configuration file to test. The FME Flow services must be restarted after modifying the file.

For more troubleshooting information, see https://support.safe.com/hc/en-us/sections/25623298793101-Troubleshooting.