Configuring a Distributed Database

Power Operation can connect to a remote PostgreSQL database or to a local instance of PostgreSQL database that has not been installed by EPO. For a successful connection to the remote/local instance of PostgreSQL database, confirm the following setup tasks are completed prior to installation of EPO. If the local instance of the PostgreSQL database has been installed by a previous version of EPO, then no additional setup tasks are required. Contact the Schneider Electric Support team if you need support with PostgreSQL installation on your remote computer.

NOTE: It is recommended that you uncheck the pg_admin option during the manual installation of PostgreSQL database.

Prerequisites:

  • PostgreSQL is installed and running on the remote machine.
  • Confirm PostgreSQL is running on port 5432.
  • You have the PostgreSQL superuser credentials and password.

To configure PostgreSQL distributed database:

To configure PostgreSQL distributed database on Microsoft Windows:

  1. Copy and save the following scripts from the install ISO path

    ...:\Power Operation\Setup\Setup Support\PostgreSQL

    • create_default_po_user.sql
    • po_db_schema.sql
    • set_po_user_db_access.sql
  2. Open the create_default_po_user.sql script, update the password, and save the file.

    NOTE: The password saved in this file is the same password that you need to enter for the User Name po_data during EPO installation.

  3. Run the following commands from the postgresql bin directory:

    psql -U postgres -c "REVOKE CREATE ON SCHEMA public FROM PUBLIC;

  4. Run the following command from the postgresql bin directory:

    psql -U postgres -f ...\Users\<User ID>\<folder>\Scripts\create_default_po_user.sql.

  5. Run the following command from the postgresql bin directory:

    echo SELECT 'CREATE DATABASE po_scadadb' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'po_scadadb'); \gexec | "...\Program Files\PostgreSQL\14\bin\psql.exe" -U postgres

  6. Run the following command from the postgresql bin directory:

    psql -U postgres -d po_scadadb -f "...\Users\<User ID>\<folder>\Scripts\set_po_user_db_access.sql"

  7. Run the following command from the postgresql bin directory:

    psql -U postgres -d po_scadadb -c "CALL set_po_user_db_access('po_data', 'po_data_role'); DROP PROCEDURE IF EXISTS set_po_user_db_access(text, text);"

  8. Run the following command from the postgresql bin directory:

    psql -U postgres -d po_scadadb -f "...\Users\<User ID>\<folder>\Scripts\po_db_schema.sql"

To configure PostgreSQL distributed database on Linux machine:

  1. Copy and save the following scripts from the install ISO path

    ...:\Power Operation\Setup\Setup Support\PostgreSQL

    • create_default_po_user.sql
    • po_db_schema.sql
    • set_po_user_db_access.sql
  2. Open the create_default_po_user.sql script, update the password, and save the file.

    NOTE: The password saved in this file is the same password that you need to enter for the User Name po_data during EPO installation.

  3. Run the following commands from the postgresql bin directory:

    psql -U postgres -c "REVOKE CREATE ON SCHEMA public FROM PUBLIC;

  4. Run the following command from the postgresql bin directory:

    psql -U postgres -f "...\Users\<User ID>\<folder>\Scripts\create_default_po_user.sql".

  5. Run the following command from the postgresql bin directory:

    CREATE DATABASE po_scadadb -U postgres

  6. Run the following command from the postgresql bin directory:

    psql -U postgres -d po_scadadb -f "...\Users\<User ID>\<folder>\Scripts\set_po_user_db_access.sql"

  7. Run the following command from the postgresql bin directory:

    psql -U postgres -d po_scadadb -c "CALL set_po_user_db_access('po_data', 'po_data_role'); DROP PROCEDURE IF EXISTS set_po_user_db_access(text, text);"

  8. Run the following command from the postgresql bin directory:

    psql -U postgres -d po_scadadb -f "...\Users\<User ID>\<folder>\Scripts\po_db_schema.sql"