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.

 

PostgreSQL-Integration-Synchronization-Codeless.png

 

 

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.

 

PostgreSQL-Data-Source-SharePoint.JPG

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 programm to install the required components.

 

Postgres-data-provider-installation.jpg

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. 

 

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.

 

Layer2-Cloud-Conenctor-Create-New-Connection.JPG

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

 

 

postgresql-connection-to-sync-to-sharepoint.jpg

 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. 

 

PostgreSQL-Configuration-In-Layer2-Cloud-Connector.jpg

 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. 

 

SharePoint-Configuration-In-Layer2-Cloud-Connector.jpg

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

 

 

Please note:

  • Give the data entity a title to refer to.
  • Select the Layer2 SharePoint Provider (CSOM) to connect to SharePoint or Office 365. If it does not appear in the list it is not installed. Please check 32-/64-bit issues and install from the Cloud Connector package.
  • Enter a valid connection string with the parameters URL, List, View, Authentication, User, Password. Find more about connecting to SharePoint and Office 365.
  • 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.

 

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.

 

Postgres-SharePoint-Field-Column-Mapping.jpg

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. 

 

Postgres-SharePoint-Integration-First-Synchronization.jpg

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.

 

PostgreSQL-Data-Connected-to-SharePoint.JPG

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 [email protected] in case of any questions or register and download the Layer2 Cloud Connector now.

Ready to go next steps?

Icon for Product Regsitration - Layer2 leading solutions

Register for free download.

Keep your Sharepoint in sync. Download and try today.

Contact Us Icon for Layer2 leading solutions

Questions? Contact us.

We are here to help. Contact us and our consulting will be happy to answer your questions.