How to emulate a branchable Postgres workflow

Create an isolated copy of a PostgreSQL database for a feature branch or pull request preview, then remove the copy when the work is complete. This guide covers three techniques for a self-managed PostgreSQL deployment.

Choosing a technique#

Run Techniques 1 and 2 over SSH on a PostgreSQL instance. Run Technique 3 from a workstation with the OpenStack CLI.

Use template databases for short-lived, same-instance development. Use pg_dump when the branch must move to another instance or outlive the source. Use a volume snapshot when the branch needs the source volume's PostgreSQL data files and WAL state.

Technique 1: Create a template database on the source instance#

PostgreSQL can copy a database's schema and data with a template database. PostgreSQL copies the database files instead of replaying a logical dump.

Connect to the self-managed-postgres instance over SSH, then create the branch:

sudo -u postgres psql -c "CREATE DATABASE appdb_branch_feature_x TEMPLATE appdb;"

Connect to the branch like any other database:

PGPASSWORD='YOUR_APP_PASSWORD' psql -h 127.0.0.1 -U appuser -d appdb_branch_feature_x -c "\dt"

Drop the branch when you finish:

sudo -u postgres psql -c "DROP DATABASE appdb_branch_feature_x;"

PostgreSQL requires the source database to have no active sessions while it creates the copy. If PostgreSQL reports that the source database is in use, close its active sessions and run the command again.

Technique 2: Restore a logical dump into a branch database#

Use a logical dump to create the branch under a new name on the source instance or on a separate PostgreSQL instance.

On the source instance, dump the database:

sudo -u postgres pg_dump appdb | gzip > appdb-branch-feature-x.sql.gz

Copy the dump to the target instance when you want separate compute and storage. On the target instance, create the branch database and restore the dump:

sudo -u postgres createdb appdb_branch_feature_x
gunzip -c appdb-branch-feature-x.sql.gz | \
  sudo -u postgres psql -d appdb_branch_feature_x

Confirm that PostgreSQL restored the branch:

sudo -u postgres psql -d appdb_branch_feature_x -c "\dt"

See How to restore PostgreSQL from a self-managed PostgreSQL backup for a restore drill that uses a separate scratch instance.

Technique 3: Create a branch from a volume snapshot#

Create a snapshot of the self-managed-postgres data volume, create a volume from that snapshot, and attach the new volume to a scratch instance. The copy contains the PostgreSQL data files and WAL state present on the source volume when the snapshot starts.

Identify the data volume from the template's outputs, then create the snapshot:

DATA_VOLUME_ID=$(tofu output -raw data_volume_id)
openstack volume snapshot create \
  --volume "$DATA_VOLUME_ID" \
  --force \
  pgdata-branch-feature-x

The data volume remains attached to the source instance, so the snapshot command requires --force. Wait for the snapshot to become available:

openstack volume snapshot show \
  pgdata-branch-feature-x \
  -f value \
  -c status

The command returns available when the snapshot is ready. Create a volume from the snapshot, then attach it to a scratch instance that runs the same PostgreSQL major version as the source:

openstack volume create \
  --snapshot pgdata-branch-feature-x \
  --size 50 \
  pgdata-branch-feature-x-vol

Follow How to clone a volume for the attach-and-mount sequence. How to schedule volume snapshots and restore data covers the underlying snapshot and restore workflow. Mount the cloned volume at /var/lib/postgresql on the scratch instance, then start PostgreSQL against it.

Delete the snapshot and the cloned volume when you finish with the branch; both continue to bill for storage while they exist.

See also#

• Self-Managed PostgreSQL: the template this guide branches from
• How to restore PostgreSQL from a self-managed-postgres backup: the restore mechanics Technique 2 builds on
• How to create a volume snapshot and How to clone a volume: the volume-level mechanics Technique 3 builds on
• How to schedule volume snapshots and restore data