Connect to local or staging PostgreSQL database. Use when you need to inspect tables, run queries, check migration status, or debug database issues.
Extract variables from .env:
grep '^DB_' .env
Parse output, construct postgresql URL, then connect:
psql "postgresql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
Default local connection:
PGPASSWORD=postgres psql -h localhost -p 5432 -U postgres -d iota_erp
Extract public database URL from Railway:
railway variables -e staging -s db --kv | grep DATABASE_PUBLIC_URL
Connect using extracted URL:
psql <DATABASE_PUBLIC_URL>
Example for IOTA SDK staging:
# Get connection details from Railway
railway variables -e staging -s db --kv
# Connect (example)
PGPASSWORD=<password> psql -h <host> -U postgres -p <port> -d railway
Common diagnostics after connecting:
SELECT schemaname, tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;
SELECT datname, count(*)
FROM pg_stat_activity
GROUP BY datname;
-- Schema migrations (sql-migrate)
SELECT version, applied_at
FROM schema_migrations
ORDER BY applied_at DESC
LIMIT 5;
SELECT pg_size_pretty(pg_database_size(current_database()));
-- Count tenants
SELECT COUNT(*) as tenant_count FROM tenants WHERE deleted_at IS NULL;
-- Count users per tenant
SELECT t.name, COUNT(u.id) as user_count
FROM tenants t
LEFT JOIN users u ON u.tenant_id = t.id AND u.deleted_at IS NULL
WHERE t.deleted_at IS NULL
GROUP BY t.id, t.name
ORDER BY user_count DESC
LIMIT 10;
-- Count organizations
SELECT COUNT(*) as org_count FROM organizations WHERE deleted_at IS NULL;
-- Organizations per tenant
SELECT t.name, COUNT(o.id) as org_count
FROM tenants t
LEFT JOIN organizations o ON o.tenant_id = t.id AND o.deleted_at IS NULL
WHERE t.deleted_at IS NULL
GROUP BY t.id, t.name
ORDER BY org_count DESC;
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
AND now() - pg_stat_activity.query_start > interval '1 minute'
ORDER BY duration DESC;
SELECT tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) as indexes_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;
SELECT schemaname, tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
AND n_distinct > 100
AND correlation < 0.1
ORDER BY n_distinct DESC
LIMIT 20;
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
-- Check if tenant_id is consistently applied
SELECT table_name
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name NOT IN ('tenants', 'schema_migrations', 'sessions')
AND table_name NOT LIKE 'pg_%'
AND table_name NOT IN (
SELECT table_name
FROM information_schema.columns
WHERE table_schema = 'public'
AND column_name = 'tenant_id'
);
-- Check if organization_id is applied where needed
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema = 'public'
AND column_name = 'organization_id';
SELECT schemaname, tablename,
last_vacuum, last_autovacuum,
last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC NULLS LAST;
-- Check for bloated indexes
REINDEX TABLE table_name;
-- Update statistics
ANALYZE table_name;
# Export from .env
export $(grep '^DB_' .env | xargs)
# Connect using exported vars
PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME
# If using pgbouncer
psql "postgresql://$DB_USER:$DB_PASSWORD@localhost:6432/$DB_NAME"
# For production/staging with SSL
psql "postgresql://$DB_USER:$DB_PASSWORD@$DB_HOST:$DB_PORT/$DB_NAME?sslmode=require"
-- Always verify you're on the right database
SELECT current_database();
-- Check current schema
SELECT current_schema();
-- Verify tenant context if applicable
-- (Application-level check, not SQL)
# Dump specific table
pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME -t table_name > backup.sql
# Dump entire database
pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME > full_backup.sql
# Local connection
PGPASSWORD=postgres psql -h localhost -p 5432 -U postgres -d iota_erp
# Staging connection (Railway)
railway variables -e staging -s db --kv | grep DATABASE_PUBLIC_URL
psql <DATABASE_PUBLIC_URL>
# Run query from file
psql -h localhost -U postgres -d iota_erp -f query.sql
# Export to CSV
psql -h localhost -U postgres -d iota_erp -c "COPY (SELECT * FROM users) TO STDOUT WITH CSV HEADER" > users.csv
# List all tables
\dt
# Describe table
\d table_name
# List all indexes
\di
# Quit
\q