ankane / pghero

A performance dashboard for Postgres

PgHero

A performance dashboard for Postgres

See it in action

🍊 Battle-tested at Instacart

Documentation

PgHero is available as a Docker image, Linux package, and Rails engine.

Select your preferred method of installation to get started.

• Docker
• Linux
• Rails

Related Projects

• Dexter - The automatic indexer for Postgres
• PgBouncerHero - A dashboard for PgBouncer
• pgsync - Sync Postgres data between databases
• pgslice - Postgres partitioning as easy as pie

Credits

A big 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

pghero's Issues

relation "pg_hero_connections" does not exist

> ActiveRecord::Base.descendants
PG::UndefinedTable: ERROR:  relation "pg_hero_connections" does not exist
LINE 5:                WHERE a.attrelid = '"pg_hero_connections"'::r...
                                          ^
:               SELECT a.attname, format_type(a.atttypid, a.atttypmod),
                     pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
                FROM pg_attribute a LEFT JOIN pg_attrdef d
                  ON a.attrelid = d.adrelid AND a.attnum = d.adnum
               WHERE a.attrelid = '"pg_hero_connections"'::regclass
                 AND a.attnum > 0 AND NOT a.attisdropped
               ORDER BY a.attnum

(pry) output error: #<ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "pg_hero_connections" does not exist
LINE 5:                WHERE a.attrelid = '"pg_hero_connections"'::r...
                                          ^
:               SELECT a.attname, format_type(a.atttypid, a.atttypmod),
                     pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
                FROM pg_attribute a LEFT JOIN pg_attrdef d
                  ON a.attrelid = d.adrelid AND a.attnum = d.adnum
               WHERE a.attrelid = '"pg_hero_connections"'::regclass
                 AND a.attnum > 0 AND NOT a.attisdropped
               ORDER BY a.attnum

> PgHero::Connection
PG::UndefinedTable: ERROR:  relation "pg_hero_connections" does not exist
LINE 5:                WHERE a.attrelid = '"pg_hero_connections"'::r...
                                          ^
:               SELECT a.attname, format_type(a.atttypid, a.atttypmod),
                     pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
                FROM pg_attribute a LEFT JOIN pg_attrdef d
                  ON a.attrelid = d.adrelid AND a.attnum = d.adnum
               WHERE a.attrelid = '"pg_hero_connections"'::regclass
                 AND a.attnum > 0 AND NOT a.attisdropped
               ORDER BY a.attnum

(pry) output error: #<ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "pg_hero_connections" does not exist
LINE 5:                WHERE a.attrelid = '"pg_hero_connections"'::r...
                                          ^
:               SELECT a.attname, format_type(a.atttypid, a.atttypmod),
                     pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
                FROM pg_attribute a LEFT JOIN pg_attrdef d
                  ON a.attrelid = d.adrelid AND a.attnum = d.adnum
               WHERE a.attrelid = '"pg_hero_connections"'::regclass
                 AND a.attnum > 0 AND NOT a.attisdropped
               ORDER BY a.attnum
>

Happens in dev and prod environment. Version is 0.1.7.

500 on pghero dashboard when query stats enabled

After running

PgHero.disable_query_stats

the dashboard works okey

After running

PgHero.enable_query_stats 

the dasboard give the following error

