Working with Databases in Kubernetes: Connections, Dumps, and Data Extraction

When your PostgreSQL database runs inside Kubernetes, simple tasks like connecting, running queries, or extracting data become slightly more involved. You can’t just psql directly from your laptop – you need to go through the cluster.

This post covers the practical ways to work with databases in Kubernetes: direct pod exec, VPN access, SOCKS5 proxies, pg_dump for backups, and kubectl cp to get files out.

Connection Methods

Method 1: Exec into the Database Pod

The simplest approach – exec into the pod and run psql locally:

# Exec into the database pod
kubectl exec -it myapp-db-0 -n myapp -- sh

# Connect to PostgreSQL (you're now inside the pod)
psql -h localhost -p 5432 -U adminuser -d myapp

You’ll be prompted for the password. Once connected, you can run queries:

-- List schemas
\dn

-- List tables in a schema
\dt myschema.*

-- Describe a table
\d myschema.users

-- Query data
SELECT * FROM myschema.users LIMIT 10;

Method 2: VPN Access (Direct Connection)

If your cluster is accessible via VPN, you can connect directly from your machine using the pod’s cluster IP or a service IP:

# Connect to VPN first
# Then connect directly to the database service
psql -h 10.0.3.9 -p 5432 -U adminuser -d myapp

This is cleaner for development work – you can use GUI tools like pgAdmin, DBeaver, or DataGrip.

Method 3: Port Forwarding

Forward the database port to your local machine:

# Forward local port 5432 to the pod's port 5432
kubectl port-forward pod/myapp-db-0 5432:5432 -n myapp

# In another terminal, connect locally
psql -h localhost -p 5432 -U adminuser -d myapp

This works without VPN but ties up a terminal.

Method 4: SOCKS5 Proxy

For more flexibility, deploy a SOCKS5 proxy pod in the cluster:

# socks5-proxy.yaml
apiVersion: v1
kind: Pod
metadata:
  name: socks5-proxy
  namespace: myapp
spec:
  containers:
  - name: socks5
    image: serjs/go-socks5-proxy
    ports:
    - containerPort: 1080
    env:
    - name: PROXY_USER
      value: "proxyuser"
    - name: PROXY_PASSWORD
      value: "proxypassword"

Deploy and port-forward:

kubectl apply -f socks5-proxy.yaml
kubectl port-forward pod/socks5-proxy 1080:1080 -n myapp

Configure your database client to use the SOCKS5 proxy at localhost:1080. This lets you access any service in the cluster through the proxy.

Extracting Data

Copy Files with kubectl cp

If you’ve generated a file inside the pod (CSV export, dump file), copy it out:

# Copy from pod to local machine
kubectl cp myapp/myapp-db-0:/home/postgres/export.csv ~/local-exports/export.csv

# Copy from local to pod (if needed)
kubectl cp ~/local-data/import.csv myapp/myapp-db-0:/tmp/import.csv

Note: kubectl cp requires tar to be installed in the container. Most database images have it, but some minimal images don’t.

Preview Files Before Copying

Check the file contents first:

# View first 10 lines
kubectl exec myapp-db-0 -n myapp -- head /home/postgres/export.csv

# Check file size
kubectl exec myapp-db-0 -n myapp -- ls -lh /home/postgres/export.csv

# Count lines
kubectl exec myapp-db-0 -n myapp -- wc -l /home/postgres/export.csv

Using rsync for Large Files

For large files or directories, rsync is more reliable than kubectl cp:

# Port-forward SSH (if the container has SSH)
kubectl port-forward pod/myapp-db-0 2222:22 -n myapp

# rsync through the forwarded port
rsync -avz -e "ssh -p 2222" postgres@localhost:/tmp/large-dump.sql ~/local-exports/

This requires SSH to be running in the container, which isn’t common in production database images. More practical for custom images or debug pods.

PostgreSQL Dumps

Basic pg_dump

Exec into the pod and run pg_dump:

# Exec into the pod
kubectl exec -it myapp-db-0 -n myapp -- sh

# Dump a specific table
pg_dump -U adminuser -d myapp -t myschema.users -f /home/postgres/users_dump.sql

# Exit the pod
exit

# Copy the dump to your local machine
kubectl cp myapp/myapp-db-0:/home/postgres/users_dump.sql ~/exports/users_dump.sql

Dump Options

Different dump formats for different needs:

# Standard SQL dump (for restoring with psql)
pg_dump -U adminuser -d myapp -t myschema.users -f dump.sql

# With column inserts (more portable, slower to restore)
pg_dump -U adminuser -d myapp -t myschema.users --column-inserts -f dump_inserts.sql

# Custom format (compressed, use pg_restore)
pg_dump -U adminuser -d myapp -t myschema.users -Fc -f dump.custom

# Plain text with CREATE TABLE included
pg_dump -U adminuser -d myapp -t myschema.users --no-owner --no-acl -f dump_clean.sql

Export to CSV

For data analysis or migration, CSV is often more useful:

