Connecting Xtraction to a PostgreSQL database

Version 3

    Xtraction has the ability to connect to PostgreSQL databases, however for this to work, a PostgreSQL .NET Data Provider needs to be installed on the Xtraction server. The instructions below detail how to configure the Xtraction server to enable connections to a PostgreSQL database.

     

    1. Download and install the PostgreSQL .NET Data Provider on the Xtraction Server

     

    The PostgreSQL .NET Data Provider that has been tested and works with Xtraction is called Npgsql. The latest release of Npgsql is available here. Download the msi file and run this file on the Xtraction server to install the .NET data provider. Accept all defaults to install the data provider to the Xtraction server.

     

    2. Copy the Npgsql.dll file to the Tools and Bin folders

     

    During the installation of the .NET data provider, a file named Npgsql.dll will be installed to the following directory: C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Npgsql\v4.0_3.0.5.0__5d8b90d52f46fda7. If this directory does not exist, search your computer for the file Npgsql.dll. Copy the file Npgsql.dll to the following locations (default Xtraction folders provided below):

    • C:\Program Files (x86)\Xtraction Software\Xtraction\Tools
    • C:\Program Files (x86)\Xtraction Software\Xtraction\Web\Server\bin

     

    3. Create a .config file for DataModelEditor.exe, Xtraction.SchedulerService.exe and Xtraction.AlertService.exe

     

    In the Xtraction Tools folder (default location C:\Program Files (x86)\Xtraction Software\Xtraction\Tools) create the following text files if they don't already exist:

    • C:\Program Files (x86)\Xtraction Software\Xtraction\Tools\DataModelEditor.exe.config
    • C:\Program Files (x86)\Xtraction Software\Xtraction\Tools\Xtraction.SchedulerService.exe.config
    • C:\Program Files (x86)\Xtraction Software\Xtraction\Tools\Xtraction.AlertService.exe.config

     

    If the file already exists, it will need to be edited, which is covered in the following steps. Ensure a backup of the .config file is taken before making any changes to the existing file.

     

    4. Add the system.data section to the config files

     

    Open DataModelEditor.exe.config in a text editor such as Notepad. If the file was created in the previous step, add the following text to the file:

     

    <?xml version="1.0" encoding="utf-8"?>

    <configuration>

      <system.data>

        <DbProviderFactories>

          <remove invariant="Npgsql" />

          <add name="Npgsql Data Provider" invariant="Npgsql" description=".Net Framework Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql, Version=3.0.5.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />

        </DbProviderFactories>

      </system.data>

    </configuration>

     

    Note the Version attribute above will need to match the version of Npgsql that has been installed.

     

    If the file DataModelEditor.exe.config already exists and has a <system.data> section and <DbProviderFactories> section, you will only need to add the 2 lines between <DbProviderFactories> and </DbProviderFactories>. Save the changes you have made to the config file. An example of the DataModelEditor.exe.config file has been attached to this document.

     

    Repeat this process for Xtraction.SchedulerService.exe.config and Xtraction.AlertService.exe.config. The file Xtraction.AlertService.exe.config will already exist for many Xtraction installations, in which case you will need to add everything from <system.data> to </system.data> directly under the <configuration> section of the config file.

     

    5. Update web.config file

     

    Take a backup of the file web.config found in the default folder of C:\Program Files (x86)\Xtraction Software\Xtraction\Web\Server. Open web.config in a text editor and add the same <system.data> section to the <configuration> section of the file. Again, the Version attribute above will need to match the version of Npgsql that has been installed.

     

      <system.data>

        <DbProviderFactories>

          <remove invariant="Npgsql" />

          <add name="Npgsql Data Provider" invariant="Npgsql" description=".Net Framework Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql, Version=3.0.5.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />

        </DbProviderFactories>

      </system.data>

     

    Save the changes to the web.config file. Xtraction should now be able to connect to PostgreSQL databases.

     

    6. Test connection to a PostgreSQL database in the Data Model Editor

     

    Open the Data Model Editor and add a new PostgreSQL data source to a new or existing data model. Enter the details of your PostgreSQL database server in the Connection Details form (example below). The default port for PostgreSQL is 5432.

     

    PostgreSQLConnection.png

     

    Click the Test button to confirm the connection to the PostgreSQL database is working successfully.