For optional configurations for how the DataSync Agent interacts with your PostgreSQL database. Contact Perspectium Support if you have any questions.
First, you will need to install a DataSync Agent configured to Set up DataSync Agent to share to PostgreSQL.
You will also need to create a ServiceNow dynamic share with an update trigger or create a ServiceNow bulk share.
Finally, make sure to stop running your DataSync Agent before making any Agent configuration changes.
Using 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:
Acquire the databases.xml configuration file from Perspectium Support.
Move 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 Value | New Value |
|---|---|
| TIMESTAMP | TIMESTAMP WITH TIME ZONE |
Snippet of databases.xml:
<database>
<database_type>postgresql</database_type>
<datatypes>
.
<mapping sn_type="93" db_type="93">TIMESTAMP WITH TIME ZONE</mapping>
.
</datatypes>
</database>
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:
# 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 and set its timezone to UTC if you prefer not to make this change to the wrapper.conf.