Command Examples¶

Real-world examples using pg for common PostgreSQL tasks.

Daily Operations¶

Connect and Query¶

# Interactive session
pg query -s production

# Run a single query
pg query -s production --command "select version();"

# Run script from file
pg query -s production -f queries.sql
echo "select version();" | pg query -s production

Using --command flag

Use the long form --command instead of -c to avoid conflicts with pg's connection string flag.

Export Data (Logical Dumps)¶

# Export single database
pg dump db -s production -f myapp-dump.sql
# or pg export db -s production -f myapp-dump.sql

# Export with custom format (for compression)
pg dump db -s production --format=custom -f myapp.dump
# or pg export db -s production --format=custom -f myapp.dump

# Export all databases
pg dump all -s production -f cluster-dump.sql
# or pg export all -s production -f cluster-dump.sql

# Export specific tables only
pg dump db -s production --table=users --table=orders -f users-orders.sql
# or pg export db -s production --table=users --table=orders -f users-orders.sql

Import Data¶

# Import SQL dump
pg query -s staging -f myapp-dump.sql

# Import custom format dump
pg dump restore -s staging myapp.dump # or pg import -s staging myapp.dump

Database Management¶

Create and Drop Databases¶

# Create new database
pg create db -s development feature_branch

# Drop database
pg drop db -s development old_feature

# Create with specific encoding
pg create db -s development newapp --encoding=UTF8 --locale=en_US.UTF-8

User Management¶

# Create user
pg create user -s production newuser

# Create user with specific privileges
pg create user -s production appuser --createdb --no-superuser

# Drop user
pg drop user -s production olduser

Maintenance Tasks¶

# Vacuum database
pg maintain vacuum -s production

# Vacuum with analyze
pg maintain vacuum -s production --analyze

# Reindex database
pg maintain reindex -s production

# Cluster tables
pg maintain cluster -s production

# Check for corruption
pg maintain check -s production

Performance Testing¶

# Basic benchmark
pg bench -s testing

# Custom benchmark
pg bench -s testing --clients=10 --transactions=1000 --time=60

Backup and Recovery¶

Physical Backups (True Backups)¶

# Take base backup
pg backup base -s production /backups/$(date +%Y%m%d)

# Verify backup integrity
pg backup verify /backups/20241201

# Combine incremental backups
pg backup combine /backups/full /backups/incremental /backups/combined

Restoring a Postgres backup

pg_basebackup does not handle restore. You "just" need to copy the (and possibly untar) the backup to the right directory. That's one of the reasons why it is highly recommend to use another backup/recovery tool in the Postgres ecosystem. I personnaly love pgBackRest.

Monitoring and Status¶

Server Health¶

# Check if server is ready
pg info ready -s production

# Get the installed PostgreSQL building environment configuration
pg info config

# Check with specific timeout
pg info ready -s production --timeout=5

Streaming and Replication¶

# Stream WAL files
pg stream wal -s production --directory=/wal-archive

# Logical replication
pg stream logical -s production --slot=replica_slot

Development Workflow¶

Schema Development¶

# Dump schema only
pg dump db -s development --schema-only -f schema.sql
# or pg export db -s development --schema-only -f schema.sql

# Dump data only (no schema)
pg dump db -s development --data-only -f data.sql
# or pg export db -s development --data-only -f data.sql

# Preprocess embedded SQL
pg dev application.pgc

Environment Synchronization¶

# Copy production schema to staging
pg dump db -s production --schema-only | pg query -s staging
# or pg export db -s production --schema-only | pg query -s staging

# Refresh development with production data
pg dump db -s production -f dev-refresh.sql
# or pg export db -s production -f dev-refresh.sql
pg query -s development -f dev-refresh.sql

Advanced Examples¶

Using Dry Run Mode¶

# Test commands before execution
pg dump db --dry-run -s production -f large-dump.sql
# or pg export db --dry-run -s production -f large-dump.sql
pg backup base --dry-run -s production /backups/test

Debug Connection Issues¶

# Enable debug logging
pg query -s production --pg-verbose

# Test connectivity
pg info ready -s production --pg-verbose

Next: Check the complete Command Reference for all available options.