How do I install the Postgres skill?

Run npx skillmds@latest add tomevault-io/postgres in your terminal (requires Node.js), paste this page's agent-chat prompt into Claude, Cursor, or any MCP-connected agent, or download the SKILL.md file and copy it into your agent's skills directory.

What does the Postgres skill do?

PostgreSQL CLI client (psql) for database operations running inside a Docker container Use when this capability is needed. It is listed under DevOps & Infra on SkillMD.

Is Postgres safe to use?

This skill has not completed SkillMD's automated safety review yet. Independent scanners report: SkillSpector: CAUTION, Skill Scanner: PASS. SkillMD never runs a skill's scripts for you; review the SKILL.md before installing.

Which AI agents work with Postgres?

This skill is tagged as working with Claude Code, Claude.ai, OpenAI Codex. SKILL.md is an open format, so most agents that read a skills directory can load it too.

Is Postgres free to use?

Yes. Installing skills from SkillMD is free, and the skill stays under its author's original license.

Who published Postgres?

tomevault-io (@tomevault-io) published this skill. Their other Agent Skills are listed on their SkillMD profile.

Postgres by tomevault-io — Agent Skill

Name: Postgres
Author: tomevault-io

PostgreSQL Skill

Access PostgreSQL databases using the psql CLI client running inside a Docker container. Execute queries, manage databases, run scripts, export/import data, and perform database operations without installing PostgreSQL locally.

Overview

This skill provides the psql command-line client through a containerized runtime. Connect to any PostgreSQL database (local or remote), execute SQL commands, run migration scripts, and manage your databases - all while keeping your host system clean.

When to Use This Skill

Use this skill when you need to:

Connect to PostgreSQL databases without local installation
Execute SQL queries and commands
Run database migration scripts
Export and import data (CSV, SQL dumps)
List databases, tables, and schemas
Inspect table structures and relationships
Execute administrative tasks
Test database connections
Run ad-hoc queries during development

Prerequisites

Docker

The PostgreSQL client runs inside a Docker container:

# macOS (using Homebrew)
brew install --cask docker

# Linux (Ubuntu/Debian)
sudo apt-get update
sudo apt-get install docker.io
sudo systemctl start docker

PostgreSQL Server

You need access to a PostgreSQL server:

Local PostgreSQL instance
Remote database server
Cloud-hosted PostgreSQL (AWS RDS, Google Cloud SQL, Azure, etc.)
Docker PostgreSQL container

Configuration

Add to your .skill-engine.toml:

[skills.postgres]
source = "docker:postgres:16-alpine"
runtime = "docker"
description = "PostgreSQL CLI client"

[skills.postgres.docker]
image = "postgres:16-alpine"
entrypoint = "psql"
environment = ["PGPASSWORD=${PGPASSWORD:-}"]
network = "bridge"
memory = "256m"
rm = true

Configuration Explained

image: postgres:16-alpine - PostgreSQL 16.x Alpine image (~80MB)
entrypoint: psql - PostgreSQL interactive terminal
environment: Sets PGPASSWORD from host environment
network: bridge - Allows connections to databases (local and remote)
memory: 256m - Limits container to 256MB RAM
rm: true - Automatically removes container after execution

Usage

PostgreSQL skill uses pass-through syntax - all arguments after -- are passed directly to psql:

skill run postgres -- [psql arguments]

Connection Methods

Using Command-Line Arguments

# Basic connection
skill run postgres -- -h localhost -U postgres -d mydb

# With specific port
skill run postgres -- -h localhost -p 5432 -U myuser -d mydb

# Remote database
skill run postgres -- -h db.example.com -U admin -d production

Using Environment Variables

# Set connection details
export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=mydb
export PGPASSWORD=secret

# Connect with defaults
skill run postgres --

Using Connection URI

# PostgreSQL connection string
skill run postgres -- postgresql://user:password@host:5432/database

# With SSL
skill run postgres -- "postgresql://user:password@host:5432/database?sslmode=require"

Common Operations

Running Queries

# Execute single SQL command
skill run postgres -- -h localhost -U postgres -c "SELECT * FROM users;"

# Multiple commands
skill run postgres -- -h localhost -U postgres -c "SELECT count(*) FROM users; SELECT version();"