# Using \COPY (runs on the server, writes to server filesystem)
kubectl exec -it myapp-db-0 -n myapp -- \
  psql -U adminuser -d myapp -c "\COPY (SELECT * FROM myschema.users) TO '/home/postgres/users.csv' WITH CSV HEADER"

# Copy the CSV out
kubectl cp myapp/myapp-db-0:/home/postgres/users.csv ~/exports/users.csv

Dump Entire Database

For full backups:

# Dump everything
pg_dump -U adminuser -d myapp -f full_backup.sql

# Dump with compression
pg_dump -U adminuser -d myapp | gzip > full_backup.sql.gz

# Dump schema only (no data)
pg_dump -U adminuser -d myapp --schema-only -f schema.sql

# Dump data only (no schema)
pg_dump -U adminuser -d myapp --data-only -f data.sql

Dump Multiple Tables

# Multiple tables
pg_dump -U adminuser -d myapp \
  -t myschema.users \
  -t myschema.orders \
  -t myschema.products \
  -f multiple_tables.sql

# Entire schema
pg_dump -U adminuser -d myapp -n myschema -f schema_dump.sql

Restoring Data

Restore from SQL Dump

# Copy dump file into the pod
kubectl cp ~/exports/users_dump.sql myapp/myapp-db-0:/tmp/users_dump.sql

# Exec in and restore
kubectl exec -it myapp-db-0 -n myapp -- \
  psql -U adminuser -d myapp -f /tmp/users_dump.sql

Restore from Custom Format

# Copy custom format dump
kubectl cp ~/exports/dump.custom myapp/myapp-db-0:/tmp/dump.custom

# Restore with pg_restore
kubectl exec -it myapp-db-0 -n myapp -- \
  pg_restore -U adminuser -d myapp /tmp/dump.custom

Import CSV

# Copy CSV into pod
kubectl cp ~/data/users.csv myapp/myapp-db-0:/tmp/users.csv

# Import with \COPY
kubectl exec -it myapp-db-0 -n myapp -- \
  psql -U adminuser -d myapp -c "\COPY myschema.users FROM '/tmp/users.csv' WITH CSV HEADER"

Quick Reference

Common psql Commands

-- List databases
\l

-- Connect to database
\c myapp

-- List schemas
\dn

-- List tables in current schema
\dt

-- List tables in specific schema
\dt myschema.*

-- Describe table
\d myschema.users

-- Show table size
SELECT pg_size_pretty(pg_total_relation_size('myschema.users'));

-- List users/roles
\du

-- Show current connection info
\conninfo

-- Exit
\q

One-Liners

# Run a query without interactive session
kubectl exec myapp-db-0 -n myapp -- \
  psql -U adminuser -d myapp -c "SELECT COUNT(*) FROM myschema.users"

# Export query result to CSV in one command
kubectl exec myapp-db-0 -n myapp -- \
  psql -U adminuser -d myapp -c "\COPY (SELECT * FROM myschema.users WHERE active = true) TO STDOUT WITH CSV HEADER" \
  > ~/exports/active_users.csv

# Check database size
kubectl exec myapp-db-0 -n myapp -- \
  psql -U adminuser -d myapp -c "SELECT pg_size_pretty(pg_database_size('myapp'))"

Gotchas

Permission Issues

If kubectl cp fails with permission errors:

# Check file permissions inside the pod
kubectl exec myapp-db-0 -n myapp -- ls -la /home/postgres/

# Write to /tmp instead (usually writable)
kubectl exec myapp-db-0 -n myapp -- \
  psql -U adminuser -d myapp -c "\COPY (SELECT * FROM myschema.users) TO '/tmp/users.csv' WITH CSV HEADER"

kubectl cp myapp/myapp-db-0:/tmp/users.csv ~/exports/users.csv

Large Dumps Timing Out

For large databases, increase timeout or use streaming:

# Stream directly to local file (avoids storing on pod)
kubectl exec myapp-db-0 -n myapp -- \
  pg_dump -U adminuser -d myapp | gzip > ~/exports/backup.sql.gz

Character Encoding Issues

Ensure consistent encoding:

# Specify encoding in dump
pg_dump -U adminuser -d myapp -E UTF8 -f dump.sql

# Or set in psql
psql -U adminuser -d myapp -c "SET client_encoding = 'UTF8';"

Summary

Task	Command
Exec into pod	kubectl exec -it myapp-db-0 -n myapp -- sh
Connect to psql	psql -h localhost -p 5432 -U adminuser -d myapp
Port forward	kubectl port-forward pod/myapp-db-0 5432:5432 -n myapp
Dump table	pg_dump -U adminuser -d myapp -t schema.table -f dump.sql
Export CSV	psql -c "\COPY (SELECT...) TO '/path/file.csv' WITH CSV HEADER"
Copy file out	kubectl cp myapp/myapp-db-0:/path/file.csv ~/local/file.csv

Working with databases in Kubernetes adds friction, but once you know the patterns, it becomes muscle memory. Keep these commands in a cheat sheet – you’ll use them more often than you’d expect.

---

Got other database + Kubernetes tips? Find me on LinkedIn.