PostgreSQL Integration and Synchronization with SharePoint or Office 365

PostgreSQL, often simply "Postgres", is an open source object-relational database management system (ORDBMS) that can be connected and synchronized with almost any other corporate data sources, e.g. native lists in Microsoft SharePoint or Office 365, codeless and bi-directional via the Layer2 Cloud Connector.

To connect and synchronize Postgres with SharePoint (or almost any other corporate data source) we need to create a sample table or query in Postgres first.

Fig.: Data table in Postgres to integrate with SharePoint or Office 365.

To connect and sync we need to install the Npgsql .NET Data Provider. You can download the provider here:

https://github.com/npgsql/npgsql/releases 

Please use the version that matches your installed Microsoft .NET Framework version.

Please execute the setup program to install the required components. Make sure that the provider is also installed into the GAC.

Fig.: Component selection required for PostgreSQL data integration.

Now we are ready to synchronize our contacts sample table from PostgreSQL with a native Microsoft SharePoint contact list, on-premises or even in the Microsoft Office 365 cloud. 

Fig.: The PostgreSQL sample table contacts has some usual columns to sync with SharePoint.

Please note:

• Use lowercase characters for table and column names.
• You need to specify a primary key containing unique values, „email“ in this sample table.

Configure Postgres as data source for data synchronization with SharePoint

​As a first step create a new connection in the Layer2 Cloud Connector Connection Manager as follows.

 Fig.: Create a new connection to synchronize PostgreSQL with SharePoint.

 Fig.: Configuring a bi-directional connection to connect PostgreSQL with SharePoint.

Please note:

• In this case a bi-directional synchronization is configured. You can use uni-directional connections as well to be sure taht noting is changed in your data source, if required.
• You can configure scheduling later on, after testing is finished. You can set appropiate sync intervals regarding your requirements, configuration, amount of data and data changes.
• You can select between different options for error management.
• You can make use of the "Run Now" toolbox to test your connection as it is configured.

Next we have to configure the Postgres data entity for sync as follows. 

 Fig.: Postgres data entity configuration in the Layer2 Cloud Connector Connection Manager.

Please note: 

• Give the data entity a title to refer to.
• Select the Npsql Data Provider to connect to PostgreSQL. If it does not appear in the list it is not installed. Please check 32-/64-bit issues.
• Enter a valid connection string with the parameters Server, User, Password and Database. Find more about PostgreSQL connection strings.
• Enter a valid data query (select statement): For this sample select all fields from contacts table. In case of a bi-directional synchronization make sure that your query is generally updatable, contains a primary key and you have appropiate access rights to modify data.

No configuration changes are required on the PostgreSQL side. Please verify all settings and display a data preview (in the actions menu, right hand side).

SharePoint or Office 365 configuration to integrate PostgreSQL

We have to configure the SharePoint or Office 365 data entity to sync with Postgres in a next step. No installation, configuration changes or administrative rights are required on the SharePoint side. You can use an existing list or create a new list, e.g. a contact list in this case. 

Fig.: SharePoint sample data entity configuration to sync with Postgres.

Please note:

Please verify all settings and display a data preview (via right hand side action menu).

Postgres and SharePoint Integration: Column & Field Mapping

When both data entities are well connected, we can go the mapping section to assign SharePoint columns (contact properties in this case) to Postgres data query fields.

Fig.: Sample field / column mapping to integrate a PostgreSQL query with a SharePoint contact list

 Please note:

• In case your fields and columns have the same names you can use the auto-mapping feature. Otherwise map manually.
• Take care of appropiate data types. The Cloud Connector processes data conversions, if required. For assignments of more complex SharePoint specific fields like person or group lookups, choice or others see FAQs.
• You can select a conflict resolution mode to better manage changes on both sides at the same time, e.g. PostgreSQL always wins or similar.

Please verify mapping to finish the configuration.

Postgres and SharePoint Integration: First Synchronization and Next Steps

​Now we are ready for the first synchronization. 

Fig.: First test of synchronization to fix possible issues and sync in backround later on.

As expected the 3 records from Postgres are inserted in the SharePoint contact list with this first sync. Please check the log for any possible errors or warnings. Best to sync again without any changes - no updates should appear in this case.

Fig.: Postgres data query synchronized bi-directional with a native SharePoint contact list.

PostgreSQL Integration: Where To Go Next

​As a next step you should make some changes on both sides and sync again. Only changes should be processed. Next go back to the connection settings and setup your scheduling, e.g. daily, each hour or every 15 minutes - depending on requirements.

Please note:

• A Windows Service will process changes as scheduled in background. You don't need to have the Connection Manager open or be logged in.
• You can setup logging to files (by default), but also to database or Windows Event Log (to be notified in case of any issues with the sync).
• You can sync to other SharePoint list types as well, as tasks, calendar events etc. Specific lists have certain features that are fully supported. For example you can sync SharePoint contacts down to Microsoft Office Outlook for offline availability.
• Your synchronized data are fully available for SharePoint / Office 365 search and mobile access (BYOD).
• You can add (not mapped) SharePoint only data, e.g. metadata or attachments.
• You can use the connector for reporting and data aggregation (multiple connections to one target list).
• You can add notifications and workflows to start business processes while external records are changing.
• This sample is about connecting Postgres to SharePoint and Office 365, but it will work with almost any other data entity as well. You can sync Postgres to any SQL database like SQL Server, Oracle, mySQL, ERP/CRM etc. via ODBC, OLEDB, OData, XML, Web Services etc. No problem to connect to systems like Microsoft CRM / ERP, SAP, Microsoft Exchange, Salesforce and many more using Layer2 or 3rd party data providers.

Contact sales@layer2solutions.com in case of any questions or register and download the Layer2 Cloud Connector now.

Explore frequently asked questions by topics.

Ready to go next steps?