Thread: pg_dump fails..does not like "text" data..

pg_dump fails..does not like "text" data..

From
"Peter Kelly"
Date:
I am trying to do a pg_dump of a DB that has one table with approx.
732,390 rows in it.

Notes:

- I have tried pg_dump -d and pg_dump -D -- both with errors.
- I am running PostgreSQL 7.0.3 on Linux RedHat 7.0 (with all updates)
postgresql-devel-7.0.3-8
postgresql-server-7.0.3-8
postgresql-perl-7.0.3-8
postgresql-python-7.0.3-8
postgresql-jdbc-7.0.3-8
postgresql-odbc-7.0.3-8
postgresql-7.0.3-8
- 'vacuumdb --analyze vrelay' works fine.
- The field I am thinking it is bombing on is a "TEXT" field with email
header information (at least that is what it pukes below consistantly
and it does not seem to be one row it does not like -- it seems random)
- The physical server has 196MB ram and the postgresql data is on a
hardware raid 5 array.

Error:

The error is (I also included the table schema below):

[root@krusty base]# pg_dump -D vrelay > /tmp/vrelay.dump
Backend sent B message without prior T
85256C0C.001BCBE4-85256C0C.001BCBE4@oyp.com>
Date: Mon, 5 Aug 2002 01:03:36 -0400
X-MIMETrack: Serialize by Router on TORSMTP1/OYP(Release 5.0.10 |March
22, 2002) at 08/05/2002
 01:03:32 AM
MIME-Version: 1.0
Content-type: text/plain; charset=us-ascii
Backend sent D message without prior T
unexpected character M following 'I'
dumpClasses(): command failed.  Explanation from backend: '-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2651.58)
Content-Type: multipart/mixed;
        boundary="----_=_NextPart_000_01C23C97.DCBD58C0"
Unknown protocol character 'Unknown protocol character '0' read from
backend.  (The protocol character is the first character the backend
sends in response to a query it receives).
Unknown protocol character '
' read from backend.  (The protocol character is the first character the
backend sends in response to a query it receives).
ANUnknown protocol character 'Unknown protocol character 'Unknown
protocol character 'e' read from backend.  (The protocol character is
the first character the backend sends in response to a query it
receives).
Unknown protocol character 'h' read from backend.  (The protocol
character is the first character the backend sends in response to a
query it receives).
Unknown protocol character ' ' read from backend.  (The protocol
character is the first character the backend sends in response to a
query it receives).
Unknown protocol character 'm' read from backend.  (The protocol
character is the first character the backend sends in response to a
query it receives).

CREATE TABLE "smtp_log" (
        "rid" int4 DEFAULT nextval('smtp_log_rid_seq'::text) NOT NULL,
        "node_date" timestamp NOT NULL,
        "node_fqdn" character varying(100) NOT NULL,
        "status" character varying(10) NOT NULL,
        "fromaddr" text NOT NULL,
        "toaddr" text NOT NULL,
        "headers" text,
        "msg_id" character varying(100),
        "size" int4,
        "smtp_info" text,
        "virus_info" text,
        "virusid" character varying(100),
        "createdon" timestamp DEFAULT "timestamp"('now'::text) NOT NULL
);

Any help would be appreciated.

--

Peter Kelly
ETS.NET INC.
10-17705 Leslie Street
Newmarket, ON
L3Y 3E3
CANADA

Phone:    1-905-713-9978 ext. 405
toll-free:    1-866-713-9978 ext. 405
Fax:        1-905-726-8118

Visit: http://www.ets.net
Enterprise Email Virus Protection: http://vRelay.NET


Re: pg_dump fails..does not like "text" data..

From
Tom Lane
Date:
"Peter Kelly" <pkelly@ETS.NET> writes:
> - I have tried pg_dump -d and pg_dump -D -- both with errors.
> - I am running PostgreSQL 7.0.3 on Linux RedHat 7.0 (with all updates)

Leave out both -d and -D.

If you really want to use those formats, you'll need to get a more
recent pg_dump --- I think 7.2 is the first version that doesn't try to
buffer the whole table on the client side when -d or -D is used.
Basically your problem is that pg_dump is running out of memory.
(It might be that increasing pg_dump's ulimit or available swap space
would make it possible to complete the dump, but on the whole I think
you're best off with a COPY-style dump anyway for that much data.)

            regards, tom lane

Re: pg_dump fails..does not like "text" data..

From
Chris Gamache
Date:
root@database:~# ps -auxww | grep SELECT
postgres 10906 99.9  0.4  7596 4008 ?        R    Jul31 18445:54 postgres:
postg
res database 192.168.2.1 SELECT

Any clues on how to stop this runaway select without having to force the whole
database down? This was started from an ODBC connection. Obviously the
connection timed out...  :)

Can I kill -TERM 10906 ?
Can I kill -KILL 10906 ?

CG

__________________________________________________
Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com

Runaway SELECT

From
Chris Gamache
Date:
<embarassed>Fixed the subject line...</embarassed>

root@database:~# ps -auxww | grep SELECT
postgres 10906 99.9  0.4  7596 4008 ?        R    Jul31 18445:54 postgres:
postg
res database 192.168.2.1 SELECT

Any clues on how to stop this runaway select without having to force the
whole
database down? This was started from an ODBC connection. Obviously the
connection timed out...  :)

Can I kill -TERM 10906 ?
Can I kill -KILL 10906 ?

CG


__________________________________________________
Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com

Re: pg_dump fails..does not like "text" data..

From
Tom Lane
Date:
Chris Gamache <cgg007@yahoo.com> writes:
> Any clues on how to stop this runaway select without having to force the whole
> database down? This was started from an ODBC connection. Obviously the
> connection timed out...  :)

> Can I kill -TERM 10906 ?
> Can I kill -KILL 10906 ?

kill -INT is a good idea, the others are bad ideas (and will in fact
lead to a database restart).

            regards, tom lane