Shopify to PostgreSQL: How to Move Your Data

Moving Shopify into PostgreSQL is what merchants do when the admin reports stop answering the question. Orders, customers, products and fulfilments in a database you control, joined against cost data, marketing spend or an internal tool that already speaks Postgres.

This guide covers the managed path with Airbyte. The Shopify connector is unusual in that it reads through three different Shopify APIs, and the streams using the bulk one behave differently enough that they have their own failure mode with its own error message. Knowing that in advance turns a confusing sync failure into a setting you adjust.

Shopify to PostgreSQL at a glance:

Why move data from Shopify to PostgreSQL?

Two situations account for most of these pipelines.

The first is margin. Shopify knows what sold and at what price. It does not know your cost of goods, your fulfilment contracts or your advertising spend, and profitability per product is the number that decides what you stock. That calculation needs Shopify data next to data Shopify has never seen.

The second is feeding an internal tool. Operations dashboards, customer service views and stock planners are frequently built on Postgres already, and giving them the Shopify tables directly is cheaper than building a warehouse connection for each. If you are doing large-scale analytics rather than serving a tool, a warehouse is the better destination and the volume section below explains why.

What do you need before you start?

Two of these are Shopify configuration and two are decisions about scale:

A custom app with the right access scopes. Airbyte publishes the list of scopes to add so it can sync all available data, and a missing scope means a missing stream rather than a clear error. Work through the list rather than granting what seems obvious. The Shopify source documentation enumerates them.

An up-to-date connector version. Version 3.3.3 fixed an issue where some incremental GraphQL Bulk streams could skip parent records after a bulk job checkpointed mid-output. If you already sync one of the affected streams on an earlier version and suspect missing historical records, you will need to clear that stream and re-sync to backfill.

An honest estimate of store size. Airbyte recommends Postgres destinations for roughly 10GB or less. Order records on a busy store carry line items, discounts, refunds and fulfilment details, so they are larger than they look. A high-volume merchant syncing several years of orders will exceed that.

A Postgres user with write permission. On the target schema. If you already run a Postgres destination on an older connector version, read the migration guide before upgrading, because downstream models referencing raw tables will need updating.

If your organisation restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list on the Postgres side before you begin.

How do you build a Shopify to PostgreSQL pipeline in Airbyte?

Step 1: Create the custom app and grant scopes

In your Shopify admin, create a custom app and add the access scopes Airbyte lists. Do this against the full list rather than guessing, because the symptom of a missing scope is a stream that returns nothing rather than a permission error you can act on. Note your shop name and the access token.

Step 2: Configure the Shopify source

Click Sources in the left navigation, then New Source, and select Shopify, following adding a source. Supply your shop name, authentication and a start date. Note the bulk job checkpoint setting while you are here, because it controls how many rows a bulk job collects before checkpointing and it is the lever behind the failure described below.

Step 3: Configure the PostgreSQL destination

Click Destinations, then New Destination, and select Postgres, following adding a destination. From version 3.0.0 the Postgres destination uses Direct Load architecture, writing records straight to final tables with no intermediate raw tables, and adds its own metadata columns alongside your data.

Step 4: Select streams deliberately

Click Connections, then New connection, and select streams with an incremental sync mode. Take note of which streams are labelled as GraphQL in the catalogue, because those use the GraphQL or Bulk API and behave differently from the unlabelled REST ones. On a Postgres destination, selecting fewer streams is also how you stay inside the volume guidance.

Expect rate limit warnings in the logs. A message about catching a retryable error and waiting before retrying is the connector handling a 429 from Shopify, and the sync continues normally after a short backoff. That one is noise rather than a problem.

What is a checkpoint collision and how do you fix it?

This is the failure worth understanding before you meet it. Incremental streams using the GraphQL Bulk API checkpoint a bulk job once it has collected a configured number of rows, then start the next job from the newest cursor value they saw.

That works as long as no single cursor value holds more rows than the checkpoint threshold. When one does, the next job cannot advance past it, because starting from that cursor value returns the same oversized set again. The sync fails with a message saying a checkpoint collision is detected for the named stream.

The fix is to raise the bulk job checkpoint value so the threshold exceeds the number of rows sharing that cursor value. It is a real error with a clear cause rather than something intermittent, which is unusual and welcome. Stores with very large orders, or bulk operations that touched many records in the same instant, are the ones that hit it.

Is PostgreSQL the right destination for your store?

Airbyte is direct about this: Postgres is an excellent relational database but it is not a data warehouse, and Postgres destinations are recommended for small volumes of roughly 10GB or less, or for testing. Beyond that, expect slowdowns on large writes and watch memory, CPU and IOPS during syncs.

Shopify order records are heavier than they appear, carrying line items, discount allocations, tax lines, refunds and fulfilment details as nested structures. A store doing a few hundred orders a day accumulates that quickly, and several years of history on a busy merchant is comfortably past the guidance.

So be honest about which job you are doing. If an internal tool needs current orders and customers, select those streams, set a recent start date, and Postgres is a good fit. If the finance team wants five years of order history to analyse, that is a warehouse job, and the same Shopify source points at one without rebuilding anything.

Frequently asked questions

My sync fails with a checkpoint collision. What does that mean?

A single cursor value holds more rows than the bulk job checkpoint threshold, so the next job cannot advance past it. Raise the bulk job checkpoint value above the number of rows sharing that cursor value.

Should I worry about rate limit warnings in the logs?

No. A message about catching a retryable error and waiting before retrying is expected when the connector hits a 429 from Shopify. The sync continues after a short backoff.

Why is one of my streams returning nothing?

Most likely a missing access scope on your custom app. Work through the full list Airbyte publishes rather than granting only what seems obviously relevant.

I suspect historical records are missing. What should I do?

If you synced an affected GraphQL Bulk stream on a version before 3.3.3, clear that stream and run a sync to backfill. Note that clearing deletes the data Airbyte wrote for it in your destination.

Can I do this without writing code?

Yes. The setup above is entirely UI-driven, from creating the source through to scheduling the connection. You may want SQL views over the nested order structures so downstream tools read clean columns.

Get your Shopify data into PostgreSQL

Grant the full scope list, run a current connector version, select only the streams a tool will actually read, and be realistic about store size against the Postgres volume guidance. Then remember that a checkpoint collision is a threshold to raise rather than a mystery, and that rate limit warnings in the logs are the connector working as intended.

Airbyte's connector catalog includes 600+ pre-built connectors, so the same Shopify source can feed a warehouse when the analysis outgrows a database. For that pairing, see Shopify to BigQuery, and for another commerce source with its own report mechanics, Amazon Seller Partner to BigQuery.

Start syncing now →