Thread: PostgeSQL problem (server crashed?)
My PostgreSQL server is not reachable at this moment. It look likes a
crash from the postmaster proces.
Since saturday 13-03-2004 at 20.09 (Dutch Time / UTC+1) the server is
not working correctly. Before 20.09, the server was running perfectly.
I can't query the database. On the Postgres powered websites i see the
folowing errors.
Warning: pg_query(): Query failed: ERROR: syntax error at or near
"'5a55385afe83062516f465d5dbb23e96'" at character 64 . in
/home/epauli/WWW/qst-new/_inc/main.inc.php on line 9
Warning: pg_query(): Query failed: ERROR: syntax error at or near
"hoofdid" at character 8 . in
/home/epauli/WWW/qst-new/_inc/main.inc.php on line 81
Warning: pg_fetch_object(): supplied argument is not a valid
PostgreSQL result resource in
/home/epauli/WWW/qst-new/_inc/main.inc.php on line 89
On all of the pages where PostgreSQL is used, this error occured.
When i see this problem the first time, i've stopped the postmaster
proces directly.
After that, i trying to start the postmaster proces with pg_ctl.
Normaly, this start proces is done in about 1 or 2 seconds, but now
there is a timeout after 60 seconds.
With the -l option, i've tried to log the server output to
/var/log/pgsql, but i see nothing in that file after giving the pg_ctl
command and waiting for 60 seconds.
The permission from /var/log/pgsql is correct (owner:pgsql,
group:pgsql, perm:700)
There is only one message logged. That is in /var/log/messages
Mar 14 00:09:40 epauli postgres[27088]: [1-1] FATAL: the database
system is starting up
Mar 14 00:09:42 epauli postgres[27093]: [1-1] ERROR: syntax error at
or near "d" at character 8
Mar 14 00:09:43 epauli postgres[27097]: [1-1] ERROR: syntax error at
or near "d" at character 8
Mar 14 00:09:44 epauli postgres[27101]: [1-1] ERROR: syntax error at
or near "d" at character 8
This lines are repeated 60 times (1 minute)
The server is running PostgreSQL 7.4.1 on FreeBSD 4.9-RELEASE-p3
Does anybody know what's wrong?
--
E-mail : edwin@quicksteps.nl
: e.pauli@student.hhs.nl
Website : http://www.quicksteps.nl/
Edwin Pauli <edwin@epauli.dyndns.org> writes:
> My PostgreSQL server is not reachable at this moment. It look likes a
> crash from the postmaster proces.
There is nothing in what you report to indicate that there is anything
wrong with the server at all. It looks to me like you have client
programs that are issuing bogus queries, but you need to go fix that
on the client side; it's hardly the server's fault.
> Warning: pg_query(): Query failed: ERROR: syntax error at or near
> "'5a55385afe83062516f465d5dbb23e96'" at character 64 . in
> /home/epauli/WWW/qst-new/_inc/main.inc.php on line 9
Syntax error in client-generated query.
> Warning: pg_query(): Query failed: ERROR: syntax error at or near
> "hoofdid" at character 8 . in
> /home/epauli/WWW/qst-new/_inc/main.inc.php on line 81
Ditto.
> Warning: pg_fetch_object(): supplied argument is not a valid
> PostgreSQL result resource in
> /home/epauli/WWW/qst-new/_inc/main.inc.php on line 89
Internal problem in PHP (possibly an indication of code that isn't
checking for query failure).
> When i see this problem the first time, i've stopped the postmaster
> proces directly.
> After that, i trying to start the postmaster proces with pg_ctl.
Unnecessary intervention.
> There is only one message logged. That is in /var/log/messages
> Mar 14 00:09:40 epauli postgres[27088]: [1-1] FATAL: the database
> system is starting up
> Mar 14 00:09:42 epauli postgres[27093]: [1-1] ERROR: syntax error at
> or near "d" at character 8
> Mar 14 00:09:43 epauli postgres[27097]: [1-1] ERROR: syntax error at
> or near "d" at character 8
> Mar 14 00:09:44 epauli postgres[27101]: [1-1] ERROR: syntax error at
> or near "d" at character 8
> This lines are repeated 60 times (1 minute)
You've got some client process that is connecting and issuing a bogus
query once a minute. The first of these messages occurred because it
tried to do so while the database was still restarting. However, the
restart clearly completed without incident, because we see fresh backend
sessions running to issue the later messages.
> Does anybody know what's wrong?
Nothing on the postgres side, AFAICS. Possibly you need a PHP guru.
You might also try turning on log_statement so you can see the full text
of the invalid SQL commands in the log; that might help track down the
source.
regards, tom lane
On Sunday 14 March 2004 00:34, Edwin Pauli wrote: > My PostgreSQL server is not reachable at this moment. It look likes a > crash from the postmaster proces. > > Since saturday 13-03-2004 at 20.09 (Dutch Time / UTC+1) the server is > not working correctly. Before 20.09, the server was running perfectly. To follow up to Tom's message, you need to look at two things: 1. What happened at 20:09 (and *something* must have) 2. Does pg_ctl start up the server and can you access it from psql? -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > To follow up to Tom's message, you need to look at two things: > 1. What happened at 20:09 (and *something* must have) The only thing i can see is that at 20.19 (20.09 was a typo, but it doesn't matter) all of the query are result in a error. Before 20.19, there are no errors from PostgreSQL in /var/log/messages. Here is the message log (/var/log/messages) Mar 13 20:18:31 epauli dhcpd: Dynamic and static leases present for 192.168.1.4. Mar 13 20:18:31 epauli dhcpd: Remove host declaration Dennis or remove 192.168.1.4 Mar 13 20:18:31 epauli dhcpd: from the dynamic address pool for 192.168.1.0/24 Mar 13 20:19:29 epauli postgres[23313]: [1-1] ERROR: syntax error at or near "'d41261f4c273da146b119b225df7849a'" at character 64 Mar 13 20:19:29 epauli postgres[23313]: [2-1] ERROR: syntax error at or near "hoofdid" at character 8 Mar 13 20:19:29 epauli postgres[23313]: [3-1] ERROR: syntax error at or near "TO_CHAR" at character 17 Mar 13 20:19:29 epauli postgres[23313]: [4-1] ERROR: syntax error at or near "TO_CHAR" at character 17 Mar 13 20:19:29 epauli postgres[23313]: [5-1] ERROR: syntax error at or near "klasse_id" at character 17 > 2. Does pg_ctl start up the server and can you access it from psql? No, it doesn't startup A big problem is that pg_ctl don't log the error, also when de log facility in the postgres conf is enabled. When i copy a backup from 3 weeks ago to /usr/local/pgsql, the server can startup and working correctly. Maybe the database is corrupt? -- Edwin Pauli
On Sun, 14 Mar 2004 20:53:00 +0100 Edwin Pauli <edwin@epauli.dyndns.org> sat down, thought long and then wrote: > Richard Huxton wrote: > > To follow up to Tom's message, you need to look at two things: > > 1. What happened at 20:09 (and *something* must have) > > The only thing i can see is that at 20.19 (20.09 was a typo, but it > doesn't matter) all of the query are result in a error. Before 20.19, > there are no errors from PostgreSQL in /var/log/messages. > > Here is the message log (/var/log/messages) > > Mar 13 20:18:31 epauli dhcpd: Dynamic and static leases present for > 192.168.1.4. > Mar 13 20:18:31 epauli dhcpd: Remove host declaration Dennis or remove > 192.168.1.4 > Mar 13 20:18:31 epauli dhcpd: from the dynamic address pool for > 192.168.1.0/24 > Mar 13 20:19:29 epauli postgres[23313]: [1-1] ERROR: syntax error at > or near "'d41261f4c273da146b119b225df7849a'" at character 64 > Mar 13 20:19:29 epauli postgres[23313]: [2-1] ERROR: syntax error at > or near "hoofdid" at character 8 > Mar 13 20:19:29 epauli postgres[23313]: [3-1] ERROR: syntax error at > or near "TO_CHAR" at character 17 > Mar 13 20:19:29 epauli postgres[23313]: [4-1] ERROR: syntax error at > or near "TO_CHAR" at character 17 > Mar 13 20:19:29 epauli postgres[23313]: [5-1] ERROR: syntax error at > or near "klasse_id" at character 17 > > > 2. Does pg_ctl start up the server and can you access it from psql? > > No, it doesn't startup > A big problem is that pg_ctl don't log the error, also when > de log facility in the postgres conf is enabled. > > When i copy a backup from 3 weeks ago to /usr/local/pgsql, the server > can startup and working correctly. > Maybe the database is corrupt? The error messages above have nothing in common with a corrupt database. These are faulty queries. I recommend to shut down the webserver and (re)start the database engine with statement logging enabled (not to messages but to a sparate logfile), and afterwards start the webserver again, to see, what query causes these errors. Did you check your script home/epauli/WWW/qst-new/_inc/main.inc.php very carefully? Maybe, if you show us the queries and, if possible, the corresponding table structure, we can find out more. Regards, Frank.
Frank Finner wrote:
> The error messages above have nothing in common with a corrupt database. These are faulty queries. I
> recommend to shut down the webserver and (re)start the database engine with statement logging
> enabled (not to messages but to a sparate logfile), and afterwards start the webserver again, to
> see, what query causes these errors.
I've shutdown the Apache-webserver, increased the log level from
Postgres and log everything to /usr/local/pgsql/errlog. After that, i
tried to start the Postgres server with the following command:
# su -l pgsql /usr/local/bin/pg_ctl -w start > /usr/local/pgsql/errlog
2>&1 &
epauli# cat errlog
DEBUG: /usr/local/bin/postmaster: PostmasterMain: initial environ dump:
waiting for postmaster to start...DEBUG:
-----------------------------------------
.DEBUG: USER=pgsql
DEBUG: MAIL=/var/mail/pgsql
DEBUG: HOME=/usr/local/pgsql
DEBUG: PGLIB=/usr/local/lib
DEBUG: TERM=xterm-color
DEBUG: BLOCKSIZE=K
DEBUG:
PATH=/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/games:/usr/local/sbin:/usr/local/bin:/usr/X11R6/bin:/usr/local/pgsql/bin
DEBUG: SHELL=/bin/sh
DEBUG: PGDATA=/usr/local/pgsql/data
DEBUG: FTP_PASSIVE_MODE=YES
DEBUG: -----------------------------------------
DEBUG: found "/usr/local/bin/postgres" using argv[0]
DEBUG: invoking IpcMemoryCreate(size=8970240)
DEBUG: found "/usr/local/bin/postmaster" using argv[0]
............................................................failed
pg_ctl: postmaster does not start
Everything other than the last line look good.
The server don't start
> Did you check your script
> home/epauli/WWW/qst-new/_inc/main.inc.php very carefully?
Yes. Everythings look good. This PHP script was changed at Mar 6
15:27, and the problem was begin saturday (Mar 13) at 20.19.
> Maybe, if you show us the queries and, if possible, the corresponding table structure, we can find
> out more.
This are the query where the error occures.
INSERT INTO sessies ( phpsessid, tijd, ipadres, waar ) VALUES
('".session_id()."','now()','".$_SERVER['REMOTE_ADDR']."','".$pagina2[1]."')";
The variable $pagina2 is created as follow:
$pagina1 = $_SERVER[PHP_SELF];
$pagina2 = split ( "/",$pagina1 );
This query running a few months without problems, so i think it's not
a problem with PHP, but with Postgres. Or is this opinion wrong?
--
Edwin Pauli
Edwin Pauli <edwin@epauli.dyndns.org> writes:
> DEBUG: found "/usr/local/bin/postgres" using argv[0]
> DEBUG: invoking IpcMemoryCreate(size=8970240)
> DEBUG: found "/usr/local/bin/postmaster" using argv[0]
> ............................................................failed
> pg_ctl: postmaster does not start
Hm. This is the first thing you've shown us that looked even remotely
like a server problem. Have you been changing things around since your
previous reports?
Anyway, it looks to me like the next interesting thing that happens
after those messages is pgstat_init(), which will try to open a UDP
port on address "localhost". If there's something munged about your
DNS setup, perhaps that could fail (though I'm not sure why it would
fail without any error message).
You might try turning off the stats collector altogether --- set
"stats_start_collector" off in postgresql.conf (and make sure
that stats_command_string, stats_row_level, stats_block_level are
not turned on). Does it get any further if you do that?
Also, I'm not aware of any reason that the postmaster would exit
without printing an error message --- unless it crashed completely,
and in that case it should leave a core file. Do you see any core
file? If so, can you get a stack trace from it?
regards, tom lane
Tom Lane wrote: > Anyway, it looks to me like the next interesting thing that happens > after those messages is pgstat_init(), which will try to open a UDP > port on address "localhost". If there's something munged about your > DNS setup, perhaps that could fail (though I'm not sure why it would > fail without any error message). There runs no DNS server on this box. I use the DNS server from my ISP. epauli# cat /etc/resolv.conf search adsl.cistron.nl nameserver 62.216.31.50 nameserver 62.216.31.60 > You might try turning off the stats collector altogether --- set > "stats_start_collector" off in postgresql.conf (and make sure > that stats_command_string, stats_row_level, stats_block_level are > not turned on). Does it get any further if you do that? No. The error messages are the same with that options off. > Also, I'm not aware of any reason that the postmaster would exit > without printing an error message --- unless it crashed completely, > and in that case it should leave a core file. Do you see any core > file? If so, can you get a stack trace from it? Yes. In /usr/local/pgsql there is a postgres.core I've put a strace on my webspace. http://epauli.dyndns.org/strace Very trange is that the server will start without any problem when i copy a 3 weeks old backup to /usr/local/pgsql/ Later today i will look further to the problem and make a strace from pg_ctl with the setup from 3 weeks ago. Thanks for the help! -- Edwin Pauli
On Mon, 15 Mar 2004 08:26:14 +0100 Edwin Pauli <edwin@epauli.dyndns.org> sat down, thought long and then wrote: > Very trange is that the server will start without any problem when i > copy a 3 weeks old backup to /usr/local/pgsql/ > Seems we have been hunting the wrong bugs in the beginning. Could it be possible that there was a major version update of the database engine (7.2.x -> 7.4.x for example), without a database reload? I have noticed several new "features" for queries, I think, 7.4 is much closer to SQL standards than 7.3, which, in turn, is closer than 7.2, especially regarding the necessity of type casts. In one of our applications we had to rewrite most of the queries, because they did not work properly with newer PostgreSQL releases. So that could have caused the PHP-Errors, and the restart of the database without a dump/restore could then cause the start up failure. Just a theory. Well, if there was no database engine update, this theory is rubbish, of course. It could also be a simple coincidence, that there were query errors and server problems at the same time. Did you compare the actual binaries and the binaries of the backup? Are they identical? Regards, Frank.
On Sun, 14 Mar 2004, Edwin Pauli wrote: > My PostgreSQL server is not reachable at this moment. It look likes a > crash from the postmaster proces. > > Since saturday 13-03-2004 at 20.09 (Dutch Time / UTC+1) the server is > not working correctly. Before 20.09, the server was running perfectly. > > I can't query the database. On the Postgres powered websites i see the > folowing errors. > > Warning: pg_query(): Query failed: ERROR: syntax error at or near > "'5a55385afe83062516f465d5dbb23e96'" at character 64 . in > /home/epauli/WWW/qst-new/_inc/main.inc.php on line 9 > > Warning: pg_query(): Query failed: ERROR: syntax error at or near > "hoofdid" at character 8 . in > /home/epauli/WWW/qst-new/_inc/main.inc.php on line 81 > > Warning: pg_fetch_object(): supplied argument is not a valid > PostgreSQL result resource in > /home/epauli/WWW/qst-new/_inc/main.inc.php on line 89 I'm going to make a wild assed guess that the flexer / parser or something it depends on in the database is corrupted. You said in a later post that reinstalling an old backup made it work. Is this a file level or pg_dump type backup? If it's a file level, that would support my supposition that something in the system tables used by the parser is corrupted. You might have a bad sector on your harddrive or something like that. But it's just a guess.
Frank Finner wrote:
> Seems we have been hunting the wrong bugs in the beginning. Could it be possible that there was a
> major version update of the database engine (7.2.x -> 7.4.x for example), without a database reload?
> I have noticed several new "features" for queries, I think, 7.4 is much closer to SQL standards than
> 7.3, which, in turn, is closer than 7.2, especially regarding the necessity of type casts. In one of
> our applications we had to rewrite most of the queries, because they did not work properly with
> newer PostgreSQL releases. So that could have caused the PHP-Errors, and the restart of the database
> without a dump/restore could then cause the start up failure. Just a theory.
3 weeks ago, i've upgraded my PostgreSQL server from 7.2.4 to 7.4.1.
In 7.2 i've dumped the tables with pg_dump and after the upgrade the
tables are reloaded with psql.
> Did you compare the actual binaries and the binaries of the backup? Are they identical?
There are no Postgres binaries in /usr/local/pgsql, but in
/usr/local/bin.
Because i've only copied /usr/local/pgsql, the binaries are no changed.
--
E-mail : edwin@quicksteps.nl
: e.pauli@student.hhs.nl
Website : http://www.quicksteps.nl/
On Monday 15 March 2004 18:49, Edwin Pauli wrote: > There are no Postgres binaries in /usr/local/pgsql, but in > /usr/local/bin. > Because i've only copied /usr/local/pgsql, the binaries are no changed. If you're binaries work with the old data but not the new, then they are old binaries. You cannot use binaries from different major versions (e.g. 7.2 vs 7.3 vs 7.4). -- Richard Huxton Archonet Ltd
Richard Huxton wrote:
> On Monday 15 March 2004 18:49, Edwin Pauli wrote:
>
>>There are no Postgres binaries in /usr/local/pgsql, but in
>>/usr/local/bin.
>>Because i've only copied /usr/local/pgsql, the binaries are no changed.
>
>
> If you're binaries work with the old data but not the new, then they are old
> binaries. You cannot use binaries from different major versions (e.g. 7.2 vs
> 7.3 vs 7.4).
I don't use binaries from 7.2 or 7.3.
The tables are created in 7.4 and the binaries are from 7.4.
The backup from 3 weeks ago is also from 7.4
Edwin Pauli
--
E-mail : edwin@quicksteps.nl
: e.pauli@student.hhs.nl
Website : http://www.quicksteps.nl/
Edwin Pauli wrote: > I've put a strace on my webspace. > http://epauli.dyndns.org/strace I've never used the strace command. I see no crazy things in the strace, is that opinion true? -- Edwin Pauli