pgmonkey (6/8) — The CLI Toolbox

Summary: pgmonkey’s CLI goes well beyond basic connectivity — it generates config files, tests connections across all four types, writes working Python code, recommends server settings, and audits a live PostgreSQL server against those recommendations.

0. Prerequisites

• Completed Part 1 (pgmonkey installed, config file working)
• A running PostgreSQL server for the test, audit, and server config commands

1. Generate a Configuration Template

The pgconfig create command writes a fully commented YAML template.

pgmonkey pgconfig create --type pg --connconfig pgmonkey_config.yaml

This creates pgmonkey_config.yaml in the current directory with every setting pgmonkey supports, default values filled in, and inline comments explaining each option. It is the fastest way to start a new project.

Tip: If the file already exists, the command overwrites it. Back up your config before regenerating.

2. Test a Connection

The pgconfig test command validates that your config actually connects.

pgmonkey pgconfig test --connconfig pgmonkey_config.yaml

Testing connection...
Connection successful!

Override the connection type to test a specific mode.

pgmonkey pgconfig test --connconfig pgmonkey_config.yaml --connection-type pool

pgmonkey pgconfig test --connconfig pgmonkey_config.yaml --connection-type async

pgmonkey pgconfig test --connconfig pgmonkey_config.yaml --connection-type async_pool

Test with environment variable interpolation when your config uses ${VAR} references (covered in Part 5).

pgmonkey pgconfig test --connconfig pgmonkey_config.yaml --resolve-env

pgmonkey pgconfig test --connconfig pgmonkey_config.yaml --resolve-env --allow-sensitive-defaults

Note: The test command creates a real connection, runs a quick validation, and disconnects. It does not modify your database.

3. Generate Python Code — pgmonkey Library

Point the CLI at your config file and a connection type, and it generates a working Python script.

pgmonkey pgconfig generate-code --connconfig pgmonkey_config.yaml --connection-type normal --library pgmonkey

Generated normal synchronous connection code using pgmonkey:
from pgmonkey import PGConnectionManager
def main():
    connection_manager = PGConnectionManager()
    config_file_path = 'pgmonkey_config.yaml'
    connection = connection_manager.get_database_connection(config_file_path, 'normal')
    with connection as conn:
        with conn.cursor() as cur:
            cur.execute('SELECT 1;')
            print(cur.fetchone())
if __name__ == "__main__":
    main()

Generate code for all four types.

pgmonkey pgconfig generate-code --connconfig pgmonkey_config.yaml --connection-type pool --library pgmonkey

pgmonkey pgconfig generate-code --connconfig pgmonkey_config.yaml --connection-type async --library pgmonkey

pgmonkey pgconfig generate-code --connconfig pgmonkey_config.yaml --connection-type async_pool --library pgmonkey

Each generated script is a complete, runnable example that reads your config file and executes a query. Copy it into your project as a starting point.

4. Generate Python Code — Native psycopg

If you want to see the equivalent code using psycopg and psycopg_pool directly (without pgmonkey’s API), use --library psycopg.

pgmonkey pgconfig generate-code --connconfig pgmonkey_config.yaml --connection-type normal --library psycopg

Generated normal synchronous connection code using psycopg:
import yaml
import psycopg
def main():
    config_file_path = 'pgmonkey_config.yaml'
    with open(config_file_path, 'r') as f:
        config = yaml.safe_load(f)
    conn_settings = config['connection_settings']
    conn_params = {k: v for k, v in conn_settings.items() if v is not None}
    with psycopg.connect(**conn_params) as conn:
        with conn.cursor() as cur:
            cur.execute('SELECT 1;')
            print(cur.fetchone())
if __name__ == "__main__":
    main()

This is useful for understanding what pgmonkey does behind the scenes, or for teams evaluating whether to adopt pgmonkey or stick with native psycopg.

Tip: The native psycopg code for pooled and async types is significantly more verbose — it handles pool creation, GUC settings, configure callbacks, and cleanup manually. Comparing the two outputs shows the boilerplate pgmonkey eliminates.

5. Server Configuration Recommendations

The pgserverconfig command reads your client config and generates matching server-side settings.

pgmonkey pgserverconfig --connconfig pgmonkey_config.yaml

This outputs recommended entries for two PostgreSQL server files:

pg_hba.conf — host-based authentication rules. pgmonkey generates the right rule type (host vs hostssl) based on your sslmode, and adds clientcert options when your config includes client certificates.

postgresql.conf — server parameters. pgmonkey calculates max_connections based on your pool max_size (plus a 10% buffer), and adds SSL settings when your config requires encryption.

Note: This command generates recommendations — it does not modify your server. You review the output and apply the changes yourself.

6. Live Server Audit

Add --audit to compare your server’s current settings against the recommendations.

pgmonkey pgserverconfig --connconfig pgmonkey_config.yaml --audit

The audit connects to your database (read-only), queries pg_settings, and compares the live values against what pgmonkey recommends. It reports matches, mismatches, and missing settings.

This catches configuration drift — situations where the client config expects one thing (like SSL enabled) but the server is configured differently. Finding these mismatches before deployment is far better than debugging them at 2 AM.

Warning: The audit requires SELECT access to pg_settings. Most PostgreSQL users have this by default, but heavily locked-down servers may restrict it. If the audit cannot query a setting, it falls back to showing the recommendation without a comparison.

7. CSV Import and Export

The CLI includes data import/export commands. These are covered in detail in Part 7, but here is the quick syntax.

Export a table to CSV:

pgmonkey pgexport --table public.customers --connconfig pgmonkey_config.yaml --export_file customers.csv

Import a CSV into a table:

pgmonkey pgimport --table public.customers --connconfig pgmonkey_config.yaml --import_file customers.csv

Both commands use PostgreSQL’s COPY protocol for fast bulk transfers.

8. CLI Quick Reference

Summary

You explored every pgmonkey CLI command.

• pgconfig create generates a fully commented YAML template in seconds
• pgconfig test validates connections across all four types, with optional env var resolution
• pgconfig generate-code writes working Python code for both pgmonkey and native psycopg
• pgserverconfig recommends server-side settings that match your client config
• pgserverconfig --audit compares a live server against those recommendations
• pgimport and pgexport move CSV data in and out of PostgreSQL tables

Next up: pgmonkey (7/8) — CSV Import and Export dives into the data import/export commands in detail — auto-detection, custom configuration, encoding handling, and roundtrip workflows.