Skip to content

Connection Options

pg supports all standard PostgreSQL connection methods, with service files being the recommended approach.

Create ~/.pg_service.conf:

[development]
host=localhost
port=5432
user=dev_user
dbname=myapp_dev

[staging]
host=staging-db.company.com
port=5432
user=app_user
dbname=myapp_staging
sslmode=require

[production]
host=prod-db.company.com
port=5432
user=app_user
dbname=myapp_prod
sslmode=require

Usage: 

pg query -s development
pg dump db -s production -f dump.sql # or pg export db -s production -f dump.sql
pg create db -s staging newfeature # or pg create db -s staging newfeature

Learn More

For complete service file documentation, see the PostgreSQL service file documentation.

Why Service Files?

  • 🎯 Simple - Clean, readable commands
  • 🔄 Flexible - Switch environments easily
  • 🛠️ Maintainable - Change connection details in one place
  • 👔 Professional - How experienced PostgreSQL DBAs connect

Connection Strings

For one-off connections: 

# Key-value format
pg query -c "host=localhost port=5432 user=admin dbname=myapp"
pg dump db -c "host=server user=admin dbname=myapp sslmode=require" -f dump.sql
# or pg export db -c "host=server user=admin dbname=myapp sslmode=require" -f dump.sql

# URI format
pg query -c "postgresql://admin@localhost:5432/myapp"
pg dump db -c "postgresql://admin@server/myapp?sslmode=require" -f dump.sql
# or pg export db -c "postgresql://admin@server/myapp?sslmode=require" -f dump.sql

Learn More

For complete connection string documentation, see the PostgreSQL connection string documentation.

Individual Flags

Traditional PostgreSQL connection flags: 

pg query -h localhost -p 5432 -u admin -d myapp
pg maintain vacuum -h server -u admin -d myapp

Available flags:

  • -h, --host - Hostname or socket path
  • -p, --port - Port number
  • -u, --user - Username
  • -d, --database - Database name (database name only, not connection string)

Database Flag Limitation

Unlike some PostgreSQL clients, pg's -d flag only accepts database names, not connection strings. Use -c for connection strings.

Environment Variables

pg respects standard PostgreSQL environment variables: 

export PGHOST=localhost
export PGPORT=5432
export PGUSER=admin
export PGDATABASE=myapp

pg query  # Uses environment variables

Don't mix connection methods - pg will reject conflicting options: 

# ❌ This will fail
pg query -s production -h localhost

# ❌ This will also fail  
pg query -c "host=server" -u admin

Next: See real-world Command Examples using these connection methods.