# How to emulate a branchable Postgres workflow Source: https://docs.quake.ai/docs/automation/how-to/branchable-postgres-workflow Markdown: https://docs.quake.ai/docs/automation/how-to/branchable-postgres-workflow.md --- # 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](/resources/iac-templates/self-managed-postgres). You create and remove each database branch with PostgreSQL and Block Storage commands. The branch remains within infrastructure you operate. - A running [self-managed-postgres](/resources/iac-templates/self-managed-postgres) deployment - SSH access to the instance over the private subnet (bastion host, VPN, or a jump instance on the same network) - For the volume-snapshot technique: familiarity with [Cinder volume snapshots](/docs/block/how-to/create-snapshot) ## Choosing a technique | Technique | Speed | Isolation | Survives losing the source instance | | --- | --- | --- | --- | | Template databases | Seconds | Data only; shares compute and storage with the source | No | | `pg_dump` and `psql` | Minutes | Database-level; portable to another instance | Yes | | Volume snapshots | Minutes | Block-level, including PostgreSQL data files and WAL | Yes | 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: ```bash sudo -u postgres psql -c "CREATE DATABASE appdb_branch_feature_x TEMPLATE appdb;" ``` Connect to the branch like any other database: ```bash 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: ```bash sudo -u postgres psql -c "DROP DATABASE appdb_branch_feature_x;" ``` A template database isolates data while sharing compute and storage. A heavy query against the branch competes with the source database for CPU, memory, and disk I/O. Use this technique for schema experiments and small data checks. Use a separate instance for load testing. 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: ```bash 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: ```bash 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: ```bash sudo -u postgres psql -d appdb_branch_feature_x -c "\dt" ``` See [How to restore PostgreSQL from a self-managed PostgreSQL backup](/docs/automation/how-to/restore-postgres-from-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. A snapshot taken while PostgreSQL accepts writes is crash-consistent. PostgreSQL uses WAL recovery when the scratch instance starts. Quiesce application writes before taking the snapshot when the branch requires an application-consistent recovery point. Identify the data volume from the template's outputs, then create the snapshot: ```bash 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: ```bash 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: ```bash openstack volume create \ --snapshot pgdata-branch-feature-x \ --size 50 \ pgdata-branch-feature-x-vol ``` Follow [How to clone a volume](/docs/block/how-to/clone-volume) for the attach-and-mount sequence. [How to schedule volume snapshots and restore data](/docs/block/how-to/schedule-snapshots-and-restore) 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](/resources/iac-templates/self-managed-postgres): the template this guide branches from - [How to restore PostgreSQL from a self-managed-postgres backup](/docs/automation/how-to/restore-postgres-from-backup): the restore mechanics Technique 2 builds on - [How to create a volume snapshot](/docs/block/how-to/create-snapshot) and [How to clone a volume](/docs/block/how-to/clone-volume): the volume-level mechanics Technique 3 builds on - [How to schedule volume snapshots and restore data](/docs/block/how-to/schedule-snapshots-and-restore)