Migrate from another PostgreSQL provider to Xata

This guide walks you through creating a one-time copy of your Postgres database on the Xata platform.

Note: This tutorial uses a point-in-time consistent snapshot. If you'd like to migrate to Xata without downtime, by using logical replication, please contact us and we'll gladly help you.

1. Prerequisites

• A Xata account (sign up here)
• The Xata CLI installed:

  curl -fsSL https://xata.io/install.sh | bash

  Copy Code
• Access to your production PostgreSQL database

2. Create a Xata project and branch

In the Console, create a new project and then click the Create main branch button to create the PostgreSQL instance.

If you intend to use this as your production setup, we recommend using at least 1 replica. It's a good idea to start with a similar instance size as you are running in your current PostgreSQL provider. You will be able to adjust it later.

3. Configure the Xata CLI

Authenticate the CLI by running:

xata auth login

Initialize the project by running:

xata init

You would typically run xata init command in your application source directory, or in any directory/repository where you'd like to keep the configuration. It will create a .xata folder with several configuration files.

Follow the instructions from the prompt to select the project and branch that you created at the previous step. Choose the same database name as you use in production.

4. Get the connection string of your current provider

This step depends on your Postgres provider, but keep the following recommendations in mind:

• If your provider offers a read-only endpoint, it is recommended to use that one in order to avoid extra load to your primary instance.
• If your provider offers a "pooler" or "pgbouncer" endpoint, avoid that one, because xata clone uses a long running transaction.
• The Postgres connection string generally has the form postgres://user:pass@host:5432/dbname.

5. Run xata clone

You can start the cloning process like this:

xata clone start --source-url $CONN_STRING --validation-mode=relaxed

Where the CONN_STRING variable is set to the production connection string from the previous step.

The duration of xata clone depends on the size of your database.

6 Connect to Postgres on Xata

You can now connect to your new Postgres database:

psql `xata branch url`

The newly created PostgreSQL database on Xata contains a consistent snapshot of your production database, and you can start exploring the Xata functionality, like instant branching and schema changes.