Postgresql – weird postgresql log entries

loggingpostgresql

I am trying to figure out why I get some weird entries in my postgresql log after I do a restart:

2010-05-14 11:30:25 EEST LOG:  database system was shut down at 2010-05-14 11:30:22 EEST
2010-05-14 11:30:25 EEST LOG:  autovacuum launcher started
2010-05-14 11:30:25 EEST LOG:  database system is ready to accept connections
2010-05-14 11:30:25 EEST LOG:  incomplete startup packet
2010-05-14 11:30:40 EEST WARNING:  there is already a transaction in progress
2010-05-14 11:30:40 EEST LOG:  could not receive data from client: Connection reset by peer
2010-05-14 11:30:40 EEST LOG:  unexpected EOF on client connection

First, there's the 2010-05-14 11:30:25 EEST LOG: incomplete startup packet which bugs me. Anyone has any idea why this happens?

And also, this one is very strange: 2010-05-14 11:30:40 EEST WARNING: there is already a transaction in progress …

Best Answer

incomplete startup packet means a connection was established but the Postgres server didn't get the handshake it was expecting (I get a ton of these as my monitoring system checks to make sure port 5432 is open but isn't smart enough to actually log in to & query the DB).
Whether or not this is a problem depends on if you expect to have something connecting and not handshaking: For me seeing it once every 5 minutes isn't an issue, but if you're not expecting it this message could be an indication that you're being port scanned.

WARNING: there is already a transaction in progress means exactly what it says: Someone tried to start a transaction while already in a transaction (BEGIN ... BEGIN).
If you see this a lot somebody wrote some bad SQL in a program and you should find/fix it because the corresponding COMMIT and ROLLBACK statements may not do what you expect. If you see it once and never again someone probably fat-fingered a transaction in psql.

Related Solutions

Postgresql – How to see active connections and “current activity” in PostgreSQL 8.4

OK, got it from someone else. This query should do the trick:

select *
from pg_stat_activity
where datname = 'mydatabasename';

Postgresql spawning a ridiculous number of postmaster processes

Postgres spawns a postmaster for every connection. This is the way it's supposed to work. If you're spawning a ton of postmaster processes you have something initiating a ton of connections, and if that's not what you expect you probably have a broken/badly-behaved application creating a mess (a common example: One that keeps re-initializing the DB connection, but never closes it. You'll leak a session for every connection initialization).

If this is recent, start with "What Changed?" If not, start looking at all your apps that talk to Postgres (roughly in order of usage volume) and see if you can find the bad apple.

Re: your log messages --

unexpected EOF on client connection
Something caused an established Postgres connection to die without properly closing (the postmaster may hang around for a while cleaning up the resulting mess). Find out what caused the connection to die (did the process initiating it crash? Is the application poorly-written and exiting before cleaning up its DB handles?) and fix it
incomplete startup packet
Something connected to Postgres and didn't initiate a proper Postgres handshake/startup. Usually the resulting postmaster goes away in a few seconds.
Find what's doing this and fix it. (This could be your monitoring system, someone port-scanning your network, or a badly written application somewhere)
sorry, too many clients already
I think this one is self-explanatory. Fix the above and it will probably go away, otherwise consider increasing max_connections in postgresql.conf.

Best Answer

Related Solutions

Postgresql – How to see active connections and “current activity” in PostgreSQL 8.4

Postgresql spawning a ridiculous number of postmaster processes

Related Topic