# Query with output formatting
skill run postgres -- -h localhost -U postgres -c "SELECT * FROM users;" -x  # Expanded display

Database Management

# List all databases
skill run postgres -- -h localhost -U postgres -l

# Create database
skill run postgres -- -h localhost -U postgres -c "CREATE DATABASE newdb;"

# Drop database
skill run postgres -- -h localhost -U postgres -c "DROP DATABASE olddb;"

# Connect to specific database
skill run postgres -- -h localhost -U postgres -d mydb

Table Operations

# List tables in current database
skill run postgres -- -h localhost -U postgres -d mydb -c "\dt"

# List tables with sizes
skill run postgres -- -h localhost -U postgres -d mydb -c "\dt+"

# Describe table structure
skill run postgres -- -h localhost -U postgres -d mydb -c "\d users"

# Show table constraints
skill run postgres -- -h localhost -U postgres -d mydb -c "\d+ users"

Schema Operations

# List schemas
skill run postgres -- -h localhost -U postgres -d mydb -c "\dn"

# List tables in specific schema
skill run postgres -- -h localhost -U postgres -d mydb -c "\dt myschema.*"

# Set search path
skill run postgres -- -h localhost -U postgres -d mydb -c "SET search_path TO myschema,public;"

Executing SQL Files

# Run SQL script
skill run postgres -- -h localhost -U postgres -d mydb -f migration.sql

# Run with error handling
skill run postgres -- -h localhost -U postgres -d mydb -f script.sql -v

