remote pg_dump hangs always at same table

From: Axel Rau <Axel(dot)Rau(at)chaos1(dot)de>
To: pgsql-admin(at)postgresql(dot)org
Subject: remote pg_dump hangs always at same table
Date: 2010-03-13 12:24:50
Message-ID: 63A3189E-E6F9-491B-91CF-E907EF9C8C6C@chaos1.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi all,

I have a daily remote backup session like
pg_dump -h db -i -Fp database | gzip > file
which hangs after producing about 200 MB of compressed output:
----------
TIME CMD
0:00.05 /usr/local/pgsql/bin/pg_dumpall -h db -i
0:21.45 /usr/local/pgsql/bin/pg_dump -h db -i -Fp database
----------
It happens always during copy of this table:
------------
Table "archiveopteryx.bodyparts"
Column | Type | Modifiers
--------+---------+----------------------------------------------------
id | integer | not null default nextval('bodypart_ids'::regclass)
bytes | integer | not null
hash | text | not null
text | text |
data | bytea |
Indexes:
"bodyparts_pkey" PRIMARY KEY, btree (id)
"b_h" btree (hash)
Referenced by:
TABLE "unparsed_messages" CONSTRAINT
"unparsed_messages_bodypart_fkey" FOREIGN KEY (bodypart) REFERENCES
bodyparts(id) ON DELETE CASCADE
-----------
Interesting: I have a test and a production database with the same
schema but different data and it happens with both databases at
exactly this table.

There is no problem to dump the databases locally on the server and
fetch the dump from the client via scp.

Versions: Server: 8.3.6 on FreeBSD 7.0; Client: 8.3.7 on darwin 9.8.0.
Network: 2 adjacent LANs via firewall (OpenBSD 4.6/pf). State table
looks ok; extract:
---------
em0 tcp xxx.yyy.zzz.10:5432 <- .10:63169 ESTABLISHED:ESTABLISHED
[615902410 + 524280] wscale 3 [396592834 + 66176] wscale 3
age 10:47:05, expires in 23:15:07, 199433:386306 pkts,
10820556:561637949 bytes, rule 77
id: 4b8ecbd8001ddfbd creatorid: cd2329ff
dc0 tcp uuu.vvv.www.10:63169 -> xxx.yyy.zzz.10:5432
ESTABLISHED:ESTABLISHED
[396592834 + 66176] wscale 3 [615902410 + 524280] wscale 3
age 10:47:05, expires in 23:15:07, 199433:386306 pkts,
10820556:561637949 bytes, rule 172
id: 4b8ecbd8001ddfbe creatorid: cd2329ff
---------
Netstat on client: tcp4 0 0 z.63169 db.postg ESTABLISHED
Netstat on Server: tcp4 0 0 db.postgresql z.63169 ESTABLISHED

Any help appreciated,
Axel
---
axel(dot)rau(at)chaos1(dot)de PGP-Key:29E99DD6 +49 151 2300 9283 computing @
chaos claudius

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2010-03-13 16:33:25 Re: remote pg_dump hangs always at same table
Previous Message Koichi Suzuki 2010-03-13 00:53:45 Re: Off-site storage for PITR logs