Thread: Postgres errors in Drupal install.

I'm having huge problems with a Drupal install using Postgres 8.4.8.

I'm getting the following error:

PDOException: SQLSTATE[08006] [7] could not connect to server:
Connection refused Is the server running on host "localhost" and
accepting TCP/IP connections on port 5432? in lock_may_be_available()
(line 164 of /var/www/includes/lock.inc).

The Site is under minimal load with possibly two or three people on it at once.

Can anyone tell me why I get the error every hour or so?

And what steps I could take to remedy this?

Many thanks,

Dave Coventry

On Sunday, June 26, 2011 12:36:59 pm Dave Coventry wrote:
> I'm having huge problems with a Drupal install using Postgres 8.4.8.
>
> I'm getting the following error:
>
> PDOException: SQLSTATE[08006] [7] could not connect to server:
> Connection refused Is the server running on host "localhost" and
> accepting TCP/IP connections on port 5432? in lock_may_be_available()
> (line 164 of /var/www/includes/lock.inc).
>
> The Site is under minimal load with possibly two or three people on it at
> once.
>
> Can anyone tell me why I get the error every hour or so?

A user or machine is not authorized in pg_hba.conf?

>
> And what steps I could take to remedy this?

Look at pg_hba.conf.  Look at the Drupal/Postgres logs to try to see who or what
is trying to connect at those times.

>
> Many thanks,
>
> Dave Coventry

--
Adrian Klaver
adrian.klaver@gmail.com

On Sun, Jun 26, 2011 at 09:36:59PM +0200, Dave Coventry wrote:
> I'm having huge problems with a Drupal install using Postgres 8.4.8.
>
> I'm getting the following error:

Do you get the error in the logs or do you see it while going to the
site manually and does it show this instead of rendering a page?

If it's in the logs, and you're running cron or poorman's cron once
an hour, it could be that's where these messages are coming from.

You can run cron manually from the interface somewhere, so you
can try if you can reproduce the error more reliably and work from
there.

Cheers,
Peter
--
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
                            -- Donald Knuth

Thank you very much for your responses.

On 26 June 2011 21:54, Peter Bex <Peter.Bex@xs4all.nl> wrote:
> Do you get the error in the logs or do you see it while going to the
> site manually and does it show this instead of rendering a page?

The log don't really give much indication of anything untoward
happening. It's really that the page stops rendering and displays the
message.

> If it's in the logs, and you're running cron or poorman's cron once
> an hour, it could be that's where these messages are coming from.
>
> You can run cron manually from the interface somewhere, so you
> can try if you can reproduce the error more reliably and work from
> there.

It doesn't seem quite regular enough to be part of the cron job.
Having said that the errors below do make reference to cron.

----------------------------------- snip
--------------------------------------------------
2011-06-26 15:57:15 EDT STATEMENT:  SELECT base.fid AS fid, base.uid
AS uid, base.filename AS filename, base.uri AS uri, base.filemime AS
filemime, base.filesize AS filesize, base.status AS status,
base.timestamp AS timestamp
    FROM
    file_managed base
    WHERE  (base.fid IN  ('0', 'Upload', 'Remove', ''))
2011-06-26 15:57:15 EDT WARNING:  nonstandard use of \\ in a string
literal at character 185
2011-06-26 15:57:15 EDT HINT:  Use the escape string syntax for
backslashes, e.g., E'\\'.
2011-06-26 15:57:30 EDT WARNING:  nonstandard use of \\ in a string
literal at character 115
2011-06-26 15:57:30 EDT HINT:  Use the escape string syntax for
backslashes, e.g., E'\\'.
2011-06-26 15:57:31 EDT WARNING:  nonstandard use of \\ in a string
literal at character 256
2011-06-26 15:57:31 EDT HINT:  Use the escape string syntax for
backslashes, e.g., E'\\'.
2011-06-26 15:57:31 EDT WARNING:  nonstandard use of \\ in a string
literal at character 248
2011-06-26 15:57:31 EDT HINT:  Use the escape string syntax for
backslashes, e.g., E'\\'.
2011-06-26 15:57:31 EDT WARNING:  nonstandard use of \\ in a string
literal at character 256
2011-06-26 15:57:31 EDT HINT:  Use the escape string syntax for
backslashes, e.g., E'\\'.
2011-06-26 15:57:31 EDT WARNING:  nonstandard use of \\ in a string
literal at character 248
2011-06-26 15:57:31 EDT HINT:  Use the escape string syntax for
backslashes, e.g., E'\\'.
2011-06-26 15:57:31 EDT ERROR:  duplicate key value violates unique
constraint "semaphore_pkey"
2011-06-26 15:57:31 EDT STATEMENT:  INSERT INTO semaphore (name,
value, expire) VALUES ('cron', '2399653214e078f2b5a48e5.75113719',
'1309118491.3697')
----------------------------------- snip
--------------------------------------------------