NoMethodError: undefined method `[]' for nil:NilClass
- 157 non-project frames 
1
File "/app/vendor/bundle/ruby/2.1.0/gems/pghero-1.2.4/lib/pghero/methods/suggested_indexes.rb" line 121 in block (2 levels) in best_index_helper
2
File "/app/vendor/bundle/ruby/2.1.0/gems/pghero-1.2.4/lib/pghero/methods/suggested_indexes.rb" line 121 in map
3
File "/app/vendor/bundle/ruby/2.1.0/gems/pghero-1.2.4/lib/pghero/methods/suggested_indexes.rb" line 121 in block in best_index_helper
4
File "/app/vendor/bundle/ruby/2.1.0/gems/pghero-1.2.4/lib/pghero/methods/suggested_indexes.rb" line 103 in each
5
File "/app/vendor/bundle/ruby/2.1.0/gems/pghero-1.2.4/lib/pghero/methods/suggested_indexes.rb" line 103 in best_index_helper
6
File "/app/vendor/bundle/ruby/2.1.0/gems/pghero-1.2.4/lib/pghero/methods/suggested_indexes.rb" line 17 in suggested_indexes_by_query

It's important to says it's a production environment of heroku. Using a postgresql version 9.3

Support for multiple databases

Supports Postgres Query Plan Visualization

Postgres Query Plan Visualization
https://github.com/AlexTatiyants/pev
http://tatiyants.com/postgres-query-plan-visualization/
It's awesome.

Query stats are available but not enabled

in postgresql.conf

shared_preload_libraries = 'pg_stat_statements'     # (change requires restart)

CREATE extension pg_stat_statements;
ERROR:  function "pg_stat_statements_reset" already exists with same argument types

Still see "Query stats are available but not enabled" in pghero.

psql (9.2.9)
pghero (0.1.3)

Undo enabling query stats

I tried to enable query stats from the dashboard, but after clicking the button got a 500 server error. I checked the logs and saw ERROR: pg_stat_statements must be loaded via shared_preload_libraries. I decided I don't want to troubleshoot the issue as outlined in the help guide and would rather just turn off query stats.

The problem is I have no idea how to do this. The server replies with a 500 internal server error. I can't access the dashboard at all. I tried uninstalling and re-installing pghero to no avail. Where's the undo button?

The frontpage should explain what pghero is all about

I a missing a brief introduction to pghero. What is it all about?

Please update the frontpage: https://github.com/ankane/pghero

Cleanup of the historical query table

Build a mechanism for the pghero_query_stats table to be cleaned up such that records older than a week get purged as new records are imported by the pghero:capture_query_stats or provide a pghero:cleanup_query_stats kind of function to do it.

Setup with RDS: Error saving: Cannot modify a default parameter group. (Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterValue

When I try setting the value of shared_preload_libraries to pg_stat_statements in our Parameter Group, I get:

Error saving: Cannot modify a default parameter group. (Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterValue; Request ID: ff5b3371-26b2-11e6-8709-e93cdd73ea37)

Metrics over time

Chart query stats and other important metrics over time

[Bug] Getting error "wrong constant name" in development

This bug sometimes happens after I visit pghero page in development
Restarting the app seems to solve it

I want to know why and see if anything can be done to solve it permanently

Not everything filters on database

The Queries section filters on DB with the WHERE below with but other things like long running queries on the main page do not. We run a large shared instance where we'd like to prevent individual DBs (one allocated to each service/team) from seeing/killing the queries of other DBs.

 INNER JOIN
              pg_database ON pg_database.oid = pg_stat_statements.dbid
            WHERE
              pg_database.datname = current_database()

Add Travis CI Builds

Travis will run test for you and give you super awesome badges to stick on the README.md. Also gives you confidence when merging PRs that all tests are passing without having to clone down locally. Would be a good improvement to have.

Fail to start if password contain @

[25722] ! Unable to load application: URI::InvalidURIError: the scheme postgres does not accept registry part: postgres:H!HxDu3dg07@[email protected]:5432 (or bad hostname?)
/opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/uri/generic.rb:214:in `initialize': the scheme postgres does not accept registry part: postgres:F!#D3vds056@3%[email protected]:5432 (or bad hostname?) (URI::InvalidURIError)
        from /opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/uri/common.rb:218:in `new'
        from /opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/uri/common.rb:218:in `parse'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/connection_specification.rb:36:in `initialize'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/connection_specification.rb:270:in `new'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/connection_specification.rb:270:in `resolve_url_connection'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/connection_specification.rb:258:in `resolve_hash_connection'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/connection_specification.rb:215:in `resolve_connection'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/connection_specification.rb:139:in `resolve'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/connection_specification.rb:151:in `block in resolve_all'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/connection_specification.rb:150:in `each'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/connection_specification.rb:150:in `resolve_all'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_handling.rb:69:in `resolve'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/core.rb:46:in `configurations='
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/core.rb:48:in `block in <module:Core>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activesupport-4.2.3/lib/active_support/concern.rb:120:in `class_eval'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activesupport-4.2.3/lib/active_support/concern.rb:120:in `append_features'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/base.rb:284:in `include'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/base.rb:284:in `<class:Base>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/base.rb:269:in `<module:ActiveRecord>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/base.rb:26:in `<top (required)>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/pghero-1.1.0/lib/pghero.rb:8:in `<module:PgHero>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/pghero-1.1.0/lib/pghero.rb:6:in `<top (required)>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/bundler-1.7.12/lib/bundler/runtime.rb:76:in `require'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/bundler-1.7.12/lib/bundler/runtime.rb:76:in `block (2 levels) in require'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/bundler-1.7.12/lib/bundler/runtime.rb:72:in `each'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/bundler-1.7.12/lib/bundler/runtime.rb:72:in `block in require'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/bundler-1.7.12/lib/bundler/runtime.rb:61:in `each'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/bundler-1.7.12/lib/bundler/runtime.rb:61:in `require'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/bundler-1.7.12/lib/bundler.rb:134:in `require'
        from /opt/pghero/config/application.rb:2:in `<top (required)>'
        from /opt/pghero/config/environment.rb:2:in `require'
        from /opt/pghero/config/environment.rb:2:in `<top (required)>'
        from config.ru:3:in `require'
        from config.ru:3:in `block in <main>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:55:in `instance_eval'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:55:in `initialize'
        from config.ru:in `new'
        from config.ru:in `<main>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:49:in `eval'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:49:in `new_from_string'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:40:in `parse_file'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/configuration.rb:102:in `load_rackup'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/configuration.rb:69:in `app'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/runner.rb:113:in `load_and_bind'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/cluster.rb:302:in `run'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/cli.rb:216:in `run'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/bin/puma:10:in `<top (required)>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/bin/puma:23:in `load'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/bin/puma:23:in `<main>'

Wrong suggested index

Hi there,

I've installed pghero into my DB server, to get some additional information about it.

Pghero is showing me that I should create an index, that already exists.

CREATE INDEX CONCURRENTLY ON ja_customers (clientid)

I've got this index in my DB:
CREATE INDEX ix_clientid_customers ON ja_customers ("clientid");

The query that is presented to me, is this one:
SELECT id, name_first, name_last, company FROM ja_customers WHERE clientid=? AND deleted = ? ORDER BY company, name_last, name_first ASC

If I run it using explain, I can see the query hitting the index.

Why is pghero showing me that index, if it already exists?

Cheers
Lucas

Historical Query Stats job fails to run

The following error is received when attempting to run the historical query stats scheduled task:

$ sudo pghero run pghero:capture_query_stats
sh: 1: exec: pghero:capture_query_stats: not found

The instructions to install PgHero on Linux (Ubuntu 14.04) were followed. It appears that PgHero us up and running correctly but the scheduled job produces the error both when run by cron and when run manually.

Allow historical query view go to back further than a day

It is clear from the growing pghero_query_stats DB that this can go back further than a day - please make the sliders go back further. Perhaps to a week?

Schema name for Index Usage page

Would be very useful to have schema shown for the Index Usage listing since table names can be duplicated across schemas.

Issue Running on Heroku

I get the following error when running on Heroku with gem version 0.1.3:

2014-08-05T19:52:57.111637+00:00 app[web.1]: App 132 stdout: Started GET "/pghero/" for XXX.XXX.XXX.XXXX at 2014-08-05 19:52:56 +0000
2014-08-05T19:52:57.111684+00:00 app[web.1]: App 132 stdout: 
2014-08-05T19:52:57.111689+00:00 app[web.1]: App 132 stdout: ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR:  column "pid" does not exist
2014-08-05T19:52:57.111691+00:00 app[web.1]: App 132 stdout: LINE 1: SELECT pid, state, application_name AS source, age(now(), xa...
2014-08-05T19:52:57.111695+00:00 app[web.1]: App 132 stdout:                ^
2014-08-05T19:52:57.111697+00:00 app[web.1]: App 132 stdout: : SELECT pid, state, application_name AS source, age(now(), xact_start) AS duration, waiting, query, xact_start AS started_at FROM pg_stat_activity WHERE query <> '<insufficient privilege>' AND state <> 'idle' AND pid <> pg_backend_pid() AND now() - query_start > interval '5 minutes' ORDER BY query_start DESC):
2014-08-05T19:52:57.111736+00:00 app[web.1]: App 132 stdout:   app/middleware/force_login_page.rb:19:in `call'
2014-08-05T19:52:57.111738+00:00 app[web.1]: App 132 stdout:   app/middleware/force_ssl.rb:8:in `call'

If I do a select *, this is what I see:

{"datid"=>"16385", "datname"=>"db0jgg0q3e3c6s", "procpid"=>"293", "usesysid"=>"16386", "usename"=>"collectd", "application_name"=>"", "client_addr"=>nil, "client_hostname"=>nil, "client_port"=>nil, "backend_start"=>nil, "xact_start"=>nil, "query_start"=>nil, "waiting"=>nil, "current_query"=>"<insufficient privilege>"}

Could this be because Heroku hasn't enabled pg_stat_statements? Here is what I get when I run PgHero.enable_query_stats.

   (2.0ms)  CREATE EXTENSION pg_stat_statements
ActiveRecord::StatementInvalid: PG::InvalidParameterValue: ERROR:  extension "pg_stat_statements" is not whitelisted
DETAIL:  Installing the extension "pg_stat_statements" failed, because it is not on the whitelist of user-installable extensions.
HINT:  Your system administrator has allowed users to install certain extensions. See: SHOW extwlist.extensions;

Add invalid indexes

http://stackoverflow.com/questions/2102136/what-are-the-available-options-to-identify-and-remove-the-invalid-objects-in-pos

SELECT n.nspname, c.relname
FROM   pg_catalog.pg_class c, pg_catalog.pg_namespace n,
       pg_catalog.pg_index i
WHERE  (i.indisvalid = false OR i.indisready = false) AND
       i.indexrelid = c.oid AND c.relnamespace = n.oid AND
       n.nspname != 'pg_catalog' AND
       n.nspname != 'information_schema' AND
       n.nspname != 'pg_toast'

SQL auto-indent and syntax highlighting

It would be nice if SQL queries were automatically indented, and had SQL syntax highlighting applied, so they're easier to identify.

What it currently looks like: (note the first query is a raw string in our app, which is why it's indented)

I'd be happy to implement this, if you think it's a worthwhile change @ankane

insufficient privilege

Our single most expensive query shows up as <insufficient privilege>:

I was able to discover the query in question by manually running slow_queries as the postgres user. Turns out it's a query run by a different database user than the one hosting PGHero. From a non-superuser account you can see this by joining the pg_user table:

SELECT u.usename, (total_time / 1000 / 60) as total_minutes,
  (total_time/calls) as average_time, query 
FROM pg_stat_statements p 
JOIN pg_user u ON (u.usesysid = p.userid) ORDER BY 2 DESC LIMIT 1;
 usename | total_minutes |   average_time   |          query           
---------+---------------+------------------+--------------------------
 foo     |   199.0077556 | 333.073461212308 | <insufficient privilege>
 bar     |   170.0383281 | 165.831729885244 | SELECT ...;
(1 row)

Where bar is the current account in use, and foo is the other account.

Assuming there's no way to solve this without running PGHero with superuser permissions, it might be good to document the error in the README / inline on the web page.

Add dependency to actionpack to gemspec

Segmentation fault under postgres 9.5

First off, thank you for an awesome product! It's hard to imagine going back to a world before...

On to the unfortunate issue:

Previous State:

pghero setup on trusty 14.04 with apt.postgresql.org postgresql-9.3; no issue running for over a week.

Upgrade:

Upgraded from 9.3 to 9.5-alpha1 via apt.postgresql.org apt and pg_dump/pg_restore. All other application behavior is as expected without error.

Segmentation fault and log

However, a segmentation fault occurs on the following query issued by pghero. Seems to be related to clicking the "Queries" tab in pghero. Cannot provide the core dump due to proprietary information.

This seems like misbehavior from postgres per se, but since it affects pghero and you are likely more capable of understanding and reproducing with shareable core dump for core development, I am filing here to start.

2015-07-06 00:37:52 UTC [24260-2] LOG:  server process (PID 10695) was terminated by signal 11: Segmentation fault
2015-07-06 00:37:52 UTC [24260-3] DETAIL:  Failed process was running: SELECT pid, state, application_name AS source, age(now(), xac
t_start) AS duration, waiting, query, xact_start AS started_at FROM pg_stat_activity WHERE query <> '<insufficient privilege>' AND s
tate <> 'idle' AND pid <> pg_backend_pid() AND now() - query_start > interval '60 seconds' ORDER BY query_start DESC

Queries are truncated

Most queries showing in the Queries page seem to be truncated - is this a PgHero thing? Makes it impossible to figure out what the actual query was - multiple queries start the same way :-/

Provide a homebrew formula

PgHero looks awesome. Is there any chance to make it available on OS X for noobs like me, e.g. by means of a homebrew formula?

DB instance identifier broken

After d881d0c
PgHero.db_instance_identifier
is returning nil

Extended support for EXPLAIN

There are a number of optional settings to pass to an explain:

• analyze
• verbose
• costs
• buffers
• timing
• format

www.postgresql.org/docs/9.3/static/sql-explain.html#AEN77705

How you do you think these would best fit into the UI? Perhaps we could have a check box for each?

The index migration needs to mention `disable_ddl_transaction!`

Trying to create a index concurrently in Rails need the migration transaction to be disabled. Need to mention that.

https://robots.thoughtbot.com/how-to-create-postgres-indexes-concurrently-in

Also, GIST indexes can easily be defined int the Rails format itself. Can update that too.

I'll submit a PR for both changes, just putting them down here so I don't forget.

add index_exists? on remove_index migration code

Pghero shows code snippets for using migration like this,

remove_index :posts, name: "index_posts_on_slug"

And users can make migration code like this.

class RemoveUnusedIndexes < ActiveRecord::Migration
  def up
    remove_index :posts, name: "index_posts_on_slug"
  end

  def down
  end
end

I think it has problem that cannot retry after rollback so it's better (and safer).

remove_index :posts, name: "index_posts_on_slug" if index_exists?(:posts, name: "index_posts_on_slug")

Show duplicate indexes

https://gist.github.com/jberkus/6bbffae5ce10fb399d29

PgHero doesn't use the Rails connection pool, gets whacked by PgBouncer

I'm not sure how connection pooling happens inside PgHero, but it doesn't seem to survive for very long when running behind PgBouncer. Ironic, yes.

Is there an internal pool being used for connections? I'm mounting it on a rails app and the rest of the app works fine, so I'm guessing PgHero is doing its own thing with regards to connection pooling. PgHero stops being able to use connections after a few hours, and works again only after a restart.

Suggest parameter values

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and other guides.

If you have good resources, please share!

AWS - NoMethodError: undefined method `match' for nil:NilClass\nDid you mean

