How to Create a Reliable Testing Dataset with pg_dump and pg_restore

A practical guide to extracting a test dataset from Postgres using pg_dump, pg_restore and psql

As your Postgres database grows, you’ll likely need a way to generate a smaller, ‘good-enough’ dataset that preserves the structure and referential integrity of production but is better suited for testing.

There are several ways to do this, but here’s a straightforward approach using pg_dump, pg_restore, psql and GitHub Actions.

Running partial data dumps inside GitHub Actions

You can run pg_dump, pg_restore, and psql from the command line, but sometimes, an automated, reproducible approach is more convenient. To better control when data dumps occur, I use a scheduled GitHub Action to export data from my production database and restore it to a testing database. This method works across different Postgres database providers, but if you’re looking for a cost-effective testing environment, consider trying Neon. Check out our getting started guide to see how easy it is to set up.