Postgres Query Stats?

isaac-hinman

PROOP

2 years ago

I have deployed Postgres via the first-class Railway way, and want to use PGHero to monitor the database.

To enable query stats, these two things must be set:

```

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.track = all

```

How can I achieve this? Perhaps some combination of POSTGRES_INITDB_ARGS and something else?

In general it would be nice if Railway offered basic monitoring and query stats out of the box. Heroku, for example, does this.

6 Replies

isaac-hinman

PROOP

2 years ago

Found this ticket:

https://help.railway.app/questions/modifying-postgresql-conf-f7ec0398

Therefore my ticket can be considered a duplicate, although no helpful support was actually offered in the original ticket.

My point about a feature request for out-of-the-box monitoring stands, though – would be very useful and likely a smallish project for the Railway team.

isaac-hinman

PROOP

2 years ago

Actually, scratch that. pg_stat_statements needs to be added to shared_preload_libraries and requires a restart. Not sure how one would achieve this.

I can see another instance of a user seemingly breaking their db by attempting something similar:

https://help.railway.app/questions/postgres-crashes-after-config-0cc1cbe3

isaac-hinman

PROOP

2 years ago

Tried a variety of different env vars for PG – none worked.

ray-chen

EMPLOYEE

2 years ago

I was able to do this on a test database I deployed just fine:

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

SHOW shared_preload_libraries;
| shared_preload_libraries |
|--------------------------|
| pg_stat_statements       |

Seems to work after a redeploy of the database. Perhaps try that out on a test instance?

Also yup, we're looking at exposing more database internals in a managed interface soon!

Status changed to Awaiting User Response Railway • almost 2 years ago

isaac-hinman

PROOP

2 years ago

Thanks, I was sure I tried the exact same yesterday, but it did not work.

I can confirm that running the ALTER command you suggested, plus a redeploy of pg, works.

We're now able to run pghero on Railway.

Status changed to Awaiting Railway Response Railway • almost 2 years ago

Status changed to Solved jake • almost 2 years ago

isaac-hinman

PROOP

10 months ago

Has the approach changed? I saw in a recent Railway changelog that database settings can be passed as config.

Status changed to Open Railway • 10 months ago

Welcome!