When I try to view the RDS CPU info using my PGHERO_ACCESS_KEY_ID, PGHERO_SECRET_ACCESS_KEY and PGHERO_DB_INSTANCE_IDENTIFIER, I get:

["note_type", "Error"], ["note", "NoMethodError: undefined method `match' for nil:NilClass\nDid you mean?  catch"], ["action", "pg_hero/home#connection_stats"], ["status", 500]

How does it compare to kirs/pg_web_stats?

There is also a gem which aims the same purpose: https://github.com/kirs/pg_web_stats
Would you look at it and say pros and cons?

User methods

The db user methods as documented here no longer seem to work:
https://github.com/ankane/pghero/blob/master/guides/Rails.md#users

PgHero.create_user("blazer", readonly: true)
NoMethodError: undefined method `create_user' for PgHero:Module

How does one go about using these now?

Show slow queries with sequence scans

Since you can't run explain on normalized queries from pg_stat_statements, need to capture live query.

ActiveRecord::AdapterNotSpecified (database configuration does not specify adapter):

I have a database.yml as following:

production:
  adapter: postgresql
  encoding: unicode
  database: database_production
  pool: 5

when I try to access /pghero, I see the error on title. Did I missed something?

Apache proxy error

Hi there,

I've installed pghero in a Centos 6.7, following the docs.

Also, I'm using Apache as web server.

httpd.conf:

NameVirtualHost *:80

<VirtualHost *:80>
ProxyPreserveHost On
ProxyPass / http://localhost:6000/
ProxyPassReverse / http://localhost:6000/

DocumentRoot / 
ServerName 10.208.7.32
</VirtualHost>

When I open the browser to connect into pghero, I got the following error:

(70007)The timeout specified has expired: proxy: error reading status line from remote server localhost
 proxy: Error reading from remote server returned by /

[root@localhost]# curl -v http://localhost:6000/
* About to connect() to localhost port 6000 (#0)
*   Trying ::1... Connection refused
*   Trying 127.0.0.1... connected
* Connected to localhost (127.0.0.1) port 6000 (#0)
> GET / HTTP/1.1
> User-Agent: curl/7.19.7 (x86_64-redhat-linux-gnu) libcurl/7.19.7 NSS/3.19.1 Basic ECC zlib/1.2.3 libidn/1.18 libssh2/1.4.2
> Host: localhost:6000
> Accept: */*

Do you guys have any idea about how can I solve that one?
Cheers

Explain how to enable query stats

Great gem! 👏

I installed it and it told me query stats are not enabled and, upon trying to enable them, "The database user does not have permission to enable query stats"
Could you quickly explain in the README what the query stats are and how to enable them? Are they same as the Statistics Collector?
A bit more info would be great for Postgres newbies like me.

Also, it might make sense to mention in the README that the performance of the app is not affected by enabling PGHero (I assume it's not 😄).

Multi PostgreSQL instances

Hi,
PGhero supports multi-connections?
Example,
sudo pghero config:set DATABASE_URL=postgres://user:password@hostname1:5432/dbname
sudo pghero config:set DATABASE_URL=postgres://user:password@hostname2:5432/dbname

if not, whether it is possible to make ?

Unable to load application: NameError: uninitialized constant ActionView::Helpers::ActiveModelHelper

[26130] * Preloading application
[26130] ! Unable to load application: NameError: uninitialized constant ActionView::Helpers::ActiveModelHelper
/opt/pghero/vendor/bundle/ruby/2.0.0/gems/actionview-4.2.3/lib/action_view/helpers.rb:40:in `<module:Helpers>': uninitialized constant ActionView::Helpers::ActiveModelHelper (NameError)
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/actionview-4.2.3/lib/action_view/helpers.rb:4:in `<module:ActionView>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/actionview-4.2.3/lib/action_view/helpers.rb:3:in `<top (required)>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/actionview-4.2.3/lib/action_view/base.rb:5:in `<top (required)>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/actionview-4.2.3/lib/action_view/view_paths.rb:1:in `<top (required)>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/actionpack-4.2.3/lib/abstract_controller/rendering.rb:4:in `<top (required)>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/actionpack-4.2.3/lib/action_controller/base.rb:204:in `<class:Base>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/actionpack-4.2.3/lib/action_controller/base.rb:164:in `<module:ActionController>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/actionpack-4.2.3/lib/action_controller/base.rb:5:in `<top (required)>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/pghero-1.1.0/app/controllers/pg_hero/home_controller.rb:2:in `<module:PgHero>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/pghero-1.1.0/app/controllers/pg_hero/home_controller.rb:1:in `<top (required)>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/engine.rb:472:in `block (2 levels) in eager_load!'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/engine.rb:471:in `each'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/engine.rb:471:in `block in eager_load!'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/engine.rb:469:in `each'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/engine.rb:469:in `eager_load!'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/engine.rb:346:in `eager_load!'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/application/finisher.rb:56:in `each'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/application/finisher.rb:56:in `block in <module:Finisher>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/initializable.rb:30:in `instance_exec'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/initializable.rb:30:in `run'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/initializable.rb:55:in `block in run_initializers'
        from /opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/tsort.rb:150:in `block in tsort_each'
        from /opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/tsort.rb:183:in `block (2 levels) in each_strongly_connected_component'
        from /opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/tsort.rb:219:in `each_strongly_connected_component_from'
        from /opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/tsort.rb:182:in `block in each_strongly_connected_component'
        from /opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/tsort.rb:180:in `each'
        from /opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/tsort.rb:180:in `each_strongly_connected_component'
        from /opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/tsort.rb:148:in `tsort_each'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/initializable.rb:54:in `run_initializers'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/application.rb:352:in `initialize!'
        from /opt/pghero/config/environment.rb:5:in `<top (required)>'
        from config.ru:3:in `require'
        from config.ru:3:in `block in <main>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:55:in `instance_eval'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:55:in `initialize'
        from config.ru:in `new'
        from config.ru:in `<main>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:49:in `eval'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:49:in `new_from_string'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:40:in `parse_file'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/configuration.rb:102:in `load_rackup'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/configuration.rb:69:in `app'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/runner.rb:113:in `load_and_bind'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/cluster.rb:302:in `run'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/cli.rb:216:in `run'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/bin/puma:10:in `<top (required)>'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/bin/puma:23:in `load'
        from /opt/pghero/vendor/bundle/ruby/2.0.0/bin/puma:23:in `<main>'

