Description of problem: A user would like to enable sql statement execution statistics in postgresql in OpenShift Online, as documented here: http://www.postgresql.org/docs/9.2/static/pgstatstatements.html Specifically, they would like to add these two lines of configuration: shared_preload_libraries = 'pg_stat_statements' # (change requires restart) pg_stat_statements.track = all I see two possible solutions to this: 1) put an "include_if_exists" directive in so that users have a place to drop miscellaneous extra options 2) add yet another environment variable to enable these specific options
I've added environment variable called 'OPENSHIFT_POSTGRESQL_CONFIG' with this you specify additional lines you want to include inside postgresql.conf file. Each configuration line (that's postgresql requirement) has to be separated with ';', eg. for above case: rhc set-env OPENSHIFT_POSTGRESQL_CONFIG="shared_preload_libraries = 'pg_stat_statements';pg_stat_statements.track = all" The resulting postgresql.conf will have each of this in separate line. See this PR: https://github.com/openshift/origin-server/pull/6059
Commits pushed to master at https://github.com/openshift/origin-server https://github.com/openshift/origin-server/commit/296a44cd3222d9f09596fb9e8fdccf63b06827fc Bug 1185031 - allow enabling of execution statistics https://github.com/openshift/origin-server/commit/5d5cc9536a05ce3354296badd911b90fc581460d Merge pull request #6059 from soltysh/bug1185031 Merged by openshift-bot
It's fixed, verified on devenv_5409 for postgresql 8.4 and 9.2, please refer to the following results: 1. Create an app with postgresql 8.4/9.2 rhc app create cphps php-5.4 postgresql-9 --no-git -s 2. Set the OPENSHIFT_POSTGRESQL_CONFIG variable with below value: rhc set-env OPENSHIFT_POSTGRESQL_CONFIG="shared_preload_libraries = 'pg_stat_statements';pg_stat_statements.track = all" -a cphps 3. Restart the postgresql to enable changes rhc app restart cphps 4. SSH into the app and run the below psql commands after logging into the postgresql: [cphps-cdm.dev.rhcloud.com 54ca1345054f750210000165]\> psql cphps=# \i /usr/share/pgsql/contrib/pg_stat_statements.sql cphps=# select * from pg_stat_statements; userid | dbid | query | calls | total_time | rows --------+-------+-------------------------------------------------------------------------------------------------------------------+-------+------------+------ 16384 | 16385 | CREATE VIEW pg_stat_statements AS +| 1 | 50.825 | 0 | | SELECT * FROM pg_stat_statements(); | | | 16384 | 16385 | /* $PostgreSQL: pgsql/contrib/pg_stat_statements/pg_stat_statements.sql.in,v 1.1 2009/01/04 22:19:59 tgl Exp $ */+| 1 | 0.916 | 0 | | +| | | | | SET search_path = public; | | | 16384 | 16385 | CREATE FUNCTION pg_stat_statements( +| 1 | 30.118 | 0 | | OUT userid oid, +| | | | | OUT dbid oid, +| | | | | OUT query text, +| | | | | OUT calls int8, +| | | | | OUT total_time float8, +| | | | | OUT rows int8 +| | | | | ) +| | | | | RETURNS SETOF record +| | | | | AS '$libdir/pg_stat_statements' +| | | | | LANGUAGE C; | | | 16384 | 16385 | REVOKE ALL ON FUNCTION pg_stat_statements_reset() FROM PUBLIC; | 1 | 0.056 | 0 16384 | 16385 | GRANT SELECT ON pg_stat_statements TO PUBLIC; | 1 | 0.069 | 0 16384 | 16385 | CREATE FUNCTION pg_stat_statements_reset() +| 1 | 24.616 | 0 | | RETURNS void +| | | | | AS '$libdir/pg_stat_statements' +| | | | | LANGUAGE C; | | | 10 | 12923 | SELECT d.datname as "Name", +| 1 | 0.122 | 4 | | pg_catalog.pg_get_userbyid(d.datdba) as "Owner", +| | | | | pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", +| | | | | d.datcollate as "Collate", +| | | | | d.datctype as "Ctype", +| | | | | pg_catalog.array_to_string(d.datacl, ?) AS "Access privileges" +| | | | | FROM pg_catalog.pg_database d +| | | | | ORDER BY 1; | | | (7 rows)
Hello, We have tried this on our own applications with the following results. Please could you confirm if this bug should be available to all existing cartridges with Postgres. Step 1 : Ran the set-env command on an existing application ===================================== PS C:\> rhc set-env OPENSHIFT_POSTGRESQL_CONFIG="shared_preload_libraries = 'pg_stat_statements';pg_stat_statements.track = all" -a st4 -n mudanods -l sandipan.chanda DL is deprecated, please use Fiddle Setting environment variable(s) ... done Step 2 : Restarted the application ===================== PS C:\> rhc app restart st4 -n mudanods -l sandipan.chanda DL is deprecated, please use Fiddle RESULT: st4 restarted Step 3 : Checked in environement variable has changed ================================== [st4-mudanods.rhcloud.com contrib]\> printenv OPENSHIFT_POSTGRESQL_CONFIG shared_preload_libraries = 'pg_stat_statements';pg_stat_statements.track = all [st4-mudanods.rhcloud.com contrib]\> Step 4 : Ran command requested in psql ========================= [st4-mudanods.rhcloud.com contrib]\> psql psql (9.2.8) Type "help" for help. st4=# \i /usr/share/pgsql/contrib/pg_stat_statements.sql SET psql:/usr/share/pgsql/contrib/pg_stat_statements.sql:10: ERROR: function "pg_stat_statements_reset" already exists with same argument types psql:/usr/share/pgsql/contrib/pg_stat_statements.sql:22: ERROR: function "pg_stat_statements" already exists with same argument types psql:/usr/share/pgsql/contrib/pg_stat_statements.sql:26: ERROR: relation "pg_stat_statements" already exists GRANT REVOKE st4=# select * from pg_stat_statements; ERROR: pg_stat_statements must be loaded via shared_preload_libraries Issue : Hit the above error in psql
Andrew, I'm assuming you've checked it in OpenShift Online. The patch I've created for this bug was just recently merged. It should go into OpenShift Online in a couple of weeks, I wouldn't expect it to be there sooner than end of February or so.