Full Twin

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

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 ET (us-east-1)
  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.

pg_restore flags

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

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.

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.

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 ET (us-east-1)
  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

• Neon Twin: Move Dev/Test/Staging to Neon, Keep Production on RDS
• Neon Twin: How to deploy a change tested in Neon to prod in RDS