Fedora 22.
Installed from your rpm repo.

rpm -q pghero

pghero-1.1.0-1435724100.41a868c.fedora20.x86_64

locks section

I love using pghero. Are there plans to have a locks section, so I can see the current locks in the database which queries are affected ?

identify redundant indexes

Perhaps worth adding functionality to identify redundant indexes, such as when a column is covered by both a compound key and an individual key.

The active_record_doctor Rubygem does this: https://github.com/gregnavis/active_record_doctor#removing-extraneous-indexes

ERROR: relation "pghero_query_stats" does not exist

Hi there,

I've installed pghero in my CentOS from YUM and I created the table as mentioned on the docs:

However, I created the pghero_query_stats table in another SCHEMA. And now I'm getting that error when performing:

ERROR:
ERROR: relation "pghero_query_stats" does not exist

sudo pghero run rake pghero:capture_query_stats

Is there anything I need to do to allow pghero to see the other SCHEMA?

Cheers

Systemd Support

When I try to start pghero on Ubuntu 15.10, it gives me this error:

initctl: Unable to connect to Upstart: Failed to connect to socket /com/ubuntu/upstart: Connection refused

This is evidently because Ubuntu has switched from upstart to systemd in the latest versions. Is there any chance of adding support for systemd? Or is there another way that I can run pghero without installing upstart?

