PostgreSQL is the world's most advanced open-source relational database, powering everything from small applications to enterprise systems. Effective PostgreSQL administration requires understanding both daily operations and advanced features. This guide covers essential PostgreSQL administration from a senior developer's perspective.
Why PostgreSQL Administration Skills Matter
Database administration enables:
- Performance Optimization: Faster queries, better user experience
- Data Integrity: Reliable backups and recovery
- Troubleshooting: Quick identification of issues
- Security: Proper access control and encryption
- Scalability: Handle growing data and traffic
Connection and Basic Operations
Connecting to PostgreSQL
# Connect as the postgres user
psql -U postgres
# Connect to a specific database
psql -U postgres -d mydatabase
# Connect to a remote server
psql -h hostname -p 5432 -U username -d database
# Connection string format
psql "postgresql://user:password@host:5432/database?sslmode=require"
Basic psql Commands
-- List databases
\l
-- Connect to a database
\c mydatabase
-- List tables
\dt
-- List all tables, including system tables
\dt *.*
-- Describe table structure
\d tablename
-- List indexes
\di
-- List users/roles
\du
-- Show current user
SELECT current_user;
-- Show current database
SELECT current_database();
-- Exit psql
\q
User and Permission Management
Create and Manage Users
-- Create a user
CREATE USER appuser WITH PASSWORD 'securepassword';
-- Create a user with options
CREATE USER admin WITH
PASSWORD 'adminpass'
CREATEDB
CREATEROLE
LOGIN;
-- Alter user
ALTER USER appuser WITH PASSWORD 'newpassword';
-- Grant superuser (use carefully)
ALTER USER admin WITH SUPERUSER;
-- Drop user
DROP USER appuser;
-- List users
SELECT usename, usesuper, usecreatedb FROM pg_user;
Database Permissions
-- Create database
CREATE DATABASE myapp OWNER appuser;
-- Grant CONNECT permission
GRANT CONNECT ON DATABASE myapp TO appuser;
-- Grant all privileges on database
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;
-- Revoke privileges
REVOKE ALL PRIVILEGES ON DATABASE myapp FROM appuser;
Table Permissions
-- Grant SELECT on a specific table
GRANT SELECT ON users TO readonly_user;
-- Grant multiple permissions
GRANT SELECT, INSERT, UPDATE ON users TO appuser;
-- Grant on all tables in schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_user;
-- View table permissions
SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_name = 'users';
Useful Queries
Current Running Queries
-- Show active queries
SELECT
pid,
datname,
usename,
state,
query,
age(clock_timestamp(), query_start) AS duration
FROM pg_stat_activity
WHERE state != 'idle'
AND query NOT LIKE '%pg_stat_activity%'
ORDER BY duration DESC;
-- Show long-running queries (> 5 minutes)
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query,
state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
AND state != 'idle';
Terminate Queries
-- Cancel query (graceful)
SELECT pg_cancel_backend(pid);
-- Terminate connection (force)
SELECT pg_terminate_backend(pid);
-- Terminate all connections to the database (except yours)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydatabase'
AND pid != pg_backend_pid();
Database Size
-- Database sizes
SELECT
datname,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
-- Table sizes
SELECT
schemaname,
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::regclass)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;
Index Information
-- List all indexes
SELECT
schemaname,
tablename,
indexname,
indexdef
FROM pg_indexes
WHERE schemaname = 'public';
-- Index usage statistics
SELECT
schemaname,
relname AS tablename,
indexrelname AS indexname,
idx_scan AS times_used,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
-- Find unused indexes
SELECT
schemaname || '.' || relname AS table,
indexrelname AS index,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
idx_scan AS times_used
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique
AND idx_scan < 50
ORDER BY pg_relation_size(i.indexrelid) DESC;
PostGIS Geographic Queries
PostgreSQL with the PostGIS extension handles geographic data:
-- Enable PostGIS
CREATE EXTENSION postgis;
-- Store a point
INSERT INTO locations (name, geom)
VALUES ('Office', ST_SetSRID(ST_MakePoint(-73.935242, 40.730610), 4326));
-- Convert geometry to GeoJSON
SELECT
name,
ST_AsGeoJSON(ST_Transform(geom, 3857)) AS geojson
FROM locations;
-- Find points within radius (meters)
SELECT name
FROM locations
WHERE ST_DWithin(
geom::geography,
ST_SetSRID(ST_MakePoint(-73.935242, 40.730610), 4326)::geography,
1000 -- 1,000 meters
);
-- Check if a point is inside a polygon
SELECT ST_Contains(area.geom, point.geom)
FROM areas area, points point
WHERE area.name = 'Manhattan' AND point.name = 'Office';
-- Create a buffer around a point
SELECT ST_Buffer(
ST_Transform(geom, 3857),
500 -- 500-meter radius
) FROM locations WHERE name = 'Office';
JSON Operations
-- Create a table with a JSON column
CREATE TABLE events (
id SERIAL PRIMARY KEY,
data JSONB NOT NULL
);
-- Insert JSON data
INSERT INTO events (data) VALUES
('{"type": "click", "page": "/home", "user_id": 123}');
-- Query JSON fields
SELECT
data->>'type' AS event_type,
data->>'page' AS page,
(data->>'user_id')::int AS user_id
FROM events;
-- Filter by JSON field
SELECT * FROM events
WHERE data->>'type' = 'click';
-- Check if a key exists
SELECT * FROM events
WHERE data ? 'user_id';
-- Update JSON field
UPDATE events
SET data = jsonb_set(data, '{processed}', 'true')
WHERE id = 1;
-- Rename JSON property
UPDATE events
SET data = (data - 'old_key') || jsonb_build_object('new_key', data->'old_key')
WHERE data ? 'old_key';
Backup and Restore
pg_dump
# Back up a single database
pg_dump -U postgres mydatabase > backup.sql
# Back up with compression
pg_dump -U postgres -Fc mydatabase > backup.dump
# Back up specific tables
pg_dump -U postgres -t users -t orders mydatabase > tables.sql
# Back up schema only
pg_dump -U postgres --schema-only mydatabase > schema.sql
# Back up data only
pg_dump -U postgres --data-only mydatabase > data.sql
pg_restore
# Restore from an SQL file
psql -U postgres mydatabase < backup.sql
# Restore from custom format
pg_restore -U postgres -d mydatabase backup.dump
# Restore to a new database
createdb -U postgres newdatabase
pg_restore -U postgres -d newdatabase backup.dump
# List contents of backup
pg_restore -l backup.dump
Automated Backups
#!/bin/bash
# backup.sh - Daily PostgreSQL backup
DATE=$(date +%Y-%m-%d)
BACKUP_DIR="/backups/postgres"
DATABASES="myapp production"
mkdir -p "$BACKUP_DIR"
for DB in $DATABASES; do
pg_dump -U postgres -Fc "$DB" > "$BACKUP_DIR/${DB}_${DATE}.dump"
# Keep only the last 7 days
find "$BACKUP_DIR" -name "${DB}_*.dump" -mtime +7 -delete
done
# Optional: Upload to S3
# aws s3 sync "$BACKUP_DIR" s3://my-bucket/postgres-backups/
Performance Tuning
EXPLAIN ANALYZE
-- Analyze query execution
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id;
-- Detailed buffer usage
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM large_table WHERE indexed_column = 'value';
Create Indexes
-- B-tree index (default, most common)
CREATE INDEX idx_users_email ON users(email);
-- Partial index
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
-- Composite index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
-- GIN index for JSONB
CREATE INDEX idx_events_data ON events USING GIN(data);
-- GiST index for geographic data
CREATE INDEX idx_locations_geom ON locations USING GIST(geom);
-- Concurrent index (doesn't lock table)
CREATE INDEX CONCURRENTLY idx_large_table ON large_table(column);
Vacuum and Analyze
-- Vacuum specific table
VACUUM ANALYZE users;
-- Full vacuum (reclaims more space, locks table)
VACUUM FULL users;
-- Analyze for query planner
ANALYZE users;
-- Check when last vacuumed
SELECT
schemaname,
relname,
last_vacuum,
last_autovacuum,
vacuum_count
FROM pg_stat_user_tables;
Password Reset
# Edit pg_hba.conf to allow local trust
# Change: local all all peer
# To: local all all trust
# Restart PostgreSQL
sudo systemctl restart postgresql
# Connect without a password
psql -U postgres
# Reset password
ALTER USER postgres WITH PASSWORD 'newpassword';
# Revert pg_hba.conf and restart
Key Takeaways
- Monitor active queries: Identify slow queries early
- Use EXPLAIN ANALYZE: Understand query execution plans
- Index strategically: Balance read speed vs. write overhead
- Regular backups: Automated, tested, offsite
- Vacuum regularly: Prevent table bloat
- Secure access: Principle of least privilege
PostgreSQL administration is a deep skill—these fundamentals cover most daily needs, but continue learning advanced topics like replication, partitioning, and performance tuning as your databases grow.
