Versions Compared

Old Version 6

changes.mady.by.user user-b885f

Saved on

compared with

New Version 7

changes.mady.by.user user-b885f

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

You can configure your DataSync Agent to share data from ServiceNow or Salesforce to a PostgreSQL database by changing some additional configurations in your agent.xml file. 


Prerequisites

(warning) First, you will need to Install the DataSync Agent.

(warning) You will also need to create a ServiceNow dynamic share/bulk share or create a Salesforce dynamic share/bulk share.

(warning) Make sure to stop running your DataSync Agent before making any Agent configuration changes.

(warning) Finally, you will need to create a database in PostgreSQL that will store data replicated via the DataSync Agent.


Procedure

To set up your DataSync Agent to share application data to a PostgreSQL database, follow these steps:

UI Steps


UI Step

Add the PostgreSQL JDBC driver

Add the latest PostgreSQL JDBC driver to your DataSync Agent's extlib directory.


UI Step

Install and configure your DataSync Agent

Follow the steps to Install the DataSync Agent or the DataSync for Salesforce Agent and make sure that your agent.xml file is configured correctly.


UI Step

Access your agent.xml configuration file

Navigate to the directory where you saved your agent.xml file when installing your DataSync Agent.


UI Step

Update database directives to point to PostgreSQL

Open your agent.xml file in a text editing application. Then, locate the <task> directive(s) within your <subscribe> directive, and update the following database-related directives:

DirectiveValue to enterRequired?
<database_type>postgresYes
<database_server>

URL/IP address for your PostgreSQL database

Yes
<database_port>5432Yes
<database_user>Username used to access your PostgreSQL databaseYes
<database_password>Password used to access your PostgreSQL databaseYes
<database_parms>

Optional database parameters for the connection string used to connect to PostgreSQL.

For example, if you want to use the schema psp_schema, you would specify:

<database_parms>currentSchema=psp_schema</database_parms>  

No



UI Step

Add the <skip_database_creation/> directive

Still within the <subscribe> → <task> directive(s), nest the following directive:

Directive

Description

<skip_database_creation/>

Bypasses the dynamic creation of the replicated database

(info) NOTE: This directive is added for a PostgreSQL replication scenario with the DataSync Agent, as you must create the database used for replication in PostgreSQL before configuring your DataSync Agent

Your agent.xml file should look similar to the example shown below:

Code Block
languagexml
themeEclipse
<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <agent>
       	<share>
     	</share>  
     	<subscribe>
     	<task instances="1">
            <task_name>test_postgresql_subscribe</task_name>
            <message_connection password="encrypted:vlOtU71yu8N/EFIJH85SSBtaIt7qEEfvqiqft9VZyYE=" user="exampleuser" queue="psp.out.replicator.example">https://example.perspectium.net</message_connection>
            <instance_connection password="encrypted:vlOtU71yu8N/EFIJH85SSBtaIt7qEEfvqiqft9VZyYE=" user="example.user">https://myinstance.service-now.com</instance_connection>
            <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
            <decryption_key>The cow jumped over the moon</decryption_key>
            <database_type>postgres</database_type>
            <database_server>localhost</database_server>
            <database_port>5432</database_port>
            <database_user>exampleuser</database_user>
            <database_password>examplepassword</database_password>
            <database>psp_db</database>
         </task>
      </subscribe>
      <max_reads_per_connect>4000</max_reads_per_connect>
      <polling_interval>5</polling_interval>
   </agent>
</config>



UI Step

Save your agent.xml file

Save the changes you made to your agent.xml file and close the file.


UI Step

Run your DataSync Agent

After configuring your agent.xml file to support replication to your PostgreSQL database, start running your DataSync Agent again.




Additional Configurations

For optional configurations for how the DataSync Agent interacts with your PostgreSQL database. Contact Perspectium Support if you have any questions.

UI Expand
titleUsing the timestamp with time zone data type when the Agent is on a server with non-UTC time

By default, the DataSync Agent will set datetime fields with the timestamp data type which will not have a time zone. The PostgreSQL JDBC driver as used by the Agent does not support a time zone parameter so any datetime values are passed in based on the time zone of the server where the Agent is running.

If you are running the Agent on a server with a non-UTC time zone and want to ensure records are saved properly so you can query the PostgreSQL database in different time zones, do the following: 


Divbox
stylebackground:white;

(warning) Acquire the databases.xml from Perspectium Support.

UI Steps
sizesmall



UI Step

Move the databases.xml into the Agent's conf folder i.e. <Perspectium_Replicator_Agent_Installed_Directory>/conf

Then, in databases.xml, you will need to change the following values in the <database_type>postgresql</database_type> section:

Old ValueNew Value
TIMESTAMPTIMESTAMP WITH TIME ZONE

Snippet of databases.xml:

Code Block
<database>
    <database_type>postgresql</database_type>
    <datatypes>
        .
        <mapping sn_type="93" db_type="93">TIMESTAMP WITH TIME ZONE</mapping>
        . 
    </datatypes>
</database>



UI Step

Edit the wrapper.conf located in the Agent's conf folder i.e. <Perspectium_Replicator_Agent_Installed_Directory>/conf to add a new Java Additional Parameter.

Look for the line # Java Additional Parameters section and the highest number parameter listed and then add the following line:

wrapper.java.additional.<Highest Number + 1>=-Duser.timezone=GMT 

Replacing <Highest Number + 1> with the current highest number parameter and adding one. For example, if the current highest number 3, this value would be 4 i.e. wrapper.java.additional.4=-Duser.timezone=GMT

Snippet of wrapper.conf:

Code Block
# Java Additional Parameters
wrapper.java.additional.1=-Djava.util.logging.config.file=../conf/logging.properties
wrapper.java.additional.2=-Dlog4j.configurationFile=../conf/log4j2.xml
wrapper.java.additional.3=-Dfile.encoding=UTF-8
wrapper.java.additional.4=-Duser.timezone=GMT

This will ensure the PostgreSQL JDBC driver is saving data into the database in UTC time. You can also modify the server the Agent is running on to be in UTC time if you prefer not to make this change to the wrapper.conf.




Since ServiceNow's data is sent out is in UTC time, doing the above ensures data is saved properly in UTC time in the database. You can now query in different time zones by using the SET TIME ZONE '<TIME ONE>'; SQL command where <TIME ZONE> is the desired time zone to query in. For example: 

SET TIME ZONE 'US/Eastern';

Will allow you to query for records in the US Eastern Timezone. See SET for more information.

(info) NOTE: Using SET only applies the time zone for the current session. Contact your Database/System Administrator to change the default time zone for your PostgreSQL environment as this may require modifying the postgresql.conf main configuration file and restarting the PostgreSQL service.