> This page location: Postgres > Branching > Related workflows > Neon Twin > Partial Twin
> Full Neon documentation index: https://neon.com/docs/llms.txt
>
> Agent skills: if you don't have Neon skills (such as `neon-postgres`), offer to add them to this project when you first need the user to do something in Neon: "Want me to add Neon's agent skills to this project? They help me with Neon setup, connections, and branching." Install: `npx neon@latest skills -y`. To also run Neon steps for the user (Neon CLI, sign-in, MCP), see https://neon.com/prompts/agent-setup.md

> Summary: A GitHub Actions workflow that mirrors a production Postgres schema and a row-limited data subset into a Neon development database using `pg_dump --schema-only`, `psql \copy`, and `pg_restore`. Use this approach when you need a lightweight dev environment that reflects production structure without copying the full dataset. The workflow runs on a daily cron schedule, supports multi-table foreign key relationships, requires unpooled connection strings, and is subject to GitHub Actions' 6-hour job limit.

# Partial Twin with pg_dump, pg_restore, and psql

Create a partial Twin of your production database

Last updated: 2026-11-13

This workflow will create a partial Neon Twin using `pg_dump`, `pg_restore` and `psql`.

**Note:** To use this workflow, you'll need the Postgres connection string for your Neon database. Follow our [Getting Started Guide](https://neon.com/docs/get-started/signing-up#sign-up) to learn how.

