Xero to PostgreSQL: How to Move Your Data

Moving Xero into PostgreSQL puts accounting records beside the operational data that explains them. Xero knows what was invoiced and paid; it does not know which customer that invoice belongs to in your product, and answering questions that span both needs them in one database.

This guide covers the managed path with Airbyte. Two things shape the build: authentication is the part that takes longest, and every datetime arrives normalised to UTC while finance closes its periods somewhere else.

Xero to PostgreSQL at a glance:

Why move data from Xero to PostgreSQL?

Two situations account for most of these pipelines.

The first is joining invoices to the systems that generated them. Margin per customer and cost to serve are questions spanning your accounting records and your own application, and this is often the database the application already uses, which makes the join local rather than a project.

The second is serving finance figures to an internal tool. Accounting data is small, so nothing here strains the destination. If you need to consolidate several organisations and analyse them together at length, Xero to BigQuery covers that shape with more room to work in.

What do you need before you start?

Four things, and the first reliably takes longer than the rest combined:

A working authentication method. You create an application in Xero's developer centre and use either a bearer token or an OAuth custom connection. The two differ in availability and in how they are obtained, so check which one suits your organisation before building anything. The Xero source documentation covers both.

A tenant ID for each organisation. It identifies which Xero organisation the source connects to and is required, so a group with several entities needs a source per entity.

A PostgreSQL database and a view layer plan. The destination adds metadata columns, and finance users reading views rather than landing tables is tidier for everybody.

An agreed reporting timezone. Everything arrives in UTC, and whoever closes your periods almost certainly does not work in it. Settling that with finance early avoids reconciling a month twice.

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

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

Step 1: Solve authentication before anything else

Create your Xero application and get a credential that works, in isolation, before touching Airbyte. This is the step people underestimate: the two supported methods are not equally available to every organisation, and one of them involves obtaining a token through a flow you run yourself rather than clicking authorise. Proving the credential first separates an authentication problem from a configuration one, which otherwise look identical.

Step 2: Configure the Xero source

Click Sources in the left navigation, then New Source, and select Xero, following adding a source. Supply your credentials, the tenant ID and a start date in UTC. Name the source after the organisation rather than the tenant identifier, which is not memorable and will not be recognised by anybody reading the list later.

Step 3: Configure the PostgreSQL destination

Click Destinations, then New Destination, and select PostgreSQL, following adding a destination. Supply the host, port, database and credentials. Give this its own schema rather than mixing accounting tables into an application schema, since the two have different audiences and different sensitivity.

Step 4: Create the connection and reconcile a period

Click Connections, then New connection, select your streams and a sync mode. Use incremental, which every stream supports. Then reconcile one closed month against Xero itself before anybody builds a report, because a mismatch found now is a timezone question and a mismatch found later is a credibility problem.

Daily is ample for accounting data, and alerting matters because credential expiry is the most likely way this stops.

Why is authentication the hard part?

Because the two supported methods suit different situations and neither is simply a button. A bearer token is straightforward to supply and obtained through a flow you carry out yourself, which the documentation describes using a tool like Postman. An OAuth custom connection uses a client identifier and secret from a Xero application, and its availability is not universal.

That matters more than usual because an existing Xero application may not be compatible with what this connector expects. Teams with an integration already running sometimes assume they can reuse those credentials and discover partway through that they cannot, which is a considerably worse moment to find out than at the start.

So treat it as a piece of work with its own outcome. Establish which method your organisation can actually use, obtain a credential, confirm it returns data, and write down how you did it. Credential expiry is the most common reason a pipeline like this stops months later, and the person fixing it will not be the person who set it up.

Whose midnight defines your period?

Not UTC's, in almost every organisation, and that is what arrives. The connector does something genuinely helpful with dates, detecting Xero's legacy .NET format and converting it to ISO 8601, then normalising every datetime field to a consistent UTC representation. Consistency is exactly what you want from a pipeline and it is not the same as matching your accounts.

A finance team closes January when January ends where they are. An invoice raised late on the thirty-first in one timezone is a February record in UTC, and a report built on the raw timestamps will disagree with Xero's own by a small, persistent amount that is very hard to explain to somebody who trusts the accounting system more than the warehouse.

So put the rule in one place. Agree the reporting timezone with finance, apply it in a view that converts timestamps before any period logic runs, and have every report read that view. PostgreSQL's timezone-aware types make the conversion straightforward, which is a genuine advantage of this destination, and the discipline that matters is doing it once rather than in each query.

Frequently asked questions

Can one source cover several Xero organisations?

No. The tenant ID identifies one organisation and is required, so a group needs one source per entity and a union view to bring them together.

Can I reuse my existing Xero application?

Check rather than assume. The connector supports a bearer token or an OAuth custom connection, and an application built for a different flow may not be compatible.

Do I need to handle Xero's .NET date format?

No. The connector detects those and converts them to ISO 8601, normalising datetime fields to a consistent UTC format.

My totals differ slightly from Xero's.

Most likely the timezone. Everything arrives in UTC and your periods probably close somewhere else, so apply the reporting timezone in a view before any period logic.

Can I do this without writing code?

The pipeline, yes, though obtaining the credential involves a flow you run yourself. The timezone view and any union across entities are short SQL.

Get your Xero data into PostgreSQL

Solve authentication first and in isolation, because the methods differ in availability and an existing Xero application may not suit this connector. Collect a tenant ID per organisation and name sources after entities. Use incremental, which every stream supports. Then agree the reporting timezone with finance and apply it in one view, since everything arrives in UTC and a small persistent discrepancy is the hardest kind to defend.

Airbyte's connector catalog includes 600+ pre-built connectors, so finance data can sit beside the operations that produced it. For the same source into a warehouse, see Xero to BigQuery, and for another accounting platform into an operational database, Quickbooks to MySQL.

Start syncing now →