Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-admin by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group