Here are the messages in the logs of the last crash which might throw
a bit of light onto the subject for anyone rather more knowledgeable
than I:
----------------------------------- snip
--------------------------------------------------
2011-06-26 17:13:24 EDT LOG:  background writer process (PID 505) was
terminated by signal 9: Killed
2011-06-26 17:13:24 EDT LOG:  terminating any other active server processes
2011-06-26 17:13:24 EDT WARNING:  terminating connection because of
crash of another server process
2011-06-26 17:13:24 EDT DETAIL:  The postmaster has commanded this
server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2011-06-26 17:13:24 EDT HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2011-06-26 17:13:24 EDT LOG:  all server processes terminated; reinitializing
2011-06-26 17:13:24 EDT FATAL:  could not create shared memory
segment: Cannot allocate memory
2011-06-26 17:13:24 EDT DETAIL:  Failed system call was
shmget(key=5432001, size=29278208, 03600).
2011-06-26 17:13:24 EDT HINT:  This error usually means that
PostgreSQL's request for a shared memory segment exceeded available
memory or swap space. To reduce the request size (currently 29278208
bytes), reduce PostgreSQL's shared_buffers parameter (currently 3072)
and/or its max_connections parameter (currently 103).
    The PostgreSQL documentation contains more information about shared
memory configuration.
----------------------------------- snip
--------------------------------------------------

While the server is a little challenged in the way of RAM, it is by no
means under any sort of load.

On Sun, Jun 26, 2011 at 11:23:30PM +0200, Dave Coventry wrote:
[snip]
> 2011-06-26 17:13:24 EDT DETAIL:  Failed system call was
> shmget(key=5432001, size=29278208, 03600).
> 2011-06-26 17:13:24 EDT HINT:  This error usually means that
> PostgreSQL's request for a shared memory segment exceeded available
> memory or swap space. To reduce the request size (currently 29278208
> bytes), reduce PostgreSQL's shared_buffers parameter (currently 3072)
> and/or its max_connections parameter (currently 103).
>     The PostgreSQL documentation contains more information about shared
> memory configuration.

This sounds like you should tweak those parameters. See the relevant section:
http://www.postgresql.org/docs/current/interactive/kernel-resources.html#SYSVIPC

> While the server is a little challenged in the way of RAM, it is by no
> means under any sort of load.

Drupal is a huge memory hog.  It's quite possible that it consumes so
much memory that it doesn't leave enough for postgres.  If at all
possible, try increasing the amount of memory available.

Cheers,
Peter
--
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
                            -- Donald Knuth

On 06/26/11 2:23 PM, Dave Coventry wrote:
> While the server is a little challenged in the way of RAM, it is by no
> means under any sort of load.

I didn't read the whole thread, but is this a Linux server?   Linux has
a horrible feature, the "Out of Memory Killer", which kills off
processes when the system is low on memory.

the OOM-Killer usually leaves log entries in DMESG and maybe
/var/log/messages

If in fact its the OOM killer, you should probably lower your WORK_MEM
and SHARED_BUFFERS and maybe MAINTENANCE_WORK_MEM in postgresql.conf

--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

Apologies, I didn't notice that I was replying off-list.

On 27 June 2011 00:29, Dave Coventry <dgcoventry@gmail.com> wrote:
> On 26 June 2011 23:37, John R Pierce <pierce@hogranch.com> wrote:
>> I didn't read the whole thread, but is this a Linux server?   Linux has a
>> horrible feature, the "Out of Memory Killer", which kills off processes when
>> the system is low on memory.
>>
>> the OOM-Killer usually leaves log entries in DMESG and maybe
>> /var/log/messages
>
> Yes, it's a Ubuntu 10.10 server. I don't actually see anything in
> dmesg or in /var/log/messages that would indicate indiscriminate
> culling of processes.
>
>> If in fact its the OOM killer, you should probably lower your WORK_MEM and
>> SHARED_BUFFERS and maybe MAINTENANCE_WORK_MEM in postgresql.conf
>
> Thanks for the suggestions. I have pulled them all down to their
> recommended minimums:
>
> work_mem: 64kB
> shared_buffers: 128kB
> maintenance_work_mem: 1MB
>
> I'll see if that makes any difference.
>

On 06/26/11 4:10 PM, Dave Coventry wrote:
>
>> work_mem: 64kB
>> shared_buffers: 128kB
>> maintenance_work_mem: 1MB
>>
>> I'll see if that makes any difference.

I'd watch the systems' memory usage as shown by `top` or `free`... as
long as the 'cached' value (add in 'free' if its significant) stays
reasonably large (say, 25-50% of your total ram), you're in good shape.
using values as small as the defaults likely will degrade performance of
the queries.    postgres will behave somewhat more optimally if the
'estimated_cache_size' is near the 'cached' value.


--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast