PgHero
Postgres insights made easy
Supports PostgreSQL 9.2+
:speech_balloon: Get handcrafted updates for new features
For pure SQL, check out PgHero.sql and for a standalone app, check out PgHero Solo
A big thanks to Craig Kerstiens and Heroku for the initial queries :clap:
Installation
Add this line to your application’s Gemfile:
gem 'pghero'
And mount the dashboard in your config/routes.rb
:
mount PgHero::Engine, at: "pghero"
Be sure to secure the dashboard in production.
Insights
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
PgHero.total_connections
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
Users
Create a user
PgHero.create_user("link")
# {password: "zbTrNHk2tvMgNabFgCo0ws7T"}
This generates and returns a secure password. The user has full access to the public
schema.
Read-only access
PgHero.create_user("epona", readonly: true)
Set the password
PgHero.create_user("zelda", password: "hyrule")
Drop a user
PgHero.drop_user("ganondorf")
Security
Basic Authentication
Set the following variables in your environment or an initializer.
ENV["PGHERO_USERNAME"] = "andrew"
ENV["PGHERO_PASSWORD"] = "secret"
Devise
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.
Installation
If you have trouble enabling query stats from the dashboard, try doing it manually.
Add the following to your postgresql.conf
:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_activity_query_size = 2048
Then restart PostgreSQL. As a superuser from the psql
console, run:
CREATE extension pg_stat_statements;
Amazon RDS
Change shared_preload_libraries
to pg_stat_statements
in your Parameter Group and restart the database instance.
As a superuser from the psql
console, run:
CREATE extension pg_stat_statements;
Common Issues
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:
CREATE extension pg_stat_statements;
and reset stats with:
SELECT pg_stat_statements_reset();
Queries show up as <insufficient privilege>
For security reasons, only superusers can see queries executed by other users.
System Stats
CPU usage is available for Amazon RDS. Add these lines to your application’s Gemfile:
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
Customize
Minimum time for long running queries
PgHero.long_running_query_sec = 60 # default
Minimum average time for slow queries
PgHero.slow_query_ms = 20 # default
Minimum calls for slow queries
PgHero.slow_query_calls = 100 # default
Minimum connections for high connections warning
PgHero.total_connections_threshold = 100 # default
Bonus Projects
- See where queries come from with Marginalia - comments appear on the Live Queries tab.
- Get weekly news and articles with Postgres Weekly
- Optimize your configuration with PgTune and pgBench
TODO
- suggest indexes
- suggest paramater values
- better 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