Sync a SharePoint list to Postgres

What you need

You need SPSync installed, which we do with you on a call (book one), and a Postgres database you can create tables in. Any reasonably current Postgres works, self-hosted or managed. If you are not a tenant admin yourself, read the admin permissions guide first: delegated sign-in covers the sites you can already see, and an admin can grant Sites.Selected on specific sites for unattended runs.

Six steps to a replicated table

1. Sign in. Open the desktop app, or run spsync auth login, and authenticate with your Microsoft account. Delegated access is enough for a first run.
2. Choose the list. Search for the site, then tick the list. Tick the lists its lookup columns point to as well, or those lookups will have nothing to join to.
3. Point at Postgres. Put a connection string such as postgresql://user:password@host:5432/database?sslmode=require in an environment variable and name that variable as the destination. The string itself never goes in the configuration file.
4. Run the first sync. The first run creates the schema and loads every item. Each list becomes a table named {prefix}_{list}, keyed on sp_item_id. A lookup arrives as {col}_id and {col}_value, a person column as {col}_user_id pointing at the shared sp_users table, and a multi-value column as its own {table}__{col} child table.
5. Check the result. Query the table from psql or any SQL client, and compare its row count with the list. Deleted items will appear later as rows with sp_deleted_at set.
6. Set the schedule. The desktop app runs every five minutes by default. From the CLI, run spsync run from cron, Task Scheduler or a CI pipeline. Either way, runs after the first read only what changed, and a reconcile, weekly by default, checks the table against SharePoint.

After the first run

How long the first run takes depends on the size of the list; later runs are quick. The schema reference lists every column type mapping, and the PostgreSQL destination page covers hosted Postgres options.