This workflow will create a full Neon Twin using pg_dump and pg_restore.

note

To use this workflow, you'll need the Postgres connection string for your Neon database. Follow our Getting Started Guide to learn how.

important

Avoid using pg_dump over a pooled connection string. Use an 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.

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: '17'

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 and restore data
        run: |
          $POSTGRES/pg_dump -Fc -f "${{ github.workspace }}/dump-file.bak" "${{ env.PROD_DATABASE_URL }}"
          $POSTGRES/pg_restore --clean --no-owner --no-acl --if-exists -d "${{ env.DEV_DATABASE_URL }}" "${{ github.workspace }}/dump-file.bak"

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 that defines when the Action will run.
  • workflow_dispatch: Enables manual triggering through the GitHub UI.

env

  • PROD_DATABASE_URL: The PostgreSQL connection string for your production database.
  • DEV_DATABASE_URL: The PostgreSQL connection string for your Neon database.
  • PG_VERSION: The version of PostgreSQL to install in the Action environment.

steps

  • Install PostgreSQL: Installs the specified version of PostgreSQL into the Action environment from the Apt repository.
  • Set PostgreSQL binary path: Creates $POSTGRES variable for use in subsequent steps.
  • Dump and restore data: Uses pg_dump to create a dump-file.bak and pg_restore to read the dump-file.bak and apply it to the DEV_DATABASE_URL.

pg_dump flags

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

FlagMeaning
-FCDumps the database in a custom format.
-fSpecifies the output file where the schema dump will be stored.

pg_restore flags

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

FlagMeaning
--cleanDrops existing database objects before recreating them, ensuring a clean restore.
--no-ownerIgnores ownership information in the dump file, so restored objects are owned by the user running the restore.
--no-aclExcludes access control (GRANT/REVOKE) statements from the restore, preventing permission changes.
--if-existsEnsures that DROP commands (used with --clean) only execute if the object exists, preventing errors.
-dSpecifies the target database to restore into.

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

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

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.

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. Additionally, add an if statement to ensure the job only executes when specific conditions are met.

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: 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.

Further reading