# Run multiple files
for file in migrations/*.sql; do
  skill run postgres -- -h localhost -U postgres -d mydb -f "$file"
done

Data Export

# Export table to CSV
skill run postgres -- -h localhost -U postgres -d mydb -c "COPY users TO STDOUT CSV HEADER" > users.csv

# Export query results to CSV
skill run postgres -- -h localhost -U postgres -d mydb -c "COPY (SELECT * FROM users WHERE active=true) TO STDOUT CSV HEADER" > active_users.csv

# Export with custom delimiter
skill run postgres -- -h localhost -U postgres -d mydb -c "COPY users TO STDOUT CSV HEADER DELIMITER '|'" > users.csv

# Export as SQL INSERT statements
skill run postgres -- -h localhost -U postgres -d mydb -c "\copy users TO 'users.sql'"

Data Import

# Import CSV into table
skill run postgres -- -h localhost -U postgres -d mydb -c "COPY users FROM STDIN CSV HEADER" < users.csv

# Import with specific columns
skill run postgres -- -h localhost -U postgres -d mydb -c "COPY users(name,email) FROM STDIN CSV HEADER" < partial_users.csv

# Import SQL file
skill run postgres -- -h localhost -U postgres -d mydb -f dump.sql

User Management

# List users/roles
skill run postgres -- -h localhost -U postgres -c "\du"

# Create user
skill run postgres -- -h localhost -U postgres -c "CREATE USER newuser WITH PASSWORD 'password';"

# Grant privileges
skill run postgres -- -h localhost -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE mydb TO newuser;"

# Change password
skill run postgres -- -h localhost -U postgres -c "ALTER USER myuser WITH PASSWORD 'newpassword';"

Indexes & Performance

# List indexes
skill run postgres -- -h localhost -U postgres -d mydb -c "\di"

# Create index
skill run postgres -- -h localhost -U postgres -d mydb -c "CREATE INDEX idx_email ON users(email);"

# Analyze query performance
skill run postgres -- -h localhost -U postgres -d mydb -c "EXPLAIN ANALYZE SELECT * FROM users WHERE email='test@example.com';"

# Vacuum database
skill run postgres -- -h localhost -U postgres -d mydb -c "VACUUM ANALYZE;"

Meta-Commands

PostgreSQL psql supports special backslash commands:

Information Commands

# \l - List databases
skill run postgres -- -h localhost -U postgres -c "\l"

# \dt - List tables
skill run postgres -- -h localhost -U postgres -d mydb -c "\dt"

# \dv - List views
skill run postgres -- -h localhost -U postgres -d mydb -c "\dv"

# \df - List functions
skill run postgres -- -h localhost -U postgres -d mydb -c "\df"

# \dn - List schemas
skill run postgres -- -h localhost -U postgres -d mydb -c "\dn"

# \du - List users/roles
skill run postgres -- -h localhost -U postgres -c "\du"

# \d table - Describe table
skill run postgres -- -h localhost -U postgres -d mydb -c "\d users"

Output Formatting

# \x - Toggle expanded display (vertical)
skill run postgres -- -h localhost -U postgres -d mydb -c "\x" -c "SELECT * FROM users;"

# \t - Tuples only (no headers/footers)
skill run postgres -- -h localhost -U postgres -d mydb -c "\t" -c "SELECT name FROM users;"

# \a - Aligned/unaligned output
skill run postgres -- -h localhost -U postgres -d mydb -c "\a" -c "SELECT * FROM users;"

Advanced Examples

Database Backup

# Dump entire database
docker run --rm postgres:16-alpine pg_dump -h host -U user -d mydb > backup.sql

# Dump specific table
docker run --rm postgres:16-alpine pg_dump -h host -U user -d mydb -t users > users_backup.sql

# Dump schema only
docker run --rm postgres:16-alpine pg_dump -h host -U user -d mydb --schema-only > schema.sql

# Dump data only
docker run --rm postgres:16-alpine pg_dump -h host -U user -d mydb --data-only > data.sql

Database Restore

# Restore from dump
skill run postgres -- -h localhost -U postgres -d mydb -f backup.sql

# Restore with progress
docker run --rm -i postgres:16-alpine pg_restore -h host -U user -d mydb -v < backup.dump

Batch Operations

# Run multiple queries in transaction
skill run postgres -- -h localhost -U postgres -d mydb << EOF
BEGIN;
UPDATE users SET status='active' WHERE last_login > NOW() - INTERVAL '30 days';
DELETE FROM sessions WHERE expires_at < NOW();
COMMIT;
EOF

Connection Testing

# Test connection
skill run postgres -- -h localhost -U postgres -c "SELECT version();"

# Check connection info
skill run postgres -- -h localhost -U postgres -c "SELECT current_database(), current_user, inet_server_addr(), inet_server_port();"

# Check server status
skill run postgres -- -h localhost -U postgres -c "SELECT pg_is_in_recovery();"

Query Monitoring

# Show active queries
skill run postgres -- -h localhost -U postgres -c "SELECT pid, usename, query, state FROM pg_stat_activity WHERE state='active';"

# Show database sizes
skill run postgres -- -h localhost -U postgres -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;"

# Show table sizes
skill run postgres -- -h localhost -U postgres -d mydb -c "SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size FROM pg_tables ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC LIMIT 10;"

Environment Variables

PostgreSQL Standard Variables

Variable	Description	Example
`PGHOST`	Database host	`localhost`
`PGPORT`	Database port	`5432`
`PGUSER`	Username	`postgres`
`PGPASSWORD`	Password	`secret`
`PGDATABASE`	Database name	`mydb`
`PGSSLMODE`	SSL mode	`require`, `prefer`, `disable`

Usage

# Set environment variables
export PGHOST=localhost
export PGUSER=postgres
export PGPASSWORD=secret
export PGDATABASE=mydb

# Connect without arguments
skill run postgres --

# Or for single command
PGHOST=localhost PGPASSWORD=secret skill run postgres -- -U postgres -c "SELECT 1;"

Security Best Practices

Password Management

# Use environment variable (preferred)
export PGPASSWORD=your_password
skill run postgres -- -h host -U user -d db

# Use .pgpass file (on host)
# Create ~/.pgpass with: hostname:port:database:username:password
chmod 600 ~/.pgpass

# Use connection URI with password
skill run postgres -- "postgresql://user:pass@host:5432/db"

SSL Connections

# Require SSL
skill run postgres -- "postgresql://user:pass@host:5432/db?sslmode=require"

# Verify CA
skill run postgres -- "postgresql://user:pass@host:5432/db?sslmode=verify-ca"

# Verify full certificate
skill run postgres -- "postgresql://user:pass@host:5432/db?sslmode=verify-full"

Read-Only Access

# Start transaction in read-only mode
skill run postgres -- -h host -U user -d db -c "BEGIN READ ONLY; SELECT * FROM sensitive_table; COMMIT;"

# Grant read-only access
skill run postgres -- -h host -U postgres -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;"

Troubleshooting

Connection Refused

# Check if PostgreSQL is running
docker ps | grep postgres

# Check host is accessible
ping db-host

# Verify port
telnet localhost 5432

Authentication Failed

# Verify credentials
skill run postgres -- -h localhost -U postgres -c "SELECT 1;"

# Check PGPASSWORD is set
echo $PGPASSWORD

# Try explicit password
skill run postgres -- -h localhost -U postgres -W  # Prompts for password

Permission Denied

# Check user permissions
skill run postgres -- -h localhost -U postgres -c "\du"

# Grant necessary privileges
skill run postgres -- -h localhost -U postgres -c "GRANT ALL ON DATABASE mydb TO myuser;"

Cannot Connect to Docker Network

# If connecting to host PostgreSQL from container
# Use host.docker.internal instead of localhost (macOS/Windows)
skill run postgres -- -h host.docker.internal -U postgres

# On Linux, use host network mode
# (requires custom config)

Common Errors

Error	Cause	Solution
"connection refused"	PostgreSQL not running or wrong port	Check server status and port
"password authentication failed"	Wrong credentials	Verify username/password
"database does not exist"	Database not created	Use `-l` to list databases
"permission denied"	Insufficient privileges	Check user grants
"could not connect to server"	Network issue or wrong host	Verify host and network connectivity

PostgreSQL Client Tools

The container includes additional tools:

pg_dump (Backup)

docker run --rm -e PGPASSWORD=secret postgres:16-alpine pg_dump -h localhost -U postgres mydb > backup.sql

pg_restore (Restore)

docker run --rm -e PGPASSWORD=secret -i postgres:16-alpine pg_restore -h localhost -U postgres -d mydb < backup.dump

createdb / dropdb

# Create database
docker run --rm -e PGPASSWORD=secret postgres:16-alpine createdb -h localhost -U postgres newdb

# Drop database
docker run --rm -e PGPASSWORD=secret postgres:16-alpine dropdb -h localhost -U postgres olddb

Docker Image Details

Image: postgres:16-alpine
Base: Alpine Linux
Size: ~80MB
PostgreSQL Version: 16.x
Included Tools: psql, pg_dump, pg_restore, createdb, dropdb, pg_isready
Platforms: linux/amd64, linux/arm64

Resources

Quick Reference

Essential Commands Table

Operation	Command
Connect	`-h host -U user -d database`
Run query	`-c "SELECT * FROM table"`
Run script	`-f script.sql`
List databases	`-l`
List tables	`-c "\dt"`
Describe table	`-c "\d tablename"`
Export CSV	`-c "COPY table TO STDOUT CSV HEADER" > file.csv`
Import CSV	`-c "COPY table FROM STDIN CSV HEADER" < file.csv`
Backup database	`pg_dump -h host -U user db > backup.sql`
Restore database	`-f backup.sql`

Converted and distributed by TomeVault — claim your Tome and manage your conversions.

Postgres

File contents

PostgreSQL Skill

Overview

When to Use This Skill

Prerequisites

Docker

PostgreSQL Server

Configuration

Configuration Explained

Usage

Connection Methods

Using Command-Line Arguments

Using Environment Variables

Using Connection URI

Common Operations

Running Queries

Database Management

Table Operations

Schema Operations

Executing SQL Files

Data Export

Data Import

User Management

Indexes & Performance

Meta-Commands

Information Commands

Output Formatting

Advanced Examples

Database Backup

Database Restore

Batch Operations

Connection Testing

Query Monitoring

Environment Variables

PostgreSQL Standard Variables

Usage

Security Best Practices

Password Management

SSL Connections

Read-Only Access

Troubleshooting

Connection Refused

Authentication Failed

Permission Denied

Cannot Connect to Docker Network

Common Errors

PostgreSQL Client Tools

pg_dump (Backup)

pg_restore (Restore)

createdb / dropdb

Docker Image Details

Resources

Quick Reference

Essential Commands Table

Related skills

Frequently asked questions

How do I install the Postgres skill?

What does the Postgres skill do?

Is Postgres safe to use?

Which AI agents work with Postgres?

Is Postgres free to use?

Who published Postgres?