Обсуждение: pg_dump causes postgres crash

Fairly new (less than a week) install.
"PostgreSQL 8.2.4 on i386-pc-solaris2.10, compiled by
GCC gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath)"

database size around 43 gigabytes.

2 attempts at a pg_dump across the network caused the
database to go down...

The first time I thought it was because of mismatched
pg_dump (was version 8.0.X)...but the second time it
was definitely 8.2.4 version of pg_dump.

My first thought was corruption...but this database
has successfully seeded 2 slony subscriber nodes from
scratch as well running flawlessly under heavy load
for the past week.

Even more odd is that a LOCAL pg_dump (from on the
box) succeeded just fine tonight (after the second
crash).

Thoughts?

----First Crash-------

backup-srv2 prod_backup # time /usr/bin/pg_dump
--format=c --compress=9 --ignore-version
--username=backup --host=prod_server prod > x

pg_dump: server version: 8.2.4; pg_dump version:
8.0.13
pg_dump: proceeding despite version mismatch
pg_dump: WARNING:  terminating connection because of
crash of another server process
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.
HINT:  In a moment you should be able to reconnect to
the database and repeat your command.
pg_dump: server closed the connection unexpectedly
       This probably means the server terminated
abnormally
       before or while processing the request.
pg_dump: SQL command to dump the contents of table
"access_logs" failed: PQendcopy() failed.
pg_dump: Error message from server: server closed the
connection unexpectedly
       This probably means the server terminated
abnormally
       before or while processing the request.
pg_dump: The command was: COPY public.access_logs (ip,
username, "action", date, params) TO stdout;


------Second Crash--------

backup-srv2 ~ # time /usr/bin/pg_dump --format=c
--compress=9  --username=backup --host=prod_server
prod | wc -l
pg_dump: Dumping the contents of table "audit" failed:
PQgetCopyData() failed.
pg_dump: Error message from server: server closed the
connection unexpectedly
       This probably means the server terminated
abnormally
       before or while processing the request.
pg_dump: The command was: COPY public.audit (audit_id,
entity_id, table_name, serial_id, audit_action,
when_ts, user_id, user_ip) TO stdout;









____________________________________________________________________________________
Moody friends. Drama queens. Your life? Nope! - their life, your story. Play Sims Stories at Yahoo! Games.
http://sims.yahoo.com/

From the logs tonight when the second crash occurred..

Aug 22 20:45:12 db-1 postgres[5805]: [ID 748848
local0.info] [6-1] 2007-08-22 20:45:12 CDT   LOG:
received smart shutdown request
Aug 22 20:45:12 db-1 postgres[5805]: [ID 748848
local0.info] [7-1] 2007-08-22 20:45:12 CDT   LOG:
server process (PID 20188) was terminated by signal 11
Aug 22 20:45:12 db-1 postgres[5805]: [ID 748848
local0.info] [8-1] 2007-08-22 20:45:12 CDT   LOG:
terminating any other active server processes

There was a core file created...but I believe I do not
have postgresql compiled with debug info.....(well, a
pstack provided nothing useful)
pstack core  |more
core 'core' of 20188:   /usr/local/pgsql/bin/postgres
-D /db
 fee8ec23 sk_value (10023d, 105d8b00, d2840f,
1c7f0000, f20f883, 10584) + 33
 0c458b51 ???????? (0, 0, 511f1600, 2000400, ff001c09,
467f71ea)
 00000000 ???????? ()

Once again...a local pg_dump worked just fine 30
minutes later......

We have introduced some new network architecture which
is acting odd lately (dell managed switches, netscreen
ssgs, etc) and the database itself resides on a zfs
partition on a Pillar SAN (connected via fibre
channel)

Any thoughts would be appreciated.



____________________________________________________________________________________
Pinpoint customers who are looking for what you sell.
http://searchmarketing.yahoo.com/

Jeff Amiel <becauseimjeff@yahoo.com> writes:
> Even more odd is that a LOCAL pg_dump (from on the
> box) succeeded just fine tonight (after the second
> crash).

That seems to eliminate the theory of a crash due to data corruption
... unless the corruption somehow repaired itself in the intervening
30 minutes, which hardly seems likely.

> ----First Crash-------

> backup-srv2 prod_backup # time /usr/bin/pg_dump
> --format=c --compress=9 --ignore-version
> --username=backup --host=prod_server prod > x
> pg_dump: server version: 8.2.4; pg_dump version:
> 8.0.13
> pg_dump: proceeding despite version mismatch
> pg_dump: WARNING:  terminating connection because of
> crash of another server process
> 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.

Notice that pg_dump is showing that the crash was in some OTHER server
process, not the one it was attached to.

> ------Second Crash--------

> backup-srv2 ~ # time /usr/bin/pg_dump --format=c
> --compress=9  --username=backup --host=prod_server
> prod | wc -l
> pg_dump: Dumping the contents of table "audit" failed:
> PQgetCopyData() failed.
> pg_dump: Error message from server: server closed the
> connection unexpectedly
>        This probably means the server terminated
> abnormally
>        before or while processing the request.
> pg_dump: The command was: COPY public.audit (audit_id,

This one looks more like it might have been the directly connected
server process that crashed.  However, your postmaster log from
the other message:

> From the logs tonight when the second crash occurred..
> Aug 22 20:45:12 db-1 postgres[5805]: [ID 748848
> local0.info] [6-1] 2007-08-22 20:45:12 CDT   LOG:
> received smart shutdown request
> Aug 22 20:45:12 db-1 postgres[5805]: [ID 748848
> local0.info] [7-1] 2007-08-22 20:45:12 CDT   LOG:
> server process (PID 20188) was terminated by signal 11
> Aug 22 20:45:12 db-1 postgres[5805]: [ID 748848
> local0.info] [8-1] 2007-08-22 20:45:12 CDT   LOG:
> terminating any other active server processes

raises still more questions: where the heck did the "smart shutdown
request" (that is to say, a SIGTERM interrupt to the postmaster) come
from?  It's far too much of a coincidence for that to have occurred
within a second of detecting the server process crash.

> We have introduced some new network architecture which
> is acting odd lately (dell managed switches, netscreen
> ssgs, etc) and the database itself resides on a zfs
> partition on a Pillar SAN (connected via fibre
> channel)

I can't help thinking you are looking at generalized system
instability.  Maybe someone knocked a few cables loose while
installing new network hardware?

            regards, tom lane

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> I can't help thinking you are looking at generalized
> system
> instability.  Maybe someone knocked a few cables
> loose while
> installing new network hardware?

Database server/storage instability or network
instability?

There is no doubt that there is something flaky about
the networking between the db server and the box(es)
trying to do the pg_dump.  We have indeed had issues
(timeouts, halts, etc) moving large quantities of data
across various segments to and from these boxes...like
the db server....but how would this effect something
like a pg_dump?

Would a good stack trace (assuming I want to crash my
database again) help here?





____________________________________________________________________________________Ready for the edge of your seat?
Check out tonight's top picks on Yahoo! TV.
http://tv.yahoo.com/

Jeff Amiel <becauseimjeff@yahoo.com> writes:
> Would a good stack trace (assuming I want to crash my
> database again) help here?

Well, it'd be more information than we have now ...

            regards, tom lane