Installation Guide¶

-- Connect as superuser
psql -U postgres

-- Enable extension
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_insights';

-- Restart PostgreSQL (required)
-- Ubuntu/Debian:
-- sudo systemctl restart postgresql
-- RHEL/Rocky:
-- sudo systemctl restart postgresql-17
-- macOS:
-- brew services restart postgresql@17

-- Connect to your database
psql -U postgres -d your_database

-- Create extension
CREATE EXTENSION pg_stat_insights;

-- Verify installation
SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_insights';

-- Check extension is loaded
SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_stat_insights';

-- Verify functions exist
SELECT proname FROM pg_proc WHERE proname LIKE 'pg_stat_insights%' ORDER BY proname;

-- Verify views exist
SELECT viewname FROM pg_views WHERE viewname LIKE 'pg_stat_insights%' ORDER BY viewname;

-- Test basic query
SELECT COUNT(*) FROM pg_stat_insights;

-- Enable all tracking features
ALTER SYSTEM SET pg_stat_insights.max_queries = 10000;
ALTER SYSTEM SET pg_stat_insights.track_utility = on;
ALTER SYSTEM SET pg_stat_insights.track_planning = on;
ALTER SYSTEM SET pg_stat_insights.track_wal = on;
ALTER SYSTEM SET pg_stat_insights.track_jit = on;
ALTER SYSTEM SET pg_stat_insights.track_io_timing = on;
ALTER SYSTEM SET pg_stat_insights.track_parallel_queries = on;
ALTER SYSTEM SET pg_stat_insights.track_minmax_time = on;

-- Reload configuration (no restart needed for these)
SELECT pg_reload_conf();

-- Check current setting
SHOW pg_stat_insights.max_queries;

-- Adjust if needed
ALTER SYSTEM SET pg_stat_insights.max_queries = 10000;

-- Restart required for max_queries change

-- 1. Drop pg_stat_statements
DROP EXTENSION IF EXISTS pg_stat_statements CASCADE;

-- 2. Remove from shared_preload_libraries
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_insights';

-- 3. Restart PostgreSQL
-- sudo systemctl restart postgresql

-- 4. Create pg_stat_insights
CREATE EXTENSION pg_stat_insights;

-- 5. Verify (same views available)
SELECT * FROM pg_stat_insights LIMIT 10;

-- Check current version
SELECT extversion FROM pg_extension WHERE extname = 'pg_stat_insights';

-- Upgrade extension
ALTER EXTENSION pg_stat_insights UPDATE TO '1.1';

-- Verify new version
SELECT extversion FROM pg_extension WHERE extname = 'pg_stat_insights';

-- 1. Drop extension from all databases
DROP EXTENSION IF EXISTS pg_stat_insights CASCADE;

-- 2. Remove from postgresql.conf
ALTER SYSTEM SET shared_preload_libraries = '';

-- 3. Restart PostgreSQL
-- sudo systemctl restart postgresql

-- 4. Remove files (optional)
-- sudo rm -f $(pg_config --sharedir)/extension/pg_stat_insights*
-- sudo rm -f $(pg_config --pkglibdir)/pg_stat_insights.*

-- Extension status
SELECT 
  extname,
  extversion,
  extrelocatable
FROM pg_extension 
WHERE extname = 'pg_stat_insights';

-- Function count (should be 3)
SELECT COUNT(*) AS function_count
FROM pg_proc 
WHERE proname LIKE 'pg_stat_insights%';

-- View count (should be 11)
SELECT COUNT(*) AS view_count
FROM pg_views 
WHERE viewname LIKE 'pg_stat_insights%';

-- Test data collection
SELECT pg_sleep(1);
SELECT COUNT(*) AS tracked_queries FROM pg_stat_insights;

-- Test reset function
SELECT pg_stat_insights_reset();

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_insights';
-- Restart PostgreSQL

-- Reduce max_queries
ALTER SYSTEM SET pg_stat_insights.max_queries = 5000;

-- Or increase shared memory
ALTER SYSTEM SET shared_buffers = '256MB';

-- Restart required