PgHero
Postgres insights made easy
Supports PostgreSQL 9.2+
For pure SQL, check out PgHero.sql
A big thanks to Craig Kerstiens and Heroku for the initial queries :clap:
Installation
Add this line to your application’s Gemfile:
“by gem ‘pghero’
“
And mount the dashboard in your config/routes.rb
:
“by mount PgHero::Engine, at: “pghero”
“
Be sure to secure the dashboard in production.
Insights
“by PgHero.running_queries PgHero.long_running_queries PgHero.index_usage PgHero.missing_indexes PgHero.unused_indexes PgHero.unused_tables PgHero.database_size PgHero.relation_sizes PgHero.index_hit_rate PgHero.table_hit_rate
kill queries
PgHero.kill(pid) PgHero.kill_all
query stats
PgHero.query_stats_enabled? PgHero.enable_query_stats PgHero.disable_query_stats PgHero.reset_query_stats PgHero.query_stats PgHero.slow_queries
“
Security
Basic Authentication
Set the following variables in your environment or an initializer.
“by ENV[PGHERO_USERNAME] = “andrew” ENV[PGHERO_PASSWORD] = “secret”
“
Devise
“by authenticate :user, lambda {|user| user.admin? } do mount PgHero::Engine, at: “pghero” end
“
Query Stats
The pg_stat_statements module is used for query stats.
Common Issues
Installation
If you have trouble enabling query stats from the dashboard, try doing it manually.
Add the following to your postgresql.conf
:
“nf shared_preload_libraries = ‘pg_stat_statements’ pg_stat_statements.track = all
“
Then restart PostgreSQL. As a superuser from the psql
console, run:
“ql CREATE extension pg_stat_statements;
“
Note: Query stats are not available on Amazon RDS. Tell Amazon you want this.
pg_stat_statements must be loaded via shared_preload_libraries
Follow the instructions above.
FATAL: could not access file “pg_stat_statements”: No such file or directory
Run apt-get install postgresql-contrib-9.3
and follow the instructions above.
The database user does not have permission to …
The database user is not a superuser. You can manually enable stats from the psql
console with:
“ql CREATE extension pg_stat_statements;
“
and reset stats with:
“ql SELECT pg_stat_statements_reset();
“
System Stats
System stats are available for Amazon RDS. Add these lines to your application’s Gemfile:
“by gem ‘aws-sdk’ gem ‘chartkick’
“
And add these variables to your environment:
“ PGHERO_ACCESS_KEY_ID=accesskey123 PGHERO_SECRET_ACCESS_KEY=secret123 PGHERO_DB_INSTANCE_IDENTIFIER=datakick-production
“
TODO
- show exactly which indexes to add
- more detailed explanations on dashboard
Know a bit about PostgreSQL? Suggestions are greatly appreciated.
Thanks
Thanks to Craig Kerstiens and Heroku for the initial queries and Bootswatch for the theme.
History
View the changelog
Contributing
Everyone is encouraged to help improve this project. Here are a few ways you can help:
- Report bugs
- Fix bugs and submit pull requests
- Write, clarify, or fix documentation
- Suggest or add new features