Suggest indexes

Use slow queries as a starting point, then programmatically do what a human would

Query Stats

After trying to enable Query Stats while using the Rails plugin and Postgres RDS, I get:

FATAL: remaining connection slots are reserved for non-replication superuser connections

Crash when setting multiple databases

I'm using Debian 8, pghero is a local bundler install with config.ru, Gemfile and Gemlfile.lock stolen from the Docker example.

Everything is fine with one database defined in env DATABASE_URL.

Now, I want multiple databases. I wrote config/pghero.yml as the doc explains.

Here is the stack trace :

$ PGHERO_USERNAME=bob PGHERO_PASSWORD=sponge  bundle exec puma -t 8:12 -w 4 -b tcp://1.2.3.4:8000 -e production --preload
[20663] Puma starting in cluster mode...
[20663] * Version 2.9.1 (ruby 2.1.5-p273), codename: Team High Five
[20663] * Min threads: 8, max threads: 12
[20663] * Environment: production
[20663] * Process workers: 4
[20663] * Preloading application
[20663] ! Unable to load application
/var/www/pghero/vendor/ruby/2.1.0/gems/railties-4.1.6/lib/rails/application/configuration.rb:105:in `database_configuration': Cannot load `Rails.application.database_configuration`: (RuntimeError)
Could not load database configuration. No such file -
    from /var/www/pghero/vendor/ruby/2.1.0/gems/activerecord-4.1.6/lib/active_record/railtie.rb:128:in `block (2 levels) in <class:Railtie>'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/activesupport-4.1.6/lib/active_support/lazy_load_hooks.rb:38:in `instance_eval'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/activesupport-4.1.6/lib/active_support/lazy_load_hooks.rb:38:in `execute_hook'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/activesupport-4.1.6/lib/active_support/lazy_load_hooks.rb:28:in `block in on_load'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/activesupport-4.1.6/lib/active_support/lazy_load_hooks.rb:27:in `each'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/activesupport-4.1.6/lib/active_support/lazy_load_hooks.rb:27:in `on_load'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/activerecord-4.1.6/lib/active_record/railtie.rb:118:in `block in <class:Railtie>'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/railties-4.1.6/lib/rails/initializable.rb:30:in `instance_exec'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/railties-4.1.6/lib/rails/initializable.rb:30:in `run'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/railties-4.1.6/lib/rails/initializable.rb:55:in `block in run_initializers'
    from /usr/lib/ruby/2.1.0/tsort.rb:226:in `block in tsort_each'
    from /usr/lib/ruby/2.1.0/tsort.rb:348:in `block (2 levels) in each_strongly_connected_component'
    from /usr/lib/ruby/2.1.0/tsort.rb:427:in `each_strongly_connected_component_from'
    from /usr/lib/ruby/2.1.0/tsort.rb:347:in `block in each_strongly_connected_component'
    from /usr/lib/ruby/2.1.0/tsort.rb:345:in `each'


    from /usr/lib/ruby/2.1.0/tsort.rb:345:in `call'
    from /usr/lib/ruby/2.1.0/tsort.rb:345:in `each_strongly_connected_component'
    from /usr/lib/ruby/2.1.0/tsort.rb:224:in `tsort_each'
    from /usr/lib/ruby/2.1.0/tsort.rb:205:in `tsort_each'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/railties-4.1.6/lib/rails/initializable.rb:54:in `run_initializers'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/railties-4.1.6/lib/rails/application.rb:300:in `initialize!'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/railties-4.1.6/lib/rails/railtie.rb:194:in `public_send'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/railties-4.1.6/lib/rails/railtie.rb:194:in `method_missing'
    from config.ru:18:in `block in <main>'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/rack-1.5.2/lib/rack/builder.rb:55:in `instance_eval'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/rack-1.5.2/lib/rack/builder.rb:55:in `initialize'
    from config.ru:in `new'
    from config.ru:in `<main>'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/rack-1.5.2/lib/rack/builder.rb:49:in `eval'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/rack-1.5.2/lib/rack/builder.rb:49:in `new_from_string'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/rack-1.5.2/lib/rack/builder.rb:40:in `parse_file'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/puma-2.9.1/lib/puma/configuration.rb:96:in `app'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/puma-2.9.1/lib/puma/runner.rb:113:in `load_and_bind'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/puma-2.9.1/lib/puma/cluster.rb:283:in `run'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/puma-2.9.1/lib/puma/cli.rb:499:in `run'
    from /var/www/pghero/vendor/ruby/2.1.0/gems/puma-2.9.1/bin/puma:10:in `<top (required)>'
    from /var/www/pghero/vendor/ruby/2.1.0/bin/puma:23:in `load'
    from /var/www/pghero/vendor/ruby/2.1.0/bin/puma:23:in `<main>'

Connections from other VMs in Heroku hobby db?

I am using Heroku with Ruby 2.1.5 and looking at the below snapshot of the connections you can see that these can't be my connections.

i.e. there is code running in Ruby 1.9 and 2.2 and also sidekiq which I am not using.

Should I expect to see connections from other apps not belonging to me when using this gem on heroku and the hobby ($9/mth) database?