Postgres errors in Drupal install.

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 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

Dave and John

drupal is built on php
php is a binary which plays by the memory limitations posted in php.ini
php.ini:
memory_limit=32M ; Maximum amount of memory a script may consume (32MB)

crank that down if Drupal is exhausting heapspace
Martin Gainty
GMT+5 (this week)
______________________________________________
Jogi és Bizalmassági kinyilatkoztatás/Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Ez az
üzenet bizalmas. Ha nem ön az akinek szánva volt, akkor kérjük, hogy
jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának
készítése nem megengedett. Ez az üzenet csak ismeret cserét szolgál és
semmiféle jogi alkalmazhatósága sincs. Mivel az electronikus üzenetek
könnyen megváltoztathatóak, ezért minket semmi felelöség nem terhelhet
ezen üzenet tartalma miatt.

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.