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