citusdata / citusdata/citus

Simplify data migration from PostgreSQL to Citus

Open
#37 2 comments 0 reactions 0 assignees View on GitHub
feature usability
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

Simplify data migration from PostgreSQL (local tables) to Citus (distributed tables).

This issue has several components to it and each one would be beneficial in isolation:

1. Migrate data from an existing PostgreSQL database to the Citus coordinator. AWS has a [data migration service](https://aws.amazon.com/dms/) for Postgres that could be worth looking into.
* Do we provide this for Citus Cloud (managed), AWS, or on-prem deployments?
* Do we take any downtime when replicating the data?
* Do we follow an approach that uses logical replication (Slony, pg_logical) or physical replication?
2. Load data in Citus coordinator into distributed tables
* One way to do that is by running an ``INSERT INTO ... SELECT``. #782 and #1117 provides a good workaround for this step.
* Do we take any downtime when replicating the data?
3. Enable schema migrations for the multi-tenant data model. During migrations, Citus may require changes to the underlying data definition. For example:
* You may need to add a ``tenant_id`` column to your tables and then backfill data. This particular item comes up frequently in engineering sessions.
* You may then need to change your primary key or foreign key declarations.
4. Enable schema migrations from "one schema per tenant" databases to "shared tables." The [Apartment gem](https://github.com/influitive/apartment) & corresponding blog post talks about the "one schema per tenant" approach. We could look to easily migrate prospective users to Citus' multi-tenant model.
5. Enable schema migrations from other relational databases to PostgreSQL. AWS has a schema migration tool that may be worth looking into.
6. Automate data remodeling for the multi-tenant use case. In this migration task, we'd write software to automate the following: understand the current table schema (likely in the relational database model), pick the table that's at the top of the hierarchy, and convert the relational database model into the hierarchical one while also adding the ``tenant_id`` column to the corresponding tables.

Contributor guide

Open the contributing guide

Research direction

Start by choosing one migration component, such as loading coordinator data into distributed tables, and read the workarounds in #782 and #1117. For broader migration paths, compare the AWS Database Migration Service, logical or physical replication, and the Apartment gem approach described in the issue. Define one deployment target, migration path, downtime expectation, and completion criteria before implementing.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql
Domain
data-engineering, databases, distributed-systems
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
15/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.