Nashpush

postgresql-db-psql-connect

4 min read

This guide provides instructions for connecting to PostgreSQL databases using Kubernetes pods with postgresql-client across different environments: AWS staging, AWS production, and on-premises clusters (BTS00, BTS01, HEL01, HEL02).

Prerequisites

  • kubectl configured for the target cluster (important: you must first connect to the relevant Kubernetes cluster)
  • Appropriate RBAC permissions to create and manage pods
  • Database connection credentials

One-line PostgreSQL Client Commands

Important Note

Before using these commands, ensure you are connected to the correct Kubernetes cluster for the environment you want to access.

Environment-Specific Examples

AWS Staging Environment

# Connect to staging PostgreSQL database
kubectl run postgresql-client --rm --tty -i --restart="Never" --namespace webpush --image bitnami/postgresql --overrides='{"spec":{"tolerations":[{"key":"group","operator":"Equal","value":"application-infra","effect":"NoSchedule"}]}}' --command -- psql -h webpush-rds-stg.cluster-ro-c3mfybnk6qg7.us-east-2.rds.amazonaws.com -U webpushdb -d webpush

AWS Production Environment

# Connect to production PostgreSQL database
kubectl run postgresql-client --rm --tty -i --restart="Never" --namespace webpush --image bitnami/postgresql --overrides='{"spec":{"tolerations":[{"key":"group","operator":"Equal","value":"infra","effect":"NoSchedule"}]}}' --command -- psql -h webpush-worker-rds-prd.cluster-ro-cmpslvys4mms.us-east-2.rds.amazonaws.com -U webpushdb -d webpush

On-Premises Clusters

BTS00

# Connect to BTS00 PostgreSQL database
kubectl run postgresql-postgresql-client --rm --tty -i --restart="Never" --namespace webpush --image bitnami/postgresql --command -- psql --host 10.1.0.90 -U webpushdb -d webpush

BTS01 or HEL01

# Connect to BTS01 or HEL01 PostgreSQL database
kubectl run postgresql-postgresql-client --rm --tty -i --restart="Never" --namespace webpush --image bitnami/postgresql --command -- psql --host 10.0.0.2 -U webpushdb -d webpush

HEL02

# Connect to HEL02 PostgreSQL database
kubectl run postgresql-postgresql-client --rm --tty -i --restart="Never" --namespace webpush --image bitnami/postgresql --command -- psql --host 10.0.0.3 -U webpushdb -d webpush

Common PostgreSQL Client Commands

Executing SQL Commands

With the one-liner approach, you can extend the commands to execute SQL directly:

# Execute a single SQL query
kubectl run postgresql-client --rm --tty -i --restart="Never" --namespace webpush --image bitnami/postgresql --command -- psql -h <host> -U webpushdb -d webpush -c "SELECT version();"
 
# Connect to the database and run multiple commands
kubectl run postgresql-client --rm --tty -i --restart="Never" --namespace webpush --image bitnami/postgresql --command -- psql -h <host> -U webpushdb -d webpush -c "\l" -c "\dt"

Executing SQL Files

# Execute a SQL file against the database
cat script.sql | kubectl run postgresql-client --rm --tty -i --restart="Never" --namespace webpush --image bitnami/postgresql --command -- psql -h <host> -U webpushdb -d webpush

Database Backup and Restore

# Create a database dump
kubectl run postgresql-client --rm --tty -i --restart="Never" --namespace webpush --image bitnami/postgresql --command -- pg_dump -h <host> -U webpushdb webpush > backup.sql
 
# Restore from a dump
cat backup.sql | kubectl run postgresql-client --rm --tty -i --restart="Never" --namespace webpush --image bitnami/postgresql --command -- psql -h <host> -U webpushdb -d webpush
 
# Compressed backup
kubectl run postgresql-client --rm --tty -i --restart="Never" --namespace webpush --image bitnami/postgresql --command -- pg_dump -h <host> -U webpushdb webpush | gzip > backup.sql.gz

Useful PSQL Commands

Once connected to the database, use these common psql commands:

-- List all databases
\l
 
-- Connect to a database
\c database_name
 
-- List all tables
\dt
 
-- Describe table structure
\d table_name
 
-- List all schemas
\dn
 
-- Show current connection info
\conninfo
 
-- Execute SQL file
\i /path/to/file.sql
 
-- Toggle expanded display
\x
 
-- Quit psql
\q

Connecting to the Right Kubernetes Cluster

Before running any of the commands, make sure you are connected to the correct Kubernetes cluster using kubectl context:

# List available contexts
kubectl config get-contexts
 
# Switch to the desired context
kubectl config use-context <context-name>
 
# Verify you're connected to the right cluster
kubectl cluster-info

Note on Cleanup

When using the --rm flag in the kubectl run command, the pod will be automatically deleted after the session ends, so no manual cleanup is required.

Security Considerations

  • The one-liner commands in this guide use direct username/password authentication
  • The --rm flag ensures pods are automatically deleted after use
  • Tolerations are included to ensure pods can be scheduled on the correct nodes
  • Never use these commands to make schema changes to production databases without proper review
  • For AWS environments, the read-only endpoints are used by default

Graph View