Skip to main content

​
PostgreSQL

Connect your PostgreSQL databases to enable Tony (Database Engineer) to analyze queries, optimize performance, and monitor database health.

​
Supported Platforms

PlatformSupport
Self-hosted PostgreSQL12.x, 13.x, 14.x, 15.x, 16.x
AWS RDS PostgreSQLAll versions
AWS Aurora PostgreSQLAll versions
Google Cloud SQLAll PostgreSQL versions
Azure Database for PostgreSQLFlexible Server, Single Server

​
Setup

1

Connect as Admin

Connect to your PostgreSQL instance using an admin account:
psql -h your-host -U postgres -d your-database
2

Create Read-Only User

Create a dedicated user for CloudThinker:
CREATE USER cloudthinker_readonly WITH PASSWORD 'your-secure-password';
3

Grant Connection

Allow connection to the database:
GRANT CONNECT ON DATABASE your_database TO cloudthinker_readonly;
4

Grant Schema Usage

Grant usage on schemas:
GRANT USAGE ON SCHEMA public TO cloudthinker_readonly;
GRANT USAGE ON SCHEMA information_schema TO cloudthinker_readonly;
5

Grant Select Permissions

Grant SELECT on all tables and views:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO cloudthinker_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO cloudthinker_readonly;

-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO cloudthinker_readonly;
6

Enable pg_stat_statements

This extension is required for query analysis:
-- Enable extension (requires superuser)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Grant access to the user
GRANT SELECT ON pg_stat_statements TO cloudthinker_readonly;
For RDS/Aurora, add to parameter group:
shared_preload_libraries = 'pg_stat_statements'
7

Configure Network Access

Ensure CloudThinker can reach your database:
  • Add CloudThinker IPs to security group / firewall
  • For RDS: Enable public access or use VPC peering
8

Add Connection in CloudThinker

Navigate to Connections → PostgreSQL and enter:
  • Host
  • Port (default: 5432)
  • Database name
  • Username: cloudthinker_readonly
  • Password
  • SSL mode (recommended: require)

​
Connection String Format

postgresql://cloudthinker_readonly:your-secure-password@your-host:5432/your-database
Example: 
postgresql://cloudthinker_readonly:[email protected]:5432/production

​
Required Permissions

​
Minimum

GRANT CONNECT ON DATABASE dbname TO cloudthinker_readonly;
GRANT USAGE ON SCHEMA public TO cloudthinker_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO cloudthinker_readonly;

-- All of the above, plus:
GRANT SELECT ON pg_stat_statements TO cloudthinker_readonly;
GRANT SELECT ON pg_stat_activity TO cloudthinker_readonly;
GRANT SELECT ON pg_stat_user_tables TO cloudthinker_readonly;
GRANT SELECT ON pg_stat_user_indexes TO cloudthinker_readonly;
GRANT SELECT ON pg_statio_user_tables TO cloudthinker_readonly;
GRANT pg_read_all_stats TO cloudthinker_readonly;  -- PostgreSQL 14+

​
Agent Capabilities

Once connected, Tony can:
CapabilityDescription
Query AnalysisIdentify slow queries, analyze execution plans
Index RecommendationsFind missing indexes, identify unused indexes
Performance MetricsMonitor connections, I/O, cache hit rates
Table StatisticsAnalyze table bloat, dead tuples, vacuum status
Replication MonitoringCheck lag, streaming status (if replica)

​
Example Prompts

@tony analyze slow queries on production PostgreSQL
@tony #dashboard database performance metrics
@tony recommend index optimizations for the orders table
@tony check replication lag on the read replica

​
Connection Options

OptionDescriptionDefault
SSL Modedisable, allow, prefer, require, verify-ca, verify-fullrequire
Connection TimeoutSeconds to wait for connection10
Statement TimeoutMax query execution time (ms)30000

​
Troubleshooting

  • Verify host and port are correct
  • Check security group / firewall allows CloudThinker IPs
  • For RDS: Ensure “Publicly accessible” is enabled or use VPC peering
  • Confirm PostgreSQL is listening on the correct interface
  • Verify username and password are correct
  • Check pg_hba.conf allows the connection method
  • Ensure user has CONNECT privilege on the database
  • Verify extension is installed: SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';
  • Check shared_preload_libraries includes pg_stat_statements
  • Restart PostgreSQL after changing shared_preload_libraries
  • For RDS: Modify parameter group and reboot
  • Verify user has SELECT on required tables
  • Grant pg_read_all_stats role for PostgreSQL 14+
  • Check schema permissions with \dn+ in psql

​
Security Best Practices

  • Strong passwords - Use complex, unique passwords
  • SSL encryption - Always use SSL mode require or higher
  • Network restrictions - Limit access to CloudThinker IPs only
  • Minimal permissions - Grant only SELECT, never write access
  • Credential rotation - Rotate passwords every 90 days

Tony Agent

Database-focused optimization agent

MySQL Connection

Similar setup for MySQL databases