HOW TO SYNC Postgresql WITH Sharepoint or office 365?
PostgreSQL, often simply "Postgres", is an open source object-relational database management system (ORDBMS). But how to sync corporate database content with Microsoft SharePoint or Office 365 to share in the cloud and access from anywhere on any device?
PostgreSQL can be connected and synchronized without code and bi-directionally 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 with very flexible sync options. No issues with syncing more than 5.000 items per list. See below for a step-by-step intro.
When you want to start your Layer2 Cloud Connector free trial you can click the button.
Step 1 - prepare your PostgreSQL source to integrate with Microsoft SharePoint
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.
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.
Step 2 - set up your PostgreSQL to SharePoint connection
This walk-through will be setting up a uni-directional sync from the specified PostgreSQL table to Microsoft SharePoint. It can be changed to bi-directional later, if you want to sync contents in both locations to each other. It is also a good idea to wait before enabling scheduling; this can be done after you confirm the connection is working normally with manual synchronizations.
Start with opening Layer2 Cloud Connector Connection Manager and click "Create New Connection". You can also duplicate an existing example connection as a starting place.
Please note:
- 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.
Step 3 - configure source data entity
Next we have to configure the Postgres data entity for sync.
Please note:
- Give the data entity a title to refer to.
- Select the PostgreSQL (Layer2) provider to connect to PostgreSQL.
- Enter a valid connection string with the parameters Server, User, and Database. Find more about PostgreSQL connection strings.
- Enter the password into the Password field beneath the connection string.
- 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).
Step 4 - target configuration to your Microsoft SharePoint Online list
Next comes the Target configuration to your SharePoint Online list.
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.
Please note:
- Give the data entity a title to refer to.
- Select the SharePoint (Layer2) provider to connect to SharePoint or Office 365.
- Enter a valid connection string with the parameters URL and Authentication
Find more about connecting to SharePoint and Office 365.
Please verify all settings and display a data preview (via right hand side action menu).
Step 5 - settings for column 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.
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.
Step 6 - run your configuration
Your configuration is now complete and you can run the initial file synchronization. Click on the main connection node (with the green plug) in the left-hand column to select the connection settings. At the bottom is a Run Now button. Press that to start the sync!
If the sync finishes with errors, check the Logs node for more details. You can increase the log level in the Connection Manager root and run again to receive more detailed information.
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.
Step 7 - mange your files in a SharePoint Online list
Now take a look at your SharePoint list. You should see all the contacts from the PostgreSQL database table here.
If you are satisfied with the results, you can enable scheduling in the Connection Manager. It can be set to run every hour, every 10 minutes, or even shorter, depending on your needs.
Please note the following information about the PostgreSQL SharePoint connection
- All options of SharePoint are fully available, such as search, versioning, change notifications via RSS or email, workflows etc. Your synchronized data are fully available for SharePoint / Office 365 search and mobile access (BYOD).
- Bi-directional connections are supported as well.
- Cloud Connector has no issues with the 5.000 items list view threshold. Some view options will become unavailable in this case. But you can still use your list with unsorted, ungrouped and unfiltered views or make use of indexed columns or managed metadata navigation. Search can be used without restriction to find data. However, take care to prepare your views before reaching the threshold as you may not be able to adjust them once you reach the limit. Alternatively, you can sync to several different lists to keep things below the threshold.
- You can add additional unmapped columns for use only in SharePoint, e.g. managed metadata or feedback columns. Unmapped columns are kept as-is during updates.
- You can update specific columns with other connections, such as with database content.
- You can add additional sync options like "no insert", "no update", "no delete" for specific requirements. Check the Advanced Settings section at the bottom of the data entities to access these.
- You can sync using the scheduling service, as well as on-demand (with command line).
- 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.
Reasons why you should synchronize your PostgreSQL data to SharePoint
Synchronizing PostgreSQL data tables with SharePoint Online lists can offer several advantages, depending on your specific business needs. Here are five reasons why you might want to consider such synchronization:
- Share PostgreSQL data with a wider audience within your organization. Enable seamless collaboration by providing a centralized platform for users to access and contribute to data stored in PostgreSQL.
- Leverage SharePoint's versioning, and workflow capabilities. Combine the strengths of both platforms, using PostgreSQL for relational data and SharePoint for collaboration, ensuring comprehensive information management.
- Aggregate and analyze data from PostgreSQL and other sources using SharePoint's reporting tools. Gain deeper insights by consolidating data from multiple systems, allowing for more comprehensive and accurate reporting.
- Access PostgreSQL data on the go through SharePoint's mobile capabilities. Enable mobile users to access and contribute to data from PostgreSQL through SharePoint's mobile-friendly interface.
- Automate business processes that involve both PostgreSQL and SharePoint. Streamline and automate workflows by integrating data and actions between PostgreSQL and SharePoint, reducing manual effort and improving efficiency.
Let's have a look at how you can sync your PostgreSQL data to SharePoint step-by-step using the Layer2 Cloud Connector.
Data integration and synchronization made easy
If you haven't already tried Layer2 Cloud Connector, it's time to take the first step towards effortless connectivity. Start your Layer2 Cloud Connector trial and experience how easy it can be to connect Microsoft OneDrive for Business with File Shares.