powa-team/pg_qualstats

CREATE EXTENSION pg_qualstats

Closed this issue · 6 comments

sitrl commented

I have configured the extension with the following features:

. contrib + dev rpm installed done (Done
. modified postgresql.conf (add shared_preload_libraries = 'pg_stat_statements,pg_qualstats') (Done)
. make install pg_qualstat.c (Done)
.restart the instance (pg_ctl -D restart (Done)

It seems like good, but when I would like to add the EXTENSION Postgres write this Error:

postgres=# CREATE EXTENSION pg_qualstats ;
ERROR: This module can only be loaded via shared_preload_libraries
Time: 3.347 ms

Could you please help me?
Any suggestions will be accepted.

Regards.

Lorenzo

Extension installed:

postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+-----------------------------------------------------------
adminpack | 1.0 | pg_catalog | administrative functions for PostgreSQL
pg_stat_kcache | 2.0.2 | public | Kernel cache statistics gathering
pg_stat_statements | 1.3 | public | track execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

postgres=# show shared_preload_libraries;

shared_preload_libraries

pg_stat_statements
(1 row)

select name,pending_restart, from pg_settings where name in ('shared_preload_libraries');

       name           | pending_restart

--------------------------+-----------------
shared_preload_libraries | f

rjuju commented

That seems strange. One reason could be that pg_stat_statements were added to shared_preload_libraries with an ALTER SYSTEM command, so any further change to this parameter in the postgresql.conffile would be ignored. Can you also check the sourcefile column of pg_settings, to see if t's included in postgresql.conf or postgresql.auto.conf?

sitrl commented

Great GREAT … GREAT!!!!

WONDERFUL … IT’S RUNNING ….

THXS A LOT …..

Last question about

Explain me this topics

How can change the sourcefile of shared_preload_libraries?

I fill in into the /var/PostgreSQL/data/postgresql.conf the parameter.
So … what is the relation between postgresql.conf and /var/PostgreSQL/data/postgresql.auto.conf?

Thx a log

Lorenzo

SELECT name,
setting,
unit,
sourcefile,
sourceline,
CAST('INSERT' AS VARCHAR(6)) AS op,
CURRENT_TIMESTAMP AS recorded_ts
FROM pg_settings WHERE source='configuration file'
;
name | setting | unit | sourcefile | sourceline | op | recorded_ts
----------------------------+----------------------------------+------+-------------------------------------------+------------+--------+-------------------------------
archive_mode | off | NULL | /var/PostgreSQL/data/postgresql.conf | 211 | INSERT | 2016-10-07 16:36:06.611643+02
DateStyle | ISO, MDY | NULL | /var/PostgreSQL/data/postgresql.conf | 541 | INSERT | 2016-10-07 16:36:06.611643+02
default_text_search_config | pg_catalog.english | NULL | /var/PostgreSQL/data/postgresql.conf | 563 | INSERT | 2016-10-07 16:36:06.611643+02
dynamic_shared_memory_type | posix | NULL | /var/PostgreSQL/data/postgresql.conf | 126 | INSERT | 2016-10-07 16:36:06.611643+02
krb_caseins_users | on | NULL | /var/PostgreSQL/data/postgresql.conf | 94 | INSERT | 2016-10-07 16:36:06.611643+02
krb_server_keyfile | /var/PostgreSQL/data/krb5.keytab | NULL | /var/PostgreSQL/data/postgresql.conf | 93 | INSERT | 2016-10-07 16:36:06.611643+02
lc_messages | en_US.UTF-8 | NULL | /var/PostgreSQL/data/postgresql.conf | 556 | INSERT | 2016-10-07 16:36:06.611643+02
lc_monetary | en_US.UTF-8 | NULL | /var/PostgreSQL/data/postgresql.conf | 558 | INSERT | 2016-10-07 16:36:06.611643+02
lc_numeric | en_US.UTF-8 | NULL | /var/PostgreSQL/data/postgresql.conf | 559 | INSERT | 2016-10-07 16:36:06.611643+02
lc_time | en_US.UTF-8 | NULL | /var/PostgreSQL/data/postgresql.conf | 560 | INSERT | 2016-10-07 16:36:06.611643+02
listen_addresses | * | NULL | /var/PostgreSQL/data/postgresql.conf | 59 | INSERT | 2016-10-07 16:36:06.611643+02
log_destination | stderr | NULL | /var/PostgreSQL/data/postgresql.conf | 324 | INSERT | 2016-10-07 16:36:06.611643+02
log_line_prefix | %t | NULL | /var/PostgreSQL/data/postgresql.conf | 422 | INSERT | 2016-10-07 16:36:06.611643+02
log_timezone | Europe/Vaduz | NULL | /var/PostgreSQL/data/postgresql.conf | 448 | INSERT | 2016-10-07 16:36:06.611643+02
logging_collector | on | NULL | /var/PostgreSQL/data/postgresql.conf | 330 | INSERT | 2016-10-07 16:36:06.611643+02
max_connections | 100 | NULL | /var/PostgreSQL/data/postgresql.conf | 64 | INSERT | 2016-10-07 16:36:06.611643+02
pg_stat_statements.max | 1000 | NULL | /var/PostgreSQL/data/postgresql.conf | 145 | INSERT | 2016-10-07 16:36:06.611643+02
pg_stat_statements.track | all | NULL | /var/PostgreSQL/data/postgresql.conf | 146 | INSERT | 2016-10-07 16:36:06.611643+02
port | 5432 | NULL | /var/PostgreSQL/data/postgresql.conf | 63 | INSERT | 2016-10-07 16:36:06.611643+02
shared_buffers | 131072 | 8kB | /var/PostgreSQL/data/postgresql.conf | 113 | INSERT | 2016-10-07 16:36:06.611643+02
shared_preload_libraries | pg_stat_statements | NULL | /var/PostgreSQL/data/postgresql.auto.conf | 3 | INSERT | 2016-10-07 16:36:06.611643+02
synchronous_commit | on | NULL | /var/PostgreSQL/data/postgresql.conf | 178 | INSERT | 2016-10-07 16:36:06.611643+02
TimeZone | Europe/Vaduz | NULL | /var/PostgreSQL/data/postgresql.conf | 543 | INSERT | 2016-10-07 16:36:06.611643+02
track_activity_query_size | 2048 | NULL | /var/PostgreSQL/data/postgresql.conf | 144 | INSERT | 2016-10-07 16:36:06.611643+02
wal_level | archive | NULL | /var/PostgreSQL/data/postgresql.conf | 175 | INSERT | 2016-10-07 16:36:06.611643+02

Da: Julien Rouhaud [mailto:notifications@github.com]
Inviato: venerdì, 7. ottobre 2016 09:27
A: dalibo/pg_qualstats
Cc: Trenta Lorenzo; Author
Oggetto: Re: [dalibo/pg_qualstats] CREATE EXTENSION pg_qualstats (#8)

That seems strange. One reason could be that pg_stat_statements were added to shared_preload_libraries with an ALTER SYSTEM command, so any further change to this parameter in the postgresql.conffile would be ignored. Can you also check the sourcefile column of pg_settings, to see if t's included in postgresql.conf or postgresql.auto.conf?

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHubhttps://github.com/dalibo/pg_qualstats/issues/8#issuecomment-252171655, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AVDLd4PePC7UPhd34lGPgp2QBNChIlAbks5qxfRUgaJpZM4KQsZg.

sitrl commented

Great GREAT … GREAT!!!!

WONDERFUL … IT’S RUNNING ….

THXS A LOT …..

Last question about

Explain me this topics

How can change the sourcefile of shared_preload_libraries?

I fill in into the /var/PostgreSQL/data/postgresql.conf the parameter.
So … what is the relation between postgresql.conf and /var/PostgreSQL/data/postgresql.auto.conf?

Thx a log

Lorenzo

SELECT name,
setting,
unit,
sourcefile,
sourceline,
CAST('INSERT' AS VARCHAR(6)) AS op,
CURRENT_TIMESTAMP AS recorded_ts
FROM pg_settings WHERE source='configuration file'
;
name | setting | unit | sourcefile | sourceline | op | recorded_ts
----------------------------+----------------------------------+------+-------------------------------------------+------------+--------+-------------------------------
archive_mode | off | NULL | /var/PostgreSQL/data/postgresql.conf | 211 | INSERT | 2016-10-07 16:36:06.611643+02
DateStyle | ISO, MDY | NULL | /var/PostgreSQL/data/postgresql.conf | 541 | INSERT | 2016-10-07 16:36:06.611643+02
default_text_search_config | pg_catalog.english | NULL | /var/PostgreSQL/data/postgresql.conf | 563 | INSERT | 2016-10-07 16:36:06.611643+02
dynamic_shared_memory_type | posix | NULL | /var/PostgreSQL/data/postgresql.conf | 126 | INSERT | 2016-10-07 16:36:06.611643+02
krb_caseins_users | on | NULL | /var/PostgreSQL/data/postgresql.conf | 94 | INSERT | 2016-10-07 16:36:06.611643+02
krb_server_keyfile | /var/PostgreSQL/data/krb5.keytab | NULL | /var/PostgreSQL/data/postgresql.conf | 93 | INSERT | 2016-10-07 16:36:06.611643+02
lc_messages | en_US.UTF-8 | NULL | /var/PostgreSQL/data/postgresql.conf | 556 | INSERT | 2016-10-07 16:36:06.611643+02
lc_monetary | en_US.UTF-8 | NULL | /var/PostgreSQL/data/postgresql.conf | 558 | INSERT | 2016-10-07 16:36:06.611643+02
lc_numeric | en_US.UTF-8 | NULL | /var/PostgreSQL/data/postgresql.conf | 559 | INSERT | 2016-10-07 16:36:06.611643+02
lc_time | en_US.UTF-8 | NULL | /var/PostgreSQL/data/postgresql.conf | 560 | INSERT | 2016-10-07 16:36:06.611643+02
listen_addresses | * | NULL | /var/PostgreSQL/data/postgresql.conf | 59 | INSERT | 2016-10-07 16:36:06.611643+02
log_destination | stderr | NULL | /var/PostgreSQL/data/postgresql.conf | 324 | INSERT | 2016-10-07 16:36:06.611643+02
log_line_prefix | %t | NULL | /var/PostgreSQL/data/postgresql.conf | 422 | INSERT | 2016-10-07 16:36:06.611643+02
log_timezone | Europe/Vaduz | NULL | /var/PostgreSQL/data/postgresql.conf | 448 | INSERT | 2016-10-07 16:36:06.611643+02
logging_collector | on | NULL | /var/PostgreSQL/data/postgresql.conf | 330 | INSERT | 2016-10-07 16:36:06.611643+02
max_connections | 100 | NULL | /var/PostgreSQL/data/postgresql.conf | 64 | INSERT | 2016-10-07 16:36:06.611643+02
pg_stat_statements.max | 1000 | NULL | /var/PostgreSQL/data/postgresql.conf | 145 | INSERT | 2016-10-07 16:36:06.611643+02
pg_stat_statements.track | all | NULL | /var/PostgreSQL/data/postgresql.conf | 146 | INSERT | 2016-10-07 16:36:06.611643+02
port | 5432 | NULL | /var/PostgreSQL/data/postgresql.conf | 63 | INSERT | 2016-10-07 16:36:06.611643+02
shared_buffers | 131072 | 8kB | /var/PostgreSQL/data/postgresql.conf | 113 | INSERT | 2016-10-07 16:36:06.611643+02
shared_preload_libraries | pg_stat_statements | NULL | /var/PostgreSQL/data/postgresql.auto.conf | 3 | INSERT | 2016-10-07 16:36:06.611643+02
synchronous_commit | on | NULL | /var/PostgreSQL/data/postgresql.conf | 178 | INSERT | 2016-10-07 16:36:06.611643+02
TimeZone | Europe/Vaduz | NULL | /var/PostgreSQL/data/postgresql.conf | 543 | INSERT | 2016-10-07 16:36:06.611643+02
track_activity_query_size | 2048 | NULL | /var/PostgreSQL/data/postgresql.conf | 144 | INSERT | 2016-10-07 16:36:06.611643+02
wal_level | archive | NULL | /var/PostgreSQL/data/postgresql.conf | 175 | INSERT | 2016-10-07 16:36:06.611643+02

rjuju commented

Sourcefile is just a column telling you which file is the last to set up a parameter.

If you want to configure shared_preload_libraries with postgresql.conf instead of postgresql.auto.conf, you have to make sure you never use ALTER SYSTEM anymore to configure something after fixing it. In this case, you can do:

  • ALTER SYSTEM RESET shared_buffers ;
  • configure shared_buffers in the postgresql.conf file as you tried before
  • restart postgres
  • never use ALTER SYSTEM again
sitrl commented

Excellent!

You are the best support!!!

Amazing.

☺

Da: Julien Rouhaud [mailto:notifications@github.com]
Inviato: venerdì, 7. ottobre 2016 16:57
A: dalibo/pg_qualstats
Cc: Trenta Lorenzo; State change
Oggetto: Re: [dalibo/pg_qualstats] CREATE EXTENSION pg_qualstats (#8)

Sourcefile is just a column telling you which file is the last to set up a parameter.

If you want to configure shared_preload_libraries with postgresql.conf instead of postgresql.auto.conf, you have to make sure you never use ALTER SYSTEM anymore to configure something after fixing it. In this case, you can do:

  • ALTER SYSTEM RESET shared_buffers ;
  • configure shared_buffers in the postgresql.conf file as you tried before
  • restart postgres
  • never use ALTER SYSTEM again

—
You are receiving this because you modified the open/close state.
Reply to this email directly, view it on GitHubhttps://github.com/dalibo/pg_qualstats/issues/8#issuecomment-252274712, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AVDLd2C8KeAyx3wdhTWutruyHmWWCXwTks5qxl2vgaJpZM4KQsZg.

rjuju commented

Thanks :)