You are here: Administrator's Guide > Installation Options > Distributed Installation > Installing for N-Tier Architecture (3-Tier) > Database Configuration - PostgreSQL

Database Configuration - PostgreSQL

PostgreSQL is an open source database that can be downloaded from the Internet. For more information, see the PostgreSQL web site at:

http://www.postgresql.com

It’s assumed that PostgreSQL is already installed .

One notation used is <FMEServerDir>, which is the installation directory of FME Server. In Windows, this is typically C:\Program Files\FMEServer.

It’s also assumed that you have JRE 5 or higher and PostgreSQL database installed before you proceed further. If you want to use the JDBC driver, there are several steps to perform.

If you are upgrading, you should back up any job history you want to keep.

ClosedWindows

This section describes how to configure a PostgreSQL database. You can configure this database from other PostgreSQL database tools, depending on your personal preference.

For a PostgreSQL database configuration, various SQL scripts are provided to assist you. These SQL scripts are located in the following directory:

<FMEServerDir>\Server\database\postgresql

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

  • postgresql_createUser.sql – This script creates the FME Server database user and password, and grants all required permissions to this user. By default, both the user name and password are fmeserver.
  • postgresql_createDB.sql – This script creates the FME Server database.
  • postgresql_createSchema.sql – This script creates all FME Server related tables, indexes, views, and triggers.
  • postgresql_dropUser.sql – This script drops the FME Server database user.
  • postgresql_dropDB.sql – This script drops the FME Server database.

Using the Command Prompt

To use the command prompt to configure the database, follow these instructions:

  1. Install PostgreSQL SQL shell (psql) if you haven’t done so already.
  2. Open psql and log on as a user with the appropriate privileges:

  3. Create an FME Server database user as follows:
    • From the SQL prompt, run the postgresql_createUser.sql script by entering the following command:

      • \i '<FMEServerDIR>/Server/database/postgresql/postgresql_createUser.sql'

    • By default, the provided SQL script creates an FME Server database user named fmeserver with the password fmeserver:

  4. Create an FME Server database.

    5. From the SQL prompt, run the postgresql_createDB.sql script by entering the following command:

    \i '<FMEServerDIR>/Server/database/postgresql/postgresql_createDB.sql'

    By default, the provided SQL script creates an FME Server database named fmeserver and grants all privileges on the database to user fmeserver.

  5. Quit psql and log on as the FME Server database user.

  6. Create the FME Server database schema as follows:

    7. From the SQL prompt, run the postgresql_createSchema.sql script by entering the following command:

    \i '<FMEServerDir>/Server/database/postgresql/postgresql_createSchema.sql'

    By default, the provided SQL script creates all FME Server related tables, indexes, views, and triggers.

Note: The preceding example assumes your user name and password are fmeserver.

ClosedLinux

This section describes configuring PostgreSQL from both the command prompt and a user interface, such as the PostgreSQL Query Browser and the PostgreSQL Administrator.

For PostgreSQL configurations, various SQL scripts are provided to help with PostgreSQL database configuration. These SQL scripts are located in the <FMEServerDir>/Server/database/postgresql/ directory of the FME Server Core machine.

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

  • postgresql_createUser.sql – This script creates the FME Server database user and password, and grants all required permissions to the FME Server database user. By default the user is named fmeserver and the password is fmeserver.
  • postgresql_createDB.sql – This script drops the FME Server database and creates a new one with the required FME Server tables. By default this database is named fmeserver. Be careful when running this script because it drops the existing FME Server database.
  • postgresql_createSchema.sql – This script creates all FME Server related tables, indexes, views, and triggers.

    • Note: This script must be run as user fmeserver.

  • postgresql_dropDB.sql – This script drops the existing FME Server database, which is named fmeserver by default.
  • postgresql_dropUser.sql – This script drops the FME Server database user.

Using the Command Prompt

To use the command prompt to configure the database, follow these instructions:

  1. Install PostgreSQL Server if you haven’t already.
  2. Open a command prompt and change to the following directory, which contains the PostgreSQL SQL scripts:

    3. <FMEServerDir>/Server/database/postgresql/

  3. Run the postgresql program with the postgresql_createUser.sql script:

    psql -d postgres -h <hostname> -p <port> -f postgresql_createUser.sql

  4. Run the postgresql program with the postgresql_createDB.sql script:

    psql" -d postgres -h <hostname> -p <port> -f postgresql_createDB.sql

  5. Run the postgresql program with the postgresql_createSchema.sql script, as user fmeserver:

    psql -U fmeserver -d fmeserver -h <hostname> -p <port> -f postgresql_createSchema.sql

Note: If you drop the FME Server database and login user, run the postgresql program with the appropriate postgresql_dropDB.sql or postgresql_dropUser.sql scripts.