pgplex Validate with Database
Connect to the test PostgreSQL database to validate assumptions, compare implementations, and test queries.
Connection
Default test credentials: password testpwd1, user postgres, host localhost, port 5432. Also see .env at project root.
# One-off query
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d employee -c "SELECT version();"
# pg_dump schema
PGPASSWORD='testpwd1' pg_dump -h localhost -p 5432 -U postgres -d employee --schema-only --schema=public
# pgschema dump (reads .env automatically)
./pgschema dump --schema public
Core Workflows
Compare pg_dump vs pgschema
# 1. Dump both
PGPASSWORD='testpwd1' pg_dump -h localhost -p 5432 -U postgres -d employee --schema-only --schema=public > /tmp/pg_dump_output.sql
./pgschema dump --schema public -o /tmp/pgschema_output.sql
# 2. Compare
diff -u /tmp/pg_dump_output.sql /tmp/pgschema_output.sql
Look for: missing objects (bugs), different DDL structure (investigate), formatting differences (expected).
Validate System Catalog Queries
- Create test objects in a test database
- Query system catalogs to verify expected data
- Compare with pgschema output
- Clean up test objects
Test Plan/Apply Workflow
# 1. Create test schema
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "
DROP SCHEMA IF EXISTS test_workflow CASCADE;
CREATE SCHEMA test_workflow;
CREATE TABLE test_workflow.users (id SERIAL PRIMARY KEY, email TEXT NOT NULL UNIQUE);
"
# 2. Dump, modify, plan, apply
./pgschema dump --schema test_workflow -o /tmp/schema.sql
# Edit /tmp/schema.sql with desired changes
./pgschema plan --schema test_workflow --file /tmp/schema.sql
./pgschema apply --schema test_workflow --file /tmp/schema.sql --auto-approve
# 3. Verify and cleanup
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "\d test_workflow.users"
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "DROP SCHEMA IF EXISTS test_workflow CASCADE;"
Cross-Version Testing
PGSCHEMA_POSTGRES_VERSION=14 go test -v ./cmd/dump -run TestDumpCommand_Employee
PGSCHEMA_POSTGRES_VERSION=17 go test -v ./cmd/dump -run TestDumpCommand_Employee
Supported versions: 14, 15, 16, 17, 18.
Useful System Catalog Queries
-- Columns with types
SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as data_type,
a.attnotnull, pg_get_expr(ad.adbin, ad.adrelid) as default_value, a.attgenerated
FROM pg_attribute a
LEFT JOIN pg_attrdef ad ON (a.attrelid = ad.adrelid AND a.attnum = ad.adnum)
WHERE a.attrelid = 'public.TABLE_NAME'::regclass AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
-- Constraints
SELECT con.conname, con.contype, pg_get_constraintdef(con.oid)
FROM pg_constraint con WHERE con.conrelid = 'public.TABLE_NAME'::regclass;
-- Indexes
SELECT i.relname, am.amname, pg_get_indexdef(idx.indexrelid),
CASE WHEN idx.indpred IS NOT NULL THEN pg_get_expr(idx.indpred, idx.indrelid, true) END as where_clause
FROM pg_index idx
JOIN pg_class i ON i.oid = idx.indexrelid
JOIN pg_class t ON t.oid = idx.indrelid
JOIN pg_am am ON i.relam = am.oid
WHERE t.relname = 'TABLE_NAME' AND t.relnamespace = 'public'::regnamespace;
-- Triggers
SELECT t.tgname, pg_get_triggerdef(t.oid),
CASE WHEN t.tgqual IS NOT NULL THEN pg_get_expr(t.tgqual, t.tgrelid, false) END as when_condition
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
WHERE c.relname = 'TABLE_NAME' AND c.relnamespace = 'public'::regnamespace AND NOT t.tgisinternal;
-- Comments on tables
SELECT c.relname, d.description FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = 0
WHERE n.nspname = 'public' AND c.relkind = 'r';
Checklist
- Database is running and accessible
- pg_dump produces expected output
- pgschema produces comparable output
- System catalog queries return expected data
- Plan generates correct migration DDL
- Tested across PostgreSQL versions if version-specific
- Test database cleaned up