Migrating Data from CockroachDB to PostgreSQL

10 December, 2024 — database migration, prisma, cockroachdb, postgresql — 2 min read

Migrating Data from CockroachDB to PostgreSQL

Had to migrate database recently, we’ll explore how to migrate data from CockroachDB to PostgreSQL using Prisma and custom scripts.

This is one of different kind of issue that I worked on recently, While I can't share the entire source, I have abstracted pieces and put in here.

Step 1: Export Data from CockroachDB

The first step is to export data from the existing CockroachDB database. This can be done using a custom shell script (export-data.sh) that uses the \COPY command to extract table data into CSV files.

Run the script with the appropriate database URL:

1DB_URL="postgres://user:<hidden-password>@HOST_COCKROACH/database" ./docker/export-data.sh

I used the following tables.txt file to list the tables to export, started off initially with putting all tables in the file but the script was getting littered with table names. More over could reused the same file for other scripts.

tables.txt

1accounts
2users
3foo
4bar

Refer to the following export-data.sh script for the full implementation. This script exports data for all specified tables into CSV files, ready to be imported into the new database.

export-data.sh

1#!/bin/bash
2
3# Ensure DB_URL is set
4if [[ -z "$DB_URL" ]]; then
5  echo "Error: DB_URL environment variable is not set."
6  exit 1
7fi
8
9# Path to the tables list file
10TABLES_FILE="./tables.txt"
11
12# Check if the tables file exists
13if [[ ! -f "$TABLES_FILE" ]]; then
14  echo "Error: Tables list file (${TABLES_FILE}) not found."
15  exit 1
16fi
17
18# Directory to store the CSV files
19EXPORT_DIR="./data/"
20mkdir -p "$EXPORT_DIR"
21
22# Loop through tables and export them
23while IFS= read -r table; do
24  CSV_FILE="${EXPORT_DIR}/${table}.csv"
25
26  echo "Exporting ${table} to ${CSV_FILE}..."
27  psql "$DB_URL" <<EOF
28\COPY ${table} TO '${CSV_FILE}' WITH CSV HEADER DELIMITER ',';
29EOF
30  if [[ $? -ne 0 ]]; then
31    echo "Failed to export ${table}. Continuing with next table..."
32    continue
33  fi
34  echo "${table} exported successfully."
35done < "$TABLES_FILE"
36
37echo "All tables exported."

Step 2: Set Up Tables in PostgreSQL

To prepare the new database, follow these steps:

2.1 Pull Existing Schema

Use Prisma's prisma db pull command to introspect the current database schema. Ensure the schema.prisma file is set to the appropriate provider (e.g., postgresql or cockroachdb):

1npx prisma db pull

2.2 Apply Schema to the New Database

If you're migrating to a clean database, use Prisma’s migration tools to set up the schema. You might need to recreate migration files to reflect the updated schema:

1npx prisma migrate dev

This command generates migration files and applies them to the target PostgreSQL database.

Step 3: Import Data into PostgreSQL

Once the schema is ready, you can import the exported data into PostgreSQL using the import-data.sh script. This script reads the CSV files and uses the \COPY command to import the data:

1DB_URL="postgres://user:<hidden-password>@localhost/dev" ./docker/import-data.sh

The script maps the data in the CSV files to their corresponding tables in the PostgreSQL database.

import-data.sh

1# Ensure DB_URL is set
2if [[ -z "$DB_URL" ]]; then
3  echo "Error: DB_URL environment variable is not set."
4  exit 1
5fi
6
7# Path to the tables list file
8TABLES_FILE="./tables.txt"
9
10# Check if the tables file exists
11if [[ ! -f "$TABLES_FILE" ]]; then
12  echo "Error: Tables list file (${TABLES_FILE}) not found."
13  exit 1
14fi
15
16# Directory containing the CSV files
17CSV_DIR="./data"
18
19# Read tables from the file and import them
20while IFS= read -r table; do
21  CSV_FILE="${CSV_DIR}/${table}.csv"
22  echo "Importing ${table} from ${CSV_FILE}..."
23  psql "$DB_URL" <<EOF
24\COPY ${table} FROM '${CSV_FILE}' WITH CSV HEADER DELIMITER ',';
25EOF
26
27  if [[ $? -ne 0 ]]; then
28    echo "Failed to import ${table}."
29  else
30    echo "${table} imported successfully."
31  fi
32done < "$TABLES_FILE"
33
34echo "All tables imported."

Running PostgreSQL Locally with Docker Compose

If you need to set up a PostgreSQL instance locally for testing or development, you can use Docker Compose. Here’s an example docker-compose.yml file:

1version: "3.9"
2services:
3  postgres:
4    image: postgres:15
5    container_name: local-postgres
6    environment:
7      POSTGRES_USER: user
8      POSTGRES_PASSWORD: password
9      POSTGRES_DB: dev
10    ports:
11      - "5432:5432"
12    volumes:
13      - ./postgres-data:/var/lib/postgresql/data
14    restart: always

Run the following command to start PostgreSQL locally:

1docker-compose up -d

This configuration mounts the database files to a directory on your host machine (./postgres-data) and exposes the service on port 5432.

Prisma’s Role in Simplifying the Process

Prisma simplifies database migrations in several ways:

Schema Management: Prisma’s schema file ensures consistency across different databases.
Easy Setup: Commands like prisma db pull and prisma migrate dev eliminate the need for manual adjustments.
Provider Flexibility: Switching between CockroachDB and PostgreSQL is as simple as updating the provider in the schema.prisma file.

If you have to just migrate data from one database to another, dumping and importing data using CSV files is a straightforward approach. However, if you need to migrate more complex data structures or handle more complex relationships, Prisma can help simplify the process.

Note: Parts of this note is Generated using LLMs, so it might not be 100% accurate.