Обсуждение: can't get pg_stat_statements to work

Ubuntu 11.04
Postgresql 8.4

I've installed postgresql-contrib:

# sudo apt-get install postgresql-contrib

I now have a directory:
/usr/share/postgresql/8.4/contrib
I've added a line in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

and I connected to postgres using pgAdmin and inside a test database, I
ran the

/usr/share/postgresql/8.4/contrib/pg_stat_statements.sql

However, unless I comment out the new line in postgresql.conf, the
server won't start.

Could anyone tell me how to make this work?

Richard

richard@xentu.com writes:
> I've installed postgresql-contrib:

> # sudo apt-get install postgresql-contrib

> I now have a directory:
> /usr/share/postgresql/8.4/contrib
> I've added a line in postgresql.conf:

> shared_preload_libraries = 'pg_stat_statements'

> and I connected to postgres using pgAdmin and inside a test database, I
> ran the

> /usr/share/postgresql/8.4/contrib/pg_stat_statements.sql

> However, unless I comment out the new line in postgresql.conf, the
> server won't start.

What's the exact error message recorded in the postmaster log file?

The most likely bet is that the postmaster is failing to look in the
directory where the pg_stat_statements.so shared library is, which
would be a matter to take up with the Ubuntu packager(s) of Postgres:
postgresql-contrib really ought to be dropping its shared libraries
in a place where the postmaster's library search path will find them.
As a short-term workaround, I think it might work to put a full
path name into shared_preload_libraries instead of relying on the
code to find the library automatically.

But it could be something else.  The error message would help.

            regards, tom lane

Tom>The most likely bet is that the postmaster is failing to look in the
directory where the pg_stat_statements.so shared library is

The so file is here:
/usr/lib/postgresql/8.4/lib/pg_stat_statements.so
along with a whole load of other .so files

Nevertheless, I've tried specifying the full path to that file in
postgresql.conf:

shared_preload_libraries =
'/usr/lib/postgresql/8.4/lib/pg_stat_statements.so'

but server still won't start.

Tom> What's the exact error message recorded in the postmaster log file?

Is that the file that gets generated in
/var/lib/postgresql/8.4/main/pg_log?

I'm starting the server via the command
# /etc/init.d/postgresql start

At the terminal I get the message:

  * Starting PostgreSQL 8.4 database server
  * The PostgreSQL server failed to start. Please check the log output.

but no file get generated in /var/lib/postgresql/8.4/main/pg_log

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sat, Sep 19, 2015 at 12:29 PM, <richard@xentu.com> wrote:
>> Is that the file that gets generated in
>> /var/lib/postgresql/8.4/main/pg_log?

> ​I'm not sure what this ancient and no longer supported (by this community
> at least...) version of PostgreSQL​ does on Ubuntu but my unaltered 9.3
> installation on 14.04 places the log file into "/var/log/postgresql".  I
> suspect yours does the same.

The pg_log file is probably capturing the server's original stderr output,
which would only receive error messages emitted before the server adopts
the log destination commanded by the log-related settings specified in
postgresql.conf.  I believe that shared_preload_libraries is processed
after we adopt the log-related configuration settings, so whatever error
is getting reported about that would go where your configuration says.

(More recent versions than 8.4 try to leave a breadcrumb trail,
ie the last thing sent to the pg_log file would've been a hint
about where future error messages would go ...)

            regards, tom lane

On 2015-09-19 17:55, David G. Johnston wrote:
> On Sat, Sep 19, 2015 at 12:29 PM, <richard@xentu.com> wrote:
>
>> Is that the file that gets generated in
>> /var/lib/postgresql/8.4/main/pg_log?
>
> ​I'm not sure what this ancient and no longer supported (by this
> community at least...) version of PostgreSQL​ does on Ubuntu but my
> unaltered 9.3 installation on 14.04 places the log file into
> "/var/log/postgresql".  I suspect yours does the same.
>
> David J.

Sorry, I was unclear in my description.

In the normal course of events, log files are written in the format

/var/lib/postgresql/8.4/main/pg_log/postgresql-2015-09-19_182328.log

It's only when the server fails to start, as I described, that no such
file is generated.

richard@xentu.com writes:
> Sorry, I was unclear in my description.
> In the normal course of events, log files are written in the format
> /var/lib/postgresql/8.4/main/pg_log/postgresql-2015-09-19_182328.log
> It's only when the server fails to start, as I described, that no such
> file is generated.

In that case, the error is happening before the server switches the
log destination away from its initial stderr.  You need to look at
the postmaster start script to see where it's sending stderr to
begin with.  (In Red Hat's scripts there's a fixed file used for such
early-startup output, but I don't know what Ubuntu does about it.)

Another idea would be to try launching the server by hand, ie just

    postmaster -D /path/to/data/directory

and see what happens.  With no redirection, the desired message should
just appear on your terminal.

            regards, tom lane

On 2015-09-19 19:57, Tom Lane wrote:
> richard@xentu.com writes:
>> Sorry, I was unclear in my description.
>> In the normal course of events, log files are written in the format
>> /var/lib/postgresql/8.4/main/pg_log/postgresql-2015-09-19_182328.log
>> It's only when the server fails to start, as I described, that no such
>> file is generated.
>
> In that case, the error is happening before the server switches the
> log destination away from its initial stderr.  You need to look at
> the postmaster start script to see where it's sending stderr to
> begin with.  (In Red Hat's scripts there's a fixed file used for such
> early-startup output, but I don't know what Ubuntu does about it.)
>
> Another idea would be to try launching the server by hand, ie just
>
>     postmaster -D /path/to/data/directory
>
> and see what happens.  With no redirection, the desired message should
> just appear on your terminal.
>
>             regards, tom lane


ok, doing that reveals the problem:

2015-09-20 10:18:26 BST   LOG:  loaded library
"/usr/lib/postgresql/8.4/lib/pg_stat_statements.so"
2015-09-20 10:18:26 BST   FATAL:  could not create shared memory
segment: Invalid argument
2015-09-20 10:18:26 BST   DETAIL:  Failed system call was
shmget(key=5432001, size=33710080, 03600).
2015-09-20 10:18:26 BST   HINT:  This error usually means that
PostgreSQL's request for a shared memory segment exceeded your kernel's
SHMMAX parameter.  You can either reduce the request size or reconfigure
the kernel with larger SHMMAX.  To reduce the request size (currently
33710080 bytes), reduce PostgreSQL's shared_buffers parameter (currently
3584) and/or its max_connections parameter (currently 103).
         If the request size is already small, it's possible that it is
less than your kernel's SHMMIN parameter, in which case raising the
request size or reconfiguring SHMMIN is called for.


In postgresql.conf I've changed shared_buffers from 28MB to 16MB & the
server will now start. I'm running postgresql on a host I'm renting with
limited resources.


Thanks to Tom & David for your help.

Richard