# Source: https://neon.com/llms/import-pgcopydb.txt

# Migrate data to Neon Postgres using pgcopydb

> The document outlines the process of migrating data to Neon Postgres using the `pgcopydb` tool, detailing steps for setting up the environment, executing the data transfer, and verifying the migration.

## Source

- [Migrate data to Neon Postgres using pgcopydb HTML](https://neon.com/docs/import/pgcopydb): The original HTML version of this documentation

What you will learn:
- Why use pgcopydb
- Setting up environment variables for migration
- Monitoring the migration process
- Advanced usage options

Repo:
- [pgcopydb GitHub repository](https://github.com/dimitri/pgcopydb)

Related docs:
- [pgcopydb documentation](https://pgcopydb.readthedocs.io/)

`pgcopydb` is an open-source tool for copying Postgres databases from one server to another. It's a practical option for migrating larger Postgres databases into Neon.

## Why use pgcopydb for data migration?

`pgcopydb` builds on standard `pg_dump` and `pg_restore` but with extra features to make migrations both faster and more reliable:

- **Parallel migration**: `pgcopydb` processes multiple migration phases concurrently:
  - **Data transfer:** Streams data in parallel from multiple tables and splits large tables into chunks. This distributes the load and reduces migration time for large datasets.
  - **Index creation:** Builds indexes concurrently after data loading
  - **Constraint application:** Applies constraints in parallel while maintaining data integrity
    This parallel processing reduces migration time and minimizes downtime.

- **Dependency handling**: `pgcopydb` manages database object dependencies and migrates them in the correct order:
  - **Schema-first approach:** Creates schema objects (tables, functions, procedures) before data transfer begins
  - **Table copying precedes indexes and constraints:** Copies table data first, then creates indexes and applies constraints

  This ordered approach maintains data integrity and avoids errors during migration.

This guide walks you through using `pgcopydb` to migrate data to Neon.

   **Note**: Logical replication with `pgcopydb clone --follow` is not supported on Neon. You can still use `pgcopydb` for a one-time data migration to Neon.

## Prerequisites

Before you begin, ensure you have the following:

- **Source postgres database**: You need access to the Postgres database you intend to migrate. This can be a local instance, a cloud-hosted database (AWS RDS, GCP Cloud SQL, Azure Database for Postgres, or any other Postgres provider), or even a different Neon project.
- **Neon project**: You must have an active Neon project and a database ready to receive the migrated data. If you don't have a Neon project yet, see [Create a Neon project](https://neon.com/docs/manage/projects#create-a-project) to get started. Note that storage beyond your plan's included amount will incur additional charges.
- **pgcopydb installation**: `pgcopydb` must be installed on a machine that has network connectivity to both your source Postgres database and your Neon database. Check firewall rules and network configurations to allow traffic on the Postgres port. This machine should also have sufficient resources (CPU, memory, disk space) to handle the migration workload. Install `pgcopydb` by following the instructions in the [pgcopydb documentation](https://pgcopydb.readthedocs.io/en/latest/install.html).

## Setup environment variables

Before proceeding, set the following environment variables for your source and target Postgres databases where you will run `pgcopydb` commands:

```bash
export PGCOPYDB_SOURCE_PGURI="postgresql://source_user:source_password@source_host:source_port/source_db"
export PGCOPYDB_TARGET_PGURI="postgresql://neon_user:neon_user_password@xxxx.neon.tech/neondb?sslmode=require&channel_binding=require"
```

You can replace the placeholders with your actual connection details. You can get Neon database connection details from the Neon console. `pgcopydb` will automatically use these environment variables for the migration.

## Start data migration

Run the `pgcopydb clone` command with the `--no-owner` flag to skip ownership changes:

```bash
pgcopydb clone --no-owner
```

   **Tip**: When using `--no-owner` flag in `pgcopydb`, often pair it with `--no-acl`, especially if the source has custom ACLs or default privileges. This flag skips restoring permissions (`GRANT`/`REVOKE`, `ALTER DEFAULT PRIVILEGES`). This is crucial because the user connecting to the target database often lacks the high-level rights to reapply all source permissions. For example, even when migrating between Neon databases, the target user might get "permission denied" errors when trying to restore privileges involving administrative roles (like `cloud_admin`, `neon_superuser`), as they may lack permission to manage settings for those specific roles. This typically halts `pgcopydb` during the `pg_restore` phase. Using `--no-acl` avoids these specific permission errors and allows the migration to proceed smoothly. However, this means that any custom permissions set on the source database won't be replicated in the target database. You may need to manually set them up afterward.

## Monitor the migration progress

You can monitor the migration progress using the `pgcopydb list progress` command. This command provides real-time updates on the migration status, including the number of rows copied and the current phase. You can either set the `--source` flag to your source database connection string or make use of the `PGCOPYDB_SOURCE_PGURI` environment variable.

```bash
pgcopydb list progress --source "your-source-connection-string" --summary
```

After successful completion, you will see a summary of the migration steps and their durations, similar to the following:

```text
                                               Step   Connection    Duration    Transfer   Concurrency
 --------------------------------------------------   ----------  ----------  ----------  ------------
   Catalog Queries (table ordering, filtering, etc)       source       3s775                         1
                                        Dump Schema       source       432ms                         1
                                     Prepare Schema       target         26s                         1
      COPY, INDEX, CONSTRAINTS, VACUUM (wall clock)         both         31s                        12
                                  COPY (cumulative)         both         23s       73 MB             4
                          CREATE INDEX (cumulative)       target       533ms                         4
                           CONSTRAINTS (cumulative)       target       244ms                         4
                                VACUUM (cumulative)       target       3s009                         4
                                    Reset Sequences         both       2s223                         1
                         Large Objects (cumulative)       (null)         0ms                         0
                                    Finalize Schema         both         18s                         4
 --------------------------------------------------   ----------  ----------  ----------  ------------
                          Total Wall Clock Duration         both       1m17s                        20
```

## Switch over your application to Neon

Switch your application to Neon and validate the migration after `pgcopydb clone` completes.

1.  **Stop writes to source database**: Halt write operations to your source database.
2.  **Validate migration**: Use `pgcopydb compare schema` and `pgcopydb compare data` for validation.
3.  **Update application connection string**: Point your application to your Neon database.

## Advanced usage

`pgcopydb` offers several advanced options to optimize and customize your migration. Here are some key considerations:

### Boosting migration speed with parallelism

`--table-jobs <integer>` & `--index-jobs <integer>`: These options control the number of concurrent jobs for copying tables and creating indexes, respectively. For large databases, increasing these values is crucial for reducing migration time.

### Handling large tables efficiently

`--split-tables-larger-than <bytes>`: Automatically splits tables exceeding the specified size into smaller chunks for parallel import, dramatically accelerating migration of large datasets. Start with `1GB` or `500MB` and adjust based on your table sizes.

**Example:**

```bash
pgcopydb clone --table-jobs 8 --index-jobs 12 --split-tables-larger-than 500MB
```

This command will run the migration with **8** concurrent table jobs, **12** concurrent index jobs, and split tables larger than **500** MB into smaller chunks for parallel import.

For more detail, see [Same-table Concurrency](https://pgcopydb.readthedocs.io/en/latest/concurrency.html#same-table-concurrency) in the `pgcopydb` docs.

### Filtering and selective migration

`--filters <filename>`: Sometimes you only need to migrate a subset of your database. `--filters` allows you to precisely control which tables, indexes, or schemas are included in the migration. This is useful for selective migrations or excluding unnecessary data. For filter configuration and examples, see the [pgcopydb filtering documentation](https://pgcopydb.readthedocs.io/en/latest/ref/pgcopydb_config.html#filtering).