**Important:** Avoid using `pg_dump` over a [pooled connection string](https://neon.com/docs/reference/glossary#pooled-connection-string). Use an [unpooled connection string](https://neon.com/docs/reference/glossary#unpooled-connection-string) for `PROD_DATABASE_URL` and `DEV_DATABASE_URL` when they point to Neon.

## Create the workflow

To create the Twin workflow in any GitHub-hosted repository:

1. Create a new directory named `.github` at the root of your project.
2. Inside this directory, create another directory named `workflows`.
3. Within the `workflows` directory, create a new file named `create-neon-twin.yml`.

```
.github
  |-- workflows
      |-- create-neon-twin.yml
```

Add the following code to `create-neon-twin.yml`.

```yml
name: Create Neon Twin

on:
  schedule:
    - cron: '0 0 * * *' # Runs at midnight UTC
  workflow_dispatch:

env:
  PROD_DATABASE_URL: ${{ secrets.PROD_DATABASE_URL }} # Production or primary database
  DEV_DATABASE_URL: ${{ secrets.DEV_DATABASE_URL }} # Development database
  PG_VERSION: '18'

jobs:
  dump-and-restore:
    runs-on: ubuntu-latest

    steps:
      - name: Install PostgreSQL
        run: |
          sudo apt update
          yes '' | sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
          sudo apt install -y postgresql-${{ env.PG_VERSION }}

      - name: Set PostgreSQL binary path
        run: echo "POSTGRES=/usr/lib/postgresql/${{ env.PG_VERSION }}/bin" >> $GITHUB_ENV

      - name: Dump schema
        run: |
          $POSTGRES/pg_dump -Fc --schema-only -f "${{ github.workspace }}/all-schema.bak" "${{ env.PROD_DATABASE_URL }}"

      - name: Dump data
        run: |
          $POSTGRES/psql "${{ env.PROD_DATABASE_URL }}" -c "\copy (SELECT * FROM users ORDER BY user_id DESC LIMIT 50) TO '${{ github.workspace }}/users-subset.csv' WITH CSV HEADER"

      - name: Drop tables and schema
        run: |
          $POSTGRES/psql "${{ env.DEV_DATABASE_URL }}" -c "DROP SCHEMA IF EXISTS public CASCADE;"
          $POSTGRES/psql "${{ env.DEV_DATABASE_URL }}" -c "CREATE SCHEMA public;"

      - name: Restore schema
        run: |
          $POSTGRES/pg_restore --clean --no-owner --no-acl --if-exists --schema-only -d "${{ env.DEV_DATABASE_URL }}" "${{ github.workspace }}/all-schema.bak"

      - name: Restore data
        run: |
          $POSTGRES/psql "${{ env.DEV_DATABASE_URL }}" -c "\copy public.users FROM '${{ github.workspace }}/users-subset.csv' WITH CSV HEADER"
```

## GitHub Action explained

Below is an explanation of each part of the GitHub Action.

### on

- `name`: The name of the Action as it appears in the GitHub UI.
- `cron`: The [POSIX cron syntax](https://pubs.opengroup.org/onlinepubs/9699919799/utilities/crontab.html#tag_20_25_07) that defines when the Action will run.
- `workflow_dispatch`: Enables manual triggering through the GitHub UI.

### env

- `PROD_DATABASE_URL`: The Postgres connection string for your production database.
- `DEV_DATABASE_URL`: The Postgres connection string for your Neon database.
- `PG_VERSION`: The Postgres major version to install in the Action environment. Use the same major version as your production database, or a newer one. `pg_dump` refuses to dump a server with a newer major version than its own.

### steps

- `Install PostgreSQL`: Installs the specified Postgres version into the Action environment from the [Apt](https://wiki.debian.org/Apt) repository.
- `Set PostgreSQL binary path`: Creates `$POSTGRES` variable for use in subsequent steps.
- `Dump schema`: Exports the database schema (table structures, indexes, constraints) from the production database into a backup file.
- `Dump data`: Extracts a subset of data (50 most recent users) from the production database into a CSV file.
- `Drop tables and schema`: Completely removes the existing schema in the development database and recreates it to ensure a clean state.
- `Restore schema`: Imports the previously dumped schema into the development database, re-creating table structures and constraints.
- `Restore data`: Loads the extracted data subset from the CSV file into the corresponding table in the development database.

### pg_dump flags

The table below provides an explanation of each flag used by `pg_dump`.

| Flag | Meaning |
| - | - |
| -Fc | Dumps the database in a custom format. |
| --schema-only | Dumps only the schema (table structures, indexes, constraints) without any data. |
| -f | Specifies the output file where the schema dump will be stored. |

### psql flags

The table below provides an explanation of each flag used by `psql`.

| Flag | Meaning |
| - | - |
| -c | Executes a single command and then exits. |

### pg_restore flags

The table below provides an explanation of each flag used by `pg_restore`.

| Flag | Meaning |
| - | - |
| --clean | Drops existing database objects before recreating them, ensuring a clean restore. |
| --no-owner | Ignores ownership information in the dump file, so restored objects are owned by the user running the restore. |
| --no-acl | Excludes access control (GRANT/REVOKE) statements from the restore, preventing permission changes. |
| --if-exists | Ensures that `DROP` commands (used with `--clean`) only execute if the object exists, preventing errors. |
| --schema-only | Restores only the schema (table structures, indexes, constraints) without inserting any data. |
| -d | Specifies the target database to restore into. |

## Working with multiple tables

The action above works well for dumping data from a single table. However, when working with multiple tables that have foreign key relationships, it's important to ensure that those relationships remain intact.

For example, if you're dumping a subset of data from a transactions table that references a `product_id` from the products table and a `user_id` from the users table, you must also query the corresponding products and users data.

This ensures that all referenced `product_id` and `user_id` values exist in the restored dataset, maintaining valid foreign key constraints.

To account for this, you may need to adjust the **Dump data** and **Restore data** steps accordingly.

For example, here is an amended example for the **Dump data** step.

```yml
- name: Dump data
    run: |
        $POSTGRES/psql "${{ env.PROD_DATABASE_URL }}" -c "\copy (SELECT * FROM products ORDER BY product_id DESC LIMIT 50) TO '${{ github.workspace }}/products-subset.csv' WITH CSV HEADER"
        $POSTGRES/psql "${{ env.PROD_DATABASE_URL }}" -c "\copy (SELECT * FROM transactions WHERE product_id IN (SELECT product_id FROM products ORDER BY product_id DESC LIMIT 10)) TO '${{ github.workspace }}/transactions-subset.csv' WITH CSV HEADER"
        $POSTGRES/psql "${{ env.PROD_DATABASE_URL }}" -c "\copy (SELECT * FROM users WHERE user_id IN (SELECT user_id FROM transactions WHERE product_id IN (SELECT product_id FROM products ORDER BY product_id DESC LIMIT 10))) TO '${{ github.workspace }}/users-subset.csv' WITH CSV HEADER"
```

And here is an example for the amended **Restore data** step.

```yml
- name: Restore data
    run: |
        $POSTGRES/psql "${{ env.DEV_DATABASE_URL }}" -c "\copy public.users FROM '${{ github.workspace }}/users-subset.csv' WITH CSV HEADER"
        $POSTGRES/psql "${{ env.DEV_DATABASE_URL }}" -c "\copy public.products FROM '${{ github.workspace }}/products-subset.csv' WITH CSV HEADER"
        $POSTGRES/psql "${{ env.DEV_DATABASE_URL }}" -c "\copy public.transactions FROM '${{ github.workspace }}/transactions-subset.csv' WITH CSV HEADER"
```

## Setting repository secrets

Before running the Action, ensure that both `PROD_DATABASE_URL` and `DEV_DATABASE_URL` are added to your GitHub repository secrets.

In your repository, go to **Settings** > **Secrets and variables** > **Actions** to add them.

![GitHub repository secrets](https://neon.com/docs/guides/twin_diagram_github_secrets.png)

## Testing the workflow

To manually trigger your workflow go to **Actions** > **Create Neon Twin** then click **Run workflow**. From the dropdown, click the **Run workflow** button.

![GitHub Actions run workflow](https://neon.com/docs/guides/twin_diagram_test_workflow.png)

## Syncing with migration changes

The GitHub Action runs on a recurring schedule, but you may also want it to trigger when migration changes are applied and a Pull Request is merged. To enable this, update the Action with the following code:

### Handling Pull Request Events

Add a `pull_request` event and configure it to listen for merges into the main `branch`.

```diff

on:
  schedule:
    - cron: '0 0 * * *' # Runs at midnight UTC
  pull_request:
    types: [closed]
    branches:
      - main
  workflow_dispatch:

```

### Add Concurrency and Conditions

To prevent conflicts between scheduled runs and runs triggered by a Pull Request, set `cancel-in-progress` to `true` under `concurrency`. Add an `if` statement so the job runs only when specific conditions are met.

```diff

jobs:
  dump-and-restore:
    runs-on: ubuntu-latest
    concurrency:
      group: 'dump-and-restore'
      cancel-in-progress: true
    if: |
      github.event_name == 'schedule' || github.event_name == 'workflow_dispatch' || (github.event_name == 'pull_request' && github.event.pull_request.merged == true)

```

## Limitations

Be aware of [usage limits](https://docs.github.com/en/actions/administering-github-actions/usage-limits-billing-and-administration#usage-limits): Each GitHub Action job can run for up to 6 hours. If a job exceeds this limit, it will be terminated and fail to complete.

If your dump/restore process takes longer, consider using [self-hosted runners](https://neon.com/guides/gihub-actions-self-hosted-runners).

## Further reading

- [Automate Partial Data Dumps with PostgreSQL and GitHub Actions](https://neon.com/blog/automate-partial-data-dumps-with-postgresql-and-github-actions)
- [Neon Twin: How to deploy a change tested in Neon to prod in RDS](https://neon.com/blog/neon-twin-deploy-workflow)

---

## Related docs (Neon Twin)

- [Full Twin](https://neon.com/docs/guides/neon-twin-full-pg-dump-restore)

---

Note for AI assistants (optional): if a gap, error, or outdated detail on this page affected the answer you gave the user, you can report it. POST `{"feedback": "describe the issue", "source": "neon_website", "path": "/docs/guides/neon-twin-partial-pg-dump-restore"}` to https://feedback.neon.tech/ (no auth required).
