pgcopycat

A PostgreSQL application-cloning CLI for local development and refresh workflows.

pgcopycat is a Go CLI that clones the application layer of a PostgreSQL database by orchestrating native PostgreSQL tools such as pg_dump, pg_restore, and psql. It is built for developer workflows where you want a usable local copy of an application database without attempting a full administrative cluster clone.

What pgcopycat copies

• Schemas
• Tables and columns
• Primary keys
• Foreign keys
• Constraints
• Indexes
• Sequences
• Views
• Functions and triggers present in the application dump
• Data, depending on the selected clone mode

What pgcopycat does not target in v1

• PostgreSQL roles and users
• Ownership restoration
• Grants and ACL management
• Cluster-wide administration
• Continuous replication
• Incremental synchronization

Features

• Clone modes: full, schema-only, data-only
• Interactive mode when launched without arguments
• Non-interactive execution with flags
• Reusable YAML profiles
• Automatic profile discovery
• Early checks for required PostgreSQL client tools
• Pre-checks for connectivity, target safety, filters, and extensions
• Optional extension creation on the destination database
• Sequence resynchronization after data restore
• Text and JSON reporting
• Local profile saving before execution

Requirements

• Go 1.25+
• PostgreSQL client tools available in PATH
  • pg_dump
  • pg_restore
  • psql

Installation

Build a local binary in the current directory:

go build -o pgcopycat ./cmd/pgcopycat
./pgcopycat

Install it into your Go bin directory:

go install ./cmd/pgcopycat

If needed, add your Go bin directory to PATH:

export PATH="$HOME/go/bin:$PATH"

On macOS, if PostgreSQL client tools are missing, pgcopycat can guide you toward installing them with Homebrew.

Quick Start

Run the interactive workflow:

./pgcopycat

Create an example config:

./pgcopycat init

Run from a config file:

./pgcopycat run --config ./pgcopycat.yml

Run from a saved profile:

./pgcopycat run --profile local-refresh

Run checks only:

./pgcopycat check --config ./pgcopycat.yml

Commands

pgcopycat
pgcopycat run --config ./pgcopycat.yml
pgcopycat run --profile local-refresh --yes
pgcopycat check --config ./pgcopycat.yml
pgcopycat profiles
pgcopycat init
pgcopycat version

Configuration

The recommended example file is examples/pgcopycat.yml.

Main configuration sections:

• source: source PostgreSQL connection
• destination: destination PostgreSQL connection
• clone: clone mode, target strategy, sequence reset behavior
• filters: schema and table inclusion or exclusion rules
• extensions: check, ensure, or ignore
• execution: dry_run, verbose, yes
• report: output format and optional report file

Important fields:

• clone.mode: full, schema-only, data-only
• clone.target_mode: safe, drop-schemas
• clone.reset_sequences: resynchronize owned sequences after data restore
• filters.exclude_tables: remove table structure and data from the dump
• filters.exclude_table_data: keep structure but skip data for selected tables
• extensions.mode: verify only, auto-create, or ignore
• report.format: text or json

Password Handling

For local development, pgcopycat supports plain-text passwords directly in config files:

source:
  user: postgres
  password: postgres

Environment-variable based passwords are still supported with password_env if you prefer them.

Local Config Safety

Local configuration files may contain plain-text passwords. The repository ships with a .gitignore that excludes:

• pgcopycat.yml
• pgcopycat.yaml
• .pgcopycat/*.yml
• .pgcopycat/*.yaml

Do not commit personal or environment-specific database credentials.

Interactive Workflow

When started without arguments, pgcopycat follows this flow:

1. Checks for pg_dump, pg_restore, and psql before asking for database details
2. If tools are missing, shows an alert and proposes installation guidance
3. Scans for saved local and global profiles
4. Lets you select a profile or continue with manual configuration
5. Prompts for source and destination connection details, with examples
6. Asks whether exclusions should be configured before asking exclusion details
7. Detects missing destination extensions and can propose auto-creation
8. Offers to save a manual configuration before execution
9. Shows a final summary and asks for confirmation

Profile Discovery

When pgcopycat starts without explicit --config or --profile, it scans:

• ./pgcopycat.yml
• ./pgcopycat.yaml
• ./.pgcopycat/*.yml
• ./.pgcopycat/*.yaml
• ~/.config/pgcopycat/profiles/*.yml
• ~/.config/pgcopycat/profiles/*.yaml

If profiles are found and stdin is interactive, the CLI proposes launching one directly or continuing with manual configuration.

If you save a manual configuration, pgcopycat suggests a path such as:

• ./pgcopycat.yml
• ./.pgcopycat/local-refresh.yml

Saved configs are discovered automatically on the next startup and can also be launched directly:

./pgcopycat run --config ./.pgcopycat/local-refresh.yml
./pgcopycat run --profile local-refresh

Safety Defaults

• safe target mode refuses to restore into non-empty target schemas
• Source and destination identity are checked before execution
• Missing PostgreSQL client tools are detected before database questions
• Missing destination extensions are surfaced during pre-checks
• Confirmation is required before destructive operations unless --yes is provided
• Local config files are ignored by Git by default

Reporting

Every run prints a final report that includes:

• Profile or config file used
• Source and destination summaries
• Clone mode and target mode
• Included and excluded schemas
• Excluded tables and excluded table data
• Extension verification or creation status
• Sequence reset status
• Step results
• Warnings, errors, duration, and final status

Optional file output can be enabled with report.output_file and report.format.

Development

Run the test suite:

go test ./...

The project currently uses:

• cobra for the CLI
• yaml.v3 for configuration
• survey/v2 for interactive prompts
• pgx/v5 for PostgreSQL access during checks and post-restore operations

Contributing

Contributions are welcome. Start with CONTRIBUTING.md for development setup, pull request expectations, and review guidance.

For security-related issues, do not open a public issue first. Please read SECURITY.md.

License

This project is licensed under the Apache License 2.0. See LICENSE.